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

Alan Humphrey alan.humphrey at comcast.net
Sun Dec 13 05:05:36 GMT 2009


> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: Saturday, December 12, 2009 3:37 PM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
> while limiting rows
> 
> On Fri, Dec 11, 2009 at 02:24:29PM -0800, Alan Humphrey wrote:
> > An update on this problem.
> >
> > Database was converted to MSSQL2008.
> >
> > The latest code works fine, except if prefetch is used.  See below
> for code and trace results.
> >
> 
> Try [1]. This *should* be the end of this :)
> 
> [1] http://dev.catalyst.perl.org/repos/bast/DBIx-
> Class/0.08/branches/mssql_limit_regression/
> 

Good news and bad news.  Good news - results are coming back.  Bad news -
the sort is wrong.

Example:

Generated code and results with no paging:

SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id,
surveyor.name, surveyor.email, surveyor.phone, surveyor.login,
surveyor.password, surveyor.is_active, surveyor.is_verifier,
surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM
surveyors_survey_sites me JOIN surveyors surveyor ON surveyor.id =
me.surveyor_id ORDER BY surveyor.name: 
<snip>
Steve Grayson
Steve Johnson
Steve Johnson
Sue Yates
Sue Yates
Susie Stillman
Susie Stillman
Terry Mace
Terry Mace
Terry Mace
Terry Mace
Terry Mace
Tracey Scalici
Tristan Nunez
Vera Cragin
Woody Franzen
Woody Franzen
Zoe Froyland

Now, with paging, trying to get the last page of results.  (Other pages are
also wrong.  This just shows it most dramatically.)

SELECT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id,
surveyor.name, surveyor.email, surveyor.phone, surveyor.login,
surveyor.password, surveyor.is_active, surveyor.is_verifier,
surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM ( 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 136 AND 150 ) me JOIN
surveyors surveyor ON surveyor.id = me.surveyor_id ORDER BY surveyor.name: 
Alex Greene
Alex Greene
David Gluckman
Ian Maunsell
Ian Maunsell
Jim Oakland
John Cragin
Ken Wilson
Port Townsend Marine Science Center
Ron Martin
Ron Martin
Ron Sikes
Rosemary Sikes

- Alan




More information about the DBIx-Class mailing list