Incorrect translate array parameter in where statement "IN"

[podolinek]

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");

[dg]

Since the fix can cause a BC break, I will patch version 3.1.

[peldax]

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.

[dg]

Could you create a working minimal example? Just the database dump and the code that causes the error.

[peldax]

@dg While creating minimal example and comparing backtraces, I found it was indeed my error. In one middleware function I forgot to unpack arguments, which caused additional layer of array, which resulted in extra parentheses.Thank you for your assistance.