Yii2: YII db bug in postgres with array values

[miholeus]

What steps will reproduce the problem?

$query = (new \yii\db\Query())
            ->from('table')
            ->where(['=', 'field', [1]])
            ->count();

If values is an array, containing only 1 element, the query will not work (always returns 0), although
createCommand()->getRawSql() returns the correct query.
If I have 2 or more elements in values, then query works fine (it is executed as IN query).

What is the expected result?

According to documentation http://www.yiiframework.com/doc-2.0/yii-db-query.html#where()-detail
In case when a value is an array, an IN expression will be generated. But it doesn't happen.
Moreover I expect the results to be returned, but I always get 0 in all cases.

What do you get instead?

I get empty results, the IN statement is not generated, and getRawSql() shows wrong query.

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.12
| PHP version | 7.0.14
| Operating system | Debian 8
|Database | postgres 9.5

[samdark]

What are expected and actual queries?

[miholeus]

@samdark

1. the expected query according to documentation should contain where clause with IN, but getRawSql() returns equality.
2. The actual executed query always return 0 if I have 1 element in array. It happens
   due to bindValue in pdo.
   The query looks like this

select count(*) from table where field = :qp0

\yii\db\Command have _pendingParams with this values

array 
  ':qp0' => 
    array 
      0 => int 1

Then there is invocation of bindPendingParams

protected function bindPendingParams()
    {
        foreach ($this->_pendingParams as $name => $value) {
            $this->pdoStatement->bindValue($name, $value[0], $value[1]);
        }
        $this->_pendingParams = [];
    }

3rd Parameter in bindValue will be null. So I get 0 as result everytime.

[klimov-paul]

If you wish IN statement to be generated you should use in operator instead of =:

$query = (new \yii\db\Query())
            ->from('table')
            ->where(['in', 'field', [1]])
            ->count();

[cebe]

related to https://github.com/yiisoft/yii2/issues/7640

[miholeus]

@klimov-paul please, read the documentation once more. I provided link in the first message.

[klimov-paul]

Docs never mentions that array of single value passed to = will be converted into IN expression.

[miholeus]

@klimov-paul In case when a value is an array, an IN expression will be generated
It doesn't say that condition with array containing 1 element will not be converted to IN. It says when a value is an array. Arbitrary array, array with variable number of elements.

[miholeus]

@cebe I don't think it relates to #7640. I think you'll get the same problem in mysql.

[cebe]

@miholeus then I probably did not understand the problem, what type is the column in the database and which values does it contain?

[klimov-paul]

Converting single-value array to regular comparison has its purpose: it allows valid comparison for null values, which are usually unable to be processed via IN statement.
Compare:

SELECT * FROM Item WHERE some IS NULL

and

SELECT * FROM Item WHERE some IN (NULL)

Thus current behavior can not be changed. It is designed to facilitate regular scalar columns.
You should be aware while operating complex DB types such as array or jsonb.
Thier suport is not garanteed as #7640 displays.

[miholeus]

@cebe I have INT column

[miholeus]

@klimov-paul I'm not talking about array or jsonb. I'm talking about error in component that is based on PDO.
3rd parameter here

$this->pdoStatement->bindValue($name, $value[0], $value[1]);

is null. It is a bug.
That's why I always get 0.
I don't understand why you closed this issue. It's an obvious bug...

Once more, the query that is generated is OK. There is = in the query. The problem is with binding params as I showed above.

[klimov-paul]

Unable to reproduce: following unit test passes correctly:

public function testCompareArray()
    {
        $connection = $this->getConnection();
        $query = new Query();
        $query->from('item')
            ->where(['id' => [2]]);
        $result = $query->all($connection);
        $this->assertCount(1, $result);
        $this->assertEquals(2, $result[0]['id']);
    }

Can you provide a nit test, which reproduces your issue?

[klimov-paul]

Reproduced:

public function testCompareArray()
    {
        $connection = $this->getConnection();
        $query = new Query();
        $query->from('item')
            ->where(['=', 'id', [2]]);
        $result = $query->all($connection);
        $this->assertCount(1, $result);
        $this->assertEquals(2, $result[0]['id']);
    }

Triggers notice: ' Undefined offset: 1'

[klimov-paul]

Still, this is not a bug.
According to the docs you are refer value is an array, an IN expression will be generated only for the hash condition format, e.g. the one I mentioned at https://github.com/yiisoft/yii2/issues/14385#issuecomment-313096224

For the explicit comparison array can not contain single value, but should hold the value with bind type.

[miholeus]

@klimov-paul I see. At least it looks strange, that this code

where(['id' => [2]]);

works, but the equivalent with condition does not. Don't you agree?
Maybe it will be better to update docs to clarify that situation?