Where Expression object gets quoted

[mdavemartin]

What steps will reproduce the problem?

I have the following expression in my SearchModel along with few similar (generated) lines:

->andFilterWhere(['ilike', new Expression("first_name || ' ' || middle_name || ' ' || last_name"), $this->name])

What is the expected result?

The expression should be inserted in its raw form.

What do you get instead?

Database Exception – yii\db\Exception

SQLSTATE[42703]: Undefined column: 7 ERROR: column "first_name || ' ' || middle_name || ' ' || last_name" does not exist
LINE 1: SELECT COUNT(*) FROM "user" WHERE "first_name || ' ' || midd...
^
The SQL being executed was: SELECT COUNT(*) FROM "user" WHERE "first_name || ' ' || middle_name || ' ' || last_name" ILIKE '%martin%'

Additional info

The query which threw the error was called by the GridView widget.

After a little debugging I traced back the problem to generating the query in the QueryBuilder. The condition (where) is built by the buildCondition method.

Since this form is the 3 operand type, this funtion selects the appropriate method to process this condtion.

Finally, the buildLikeCondition gets called which quotes the column if it doesn't contain an opening parenthesis.

This line quotes the expression:

if (strpos($column, '(') === false) {
    $column = $this->db->quoteColumnName($column);
}

I replaced the referenced line with the following:

if (!($column instanceof Expression) && strpos($column, '(') === false) {
    $column = $this->db->quoteColumnName($column);
}

This resolved my issue. As I noticed other builder methods have this issue as well, for example the buildSimpleCondition

Q	A
Yii version	2.0.12
PHP version	7.1.5
PostgreSQL	9.6.2
Operating system	Ubuntu 16.04

[lourdas]

Hi, I've been affected too by this issue. I was wondering when this happened, because of a problematic commit? This issue is rather important for me (and I'm pretty sure for others too), so wouldn't maybe a fix release made?

[lourdas]

I don't understand where the issue is. I'm var_dumping variables in the framework classes and every statement is not quoted, but still fails in my case:

Invalid datetime format: 7 ERROR: invalid input syntax for type date: "TO_DATE('11/05/2017', 'DD/MM/YYYY')"
The SQL being executed was: UPDATE "ekmetalleush" SET "hmer_tropopoihshs"=CURRENT_DATE, "hmer_epitopiou_elegxou"='TO_DATE(''11/05/2017'', ''DD/MM/YYYY'')', "id_trexousa_katastash"=33 WHERE "id"=1

The above statement shows the quoted statement for the hmer_epitopiou_elegxou field.

[lourdas]

What I noticed is that the double quoting happens if you call twice the save() method in the model. At first time, the Expression works fine, but if for some reason you call a successive save() in the model, you get the error described in this issue. For example:

$model = new Actor();
$model->birthDate = new Expression('TO_DATE(:date, \'DD/MM/YYYY\')', [':date' => '15/05/1990']);
$model->save(); // this works
$model->save(); // exception raised

[samdark]

What database is that?

[lourdas]

PostgreSQL 9.x.

[jacksontong]

I think I have the same issue with mysql What steps will reproduce the problem?

Yii::$app->db->createCommand()
    ->update(ProposalPage::tableName(), [
        'layout' => new Expression("REPLACE(layout, '[[Yearly savings]]', '[[Yearly Bill Savings]]')]]")
    ], ['like', 'layout', '[[Yearly savings]]'])->rawSql

What is the expected result?

UPDATE proposal_pages 
    SET `layout`=REPLACE(layout, '[[Yearly savings]]', '[[Yearly Bill Savings]]') 
    WHERE `layout` LIKE '%[[Yearly savings]]%'

What do you get instead?

UPDATE `proposal_pages` 
    SET `layout`=REPLACE(layout, '`Yearly savings`', '`Yearly Bill Savings`') 
    WHERE `layout` LIKE '%`Yearly savings`%'

Q	A
Yii version	2.0.7
PHP version	7.0.2-1
MySQL version	5.0
Operating system	Debian jessie

[ToSchQLB]

Various builders operate differently. For example yii\db\conditions\LikeConditionBuilder vs yii\db\conditions\InConditionBuilder

I found this Example from an REST-API call the filter built this where-condition:

[
    'AND',
    ['LIKE', new Expression("data->>'eppo_code'"), 'AAB']
    ['LIKE', new Expression("data->>'type'"), ['GAF', 'PFL', 'GAI']]
]

What is the expected result?

SELECT * FROM "eintrag" 
    WHERE (data->>'eppo_code' LIKE '%AAB%') 
    AND (data->>'type' IN ('GAF', 'PFL', 'GAI'))

What do you get instead?

SELECT * FROM "eintrag" 
    WHERE (data->>'eppo_code' LIKE '%AAB%') 
    AND ("data->>'type'" IN ('GAF', 'PFL', 'GAI'))

As mentioned in the original comment, I have to bracket the expression for the IN condition. The LIKE condition also works without

[
    'AND',
    ['LIKE', new Expression("data->>'eppo_code'"), 'AAB']
    ['LIKE', new Expression("(data->>'type')"), ['GAF', 'PFL', 'GAI']]
]

Result

SELECT * FROM "eintrag" 
    WHERE (data->>'eppo_code' LIKE '%AAB%') 
    AND ((data->>'type') IN ('GAF', 'PFL', 'GAI'))

Q	A
Yii version	2.0.50
PHP version	8.3.10
PostgreSQL	14.13