[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, 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. 

Right, I see why it is happening, and where it is happening. I also see that
whatever support code was written for this 6 months ago, is utter crap, and
there is a much clearer and shorter way to do this now. If this is not urgent
I may take a stab at it about 2 weeks from now or so. Otherwise you're welcome
to hack up something temporarily - if it comes with a test and looks sane,
we will include it in 08115 (coming this week hopefully)

> 
> If I change my storage type to ::DBI::Sybase::NoBindVars then it works either with or without the +select/+as.
> 
I am not sure how this could even work. Can you show me the generated sql?

Problem located at: DBIx::Class::SQLAHacks::_Top() lines 202-211
File to add extra tests: t/42toplimit.t
svn repo: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/

Cheers