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

Alan Humphrey alan.humphrey at comcast.net
Wed Dec 9 22:27:42 GMT 2009


> -----Original Message-----
> From: Peter Rabbitson [mailto:rabbit+dbic at rabbit.us]
> Sent: Wednesday, December 09, 2009 10:48 AM
> To: DBIx::Class user and developer list
> Subject: Re: [Dbix-class] MSSQL - problem sorting by joined column
> while limiting rows
> 
> 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.
> >
> 
> Ok, please try the same branch, make sure it works cleanly against
> mssql 2000.
> Of course your original problem will not be fixed, as it is not really
> possible
> with Top-limit. Once you upgrade to >= 2005, all problems should go
> away.
> 
> Cheers
> 


Peter -

Here's the result of the latest code.  First, I had to make changes to the check version routine.  I'm currently arguing with my host, but right now they're claiming that access to system stored procedures on a shared MSSQL server represents a security risk to other databases so they're not inclined to let me execute xp_msver.  The code, as written, craps out if xp_msver doesn't exist.  I changed it to return a default:

Index: lib/DBIx/Class/Storage/DBI/MSSQL.pm
===================================================================
--- lib/DBIx/Class/Storage/DBI/MSSQL.pm (revision 8055)
+++ lib/DBIx/Class/Storage/DBI/MSSQL.pm (working copy)
@@ -230,13 +230,20 @@
sub _get_mssql_version {
my $self = shift;

- my $data = $self->_get_dbh->selectrow_hashref('xp_msver ProductVersion');
+ my $version = 0;
+ my $data = undef;
+ eval {
+ $data = $self->_get_dbh->selectrow_hashref('xp_msver ProductVersion');
+ };

- if ($data->{Character_Value} =~ /^(\d+)\./) {
- return $1;
- } else {
- $self->throw_exception(q{Your ProductVersion's Character_Value is missing or malformed!});
+ unless ( $@ ) {
+ if ($data->{Character_Value} =~ /^(\d+)\./) {
+ $version = $1;
+# } else {
+# $self->throw_exception(q{Your ProductVersion's Character_Value is missing or malformed!});
+ }
}
+ return $version;
}

sub sql_maker {

Note that I've commented out the exception thrown when the character is missing/malformed.  My thinking is that if the server has that screwed up there's nothing the user can do about it anyway, so we may as well revert to the default.  On the other hand, it might be nice to know why you're not getting the generated code you're expecting.  The modified code is consistent in that if there are any problems it silently reverts to a default value.  Whether that's the right course of action is something you'll have to decide.

Once we get past the missing stored procedure the generated SQL still has problems:

DBIx::Class::ResultSet::next(): DBI Exception: DBD::Sybase::st execute failed: Server message number=107 severity=16 state=2 line=1 server=SQLA4 text=The column prefix 'surveyor' does not match with a table name or alias name used in the query.
Server message number=107 severity=16 state=2 line=1 server=SQLA4 text=The column prefix 'surveyor' does not match with a table name or alias name used in the query.
[for Statement " SELECT id, surveyor_id, survey_site_id, year FROM ( SELECT TOP 10 id, surveyor_id, survey_site_id, year FROM ( SELECT TOP 20 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 DESC ) me ORDER BY surveyor.name "] at test.pl line 17

Hope this helps.

- Alan





More information about the DBIx-Class mailing list