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

          Flip created issue -
          Flip made changes -
          Field Original Value New Value
          Description I ran the doctrine test suite and there are a lot of tests failing with
          {code}
          [SQL Server]Incorrect syntax near the keyword 'with'
          {code}

          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:
          {code}
          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)
          {code}

          Solution:
          Placing WITH (NOLOCK) after the table(s). Depending on the wanted result it should be placed several times when using JOIN. See this SO for more information:
          http://stackoverflow.com/questions/3783525/sql-server-nolock-and-joins
          I ran the doctrine test suite and there are a lot of tests failing with
          {code}
          [SQL Server]Incorrect syntax near the keyword 'with'
          {code}

          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:
          {code}
          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)
          {code}

          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 SO for more information:
          http://stackoverflow.com/questions/3783525/sql-server-nolock-and-joins
          Flip made changes -
          Description I ran the doctrine test suite and there are a lot of tests failing with
          {code}
          [SQL Server]Incorrect syntax near the keyword 'with'
          {code}

          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:
          {code}
          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)
          {code}

          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 SO for more information:
          http://stackoverflow.com/questions/3783525/sql-server-nolock-and-joins
          I ran the doctrine test suite and there are a lot of tests failing with
          {code}
          [SQL Server]Incorrect syntax near the keyword 'with'
          {code}

          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:
          {code}
          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)
          {code}

          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
          M made changes -
          Status Open [ 1 ] Awaiting Feedback [ 10000 ]
          Steve Müller made changes -
          Link This issue is duplicated by DDC-2310 [ DDC-2310 ]
          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.
          Marco Pivetta made changes -
          Link This issue is referenced by DDC-2919 [ DDC-2919 ]
          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

          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={query=DDC-2675, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

            People

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

              Dates

              • Created:
                Updated: