Incorrect translate array parameter in where statement "IN"
podolinek opened this issue · 4 comments
Nette database 2.4.9 translates query code
$database->query("SELECT * FROM table WHERE ? <= date_to AND ? >= date_from AND type_id IN (?)",
"2020-04-01 00:00:00", "2020-04-02 00:00:00", [1,2]);
into query
SELECT * FROM table WHERE
'2020-04-01 00:00:00' <= date_to AND '2020-04-02 00:00:00' >= date_from AND
type_id IN ((1) AND (2))
Query with placed IN at beginning is ok.
$database->query("SELECT * FROM table WHERE type_id IN (?) AND ? <= date_to AND ? >= date_from",
[1,2], "2020-04-01 00:00:00", "2020-04-02 00:00:00");
Since the fix can cause a BC break, I will patch version 3.1.
Hello, I have a similar issue.
I have an region
table with foreign key to a district
table. Both tables have code
column as primary key.
Now, when I iterate over regions and in every iteration I do the ref()
call to get its district, Nette automatically optimizes this repeating query to call a database only once and get all the districts.
Resulting query in intermediate form looks like this: SELECT * FROM `district` WHERE (`code` IN (?))
However, after preprocessing the query changes to this: SELECT * FROM `district` WHERE (`code` IN ((?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)))
. Please notice the extra parentheses. This causes MySQL to compare int to a row and throws an error General error: 4078 Illegal parameter data types int unsigned and row for operation '='
.
@dg Do you have any idea what causes the issue? I have been using nette/database for many years now and I haven't had any problems so far. Now I got stuck in this simple usecase and I am not able to solve it.
I am using latest version 3.1.5.
Could you create a working minimal example? Just the database dump and the code that causes the error.