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

[Peter Rabbitson]

Alan Humphrey wrote:
>> -----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!
> 

Oh! I didn't know that - in fact we do need 2000 users to test the failover
code. We'll send you a poke when the branch is ready for testing. But yes
the Top limit simply does not scale beyond the simplest usage. Migrating
to 2008 is an excellent strategy.

Cheers