Patch: Using DISTINCT with Paging throws exception

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create and execute a SqlQuery that uses both .Distinct() and .Paged()
(e.g.
Select().From<Product>().Distinct().InnerJoin<ProductCategory>().Paged(1,25);)
2.
3.

What is the expected output? What do you see instead?
Would expect to get back the distinct paged results, but it throws an error
saying there is an error in the SQL near 'DISTINCT'. The problem is that
when it creates the paged version of the SQL for Sql2005, it writes the
following as the beginning of that statement:

SELECT *
FROM     (
SELECT ROW_NUMBER() OVER ( ORDER BY Name DESC) AS Row, 
DISTINCT [dbo].[Product].[ProductId], [dbo].[Product].[Name] 
...

There are two problems with this. 1) DISTINCT must immediately follow
SELECT. 2) By adding in the row numbers, all rows are now distinct even if
the rest of the data in them is the same.

The fix involves replacing the subquery with a sub-subquery if the query
uses the DISTINCT keyword like so:

SELECT *
FROM     (
SELECT ROW_NUMBER() OVER ( ORDER BY Name DESC) AS Row, * 
FROM ( SELECT DISTINCT [dbo].[Product].[ProductId], [dbo].[Product].[Name]
...

I only fixed this in the Sql2005Generator.cs as the ANSIGenerator uses
different paging syntax. It may need to be patched too but I am using SQL
2005, so I can't check that.

What version of the product are you using? On what operating system?
Current svn. Win XP. SQL Server 2005

Original issue reported on code.google.com by JonWynv...@gmail.com on 28 Apr 2009 at 4:56

Attachments:

• [DISTINCT with Paged.patch](https://storage.googleapis.com/google-code-attachments/subsonicproject/issue-77/comment-0/DISTINCT with Paged.patch)

[GoogleCodeExporter]

I think your Subsonic.Select() Statement is wrong.

Select().Distinct().From<Product>().InnerJoin<ProductCategory>().Paged(1,25)

Which would be the "MySQL" way to do it. Have you tried this?

Original comment by j.steinblock@gmail.com on 29 Apr 2009 at 12:10

[GoogleCodeExporter]

I initially thought that the Distinct should be before the From too, but when I 
did
that, the SQL it wrote was something like "SELECT DISTINCT DISTINCT 
[columnlist] FROM
...". Not sure why that happens because I didn't look into that.

Original comment by JonWynv...@gmail.com on 29 Apr 2009 at 1:14

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

With the MySQL Dataprovider the code generated with:

new SubSonic.Select().Distinct().From("product").BuildSqlStatement()

reads:

SELECT DISTINCT `product`.`id`, `product`.`name`, ...

 FROM `product`

which is what I expected and runs without errors against the db.

if I use:

New SubSonic.Select().From("product").Distinct().BuildSqlStatement()

the DISTINCT is swallowed,

Are you sure you updated to the latest version from trunk?
Look at the changelog from r521:
"FIXED: SqlQuery - ANSISqlGenerator added duplicate DISCTINCT values resulting 
in
malformed queries."

Original comment by j.steinblock@gmail.com on 29 Apr 2009 at 2:05

[GoogleCodeExporter]

It does look like I'll need to update the version of my files, but that still
wouldn't affect the SQL generated for paging when using distinct. So this issue 
would
still exist and the patch should still apply (I looked at the svn log and 
nothing was
changed regarding the paging in Sql2005Generator).

Original comment by JonWynv...@gmail.com on 29 Apr 2009 at 2:24

[GoogleCodeExporter]

ok, now I got it. but you should test your patch against the query

Select().Distinct().From<Product>().InnerJoin<ProductCategory>().Paged(1,25);

too, because I think that is the way it should be coded.

Original comment by j.steinblock@gmail.com on 30 Apr 2009 at 4:10

[GoogleCodeExporter]

I just updated my version of code to the latest from svn and it does work with
Select().Distinct().From<Product>().InnerJoin<ProductCategory>().Paged(1,25); 
the way
it should.

Original comment by JonWynv...@gmail.com on 30 Apr 2009 at 4:28

[GoogleCodeExporter]

When you get a chance, can you verify that this works correctly for you with 
Rev 522?
There was a small change made to address issues with DISTINCT in aggregate 
scenarios
and I'd like to make sure that no new problems were introduced...

Original comment by canof...@gmail.com on 4 May 2009 at 9:11

[GoogleCodeExporter]

I'm not able to test this because we have implemented and starting using the 
patch
for issue 61 as well, so when I try to use r522, my project doesn't build.

Original comment by JonWynv...@gmail.com on 8 May 2009 at 4:14

[GoogleCodeExporter]

would it make sense to support paging in code, not sql since sql server < 2005
doesn't support row_number()?

Original comment by john.h....@gmail.com on 2 Jun 2009 at 8:22