Yii2: using NOT IN as operator with an array as value

[buttflattery]

What steps will reproduce the problem?

Create a query like below

        $query = new Query();
        return $query->select('*')
            ->from( 'release rls')
            ->where(
                [
                    'AND',
                    ['=', 'rls.list_id', $listId],
                    ['not in', 'rls.subscriber_id', $campaignSubscribers],
                ]
            )
            ->createCommand()->rawSql;

What is the expected result?

In the above query the $campaignSubscribers is an array, if that array has some values then all is good but if the array is empty then the query is generated like below .

SELECT * FROM `release` `rls` WHERE (`rls`.`list_id` = 1) AND (`rls`.`subscriber_id` <>null)

which never yields the correct results and returns empty array although there are rows that should be returned, it should generate the last part as (rls.subscriber_id IS NOT null) rather than (rls.subscriber_id <>null)

What do you get instead?

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.27
| PHP version | 7.2
| Operating system | ubuntu

[samdark]

That's certainly incorrect result. Do you have a way to fix it? Want to work on a pull request?

[hstrychalski]

Could not reproduce this. For given case (NOT IN operator) InConditionBuilder build method returns empty string when values property of passed expression is an empty array.

 $sqlValues = $this->buildValues($expression, $values, $params);
    if (empty($sqlValues)) {
        return $operator === 'IN' ? '0=1' : '';
    }

[samdark]

@buttflattery is there anything special that is required to reproduce it?

[buttflattery]

@samdark i just looked up into my old source code where i had to change the code base and it looks like the reason is not just an empty array but, if the array is like

$campaignSubscribers=[null];

OR

$campaignSubscribers=[0=>''];

rather than

$campaignSubscribers=[];

i reverted the code and var dumped the array to re-check and if the array has null then it produces the query as mentioned in the issue above. Not sure now if it should be considered as an issue or not .

[hstrychalski]

imo if there's null value in array it should generate IS NOT NULL, now it allows to pass null in array and produces unexpected result

[buttflattery]

@strychu i agree, and to my amusement i just test the scenario with

$campaignSubscribers=[null,null];

and it generates the condition as

(`rls`.`subscriber_id` NOT IN (NULL, NULL))

[buttflattery]

@strychu It looks like if the array has only one element it always creates the query with the <> operator otherwise if it is more than one element it will treat it as an array no matter if all the values are null or not if you provide an array with lets say one element that is an id like $campaign=[1] it will still use the <> operator.

[samdark]

@buttflattery yes, you're correct.