Bug #81378 for DBIx-Class: Calling 'delete' on a search resultset generates SQL which is incompatible with MySQL (5.0)

Fri Nov 23 10:41:10 2012 DRAXIL [...] cpan.org - Ticket created

Subject:

Calling 'delete' on a search resultset generates SQL which is incompatible with MySQL (5.0)

I have a problem where DBIx::Class generates SQL which doesn't work in mysql (5.0) when I call ->delete on a resultset which has had a search applied. You get an error like: DBIx::Class::ResultSet::delete(): DBI Exception: DBD::mysql::st execute failed: You can't specify target table 'X1' for update in FROM clause [for Statement "DELETE FROM X1 WHERE ( id IN ( SELECT me.id FROM X1 me WHERE ( id > ? ) GROUP BY me.id ) )" with ParamValues: 0=5] at ./rep.pl line 10 I can completely reproduce this with the attached test case which contains a small mysql schema, a couple of classes and a script which simply produce the problem for me. In case it's significant mysql --version on my server produces: mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

Subject:

repbug.tgz

Download repbug.tgz
application/x-compressed-tar 713b

Message body not shown because it is not plain text.

Fri Nov 23 11:54:31 2012 ribasushi [...] leporine.io - Correspondence added

This is something I happened to work on just couple days ago, but didn't have a chance to fully finish. A fix should be available within this weekend.

Fri Nov 23 11:54:34 2012 The RT System itself - Status changed from 'new' to 'open'

Mon Nov 26 09:16:46 2012 DRAXIL [...] cpan.org - Correspondence added

Excellent news. Thanks.

Tue Dec 11 04:16:39 2012 ser.khomutov [...] gmail.com - Ticket #81897: Ticket created

Subject:

limit in subquery for MySQL

Hello, DBIx::Class - 0.08204 Perl v5.10.1 (*) built for x86_64-linux-gnu-thread-multi Linux 2.6.32-5-amd64 Mysql Server Version 5.5.14 my $history_rs = $self->schema->resultset('UserHistory'); my $res = $history_rs->search(undef, { order_by => { -asc =>'timestamp'} , rows => 500, page => 1 } ); $res->delete; "'DBIx::Class::ResultSet::delete(): DBI Exception: DBD::mysql::st execute failed: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' [for Statement "DELETE FROM userHistory WHERE ( id IN ( SELECT me.id FROM userHistory me ORDER BY timestamp ASC LIMIT ? ) )" with ParamValues?: 0=500]" DBIx:Class generate statement "DELETE FROM userHistory WHERE ( id IN ( SELECT me.id FROM userHistory me ORDER BY timestamp ASC LIMIT 500" - this statement doesn't yet work for mysql .

Tue Dec 11 05:57:13 2012 ribasushi [...] leporine.io - Ticket #81897: Correspondence added

This is a duplicate of 81378 - fix is forthcoming. Merging tickets.

Tue Dec 11 05:57:17 2012 The RT System itself - Ticket #81897: Status changed from 'new' to 'open'

Tue Dec 11 05:57:29 2012 ribasushi [...] leporine.io - Ticket #81897: Merged into ticket #81378

Tue Dec 11 05:57:29 2012 ribasushi [...] leporine.io - Merged into ticket #81378

Sun Jan 20 16:14:41 2013 ribasushi [...] leporine.io - Correspondence added

Finally patched up properly: http://lists.scsys.co.uk/pipermail/dbix-class-devel/2013-January/000232.html Should show up in the next release. Cheers

Sun Jan 20 16:14:46 2013 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'

Mon Jan 21 04:43:42 2013 joe [...] draxil.uklinux.net - Correspondence added

Subject:	Re: [rt.cpan.org #81378] Calling 'delete' on a search resultset generates SQL which is incompatible with MySQL (5.0)
Date:	Mon, 21 Jan 2013 09:43:07 +0000
To:	bug-DBIx-Class [...] rt.cpan.org
From:	joe higton <joe [...] draxil.uklinux.net>

Good stuff, thanks.

Mon Jan 21 04:43:45 2013 The RT System itself - Status changed from 'patched' to 'open'

Mon Jan 21 04:49:07 2013 ser.khomutov [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #81378] Calling 'delete' on a search resultset generates SQL which is incompatible with MySQL (5.0)
Date:	Mon, 21 Jan 2013 13:48:57 +0400
To:	bug-DBIx-Class [...] rt.cpan.org
From:	Сергей Хомутов <ser.khomutov [...] gmail.com>

Thanx. 2013/1/21 joe higton via RT <bug-DBIx-Class@rt.cpan.org> Show quoted text

> <URL: https://rt.cpan.org/Ticket/Display.html?id=81378 > > > Good stuff, thanks. > >

Mon Jan 21 18:09:51 2013 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'

Wed Jan 23 07:00:01 2013 ribasushi [...] leporine.io - Status changed from 'patched' to 'resolved'

Wed Jan 23 07:00:02 2013 ribasushi [...] leporine.io - Fixed in 0.08205 added