[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, November 28, 2009 12:15 PM
> 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
> >
> > The same code run against a MySQL database works fine.
> >
> > - Alan
> >
> 
> I *think* if you +select/+as the column you order by, things should
> just work.
> It's been a while since I rewrote the abomination that is _Top :) If
> this
> indeed solves the problem - we can dig into making it smarter.
> 

No joy.  Here's the code:

my $result = $schema->resultset('SurveyorsSurveySites')->search({},
    {join => 'surveyor',
     order_by => ['surveyor.name'],
     rows  => 5,
     '+select' => ['surveyor.name'],
     '+as' => ['Name']
     });

Here's the output:

SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year, name FROM ( SELECT
 TOP 5 me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.name FROM sur
veyors_survey_sites me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id
ORDER BY surveyor.name ASC ) me ORDER BY surveyor.name :
DBI Exception: DBD::Sybase::st execute failed: Server message number=107 severit
y=16 state=2 line=1 server=SQLA4 text=The column prefix 'surveyor' does not matc
h with a table name or alias name used in the query. [for Statement "SELECT TOP
100 PERCENT id, surveyor_id, survey_site_id, year, name FROM ( SELECT TOP 5 me.i
d, me.surveyor_id, me.survey_site_id, me.year, surveyor.name FROM surveyors_surv
ey_sites me JOIN surveyors surveyor ON surveyor.id = me.surveyor_id  ORDER BY su
rveyor.name ASC ) me ORDER BY surveyor.name "] at /usr/pkg/lib/perl5/site_perl/5
.10.0/DBIx/Class/Schema.pm line 1027
        DBIx::Class::Schema::throw_exception('BirdWeb::BirdWebSchema=HASH(0x8232
df0)', 'DBI Exception: DBD::Sybase::st execute failed: Server message...') calle
d at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage.pm line 123
        DBIx::Class::Storage::throw_exception('DBIx::Class::Storage::DBI::Sybase
::Microsoft_SQL_Server=HASH(...', 'DBI Exception: DBD::Sybase::st execute failed
: Server message...') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/S
torage/DBI.pm line 1038
        DBIx::Class::Storage::DBI::__ANON__('DBD::Sybase::st execute failed: Ser
ver message number=107 sev...', 'DBI::st=HASH(0x89a5360)', undef) called at /usr
/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1322
        DBIx::Class::Storage::DBI::_dbh_execute('DBIx::Class::Storage::DBI::Syba
se::Microsoft_SQL_Server=HASH(...', 'DBI::db=HASH(0x8979e30)', 'select', undef,
'ARRAY(0x8916f00)', 'HASH(0x89a5610)', 'ARRAY(0x8916b10)', undef, 'HASH(0x8a918c
0)', ...) called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.p
m line 606
        DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Sybase::Mi
crosoft_SQL_Server=HASH(...', 'CODE(0x88a26f0)', 'select', undef, 'ARRAY(0x8916f
00)', 'HASH(0x89a5610)', 'ARRAY(0x8916b10)', undef, 'HASH(0x8a918c0)', ...) call
ed at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI/MSSQL.pm line 1
57
        DBIx::Class::Storage::DBI::MSSQL::_execute('DBIx::Class::Storage::DBI::S
ybase::Microsoft_SQL_Server=HASH(...', 'select', undef, 'ARRAY(0x8916f00)', 'HAS
H(0x89a5610)', 'ARRAY(0x8916b10)', undef, 'HASH(0x8a918c0)', 5, ...) called at /
usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1715
        DBIx::Class::Storage::DBI::_select('DBIx::Class::Storage::DBI::Sybase::M
icrosoft_SQL_Server=HASH(...', 'ARRAY(0x8916f00)', 'ARRAY(0x8916b10)', undef, 'H
ASH(0x8916b70)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Storag
e/DBI/Cursor.pm line 86
        DBIx::Class::Storage::DBI::Cursor::_dbh_next('DBIx::Class::Storage::DBI:
:Sybase::Microsoft_SQL_Server=HASH(...', 'DBI::db=HASH(0x8979e30)', 'DBIx::Class
::Storage::DBI::Cursor=HASH(0x89a5200)') called at /usr/pkg/lib/perl5/site_perl/
5.10.0/DBIx/Class/Storage/DBI.pm line 616
        eval {...} called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Stor
age/DBI.pm line 614
        DBIx::Class::Storage::DBI::dbh_do('DBIx::Class::Storage::DBI::Sybase::Mi
crosoft_SQL_Server=HASH(...', 'CODE(0x877d510)', 'DBIx::Class::Storage::DBI::Cur
sor=HASH(0x89a5200)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/S
torage/DBI/Cursor.pm line 105
        DBIx::Class::Storage::DBI::Cursor::next('DBIx::Class::Storage::DBI::Curs
or=HASH(0x89a5200)') called at /usr/pkg/lib/perl5/site_perl/5.10.0/DBIx/Class/Re
sultSet.pm line 954
        DBIx::Class::ResultSet::next('DBIx::Class::ResultSet=HASH(0x8916d60)') c
alled at test.pl line 21


If I change my storage type to ::DBI::Sybase::NoBindVars then it works either with or without the +select/+as.

- Alan