[Dbix-class] MSSQL - problem sorting by joined column while limiting rows

[Alan Humphrey]

> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: Saturday, December 05, 2009 1:35 AM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
> while limiting rows
> 
> Alan Humphrey wrote:
> > Hi -
> >
> > If I try to sort by a column in a joined table in a MSSQL database
> AND limit
> > the number of rows, the generated SQL is bad.  Here's the code:
> >
> > my $result = $schema->resultset('SurveyorsSurveySites')->search({},
> >     {join => 'surveyor',
> >      order_by => ['surveyor.name'],
> >      rows => 5
> >      });
> >
> > And here's the generated SQL.
> >
> > SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year FROM (
> SELECT
> > TOP 5 me.id, me.surveyor_id, me.survey_site_id, me.year FROM
> > surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
> > me.surveyor_id  ORDER BY surveyor.name ASC ) me ORDER BY
> surveyor.name
> 
> It looks like right-side joins finally got nailed. Please test this
> branch[1] and report how things are. As a bonus you now get proper
> offset-past-end-of-rs limits (i.e. you have 5 rows, you request
> rows => 3, offset => 3 - you get 2 rows, not 3 as before).
> 
> Cheers
> 
> [1] http://dev.catalyst.perl.org/repos/bast/DBIx-
> Class/0.08/branches/mssql_rno_pagination/
> 

This solution fails on MSSQL2000.  Here's the generated SQL:

SELECT * FROM ( SELECT orig_query.*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM (SELECT me.id, me.surveyor_id, me.survey_site_id, me.year FROM (SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year FROM surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name) me) orig_query ) rno_subq WHERE rno__row__index BETWEEN 11 AND 20 

And the error:

DBI Exception: DBD::Sybase::st execute failed: Server message number=195 severity=15 state=10 line=1 server=SQLA4 text='ROW_NUMBER' is not a recognized function name.
Server message number=170 severity=15 state=1 line=1 server=SQLA4 text=Line 1: Incorrect syntax near ')'.
Server message number=170 severity=15 state=1 line=1 server=SQLA4 text=Line 1: Incorrect syntax near 'orig_query'.


Apparently ROW_NUMBER() was introduced in MSSQL2005.

That said, I've arranged to move the database to MSSQL2008 late this week.  I'll let you know how things go after the transition.

Thanks for keeping after this!

- Alan