paged + group by + where cause Incorrect syntax near the keyword 'WHERE'

[ruijterp]

Subsonic version: 2.2.0.0

In a SQL 2005 database server I've created a view:

CREATE VIEW [dbo].[V_RIT_SUMMERY] AS SELECT u.start_dtt, u.eind_dtt, DATEDIFF(MINUTE, u.start_dtt, u.eind_dtt) AS DIFF, r.rit_nmr FROM tb_uitvoering u LEFT JOIN tb_rit r ON u.guid_nmr = r.guid_nmr WHERE u.eind_dtt IS NOT NULL GROUP BY r.rit_nmr, u.start_dtt, u.eind_dtt GO

I use the following code to create a groupby selection with an avg on diff:

SubSonic.SqlQuery qry = new SubSonic.Select( Aggregate.GroupBy(VRitSummery.Columns.RitNmr), Aggregate.Avg(VRitSummery.Columns.Diff) ). From(VRitSummery.Schema). OrderAsc(VRitSummery.Columns.RitNmr). Where(VRitSummery.Columns.StartDtt).IsGreaterThanOrEqualTo(startdatum). And(VRitSummery.Columns.EindDtt).IsLessThanOrEqualTo(einddatum). Paged(page, pageSize);

Subsonic produces the following code:

SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY rit_nmr ASC) AS Row,rit_nmr AS 'GroupByOfrit_nmr', AVG(DIFF) AS 'AvgOfDIFF' FROM [dbo].[V_RIT_SUMMERY] GROUP BY rit_nmr WHERE [dbo].[V_RIT_SUMMERY].[start_dtt] >= @start_dtt0 AND [dbo].[V_RIT_SUMMERY].[eind_dtt] <= @eind_dtt1 ) AS PagedResults WHERE Row >= 1 AND Row <= 10

There is a GROUP BY cause before the WHERE cause, in SQL this is ofcourse not correct.

[tehZeno]

We are experiencing the same problems here.

If we exclude the .Paged(..) method we always get a nice clean and correct query. If we add the Paged(..) however it always puts the group by on the wrong line. (when using group by and where clause). If we ommit the where clause it also generates a correct query.

Hopefully the Subsonic will come up with a fix for the 2.* branch, as we'd rather not switch to 3.* at this moment yet (impact will be too big for now).