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

[Eden Cardim]

>>>>> "Alan" == Alan Humphrey <alan.humphrey at comcast.net> writes:

    Alan> Hi - If I try to sort by a column in a joined table in a MSSQL
    Alan> database AND limit the number of rows, the generated SQL is
    Alan> bad.  Here's the code:

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

    Alan> And here's the generated SQL.

    Alan> SELECT TOP 100 PERCENT id, surveyor_id, survey_site_id, year
    Alan> FROM ( SELECT TOP 5 me.id, me.surveyor_id, me.survey_site_id,
    Alan> me.year FROM surveyors_survey_sites me JOIN surveyors surveyor
    Alan> ON surveyor.id = me.surveyor_id ORDER BY surveyor.name ASC )
    Alan> me ORDER BY surveyor.name

    Alan> The same code run against a MySQL database works fine.

I'm working on a patch for this, can you supply the precise error
emmitted by MSSQL? thanks in advance