[BUG]:  When executing subqueries with the same conditions, the number of elements in the binding parameter array is different. The first subquery executes successfully, but the second subquery results in an error due to inconsistent binding parameter counts.

[tingleiwuyan]

Error Description

 When executing subqueries with the same conditions, the number of elements in the binding parameter array is different. The first subquery executes successfully, but the second subquery results in an error due to inconsistent binding parameter counts. 

Steps to Reproduce

sequential execution

1. Successfully Executed Query:

   $query = Tabel::query();
   $subQuery = (new Tabel)->getModelsManager()->createBuilder()
   ->from(Tabel::class)->columns('id')
   ->andWhere('id IN ({ids:array})')->getPhql();
   $query->andWhere("id IN ($subQuery)", ['ids' => [1, 2]]);
   $data = $query->limit(1)->execute()->toArray();

   

2. Unsuccessful Query:

   $query = Tabel::query();
   $subQuery = (new Tabel)->getModelsManager()->createBuilder()
   ->from(Tabel::class)->columns('id')
   ->andWhere('id IN ({id:array})')->getPhql();
   $query->andWhere("id IN ($subQuery)", ['id' => [1,2,3]]);
   $data = $query->limit(1)->execute()->toArray();

   

   Expected Behavior

    Both subqueries should execute successfully without errors related to the number of binding parameters. 

   Details

   

   • Phalcon Version: Phalcon5.6.1
   • PHP Version: PHP8.3.6 

     Additional Context

      "In Phalcon 5.6.1, no exception is thrown, but the SQL isn't executed. In Phalcon 3.4, there's an error: [2024-09-29 14:56:09] SYSTEM.ERROR: PDOException: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens."  For both queries, the SQL statements should be as follows: 

3. First Query:

   SELECT `table`.`id` AS `id` FROM `tabel` 
   WHERE (`table`.`id` IN (SELECT `table`.`id` AS `id` FROM `table` 
   WHERE `table`.`id` IN (:id0, :id1))) LIMIT :APL0

   

4. Second Query:

   SELECT `table`.`id` AS `id` FROM `tabel` 
   WHERE (`table`.`id` IN (SELECT `table`.`id` AS `id` FROM `table` 
   WHERE `table`.`id` IN (:id0, :id1, :id2))) LIMIT :APL0

[tingleiwuyan]

Steps to Reproduce

sequential execution

1. Successfully Executed Query:

   $query = Tabel::query();
   $subQuery = (new Tabel)->getModelsManager()->createBuilder()
   ->from(Tabel::class)->columns('id')
   ->andWhere('id IN ({ids:array})')->getPhql();
   $query->andWhere("id IN ($subQuery)", ['ids' => [1, 2]]);
   $data = $query->limit(1)->execute()->toArray();

   

2. Unsuccessful Query:

   $query = Tabel::query();
   $subQuery = (new Tabel)->getModelsManager()->createBuilder()
   ->from(Tabel::class)->columns('id')
   ->andWhere('id IN ({ids:array})')->getPhql();
   $query->andWhere("id IN ($subQuery)", ['ids' => [1,2,3]]);
   $data = $query->limit(1)->execute()->toArray();

[tingleiwuyan]

reference resources https://github.com/phalcon/cphalcon/issues/16573 ， I add 'options' => [ PDO::ATTR_EMULATE_PREPARES => true ] to my database configuration， The result remains unchanged

[raicabogdan]

I've tested this as well, it seems the query string somehow gets cached or something

SELECT `invoice`.`id` AS `id`, `invoice`.`total` AS `total` 
FROM `invoice` 
WHERE `invoice`.`id` 
IN (
     SELECT `invoice`.`id` AS `id` FROM `invoice` WHERE `invoice`.`id` IN (:ids0, :ids1) <-- here is the problem
) LIMIT :APL0

This here is the second query which has 3 query bound parameters. And technically if I dump the $boundParameters before the $pdo->execute() call I can indeed see the correct parameters. The problem however is that the $queryString which is being used to execute is taken from the previous query instead of regenerating it.

[raicabogdan]

Tinkering with it, I learn bit more.

@tingleiwuyan it seems this was made like that by design for performance reasons, and indeed only triggers whenever you do queries that have the same PHQL code. Meaning that whenever the PHQL remains the same, thus having the same uniqueId in the internalPhqlCache[uniqueId] it will reuse the same 'already' parsed SQL query string. So if the bound parameters changes, the parsed query string will indeed fail.

There is one simple solution when doing duplicate queries like this when the bound parameters changes, but the PHQL remains the same, and that's by clearing the internalPhqlCache before the next query, this will force regenerating all future PHQL queries, so use it wisely. Perhaps you would be better making a native query in such cases.

// first query
Phalcon\Mvc\Model\Query::clean();
// second query

[tingleiwuyan]

通过对它的修补，我学到了更多。

@tingleiwuyan看起来这是出于性能原因而设计的，并且确实只有在您执行具有相同 PHQL 代码的查询时才会触发。这意味着只要 PHQL 保持不变，因此在它里面有相同的 uniqueId，internalPhqlCache[uniqueId]它就会重用相同的“已”解析的 SQL 查询字符串。因此，如果绑定的参数发生变化，解析的查询字符串确实会失败。

当绑定参数发生变化但 PHQL 保持不变时，执行此类重复查询时有一个简单的解决方案，即在下internalPhqlCache一个查询之前清除，这将强制重新生成所有未来的 PHQL 查询，因此请明智地使用它。在这种情况下，也许您最好进行本机查询。

// first query
Phalcon\Mvc\Model\Query::clean();
// second query

I tried Phalcon\Mvc\Model\Query::clean(); and got the correct result. Thank you!

[raicabogdan]

You're welcome! Can probably close the issue since it is not a bug?