mirror of
https://github.com/doctrine/orm.git
synced 2026-03-24 06:52:09 +01:00
DDC-1785: Paginator problem with SQL Server around DISTINCT keyword. #2245
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @doctrinebot on GitHub (Apr 18, 2012).
Originally assigned to: @beberlei on GitHub.
Jira issue originally created by user @beberlei:
PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'DISTINCT'. (uncaught exception)
@doctrinebot commented on GitHub (Oct 18, 2012):
Comment created by camason:
There are four major issues with this:
1: SQLServerPlatform.php modifies the query to prepend 'SELECT ROW_NUMBER() OVER ($over)', which is inserted before the DISTINCT keyword.
2: The order needs to be placed inside the OVER($over) block. At this point, the regex is using the exact column name rather than the alias, so the outer query cannot ORDER.
3: The DISTINCT queries select only the ID columns - as OVER() required the sort column to be available in the outer query, IDs alone will not work.
4: SQL Server cannot DISTINCT on TEXT columns. 2005,2008 and 2012 recommend using VARCHAR(MAX) instead, which does support it. That doesn't help us with 2003. We work around that with a custom TEXT type that casts as varchar.
Incidentally, 2012 supports LIMIT, which gets rid of this issue altogether.
Edit: Added #3
@doctrinebot commented on GitHub (Oct 18, 2012):
Comment created by camason:
I have a (very hacky) implementation working that uses regexes to correct the query so that it will execute. This also required modification in the ORM paginator, to select all columns instead of just IDs.
4ecd018c73b416d3b2a3This is certainly not a patch - more guidance.
One interesting point... I had to wrap the whole query in a second SELECT *, as the WHERE IN confusingly returns non-distinct rows when part of the first inner query. No idea why this happens, but moving it out one layer makes it operate correctly.
@doctrinebot commented on GitHub (Oct 25, 2012):
Comment created by camason:
Updated, view all commits for this experimental branch here:
https://github.com/CraigMason/dbal/commits/mssql-distinct
@doctrinebot commented on GitHub (Oct 29, 2012):
Comment created by camason:
This got waaaay too messy with regex alone due to the complicated nesting. As such, I have written the basis of a new SqlWalker class which can be used to create DISTINCT queries based on the root identifiers. It's not proper DISTINCT support, but it's a step forward.
https://github.com/CraigMason/DoctrineSqlServerExtensions
I've also added a Paginator (which was the original issue I had!)
The current SqlWalker always sticks the ORDER BY on the end of the query, which just doesn't work properly with SqlServer. Is a vendor-specific walker breaking the DQL abstraction? Should this type of code be on the Platform object in the DBAL?
Anyway, this repo fixes our immediate problem, and it would be good to revisit this in a wider context. Hopefully we can get some good SQL server support - there are plenty of other issues to deal with (UTF-8/UCS2, nvarchar etc)
@doctrinebot commented on GitHub (Jan 19, 2013):
Comment created by @beberlei:
[~camason] We don't have an SQL Server expert on the team, so if you want really good support you should join and help us with it.