Query exception when empty array is used

[Lumeriol]

Describe the bug

If I use the WHERE condition in Mapper to search via IN and there is an empty array in the arguments, it generates a wrong SQL statement that causes an exception.

If I use the same condition in ORM, then everything is fine and the statement is generated correctly, although a bit differently than if I try to generate it via Adminer for example.

To Reproduce

public function causeDbalBug(): array
    {
        $builder = $this->connection->createQueryBuilder();

        $builder
            ->select('[id], [email]')
            ->from('%table', 't', $this->getTableName())
            ->where('[id] IN %i[]', []);

        $result = $this->connection->queryByQueryBuilder($builder);

        return $result->fetchPairs('id', 'email');
    }

Expected behavior Correct SQL command

-- Wrong SQL

SELECT `id`, `email` FROM `user` AS `t` WHERE `id` IN ();

-- Adminer generated SQL

SELECT `id`, `email` FROM `user` AS `t` WHERE `id` IN (NULL);

-- ORM generated SQL (from Tracy)

SELECT `user`.* FROM `user` AS `user` WHERE ((1=0));

Versions

• Database: MariaDB 10.6.15
• Dbal: 5.0-rc2, probably same problem in v4

Last words Thanks for the Nextras meetup in Brno in December :)

[hrach]

This is "work as indented". The modifiers do not "modify" your SQL clause at all, just their presence is replaced, i.e. the IN keyword is already there.

So either you can employ some ifs locally to create a valid SQL or you can utilize %and modifier to construct those conditions for you:

$builder = $this->connection->createQueryBuilder();
        $builder
            ->select('[id], [email]')
            ->from('%table', 't', $this->getTableName())
            ->where('%and', ['id%i[]' => []]);

I think this should work. Please let me know.

[Lumeriol]

Sorry for delay.

Okay, so for Dbal I have to use already treated data, since it is more strict than Orm.

I tried to work-around my query by checking the array variable and change it to [null] in case of an empty array and using the %?i[] modifier, since this is a stacked query for filtering products based on conditions.

Anyway, thanks for the explanation and the possibility of using "and", I'll save this construct.