Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-593

Subquery parenthesis omitted in generated SQL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical 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).

        Activity

        Hide
        John Kleijn added a comment -

        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

        Show
        John Kleijn added a comment - 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
        Hide
        Roman S. Borschel added a comment -

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

        Show
        Roman S. Borschel added a comment - This might be caused by recent AST optimizations and thus I suspect this to be a regression.
        Hide
        John Kleijn added a comment -

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

        Show
        John Kleijn added a comment - Is there a workaround (other than not using a subquery)?
        Hide
        Roman S. Borschel added a comment -

        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.

        Show
        Roman S. Borschel added a comment - 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.
        Hide
        Roman S. Borschel added a comment -

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

        Show
        Roman S. Borschel added a comment - Reproduced this successfully and already have a potential fix. Might not be a regression after all but a bug nevertheless.
        Hide
        Roman S. Borschel added a comment -

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

        Show
        Roman S. Borschel added a comment - Should be fixed now in HEAD (doctrine2/master).

          People

          • Assignee:
            Roman S. Borschel
            Reporter:
            John Kleijn
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: