DDC-1785: Paginator problem with SQL Server around DISTINCT keyword. #2438

Open
doctrinebot opened this Issue Apr 18, 2012 · 5 comments

2 participants

@doctrinebot

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

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

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.

CraigMason/dbal@4ecd018
CraigMason@b416d3b

This 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

Comment created by camason:

Updated, view all commits for this experimental branch here:

https://github.com/CraigMason/dbal/commits/mssql-distinct

@doctrinebot

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

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.

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment