nette / database

💾 A database layer with a familiar PDO-like API but much more powerful. Building queries, advanced joins, drivers for MySQL, PostgreSQL, SQLite, MS SQL Server and Oracle.
https://doc.nette.org/database
Other
502 stars 107 forks source link

whereOr adds IS NULL AND FALSE to the end of query when parameter is not associative array #294

Open matXias opened 1 year ago

matXias commented 1 year ago

Version: 3.1.4

Sending array parameter without keys to whereOr function returns imploded query with IS NULL AND FALSE

Example:

$table->whereOr(['DATE(...) < DATE(NOW())', 'DATE(...) >= DATE(NOW())])

variable $values is default [] in whereOr function and after final imploding send this query to where function like:

->where('DATE(...) < DATE(NOW()) OR DATE(...) >= DATE(NOW())', []);

and this is problem, because where with second parameter as empty array add IS NULL...

        $values = [];
                ...

            if (is_int($key)) { // whereOr(['full condition'])
                $columns[] = $val;
                 ...
        $columnsString = '(' . implode(') OR (', $columns) . ')';
        return $this->where($columnsString, $values);

DOCUMENTATION:

doc $table->where('id', []); // id IS NULL AND FALSE

expected behavior is

doc

// WHERE (user_id IS NULL) OR (SUM(`field1`) > SUM(`field2`))
$table->whereOr([
    'user_id IS NULL',
    'SUM(field1) > SUM(field2)',
]);
KminekMatej commented 5 months ago

Problem sitll persists in nette/database 3.2.1

$table->whereOr([
    'user_id IS NULL',
    'SUM(field1) > SUM(field2)',
]);

generates query:

SELECT * FROM `table` WHERE ((`user_id` IS NULL) OR (SUM(`field1`) > SUM(`field2`)) IS NULL AND FALSE)