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

[Alan Humphrey]

An update on this problem.

Database was converted to MSSQL2008.

The latest code works fine, except if prefetch is used.  See below for code and trace results.

- Alan

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


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, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM (SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined 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 15 : 
DBI Exception: DBD::Sybase::st execute failed: Server message number=8156 severity=16 state=1 line=1 server=SQLB29 text=The column 'id' was specified multiple times for 'me'.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.id" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.name" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.email" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.phone" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.login" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.password" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.is_active" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.is_verifier" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.arm_length" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.eye_height" could not be bound.
Server message number=4104 severity=16 state=1 line=1 server=SQLB29 text=The multi-part identifier "surveyor.year_joined" could not be bound.
Server message number=8156 severity=16 state=1 line=1 server=SQLB29 text=The column 'id' was specified multiple times for 'orig_query'.
Server message number=8156 severity=16 state=1 line=1 server=SQLB29 text=The column 'id' was specified multiple times for 'rno_subq'.
 [for Statement " 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, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined FROM (SELECT TOP 100 PERCENT me.id, me.surveyor_id, me.survey_site_id, me.year, surveyor.id, surveyor.name, surveyor.email, surveyor.phone, surveyor.login, surveyor.password, surveyor.is_active, surveyor.is_verifier, surveyor.arm_length, surveyor.eye_height, surveyor.year_joined 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 15 "] at /usr/local/share/perl/5.10.0/DBIx/Class/Schema.pm line 1026