Incorrect handling nested params in Query Builder

[craynot]

When you dynamically build conditions using Expression with parameter with the same name, in the result query the parameter value will be overwritten by the last assigned value.

What steps will reproduce the problem?

        $query = (new Query())
               ->select('platform')
               ->from('build');
        $platforms = ['windows', 'android'];
        $conditions = [];
        foreach ($platforms as $platform) {
            $conditions[] = [
                'IS NOT',
                new Expression('JSON_SEARCH(platform, \'one\', :platform)', [
                    ':platform' => $platform . '%',
                ]),
                null
            ];
        }
        $conditions = ArrayHelper::merge(['OR'], $conditions);
        $query->where($conditions);
        var_dump($query->createCommand()->getRawSql());

What is the expected result?

SELECT `platform` FROM `build` WHERE (JSON_SEARCH(platform, 'one', 'windows%') IS NOT NULL) OR (JSON_SEARCH(platform, 'one', 'android%') IS NOT NULL)

What do you get instead?

SELECT `platform` FROM `build` WHERE (JSON_SEARCH(platform, 'one', 'android%') IS NOT NULL) OR (JSON_SEARCH(platform, 'one', 'android%') IS NOT NULL)

Additional info

Q	A
Yii version	2.0.48.1
PHP version	7.2.34
Operating system	Alpine

[bizley]

Does it work?

foreach ($platforms as &$platform) { // <- notice &
 ...
}

[craynot]

Does it work?

foreach ($platforms as &$platform) { // <- notice &
 ...
}

No, got the same result

[bizley]

Hm, I hoped it will work, in the background it's passed to PDO param binding and it requires reference, this might be the cause. I can only suggest not using params here since you are using known values or making the :platform unique in the loop by adding index or something.

[craynot]

Hm, I hoped it will work, in the background it's passed to PDO param binding and it requires reference, this might be the cause. I can only suggest not using params here since you are using known values or making the :platform unique in the loop by adding index or something.

Yes, i fixed this by adding index. It's just confusing behavior, that params passed to different objects is overwritten each other. Maybe it's good idea to rename params to random unique names, while building Expression, not just merging them.

[DeryabinSergey]

It`s normally, because query builded only once in the end, in createCommand

[samdark]

The behavior, indeed, isn't what I'd expect as a user.

[mary2501]

I think is a correct behavior, if you change your query in this way it works:

        $query = (new Query())
             ->select('platform')
            ->from('build');
        $platforms = ['windows', 'android'];
        $conditions = [];
        foreach ($platforms as $key => $platform) {
            $conditions[] = [
                'IS NOT',
                new Expression('JSON_SEARCH(platform, \'one\', :platform_'.$key.')', [
                    ':platform_' . $key  => $platform . '%',
                ]),
                null
            ];
        }
        $conditions = ArrayHelper::merge(['OR'], $conditions);
        $query->where($conditions);
        var_dump($query->createCommand()->getRawSql());