Skip to content

Loading…

DDC-2310: Recent changes to DBAL SQL Server platform lock hinting breaks ORM SqlWalker in DQL queries with joins #3014

Closed
doctrinebot opened this Issue · 11 comments

2 participants

@doctrinebot

Jira issue originally created by user @zeroedin-bill:

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.

@doctrinebot

Comment created by stof:

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.

@doctrinebot

Comment created by @zeroedin-bill:

@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.

@doctrinebot

Comment created by @deeky666:

I don't know why this ticket is marked as "fixed" because it's obviously NOT.
Whatever, here is the patch: #910

@doctrinebot

Comment created by @deeky666:

Complementary I provided the following patch to suppress unnecessary NOLOCK hint generation in ORM: doctrine/dbal#508

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-508] was closed:
doctrine/dbal#508

@doctrinebot

Comment created by @deeky666:

This is not resolved, yet.

@doctrinebot

Comment created by @doctrinebot:

A related Github Pull-Request [GH-910] was closed:
#910

@doctrinebot

Comment created by @beberlei:

[~deeky666] When is it?

@doctrinebot

Comment created by @deeky666:

[~beberlei] It is fixed in PR: #910

@doctrinebot

Issue was closed with resolution "Fixed"

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.3.3 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.