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

[Alan Humphrey]

> 
> Indeed, it was rather hard to reproduce it, because we kept looking in
> the wrong place. Please try [1]
> 
> Cheers
> 

Peter - 

This is great!  Sorts are coming back as expected.  Running the tests, however, I'm getting errors.  See below for a snippet of the output.

The test is running against MSSQL2005-Express.

Thanks again for keeping after this!

- Alan



t/746mssql.......................................
#   Failed test 'The object isa DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server'
#   at t/746mssql.t line 29.
#     The object isn't a 'DBIx::Class::Storage::DBI::ODBC::Microsoft_SQL_Server' it's a 'DBIx::Class::Storage::DBI::Sybase::Microsoft_SQL_Server'

#   Failed test 'populate with PKs supplied ok'
#   at t/746mssql.t line 221.
# died: Transaction aborted: DBIx::Class::Schema::populate(): DBI Exception: DBD::Sybase::db do failed: Server message number=3902 severity=16 state=1 line=1 server=ALAN-PC\SQLEXPRESS text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
#  [for Statement "COMMIT"] at t/746mssql.t line 203
#  Rollback failed: DBIx::Class::Storage::TxnScopeGuard::DESTROY(): DBI Exception: DBD::Sybase::db do failed: Server message number=3903 severity=16 state=1 line=1 server=ALAN-PC\SQLEXPRESS text=The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
#  [for Statement "ROLLBACK"] at /usr/local/share/perl/5.10.0/Test/Exception.pm line 104

#   Failed test 'populate without PKs supplied ok'
#   at t/746mssql.t line 255.
# died: Transaction aborted: DBIx::Class::Schema::populate(): DBI Exception: DBD::Sybase::db do failed: Server message number=3902 severity=16 state=1 line=1 server=ALAN-PC\SQLEXPRESS text=The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
#  [for Statement "COMMIT"] at t/746mssql.t line 239
#  Rollback failed: DBIx::Class::Storage::TxnScopeGuard::DESTROY(): DBI Exception: DBD::Sybase::db do failed: Server message number=3903 severity=16 state=1 line=1 server=ALAN-PC\SQLEXPRESS text=The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
#  [for Statement "ROLLBACK"] at /usr/local/share/perl/5.10.0/Test/Exception.pm line 104
# 
SELECT me.id, me.name FROM owners me ORDER BY name: 
SELECT me.id, me.name FROM (SELECT TOP 4294967296 me.id, me.name FROM owners me ORDER BY name) me: 

#   Failed test 'Sort is preserved across IN subqueries'
#   at t/746mssql.t line 298.
#     Structures begin differing at:
#          $got->[0] = '1'
#     $expected->[0] = '3'

#   Failed test 'Expected SQL executed'
#   at t/746mssql.t line 464.
# SQL expressions differ
#      got: (SELECT TOP 4294967296 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[id], [owner].[name] FROM ( SELECT * FROM ( SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index FROM (SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] FROM (SELECT TOP 4294967296 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price] FROM [books] [me] JOIN [owners] [owner] ON [owner].[id] = [me].[owner] WHERE ( ( [owner].[name] != ? AND [source] = ? ) ) ORDER BY [owner].[name]) [me]) [me] ) rno_subq WHERE rno__row__index BETWEEN 3 AND 9 ) [me] JOIN [owners] [owner] ON [owner].[id] = [me].[owner] WHERE ( ( [owner].[name] != ? AND [source] = ? ) ) ORDER BY [owner].[name])
# expected: (
#       SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], [owner].[id], [owner].[name]
#         FROM (
#           SELECT *
#             FROM (
#               SELECT [me].*, ROW_NUMBER() OVER( ORDER BY (SELECT(1)) ) AS rno__row__index
#                 FROM (
#                   SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
#                     FROM (
#                       SELECT TOP 100 PERCENT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
#                         FROM [books] [me]
#                         JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
#                       WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
#                       ORDER BY [owner].[name]
#                     ) [me]
#                 ) [me]
#             ) rno_subq
#           WHERE rno__row__index BETWEEN 3 AND 9
#         ) [me]
#         JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
#       WHERE ( ( [owner].[name] != ? AND [source] = ? ) )
#       ORDER BY [owner].[name]
#     )
# differing in :
# [ TOP 4294967296[me].[id],[me].[source],[me].[owner],[me].[title],[me].[price],[owner].[id],[owner].[name] ] != [ TOP 100 PERCENT[me].[id],[me].[source],[me].[owner],[me].[title],[me].[price],[owner].[id],[owner].[name] ]
# 
# Looks like you failed 5 tests of 53.