Unexpected result with yii\db\Query where

Hi, i’m having an unexpected result with a query in the search function of a searchmodel.

This code




$query->joinWith('fields_a fa');

   $query->andFilterWhere(

       ['or',

           ['not',['fa.type' => $this->notConnectedTo]],

           'fa.type is NULL',

       ]);



Executes this query (correct)




SELECT `anag`.* FROM `anag` LEFT JOIN `fields_a` `fa` ON `anag`.`id` = `fa`.`field` 

WHERE (`archived`=0) AND ((NOT (`fa`.`type`=1)) OR (fa.type is NULL)) LIMIT 20



But this code, which i think is correct, and should be used since in the future i could switch database type:




$query->joinWith('fields_a fa');

   $query->andFilterWhere(

       ['or',

           ['not',['fa.type' => $this->notConnectedTo]],

           ['fa.type'=>NULL],

       ]);



Executes this query:




SELECT `anag`.* FROM `anag` LEFT JOIN `fields_a` `fa` ON `anag`.`id` = `fa`.`field` 

WHERE (`archived`=0) AND ((NOT (`fa`.`type`=1))) LIMIT 20



Why the => NULL where part is ignored?

In the manual says


['status' => null] generates status IS NULL.

And in other situations it seems to work.

http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail

This seems to be a behavior of "hash" format condition used in "Filter" mode.

These are in hash format in your code:




...

['fa.type' => $this->notConnectedTo]

...

['fa.type' => NULL],

...



They will usually converted to SQLs like:

[sql]

fa.type = 1

fa.type IS NULL

[/sql]

But it works differently in "Filter" mode. It ignores those key-value pairs that have NULL as the value.

Try to check it by using "andWhere" instead of "andFilterWhere".

What happens if you quote the NULL?

Possibly somekind of new dbExpression(‘Is NULL’) is available.