Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 2.0-BETA1
-
Fix Version/s: 2.0-BETA2
-
Component/s: None
-
Security Level: All
-
Labels:None
Description
$dQuery = $this->_em->createQuery(
'SELECT p FROM entity\system\Group p WHERE (p.lft >= (SELECT t.lft FROM entity\system\Group t WHERE t.name = :name)) AND (p.rgt <= (SELECT t2.rgt FROM entity\system\Group t2 WHERE t2.name = :name))');
As you see this includes brackets around the subqueries.
var_dump($dQuery->getSQL());
SELECT s0_.level AS level0, s0_.lft AS lft1, s0_.rgt AS rgt2, s0_.id AS id3, s0_.name AS name4, s0_.description AS description5 FROM system_group s0_ WHERE (s0_.lft >= SELECT s1_.lft FROM system_group s1_ WHERE s1_.name = ?) AND (s0_.rgt <= SELECT s2_.rgt FROM system_group s2_ WHERE s2_.name = ?)
Brackets gone, resulting in:
'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT s1_.lft FROM system_group s1_ WHERE s1_.name = 'root') AND (s0_.rgt <= SE' at line 1' in /usr/share/php/lib/Doctrine/DBAL/Connection.php:566
Brackets added and executed against database
SELECT s0_.level AS level0, s0_.lft AS lft1, s0_.rgt AS rgt2, s0_.id AS id3, s0_.name AS name4, s0_.description AS description5 FROM system_group s0_ WHERE (s0_.lft >= (SELECT s1_.lft FROM system_group s1_ WHERE s1_.name = "root")) AND (s0_.rgt <= (SELECT s2_.rgt FROM system_group s2_ WHERE s2_.name = "root"))
Works (MySQL).
Double brackets in the DQL results in
"exception 'Doctrine\ORM\Query\QueryException' with message '[Syntax Error] line 0, col 62: Error: Expected Literal, got 'SELECT'' in /usr/share/php/lib/Doctrine/ORM/Query/QueryException.php:42