{"action":"created","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/6733","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/6733/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/6733/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/6733/events","html_url":"https://github.com/cakephp/cakephp/issues/6733","id":85467706,"number":6733,"title":"Must use parenthesis to params of SQL function in FunctionExpression","user":{"login":"StewEucen","id":12193670,"avatar_url":"https://avatars.githubusercontent.com/u/12193670?v=3","gravatar_id":"","url":"https://api.github.com/users/StewEucen","html_url":"https://github.com/StewEucen","followers_url":"https://api.github.com/users/StewEucen/followers","following_url":"https://api.github.com/users/StewEucen/following{/other_user}","gists_url":"https://api.github.com/users/StewEucen/gists{/gist_id}","starred_url":"https://api.github.com/users/StewEucen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/StewEucen/subscriptions","organizations_url":"https://api.github.com/users/StewEucen/orgs","repos_url":"https://api.github.com/users/StewEucen/repos","events_url":"https://api.github.com/users/StewEucen/events{/privacy}","received_events_url":"https://api.github.com/users/StewEucen/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/cakephp/cakephp/labels/Defect","name":"Defect","color":"f7c6c7"}],"state":"closed","locked":false,"assignee":null,"milestone":{"url":"https://api.github.com/repos/cakephp/cakephp/milestones/43","html_url":"https://github.com/cakephp/cakephp/milestones/3.0.7","labels_url":"https://api.github.com/repos/cakephp/cakephp/milestones/43/labels","id":1138294,"number":43,"title":"3.0.7","description":"Bugfixes and optimizations for 3.0.x","creator":{"login":"markstory","id":24086,"avatar_url":"https://avatars.githubusercontent.com/u/24086?v=3","gravatar_id":"","url":"https://api.github.com/users/markstory","html_url":"https://github.com/markstory","followers_url":"https://api.github.com/users/markstory/followers","following_url":"https://api.github.com/users/markstory/following{/other_user}","gists_url":"https://api.github.com/users/markstory/gists{/gist_id}","starred_url":"https://api.github.com/users/markstory/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/markstory/subscriptions","organizations_url":"https://api.github.com/users/markstory/orgs","repos_url":"https://api.github.com/users/markstory/repos","events_url":"https://api.github.com/users/markstory/events{/privacy}","received_events_url":"https://api.github.com/users/markstory/received_events","type":"User","site_admin":false},"open_issues":15,"closed_issues":44,"state":"open","created_at":"2015-05-28T15:24:54Z","updated_at":"2015-06-05T11:56:34Z","due_on":"2015-06-13T04:00:00Z","closed_at":null},"comments":3,"created_at":"2015-06-05T08:06:36Z","updated_at":"2015-06-05T11:56:34Z","closed_at":"2015-06-05T11:56:34Z","body":"I found a probrem in FunctionExpression.\r\n\r\n# Steps to reproduce\r\n* Overview<br>\r\nUse subquery in SQL function.\r\n* MySQL table\r\n```\r\nCREATE TABLE `samples` (\r\n  `id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `level` int(11),\r\n  PRIMARY KEY (`id`),\r\n) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8\r\n;\r\n```\r\n* Code\r\n```PHP\r\n$theTable = $this->Samples;\r\n$theQuery = $theTable->query();\r\n\r\n$subquery = $theQuery\r\n    ->select('level')\r\n    ->where($theQuery->newExpr()->eq('id', 28, 'integer'))\r\n;\r\n\r\n$condition = $theQuery->newExpr()->gt(\r\n    'level',\r\n    $theQuery->func()->coalesce(\r\n        [$subquery, 10],\r\n        ['literal', 'integer']\r\n    ),\r\n    'literal'\r\n);\r\n\r\n$res = $theTable->find()\r\n    ->select('id')\r\n    ->where($condition)\r\n    ->all()\r\n;\r\n```\r\n# Error\r\nGot error as follow.\r\n```\r\nError: SQLSTATE[42000]: Syntax error or access violation: 1064\r\nYou have an error in your SQL syntax;\r\ncheck the manual that corresponds to your MySQL server version for the right syntax to use near\r\n'SELECT Samples.level AS `Samples__level` FROM samples Samples WHERE id' at line 1\r\n```\r\nSQL query\r\n```\r\nSELECT\r\n    Samples.id AS `Samples__id`\r\nFROM\r\n    samples Samples\r\nWHERE\r\n    level > (COALESCE(\r\n        SELECT Samples.level AS `Samples__level` FROM samples Samples WHERE id = :c0,\r\n        :c1\r\n    ))\r\n```\r\n\r\n# Cause\r\nWhen write subquery in SQL functions (ex: COALESCE()), must use parenthesis to the subquery expression.\r\n```\r\nerror  : COALESCE(subquery, 9)\r\ncorrect: COALESCE((subquery), 9)\r\n```\r\n\r\n# Fix\r\nUse parenthesis to inner expression in FunctionExpression.\r\nfile: Cake\\Database\\Expression\\FunctionExpression.php\r\nline: 137\r\n\r\naround the code\r\n```PHP\r\npublic function sql(ValueBinder $generator)\r\n{\r\n    $parts = [];\r\n    foreach ($this->_conditions as $condition) {\r\n        if ($condition instanceof ExpressionInterface) {\r\n++          $condition = sprintf('(%s)', $condition->sql($generator));\r\n--          $condition = $condition->sql($generator);\r\n        } elseif (is_array($condition)) {\r\n            $p = $generator->placeholder('param');\r\n            $generator->bind($p, $condition['value'], $condition['type']);\r\n            $condition = $p;\r\n        }\r\n        $parts[] = $condition;\r\n    }\r\n    return $this->_name . sprintf('(%s)', implode(\r\n        $this->_conjunction . ' ',\r\n        $parts\r\n    ));\r\n}\r\n```\r\n\r\n# Result\r\nGot SQL as follow\r\n```\r\nSELECT\r\n    Samples.id AS `Samples__id`\r\nFROM\r\n    samples Samples\r\nWHERE\r\n    level > (COALESCE(\r\n        (SELECT Samples.level AS `Samples__level` FROM samples Samples WHERE id = 28),\r\n        '10'\r\n    ))\r\n```\r\n\r\n# Side effect\r\nMaybe none, because to use parenthesis in parameters of SQL functions only."},"comment":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/comments/109268199","html_url":"https://github.com/cakephp/cakephp/issues/6733#issuecomment-109268199","issue_url":"https://api.github.com/repos/cakephp/cakephp/issues/6733","id":109268199,"user":{"login":"jadb","id":33527,"avatar_url":"https://avatars.githubusercontent.com/u/33527?v=3","gravatar_id":"","url":"https://api.github.com/users/jadb","html_url":"https://github.com/jadb","followers_url":"https://api.github.com/users/jadb/followers","following_url":"https://api.github.com/users/jadb/following{/other_user}","gists_url":"https://api.github.com/users/jadb/gists{/gist_id}","starred_url":"https://api.github.com/users/jadb/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jadb/subscriptions","organizations_url":"https://api.github.com/users/jadb/orgs","repos_url":"https://api.github.com/users/jadb/repos","events_url":"https://api.github.com/users/jadb/events{/privacy}","received_events_url":"https://api.github.com/users/jadb/received_events","type":"User","site_admin":false},"created_at":"2015-06-05T11:56:34Z","updated_at":"2015-06-05T11:56:34Z","body":"Closing as there is an open PR #6734 now.\r\n\r\n:clap: for the complete report."}}