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

New Issue

Open

opened 2026-01-22 14:18:30 +01:00 by admin · 5 comments

admin commented 2026-01-22 14:18:30 +01:00

Owner

Copy Link

Copy Source

Originally created by @doctrinebot on GitHub (Sep 12, 2013).

Originally assigned to: @beberlei on GitHub.

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

Originally created by @doctrinebot on GitHub (Sep 12, 2013). Originally assigned to: @beberlei on GitHub. 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

admin added the Bug label 2026-01-22 14:18:30 +01:00

admin commented 2026-01-22 14:18:30 +01:00

Author

Owner

Copy Link

Copy Source

@doctrinebot commented on GitHub (Sep 12, 2013):

• 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 commented on GitHub (Sep 12, 2013): - is duplicated by [DDC-2310: Recent changes to DBAL SQL Server platform lock hinting breaks ORM SqlWalker in DQL queries with joins](http://www.doctrine-project.org/jira/browse/DDC-2310) - is referenced by [DDC-2919: LockMode::NONE evaluation inconsistencies in ORM](http://www.doctrine-project.org/jira/browse/DDC-2919)

admin commented 2026-01-22 14:18:31 +01:00

Author

Owner

Copy Link

Copy Source

@doctrinebot commented on GitHub (Jan 15, 2014):

Comment created by @deeky666:

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

@doctrinebot commented on GitHub (Jan 15, 2014): Comment created by @deeky666: Please refer to [DDC-2310](http://www.doctrine-project.org/jira/browse/DDC-2310) as it describes exactly the same issue.

admin commented 2026-01-22 14:18:31 +01:00

Author

Owner

Copy Link

Copy Source

@doctrinebot commented on GitHub (Jan 31, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Jan 31, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-910] was closed: https://github.com/doctrine/doctrine2/pull/910

admin commented 2026-01-22 14:18:31 +01:00

Author

Owner

Copy Link

Copy Source

@doctrinebot commented on GitHub (Nov 11, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Nov 11, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-925] was assigned: https://github.com/doctrine/doctrine2/pull/925

admin commented 2026-01-22 14:18:31 +01:00

Author

Owner

Copy Link

Copy Source

@doctrinebot commented on GitHub (Nov 11, 2014):

Comment created by @doctrinebot:

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

@doctrinebot commented on GitHub (Nov 11, 2014): Comment created by @doctrinebot: A related Github Pull-Request [GH-925] was closed: https://github.com/doctrine/doctrine2/pull/925

Sign in to join this conversation.

No Branch/Tag Specified

Branches Tags

3.6.x

3.7.x

2.20.x

2.21.x

4.0.x

old-prototype-3.x

3.6.3

2.20.10

3.6.2

3.6.1

3.6.0

2.20.9

3.5.8

3.5.7

3.5.6

3.5.5

2.20.8

3.5.4

3.5.3

2.20.7

3.5.2

2.20.6

3.5.1

3.5.0

3.4.4

3.4.3

3.4.2

2.20.5

3.4.1

3.4.0

3.3.4

2.20.4

3.3.3

2.20.3

3.3.2

2.20.2

3.3.1

2.20.1

3.3.0

3.2.3

2.20.0

2.19.8

3.2.2

2.19.7

3.2.1

2.19.6

3.2.0

3.1.4

3.1.3

2.19.5

3.1.2

2.19.4

3.1.1

2.19.3

2.19.2

2.19.1

3.1.0

2.19.0

3.0.3

2.18.3

3.0.2

2.18.2

3.0.1

2.18.1

3.0.0

2.18.0

2.17.5

3.0.0-RC1

2.17.4

2.17.3

2.17.2

2.17.1

3.0.0-beta2

2.17.0

2.16.3

3.0.0-beta1

2.16.2

2.16.1

2.16.0

2.15.5

2.15.4

2.15.3

2.15.2

2.15.1

2.15.0

2.14.3

2.14.2

2.14.1

2.14.0

2.13.5

2.13.4

2.13.3

2.13.2

2.13.1

2.13.0

2.12.4

2.12.3

2.12.2

2.12.1

2.12.0

2.11.3

2.11.2

2.11.1

2.11.0

2.10.5

2.10.4

2.10.3

2.10.2

2.10.1

2.10.0

2.9.6

2.9.5

2.9.4

2.9.3

2.9.2

2.9.1

2.9.0

2.8.5

2.8.4

2.8.3

2.8.2

2.8.1

2.8.0

2.7.5

2.7.4

v2.7.3

v2.7.2

v2.7.1

v2.7.0

v2.6.6

v2.6.5

v2.6.4

v2.6.3

v2.6.2

v2.6.1

v2.6.0

v2.5.14

v2.5.13

v2.5.12

v2.5.11

v2.5.10

v2.5.9

v2.5.8

v2.5.7

v2.5.6

v2.5.5

v2.5.4

v2.5.3

v2.5.2

v2.4.8

v2.5.1

v2.5.0

v2.5.0-RC2

v2.5.0-RC1

v2.5.0-beta1

v2.5.0-alpha2

v2.5.0-alpha1

v2.4.7

v2.4.6

v2.4.5

v2.4.4

v2.4.3

v2.3.6

v2.4.2

2.3.5

v2.4.1

v2.4.0

2.4.0-RC2

2.4.0-RC1

2.4.0-BETA2

2.3.4

2.3.3

2.4.0-BETA1

2.3.2

2.3.1

2.3.0

2.3.0-RC4

2.3.0-RC3

2.3.0-RC2

2.2.3

2.3.0-RC1

2.3.0-BETA1

2.1.7

2.2.2

2.2.1

2.1.6

2.2.0

2.2.0-RC1

2.2.0-BETA2

2.2.0-BETA1

2.1.5

2.1.4

2.1.3

2.1.2

2.1.1

2.0.7

2.1.0

2.1.0RC3

2.1.0RC2

2.1.0RC1

2.0.6

2.1.0BETA1

2.0.5

2.0.4

2.0.3

2.0.2

2.0.1

2.0.0

2.0.0RC2

2.0.0RC1

2.0.0-BETA4

2.0.0-BETA3

2.0.0-BETA2

2.0.0-BETA1

Labels

Clear labels

BC Break

Bug

Can't Fix

Deprecation

Documentation

DQL

Duplicate

Failing Test

Hacktoberfest

Improvement

Improvement

Incomplete

Invalid

Missing Tests

New Feature

PR Created

pull-request

Mirrored from GitHub Pull Request

Question

Regression

Status: Waiting feedback

Waiting feedback

WIP

Won't Fix

No Label Bug

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

admin

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#3355