Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1047

Combining explicit join syntax with multiple from-clause argument-list entries will generate broken SQL on MySQL

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0.2
    • Fix Version/s: None
    • Component/s: DQL
    • Security Level: All
    • Labels:
      None

      Description

      When using explicit join syntax, MySQL requires the from-clause argument-list to have either only one entry, or, if it contains multiple entries, it requires that the complete argument-list be surrounded by parantheses. Currently, Doctrine 2 does not respect this requirement when compiling DQL statements, and produces broken SQL in some situations.

      Consider the following example of a legitimate DQL query.

      SELECT vendorListXMLRow
                              FROM \persistentData\model\import\vendorListXML\VendorListXMLRow vendorListXMLRow,
                                   \persistentData\model\import\parameterListXML\ParameterListXMLRow parameterListXMLRow
             			        JOIN vendorListXMLRow.vendorListXML vendorListXML
             			        JOIN vendorListXML.inputComponentDataVersion vendorListXMLInputComponentDataVersion
                              JOIN parameterListXMLRow.parameterListXML parameterListXML
             			        JOIN parameterListXML.inputComponentDataVersion parameterListXMLInputComponentDataVersion
                             WHERE :dataVersion MEMBER OF vendorListXMLInputComponentDataVersion.dataVersions
                               AND vendorListXMLRow.ID = parameterListXMLRow.WERT
                               AND :dataVersion MEMBER OF parameterListXMLInputComponentDataVersion.dataVersions
                               AND parameterListXMLRow.ID = :parameterID
      

      This will be compiled down to:

      SELECT v0_.ID AS ID0, v0_.DEBITORENSAMMELKONTENLISTEN_DATEINAME AS DEBITORENSAMMELKONTENLISTEN_DATEINAME1, v0_.RECHNUNGS_KONFIGURATION_DATEINAME AS RECHNUNGS_KONFIGURATION_DATEINAME2, v0_.RECHNUNGS_LAYOUT_DATEINAME AS RECHNUNGS_LAYOUT_DATEINAME3, v0_.dbID AS dbID4, v0_.vendorListXML_dbID AS vendorListXML_dbID5
      FROM VendorListXMLRow v0_, ParameterListXMLRow p1_
      INNER JOIN VendorListXML v2_ ON v0_.vendorListXML_dbID = v2_.dbID LEFT JOIN VersionedDataObject v3_ ON v2_.dbID = v3_.dbID INNER JOIN InputComponentDataVersion i4_ ON v3_.inputComponentDataVersion_dbID = i4_.dbID INNER JOIN ParameterListXML p5_ ON p1_.parameterListXML_dbID = p5_.dbID
      LEFT JOIN VersionedDataObject v6_ ON p5_.dbID = v6_.dbID
      INNER JOIN InputComponentDataVersion i7_ ON v6_.inputComponentDataVersion_dbID = i7_.dbID
      WHERE EXISTS
       (SELECT 1 FROM DataVersion_inputComponentDataVersions d8_ INNER JOIN DataVersion d9_ ON d8_.inputComponentDataVersion_dbID = d9_.dbID WHERE d8_.dataVersion_dbID = i4_.dbID AND d9_.dbID = '1') AND v0_.ID = p1_.WERT AND EXISTS
       (SELECT 1 FROM DataVersion_inputComponentDataVersions d8_ INNER JOIN DataVersion d9_ ON d8_.inputComponentDataVersion_dbID = d9_.dbID WHERE d8_.dataVersion_dbID = i7_.dbID AND d9_.dbID = '1') AND p1_.ID = 'Mandant zur Zuordnung von Analogaufträgen' LIMIT 1
      

      This SQL query can't be executed, because MySQL will complain. The error message is "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'v0_.vendorListXML_dbID' in 'on clause".

      The resulting error gives no sensible error message, and is a pain to debug, because obviously, the column mentioned does exist. The real cause of the error is that MySQL will cease to correctly interpret the aliases given, as long as parantheses not are used. I acknowledge that the MySQL behavior is downright stupid. This might well be considered a bug in MySQL, but unfortunately, the parantheses are documented in their syntax docs (http://dev.mysql.com/doc/refman/5.0/en/join.html), though I could not find any official statement regarding the use.

      Fortunately it is easy to patch Doctrine to support it.

      Now, let's add parantheses around the from clause argument list of the generated SQL statement:

      SELECT v0_.ID AS ID0, v0_.DEBITORENSAMMELKONTENLISTEN_DATEINAME AS DEBITORENSAMMELKONTENLISTEN_DATEINAME1, v0_.RECHNUNGS_KONFIGURATION_DATEINAME AS RECHNUNGS_KONFIGURATION_DATEINAME2, v0_.RECHNUNGS_LAYOUT_DATEINAME AS RECHNUNGS_LAYOUT_DATEINAME3, v0_.dbID AS dbID4, v0_.vendorListXML_dbID AS vendorListXML_dbID5
       FROM (VendorListXMLRow v0_, ParameterListXMLRow p1_) 
      INNER JOIN VendorListXML v2_ ON v0_.vendorListXML_dbID = v2_.dbID LEFT JOIN VersionedDataObject v3_ ON v2_.dbID = v3_.dbID
      INNER JOIN InputComponentDataVersion i4_ ON v3_.inputComponentDataVersion_dbID = i4_.dbID INNER JOIN ParameterListXML p5_ ON p1_.parameterListXML_dbID = p5_.dbID 
      LEFT JOIN VersionedDataObject v6_ ON p5_.dbID = v6_.dbID
      INNER JOIN InputComponentDataVersion i7_ ON v6_.inputComponentDataVersion_dbID = i7_.dbID
      WHERE EXISTS 
      (SELECT 1 FROM DataVersion_inputComponentDataVersions d8_ INNER JOIN DataVersion d9_ ON d8_.inputComponentDataVersion_dbID = d9_.dbID WHERE d8_.dataVersion_dbID = i4_.dbID AND d9_.dbID = '1') AND v0_.ID = p1_.WERT AND EXISTS
       (SELECT 1 FROM DataVersion_inputComponentDataVersions d8_ INNER JOIN DataVersion d9_ ON d8_.inputComponentDataVersion_dbID = d9_.dbID WHERE d8_.dataVersion_dbID = i7_.dbID AND d9_.dbID = '1') AND p1_.ID = 'Mandant zur Zuordnung von Analogaufträgen' LIMIT 1

      This works without complaints.

      The example query is a bit peculiar in that it uses both explicit join syntax and various from-clause entries. But according to DQL's EBNF definition, there is nothing that should prevent Doctrine 2 users from doing it. And it must work this way, because anyway else it would be impossible to specify join criteria that are no declared relationships (using explicit join-syntax only), and the other way around (using from-clause entries only, i. e. no explicit join syntax, and specifying the join conditions in the where clause) makes it impossible to join on foreign keys. Hence, under such circumstances, combining both approaches is a requirement if joins should be fully expressive.

      It is possible to completely work around these issues by sticking to a single from-clause entry, using explicit join syntax, and adding all further joins that require non-foreign-key join expressions into correlated subqueries. However, this is not straightforward and forces users into a workaround for what joins are intended for in the first place.

      This scenario is annoying, because if you were to suppress that use case completely, the EBNF would have to be significantly rewritten (and it would be the wrong thing to do IMO anyway).

      I believe the most straightforward solution is to always add parantheses around the from-clause argument-list for the MySQL dialect.

      I found this web page, which might be worth reading to get an understanding of the problem, as it describes the exact same issue (though with Hibernate):

      http://www.thinkplexx.com/learn/article/db/quer/joinwhere

      1. SqlWalker.php
        70 kB
        Daniel Alvarez Arribas
      2. SqlWalker.php
        70 kB
        Daniel Alvarez Arribas
      3. SQLWalker.php
        72 kB
        Daniel Alvarez Arribas
      4. SQLWalker for Doctrine 2.03, always generating join expressions at the end of the FROM clause.php
        73 kB
        Daniel Alvarez Arribas

        Activity

        Hide
        Daniel Alvarez Arribas added a comment -

        Sure, with the latest version it could work that way. I'll try, as I said.

        The local SQLWalker patch was just a temporary measure. I hope it will become unnecessary after rewriting the queries the way you proposed.

        Show
        Daniel Alvarez Arribas added a comment - Sure, with the latest version it could work that way. I'll try, as I said. The local SQLWalker patch was just a temporary measure. I hope it will become unnecessary after rewriting the queries the way you proposed.
        Hide
        Daniel Alvarez Arribas added a comment -

        Sorry for the delay. I am now upgrading to Doctrine 2.1 and rewriting the application's queries according to the way you explained. I will let you know about results within the next couple of days, after performing test runs based on the reworked queries.

        Show
        Daniel Alvarez Arribas added a comment - Sorry for the delay. I am now upgrading to Doctrine 2.1 and rewriting the application's queries according to the way you explained. I will let you know about results within the next couple of days, after performing test runs based on the reworked queries.
        Hide
        Daniel Alvarez Arribas added a comment -

        I rewrote all queries to match the basic form you showed in your comment from April, 3rd.

        Using the basic form

        SELECT x
        FROM drivingTable1 alias1
        JOIN alias1.otherTable1 alias2,
        drivingTable 2 alias3
        JOIN alias3.otherTable2 alias4
        ...

        the queries now work on MySQL Server without any local modifications of Doctrine 2.

        I tested it with MySQL Server version 5.0.51a, and Doctrine version 2.1.1.

        So far everything seems to be good.

        I will try it on PostgreSQL, too, and let you know about that.

        Show
        Daniel Alvarez Arribas added a comment - I rewrote all queries to match the basic form you showed in your comment from April, 3rd. Using the basic form SELECT x FROM drivingTable1 alias1 JOIN alias1.otherTable1 alias2, drivingTable 2 alias3 JOIN alias3.otherTable2 alias4 ... the queries now work on MySQL Server without any local modifications of Doctrine 2. I tested it with MySQL Server version 5.0.51a, and Doctrine version 2.1.1. So far everything seems to be good. I will try it on PostgreSQL, too, and let you know about that.
        Hide
        Daniel Alvarez Arribas added a comment -

        I tried a complex sample query on PostgreSQL using the same join syntax, and it worked like a charm.

        I'll close the issue as fixed.

        Thanks a lot.

        Show
        Daniel Alvarez Arribas added a comment - I tried a complex sample query on PostgreSQL using the same join syntax, and it worked like a charm. I'll close the issue as fixed. Thanks a lot.
        Hide
        Daniel Alvarez Arribas added a comment -

        Issue closed.

        I don't know about the DQL validation part, though. The fact that DQL can or could compile to something that is not valid SQL could be considered symptomatic of a bug by itself. Don't know about the state of that, maybe it's more robust by now.

        Show
        Daniel Alvarez Arribas added a comment - Issue closed. I don't know about the DQL validation part, though. The fact that DQL can or could compile to something that is not valid SQL could be considered symptomatic of a bug by itself. Don't know about the state of that, maybe it's more robust by now.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Daniel Alvarez Arribas
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: