Uploaded image for project: 'Doctrine 2 - ORM'
  1. Doctrine 2 - ORM
  2. DDC-2310

Recent changes to DBAL SQL Server platform lock hinting breaks ORM SqlWalker in DQL queries with joins

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: Git Master
    • Fix Version/s: 2.3.3, 2.5
    • Component/s: ORM
    • Security Level: All
    • Environment:
      SQL Server

      Description

      The SQL Server platform throws an error when you try to run DQL with JOIN statements.

      The breaking change was in the DBAL SQL Server platform – it was changed to add a ' WITH (NOLOCK)' to the appendLockHint function. Change was in this rev. The change in DBAL is not wrong, it just highlighted the bug in the ORM...

      The ORM SqlWalker runs the appendLockHint function against a generated FROM / JOIN clause in the walkFromClause func here. This is actually the wrong place to append lock hints. This is generating the FROM clause like:

       FROM foo f0_ LEFT JOIN foo_bar f1_ ON f0_.id = f1_.foo_id LEFT JOIN bar b2_ ON f1_.bar_id = b2_.id WITH (NOLOCK)

      When it should actually generate something like:

       FROM foo f0_ WITH (NOLOCK) LEFT JOIN foo_bar f1_ WITH (NOLOCK) ON f0_.id = f1_.foo_id LEFT JOIN bar b2_ WITH (NOLOCK) ON f1_.bar_id = b2_.id

      It should append lock hints after the table alias.

      I think the only reason this hasn't shown up before is that the other lock hint types haven't been applied in this way before, if at all.

        Issue Links

          Activity

          wschalle Bill Schaller created issue -
          beberlei Benjamin Eberlei made changes -
          Field Original Value New Value
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 2.3.3 [ 10329 ]
          Resolution Fixed [ 1 ]
          Hide
          stof Christophe Coevoet added a comment -

          I think the line appending the lock should be moved to this place to achieve the result displayed above.

          But it may cause issues with some other vendor.

          Show
          stof Christophe Coevoet added a comment - I think the line appending the lock should be moved to this place to achieve the result displayed above. But it may cause issues with some other vendor.
          Hide
          wschalle Bill Schaller added a comment -

          @Christophe I considered that too. None of the other platforms implement the appendLockHint function. None of the other platforms implement this because it is handled differently on other platforms – with transaction isolation levels and such.

          Show
          wschalle Bill Schaller added a comment - @Christophe I considered that too. None of the other platforms implement the appendLockHint function. None of the other platforms implement this because it is handled differently on other platforms – with transaction isolation levels and such.
          Hide
          deeky666 Steve Müller added a comment -

          I don't know why this ticket is marked as "fixed" because it's obviously NOT.
          Whatever, here is the patch: https://github.com/doctrine/doctrine2/pull/910

          Show
          deeky666 Steve Müller added a comment - I don't know why this ticket is marked as "fixed" because it's obviously NOT. Whatever, here is the patch: https://github.com/doctrine/doctrine2/pull/910
          Hide
          deeky666 Steve Müller added a comment -

          Complementary I provided the following patch to suppress unnecessary NOLOCK hint generation in ORM: https://github.com/doctrine/dbal/pull/508

          Show
          deeky666 Steve Müller added a comment - Complementary I provided the following patch to suppress unnecessary NOLOCK hint generation in ORM: https://github.com/doctrine/dbal/pull/508
          deeky666 Steve Müller made changes -
          Link This issue is referenced by DBAL-783 [ DBAL-783 ]
          Hide
          doctrinebot Doctrine Bot added a comment -

          A related Github Pull-Request [GH-508] was closed:
          https://github.com/doctrine/dbal/pull/508

          Show
          doctrinebot Doctrine Bot added a comment - A related Github Pull-Request [GH-508] was closed: https://github.com/doctrine/dbal/pull/508
          deeky666 Steve Müller made changes -
          Link This issue duplicates DDC-2675 [ DDC-2675 ]
          Hide
          deeky666 Steve Müller added a comment -

          This is not resolved, yet.

          Show
          deeky666 Steve Müller added a comment - This is not resolved, yet.
          deeky666 Steve Müller made changes -
          Resolution Fixed [ 1 ]
          Status Resolved [ 5 ] Reopened [ 4 ]
          ocramius Marco Pivetta made changes -
          Link This issue is referenced by DDC-2919 [ DDC-2919 ]
          Hide
          doctrinebot Doctrine Bot added a comment -

          A related Github Pull-Request [GH-910] was closed:
          https://github.com/doctrine/doctrine2/pull/910

          Show
          doctrinebot Doctrine Bot added a comment - A related Github Pull-Request [GH-910] was closed: https://github.com/doctrine/doctrine2/pull/910
          Hide
          beberlei Benjamin Eberlei added a comment -

          Steve Müller When is it?

          Show
          beberlei Benjamin Eberlei added a comment - Steve Müller When is it?
          Show
          deeky666 Steve Müller added a comment - Benjamin Eberlei It is fixed in PR: https://github.com/doctrine/doctrine2/pull/910
          beberlei Benjamin Eberlei made changes -
          Status Reopened [ 4 ] Resolved [ 5 ]
          Fix Version/s 2.5 [ 10522 ]
          Resolution Fixed [ 1 ]
          ocramius Marco Pivetta made changes -
          Link This issue depends on DDC-2914 [ DDC-2914 ]
          deeky666 Steve Müller made changes -
          Link This issue is referenced by DBAL-976 [ DBAL-976 ]
          ocramius Marco Pivetta made changes -
          Link This issue is required for DDC-2945 [ DDC-2945 ]

          This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

          • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={expand=changesets[0:20].revisions[0:29],reviews, query=DDC-2310}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

            • Assignee:
              beberlei Benjamin Eberlei
              Reporter:
              wschalle Bill Schaller
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: