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

[Alan Humphrey]

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