{"action":"opened","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/8796","repository_url":"https://api.github.com/repos/cakephp/cakephp","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/8796/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/8796/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/8796/events","html_url":"https://github.com/cakephp/cakephp/issues/8796","id":153771707,"number":8796,"title":"ValueBinder error in a complex query","user":{"login":"greew","id":189321,"avatar_url":"https://avatars.githubusercontent.com/u/189321?v=3","gravatar_id":"","url":"https://api.github.com/users/greew","html_url":"https://github.com/greew","followers_url":"https://api.github.com/users/greew/followers","following_url":"https://api.github.com/users/greew/following{/other_user}","gists_url":"https://api.github.com/users/greew/gists{/gist_id}","starred_url":"https://api.github.com/users/greew/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/greew/subscriptions","organizations_url":"https://api.github.com/users/greew/orgs","repos_url":"https://api.github.com/users/greew/repos","events_url":"https://api.github.com/users/greew/events{/privacy}","received_events_url":"https://api.github.com/users/greew/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2016-05-09T13:05:00Z","updated_at":"2016-05-09T13:05:00Z","closed_at":null,"body":"This is a (multiple allowed):\r\n* [x] bug\r\n* [ ] enhancement\r\n* [ ] feature-discussion (RFC)\r\n\r\n* CakePHP Version: 3.2.8\r\n* Platform and Target: OS X, PHP 7.0.6, MySQL 5.7.11\r\n\r\n### What you did\r\nA huge query of mine resulted in an invalid MySQL query, and I've now been able to reproduce it in a smaller environment, based on the cakephp/app structure, see greew/cakephp-orm-error@4a82c8ec29577171c14dd4c256c06294f2fdf147\r\n\r\n    ATable hasMany BTable\r\n    BTable hasMany CTable (using subquery strategy!!)\r\n\r\n`BTable` has a `beforeFind` method (in my huge query, it's a behaviour, but to minify the test app, I've added it directly in the model), which adds a subquery to the `select` part of the query. \r\nThis subquery works fine, but it adds some data to the ValueBinder of the query before the ValueBinder-stuff from the master query is added. \r\n\r\nWhen adding\r\n\r\n    var_dump($statement, !$isObject ?: $query->valueBinder()->bindings());\r\n\r\nbefore https://github.com/cakephp/cakephp/blob/6a463bbec5f3cf71de2a9f1a42b1ab71ba0f8fa0/src/Database/Driver/Mysql.php#L122\r\n\r\nI get the following output:\r\n\r\n```\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\n\r\nobject(PDOStatement)[120]\r\n  public 'queryString' => string 'SELECT A.id AS `A__id`, A.name AS `A__name` FROM a A WHERE name = :c0' (length=69)\r\n\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\narray (size=1)\r\n  ':c0' => \r\n    array (size=3)\r\n      'value' => string 'first' (length=5)\r\n      'type' => string 'string' (length=6)\r\n      'placeholder' => string 'c0' (length=2)\r\n\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\nobject(PDOStatement)[166]\r\n  public 'queryString' => string 'SELECT (SELECT (CONCAT((user_id), :c0, (created))) FROM audit_logs AuditLogs WHERE (foreign_key = (B.id) AND model = :c1) LIMIT 1) AS `created` FROM b B WHERE B.a_id in (:c2)' (length=174)\r\n\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\narray (size=3)\r\n  ':c0' => \r\n    array (size=3)\r\n      'value' => string ',' (length=1)\r\n      'type' => null\r\n      'placeholder' => string 'c0' (length=2)\r\n  ':c1' => \r\n    array (size=3)\r\n      'value' => string 'B' (length=1)\r\n      'type' => string 'string' (length=6)\r\n      'placeholder' => string 'c1' (length=2)\r\n  ':c2' => \r\n    array (size=3)\r\n      'value' => int 1\r\n      'type' => string 'integer' (length=7)\r\n      'placeholder' => string 'c2' (length=2)\r\n\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\nobject(PDOStatement)[180]\r\n  public 'queryString' => string 'SELECT C.id AS `C__id`, C.b_id AS `C__b_id`, C.name AS `C__name` FROM c C INNER JOIN (SELECT (B.id) FROM b B WHERE B.a_id in (:c0) GROUP BY B.id ) B ON C.b_id = (B.id)' (length=167)\r\n\r\n/Users/jesper/code/test/vendor/cakephp/cakephp/src/Database/Driver/Mysql.php:122:\r\narray (size=1)\r\n  ':c0' => \r\n    array (size=3)\r\n      'value' => string ',' (length=1)\r\n      'type' => null\r\n      'placeholder' => string 'c0' (length=2)\r\n```\r\n\r\nThe above shows, that the last query, which is loaded with subquery strategy, is using the first (:c0) ValueBinder placeholder instead of the third (:c2) which contains the correct data.\r\n\r\nIt ends out in the following queries, the first two being correct, the last one asking for `B.a_id in (',')` instead of `B.a_id in (1)`:\r\n\r\n```sql\r\nSELECT A.id AS `A__id`, A.name AS `A__name` FROM a A WHERE name = 'first'\r\n\r\nSELECT (SELECT (CONCAT((user_id), ',', (created))) FROM audit_logs AuditLogs WHERE (foreign_key = (B.id) AND model = 'B') LIMIT 1) AS `created` FROM b B WHERE B.a_id in (1)\r\n\r\nSELECT C.id AS `C__id`, C.b_id AS `C__b_id`, C.name AS `C__name` FROM c C INNER JOIN (SELECT (B.id) FROM b B WHERE B.a_id in (',') GROUP BY B.id ) B ON C.b_id = (B.id)\r\n```"}}