Skip to content

Loading…

DDC-593: Subquery parenthesis omitted in generated SQL #5099

Closed
doctrinebot opened this Issue · 7 comments

1 participant

@doctrinebot

Jira issue originally created by user jkleijn:

$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).

@doctrinebot

Comment created by jkleijn:

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

@doctrinebot

Comment created by romanb:

This might be caused by recent AST optimizations and thus I suspect this to be a regression.

@doctrinebot

Comment created by jkleijn:

Is there a workaround (other than not using a subquery)?

@doctrinebot

Comment created by romanb:

Well, a workaround for any DQL issues that is always available is a NativeQuery (createNativeQuery). In essence, a DQL query is just a high-level abstraction for a native SQL query + a ResultSetMapping. http://www.doctrine-project.org/projects/orm/2.0/docs/reference/native-sql/en#native-sql

The resulting objects from a native query are still fully managed and all, so its just a difference in query abstraction.

Nevertheless, this should be fixed soon.

@doctrinebot

Comment created by romanb:

Reproduced this successfully and already have a potential fix. Might not be a regression after all but a bug nevertheless.

@doctrinebot

Comment created by romanb:

Should be fixed now in HEAD (doctrine2/master).

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot doctrinebot added this to the 2.0-BETA2 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.