New unexprected behaviour in 2.0 version

[sartor]

Description

Same expression with same names param are now renamed and this breaks all queries with select and groups by same expression. In prepared statement params names are different and PostgreSQL serer refuses to execute such query.

How reproduce:

    public function testQuery(): void
    {
        $expr = new Expression("(site_id = :site)", ['site' => 1]);
        $sql = new Query($this->db())
            ->select($expr)
            ->from('users')
            ->groupBy($expr)
            ->createCommand()
            ->getSql();


        $this->assertSame('SELECT (site_id = :site) FROM "users" GROUP BY (site_id = :site)', $sql);
    }

Failed asserting that two strings are identical.
--- Expected
+++ Actual
@@ @@
-'SELECT (site_id = :site) FROM "users" GROUP BY (site_id = :site)'
+'SELECT (site_id = :site) FROM "users" GROUP BY (site_id = :site_0)'

Package version

2.0

PHP version

8.5

[vjik]

Param site_0 also will be added, so result SQL will be OK.

$expr = new Expression("(site_id = :site)", ['site' => 1]);
$command = new Query($db)
    ->select($expr)
    ->from('users')
    ->groupBy($expr)
    ->createCommand();

// SELECT (site_id = :site) FROM "users" GROUP BY (site_id = :site_0)
$command->getSql();

// ['site' => 1, 'site_0' => 1]
$command->getParams();

[sartor]

No, it is not ok. Values will be same, but in prepared statement there will be different placeholders, this mean SQL is invalid, because different expressions in Select and in Group used

[sartor]

Real PDO prepared statement will look like:

SELECT (site_id = $1) FROM "users" GROUP BY (site_id = $2)

SQL server don't know values of $1 and $2 and treat them like different all time

[Tigrov]

https://www.php.net/manual/en/pdo.prepare.php

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

Could you share the error message which you get for the query?

[sartor]

1) app\tests\unit\helpers\DbTest::testQuery
Yiisoft\Db\Exception\Exception: SQLSTATE[42803]: Grouping error: 7 ERROR:  column "users.site_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT (site_id = $1) FROM "users" GROUP BY (site_id = $2)
                ^
The SQL being executed was: SELECT (site_id = 1) FROM "users" GROUP BY (site_id = 1)

/var/www/analytics/vendor/yiisoft/db/src/Exception/ConvertException.php:40
/var/www/analytics/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php:208
/var/www/analytics/vendor/yiisoft/db/src/Command/AbstractCommand.php:609
/var/www/analytics/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php:302
/var/www/analytics/vendor/yiisoft/db/src/Command/AbstractCommand.php:410
/var/www/analytics/tests/unit/helpers/DbTest.php:314

Caused by
PDOException: SQLSTATE[42803]: Grouping error: 7 ERROR:  column "users.site_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT (site_id = $1) FROM "users" GROUP BY (site_id = $2)
                ^

/var/www/analytics/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php:224
/var/www/analytics/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php:204
/var/www/analytics/vendor/yiisoft/db/src/Command/AbstractCommand.php:609
/var/www/analytics/vendor/yiisoft/db/src/Driver/Pdo/AbstractPdoCommand.php:302
/var/www/analytics/vendor/yiisoft/db/src/Command/AbstractCommand.php:410
/var/www/analytics/tests/unit/helpers/DbTest.php:314

There were no error in DB lib before 2.0 version for such queries

[Tigrov]

As you see in the error message, the reason is that you need to use site_id in the GROUP BY clause
Try this

$expr = new Expression("(site_id = :site)", ['site' => 1]);
$command = new Query($db)
    ->select($expr)
    ->from('users')
    ->groupBy('site_id')
    ->createCommand();

SELECT (site_id = $1) FROM "users" GROUP BY site_id

[sartor]

This is simplified example. You solved only custom case, not problem with unexpected params renaming. In my production code expressions are much complicated and can't be replaced with simple column name.
Example:

    public function testQuery(): void
    {
        $expr = new Expression("(CASE WHEN site_id > :n THEN 1 WHEN site_id = 2 THEN 2 ELSE 3 END)", ['n' => 1]);
        $rows = new Query($this->db())
            ->select($expr)
            ->from('users')
            ->groupBy($expr)
            ->where(['site_id' => 100])
            ->all();


        $this->assertIsArray($rows);
    }

Result:

Yiisoft\Db\Exception\Exception: SQLSTATE[42803]: Grouping error: 7 ERROR:  column "users.site_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT (CASE WHEN site_id > $1 THEN 1 WHEN site_id = 2 THEN ...
                          ^
The SQL being executed was: SELECT (CASE WHEN site_id > 1 THEN 1 WHEN site_id = 2 THEN 2 ELSE 3 END) FROM "users" WHERE "site_id" = 100 GROUP BY (CASE WHEN site_id > 1 THEN 1 WHEN site_id = 2 THEN 2 ELSE 3 END)

Error message in this case the deceives the user. Because printed SQL is completely valid

[Tigrov]

Same here. Look at the error message and use site_id in the GROUP BY clause.

->groupBy('site_id')

[sartor]

Ok. You don't understand problem. May be someone else can help?

[Tigrov]

You can try to make a pull request if you see a way to solve the problem.

[vjik]

$expr = new Expression("(CASE WHEN site_id > :n THEN 1 WHEN site_id = 2 THEN 2 ELSE 3 END)", ['n' => 1]);
$rows = new Query($this->db())
    ->select($expr)
    ->from('users')
    ->groupBy($expr)
    ->where(['site_id' => 100])
    ->all();

Currently result statement contains different params in "select" expression and in "group by" expression. In this case error PDOException: SQLSTATE[42803]: Grouping error: throws.

With the same params query works in both cases: with and without emulation mode.

Working with disabled emulation mode is using undocumented PDO features. PDO deny do it:

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

But working in emulate mode is valid case for Yii DB.

[samdark]

@vjik mentioned a valid reason for it not to work as you've described. PDO doesn't allow same-named parameters to be reused if prepared statements emulation mode is not in effect. Technically we can remove that limitation, but that would mean creating another layer of query modifications. In some way we're doing that already with adding number postfixes.

@sartor Is there a real case when you need to duplicate an expression that's in SELECT into GROUP BY instead of naming it in SELECT and reusing it?

SELECT
  (CASE WHEN site_id > :n THEN 1 WHEN site_id = 2 THEN 2 ELSE 3 END) as my_stuff
FROM users
WHERE site_id = :site_id
GROUP BY my_stuff

I think the answer would be "no, but that's an example." I get that there could be a case where you want to reuse exactly the same expression with exactly the same parameter and its value and don't want to specify the parameter multiple times, right? If I understood correctly, a real example would be good, but see what @vjik described about PDO as well.

[samdark]

The fact that parameters belong to the expression itself is intuitively hinting that these will be the same values when reused:

/**
 * @param string $expression The DB expression.
 * @param array $params List of parameters to bind to this expression. The keys are placeholders appearing in
 * {@see expression} and the values are the corresponding parameter values.
 *
 * @psalm-param ParamsType $params
 */
public function __construct(
    public readonly string $expression,
    public readonly array $params = [],
) {}

[vjik]

PDO doesn't allow same-named parameters to be reused if prepared statements emulation mode is in effect, which is the default.

PDO doesn't allow same-named parameters if prepared statements emulation mode is disabled. When emulation enabled PDO replace params to values itself.