DDC-2675: WITH (NOLOCK) failing when using JOIN

[doctrinebot]

Jira issue originally created by user flip101:

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

[doctrinebot]

• is duplicated by DDC-2310: Recent changes to DBAL SQL Server platform lock hinting breaks ORM SqlWalker in DQL queries with joins
• is referenced by DDC-2919: LockMode::NONE evaluation inconsistencies in ORM

[doctrinebot]

Comment created by @deeky666:

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

[doctrinebot]

Comment created by @doctrinebot:

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

[doctrinebot]

Comment created by @doctrinebot:

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

[doctrinebot]

Comment created by @doctrinebot:

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