Hi all, I recently upgraded to DBIx::Class 0.08123, and paging does not work anymore.
I get the following error: query: Error while executing query:DBIx::Class::ResultSet::next(): Generic Subquery Limit order criteria column 'me.start_time' must be unique (no unique constraint found) at VTOM/Repo2.pm line 322 at WEB/Action.pm line 119 I modified to order by clause to use a column with a unique contraint, but the generated SQL is painfully slow: SELECT COUNT( * ) FROM job_exec me JOIN host host ON host.id = me.host_id JOIN users users ON users.id = me.users_id JOIN job job ON job.id = me.job_id JOIN application application ON application.id = job.application_id JOIN environment environment ON environment.id = application.environment_id WHERE ( return_status NOT IN ( ? ) ): '0' SELECT environment_name, start_time, end_time, id, return_status, application_name, job_name, duration FROM ( SELECT environment.name AS environment_name, me.start_time, me.end_time, me.id, me.return_status, application.name AS application_name, job.name AS job_name, me.duration FROM job_exec me JOIN host host ON host.id = me.host_id JOIN users users ON users.id = me.users_id JOIN job job ON job.id = me.job_id JOIN application application ON application.id = job.application_id JOIN environment environment ON environment.id = application.environment_id WHERE ( return_status NOT IN ( ? ) ) ) me WHERE ( SELECT COUNT(*) FROM job_exec rownum__emulation WHERE rownum__emulation.id > me.id ) BETWEEN 690 AND 719 ORDER BY me.id DESC : '0' FYI, here's the generated SQL using the previous version: SELECT COUNT( * ) FROM job_exec me JOIN host host ON host.id = me.host_id JOIN users users ON users.id = me.users_id JOIN job job ON job.id = me.job_id JOIN application application ON application.id = job.application_id JOIN environment environment ON environment.id = application.environment_id WHERE ( ( environment.name = ? AND return_status NOT IN ( ? ) ) ): 'paris_divers', '0' SELECT environment.name, me.start_time, me.end_time, me.id, me.return_status, application.name, job.name, me.duration FROM job_exec me JOIN host host ON host.id = me.host_id JOIN users users ON users.id = me.users_id JOIN job job ON job.id = me.job_id JOIN application application ON application.id = job.application_id JOIN environment environment ON environment.id = application.environment_id WHERE ( ( environment.name = ? AND return_status NOT IN ( ? ) ) ) ORDER BY start_time desc: 'paris_divers', '0' The new SQL comes straight from DBIx::Class::SQLAHacks, but I dont know where the old sql comes from... Is there a way to go back to the old method? I'm not familiar enough with the codebase and the code flow to modify it, but I'm using Sybase 15, so for this operations, I guess scrollable cursors could be used. What shall I modify to do so? Thanks
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
