Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2675

WITH (NOLOCK) failing when using JOIN

    Details

    • Type: Bug Bug
    • Status: Awaiting Feedback
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.4
    • Fix Version/s: None
    • Component/s: None
    • Security Level: All
    • Labels:
      None
    • Environment:
      MSSQL 2008 R2

      Description

      I ran the doctrine test suite and there are a lot of tests failing with

      [SQL Server]Incorrect syntax near the keyword 'with'
      

      List of failing test because of this issue:
      2) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testUnnamedScalarResultsAreOneBased
      3) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testOrderByResultVariableCollectionSize
      4) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testIsNullAssociation
      5) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testSelectSubselect
      6) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testInSubselect
      7) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testGroupByMultipleFields
      8) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testUpdateAs
      9) Doctrine\Tests\ORM\Functional\AdvancedDqlQueryTest::testDeleteAs
      10) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testCRUD
      11) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testSelfReferencingOneToOne
      12) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testSelfReferencingManyToMany
      13) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testLazyLoading2
      14) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testBulkUpdateIssueDDC368
      15) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testBulkUpdateNonScalarParameterDDC1341
      16) Doctrine\Tests\ORM\Functional\ClassTableInheritanceTest::testQueryForInheritedSingleValuedAssociation
      17) Doctrine\Tests\ORM\Functional\Locking\OptimisticTest::testJoinedChildFailureThrowsException
      18) Doctrine\Tests\ORM\Functional\Locking\OptimisticTest::testJoinedParentFailureThrowsException
      19) Doctrine\Tests\ORM\Functional\OrderedJoinedTableInheritanceCollectionTest::testOrderdOneToManyCollection
      20) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testAggregateSum
      21) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testAggregateAvg
      22) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testAggregateMin
      23) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testAggregateMax
      24) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testAggregateCount
      25) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionAbs
      26) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionConcat
      27) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionLength
      28) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionLocate
      29) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionLower
      30) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionMod
      31) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionSqrt
      32) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionUpper
      33) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionSubstring
      34) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testFunctionTrim
      35) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testOperatorAdd
      36) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testOperatorSub
      37) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testOperatorMultiply
      38) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testOperatorDiv
      39) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testConcatFunction
      40) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testDateDiff
      41) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testDateAdd
      42) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testDateSub
      43) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testBitOrComparison
      44) Doctrine\Tests\ORM\Functional\QueryDqlFunctionTest::testBitAndComparison
      45) Doctrine\Tests\ORM\Functional\SQLFilterTest::testJoinSubclassPersister_FilterOnlyOnRootTableWhenFetchingSubEntity
      46) Doctrine\Tests\ORM\Functional\SQLFilterTest::testJoinSubclassPersister_FilterOnlyOnRootTableWhenFetchingRootEntity
      47) Doctrine\Tests\ORM\Functional\Ticket\DDC163Test::testQueryWithOrConditionUsingTwoRelationOnSameEntity
      48) Doctrine\Tests\ORM\Functional\Ticket\DDC168Test::testJoinedSubclassPersisterRequiresSpecificOrderOfMetadataReflFieldsArray
      49) Doctrine\Tests\ORM\Functional\Ticket\DDC1995Test::testIssue
      50) Doctrine\Tests\ORM\Functional\Ticket\DDC1995Test::testQueryCache
      51) Doctrine\Tests\ORM\Functional\Ticket\DDC2090Test::testIssue
      53) Doctrine\Tests\ORM\Functional\Ticket\DDC279Test::testDDC279
      54) Doctrine\Tests\ORM\Functional\Ticket\DDC933Test::testLockCTIClass

      One example, test 20)
      Generated SQL:

      SELECT SUM(c0_.salary) AS sclr0
      FROM company_managers c1_
      INNER JOIN company_employees c0_ ON c1_.id = c0_.id
      INNER JOIN company_persons c2_ ON c1_.id = c2_.id
      WITH (NOLOCK)
      

      Solution:
      Placing WITH (NOLOCK) after the table(s), instead of after ON clause. Depending on the wanted result it should be placed several times when using JOIN. See this StackOverflow Post for more information:
      http://stackoverflow.com/questions/3783525/sql-server-nolock-and-joins

        Issue Links

          Activity

          Hide
          Steve Müller added a comment -

          Please refer to DDC-2310 as it describes exactly the same issue.

          Show
          Steve Müller added a comment - Please refer to DDC-2310 as it describes exactly the same issue.
          Hide
          Doctrine Bot added a comment -

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

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

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-925] was assigned: https://github.com/doctrine/doctrine2/pull/925
          Hide
          Doctrine Bot added a comment -

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

          Show
          Doctrine Bot added a comment - A related Github Pull-Request [GH-925] was closed: https://github.com/doctrine/doctrine2/pull/925

            People

            • Assignee:
              Benjamin Eberlei
              Reporter:
              Flip
            • Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated: