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

[Peter Rabbitson]

Alan Humphrey wrote:
>> 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

Cheers!

> 
> 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'

This is strange. What is the DSN envvar you are using?


> #   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
> # 

These seem to be related to the DSN as well.

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

This is my fault, I forgot to commit something - svn update and try again.