Yii2: NULL values in IN() clause require special processing

[dryu]

What steps will reproduce the problem?

$values = [null];
$query->andWhere(["country_code" => $values]);

The above condition for Query will produce the following SQL:

 country_code=NULL

With Oracle, you cannot compare any values to NULL like that, instead, country_code IS NULL should be used. Yii2 will use "IS NULL" if the $values === null, but in case the value is supplied as an array, and one of those array elements is null, it will not get any special treatment, resulting in the query never matching any records with NULL value.

What is the expected result?

$query->andWhere(["country_code" => [null]]); // country_code IS NULL
$query->andWhere(["country_code" => ["CA", "US", null]]); // country_code IN ("CA", "US") OR country_code IS NULL

What do you get instead?

$query->andWhere(["country_code" => [null]]); // country_code=NULL
$query->andWhere(["country_code" => ["CA", "US", null]]); // country_code IN ("CA", "US", NULL)

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.10-Dev?
| PHP version |7
| Operating system |Windows

[dynasource]

this looks like a bug considering the info block at http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#filter-conditions stating:
Info: A value is considered empty if it is null, an empty array, an empty string or a string consisting of whitespaces only.

[SilverFire]

Current behavior does exactly what you ask to do.
The docs mentioned by @dynasource are related to filterWhere() method.

I do not think that we should change behavior and introduce implicit handling for null values in in condition.

[samdark]

As a user I'd expect ["id" => [1, 2, null]] and ['in', 'id', [1, 2, null]] to create (id IN (1, 2) OR id IS NULL). Technically that complicates query generation a bit though.

[SilverFire]

Let me explain my position step-by-step.

1. Current behavior is consistent with SQL. The following example will fail:

SELECT * FROM posts WHERE country_code IN ("US", "UK", null);

1. Current behavior is consistent with SQL subqueries. The following example will fail as well, when subquery returns null values:

SELECT * FROM posts WHERE country_code IN (SELECT code FROM codes); 
-- one or more rows of the subquery contain `NULL` in the `code` column

How should we behave in case of the following condition:

$query->where(['in', 'country_code', (new Query())->select('code')->from('codes')]); 
// one or more rows of the subquery contain `NULL` in the `code` column

1. Current behavior is EXPLICIT. It does exactly what it is asked to do.
2. I know this feature may be used to prevent query from rows returning, so this change will be BC-breaking.

[samdark]

Hmmm... right. That makes perfect sense. @dryu won't change it then.