Quoted DB expression when used in param

[klevron]

Hello

DB Expression is quoted when used in param, e.g.

MyModel::deleteAll("expire < :expire", [':expire' => new Expression('NOW()')]);

This will make the following query :

DELETE FROM `expire` WHERE expire < 'NOW()';

Be careful, debug tool will log the correct one :

DELETE FROM `expire` WHERE expire < NOW();

http://stackoverflow.com/questions/27312579/odd-behavior-of-now-and-db-expression-in-yii2-mysql

[samdark]

Debug tool should be adjusted.

[klevron]

Well, it is a bug, not a feature : DB expression should not be quoted

[samdark]

Yes, that is true as well.

[mdmunir]

Corect me, params is native fiture of PDO, not originaly Yii. So everyting that inserted to param should be known as PDO TYPE (PDO::PARAM_XXX). If not it will be treatment as PDO::PARAM_STR.
The code should be

MyModel::deleteAll("expire < NOW()");
// or
MyModel::deleteAll(['<','expire',new Expression('NOW()')]);

Sory for my english

[klevron]

We should not care about pdo/yii here, and the three should be logically the same :

MyModel::deleteAll("expire < NOW()");
MyModel::deleteAll(['<','expire',new Expression('NOW()')]);
MyModel::deleteAll("expire < :expire", [':expire' => new Expression('NOW()')]);

[qiangxue]

Where did you get this from? DELETE FROMtest_expireWHERE expire < 'NOW()';?

[klevron]

From mysql server log (since yii debug doesn't show the real query)

PS : test_expire was a typo of course

[mdmunir]

We should not care about pdo/yii here, and the three should be logically the same.

Ideally yes. But to handle this, yii must create his own parser. I am neutral here.

[qiangxue]

As explained by @mdmunir, this is expected behavior since you are using parameter binding, a native feature of PDO. The DB expression will be converted into a string before being bound to the parameter. We can't do anything about this.

The debug tool should be adjusted to reflect the true SQL being executed.

[klevron]

There should be at least an InvalidParamException

[qiangxue]

There should be at least an InvalidParamException

Nope. It is good as is. We just need to fix the debug tool display if the issue is verified.

[klevron]

I don't see the meaning of binding a db expression if it is quoted after, there should be an exception.

[bionoren]

I suspect this is because yii\db\mysql\QueryBuilder:183 is looking for Expression in the current namespace (\yii\db\mysql), but the class actually exists in \yii\db, so that instanceof check always fails. Can somebody fix that to either fully qualify Expression or use it?

[cebe]

@bionoren why do you write under a totally unrelated issue? please report a new one next time. This has been fixed by #8596 already.

[yii-bot]

Issue moved to yiisoft/yii2-debug#78