Bug #56257 for DBIx-Class: Postgres, select count + for update generates sql error

Sat Apr 03 15:54:50 2010 mrjobson [...] gmail.com - Ticket created

Subject:

Postgres, select count + for update generates sql error

my $lop = $schema->resultset('Lop')->search( { data_checked => {'<' => \'( CURRENT_DATE - INTERVAL \'1 week\' )' }, }, { select => ['id'], order_by => { -asc => ['id'] }, rows => 1, for => 'update' }, ); print $lop; # i want it to do count and to generate eror SET search_path TO contrib, public; SELECT COUNT( * ) FROM lop me WHERE ( ( data_checked < ( CURRENT_DATE - INTERVAL '1 week' ) ) ) FOR UPDATE DBIx::Class::ResultSet::count(): DBI Exception: DBD::Pg::st execute failed: ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate functions [for Statement "SELECT COUNT( * ) FROM lop me WHERE ( ( data_checked < ( CURRENT_DATE - INTERVAL '1 week' ) ) ) FOR UPDATE" ] SQLA - 1.63 DBIC - 0.08120

Sat Apr 03 20:22:59 2010 ribasushi [...] leporine.io - Correspondence added

On Sat Apr 03 15:54:50 2010, mrjobson@gmail.com wrote: Show quoted text

> my $lop = $schema->resultset('Lop')->search( > { > data_checked => {'<' => \'( CURRENT_DATE - INTERVAL \'1 week\'

)' }, Show quoted text

> }, > { > select => ['id'], > order_by => { -asc => ['id'] }, > rows => 1, > for => 'update' > }, > ); > print $lop; # i want it to do count and to generate eror > > SET search_path TO contrib, public; > SELECT COUNT( * ) FROM lop me WHERE ( ( data_checked < ( CURRENT_DATE - > INTERVAL '1 week' ) ) ) FOR UPDATE > DBIx::Class::ResultSet::count(): DBI Exception: DBD::Pg::st execute > failed: ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate > functions [for Statement "SELECT COUNT( * ) FROM lop me WHERE ( ( > data_checked < ( CURRENT_DATE - INTERVAL '1 week' ) ) ) FOR UPDATE" ] > > SQLA - 1.63 > DBIC - 0.08120

So dbic should just drop the for attribute on ->count? Just double-checking.

Sat Apr 03 20:23:00 2010 The RT System itself - Status changed from 'new' to 'open'

Mon Apr 05 06:06:36 2010 mrjobson [...] gmail.com - Correspondence added

From:

mrjobson [...] gmail.com

On Sat Apr 03 20:22:59 2010, RIBASUSHI wrote: Show quoted text

> On Sat Apr 03 15:54:50 2010, mrjobson@gmail.com wrote:

> > my $lop = $schema->resultset('Lop')->search( > > { > > data_checked => {'<' => \'( CURRENT_DATE - INTERVAL \'1 week\'

> )' },

> > }, > > { > > select => ['id'], > > order_by => { -asc => ['id'] }, > > rows => 1, > > for => 'update' > > }, > > ); > > print $lop; # i want it to do count and to generate eror > > > > SET search_path TO contrib, public; > > SELECT COUNT( * ) FROM lop me WHERE ( ( data_checked < ( CURRENT_DATE - > > INTERVAL '1 week' ) ) ) FOR UPDATE > > DBIx::Class::ResultSet::count(): DBI Exception: DBD::Pg::st execute > > failed: ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate > > functions [for Statement "SELECT COUNT( * ) FROM lop me WHERE ( ( > > data_checked < ( CURRENT_DATE - INTERVAL '1 week' ) ) ) FOR UPDATE" ] > > > > SQLA - 1.63 > > DBIC - 0.08120

> > So dbic should just drop the for attribute on ->count? Just

double-checking. Yes, it should.

Tue May 04 04:02:07 2010 ribasushi [...] leporine.io - Correspondence added

On Mon Apr 05 06:06:36 2010, mrjobson@gmail.com wrote: Show quoted text

> On Sat Apr 03 20:22:59 2010, RIBASUSHI wrote:

> > On Sat Apr 03 15:54:50 2010, mrjobson@gmail.com wrote:

> > > my $lop = $schema->resultset('Lop')->search( > > > { > > > data_checked => {'<' => \'( CURRENT_DATE - INTERVAL \'1 week\'

> > )' },

> > > }, > > > { > > > select => ['id'], > > > order_by => { -asc => ['id'] }, > > > rows => 1, > > > for => 'update' > > > }, > > > ); > > > print $lop; # i want it to do count and to generate eror > > > > > > SET search_path TO contrib, public; > > > SELECT COUNT( * ) FROM lop me WHERE ( ( data_checked < (

CURRENT_DATE - Show quoted text

> > > INTERVAL '1 week' ) ) ) FOR UPDATE > > > DBIx::Class::ResultSet::count(): DBI Exception: DBD::Pg::st execute > > > failed: ERROR: SELECT FOR UPDATE/SHARE is not allowed with aggregate > > > functions [for Statement "SELECT COUNT( * ) FROM lop me WHERE ( ( > > > data_checked < ( CURRENT_DATE - INTERVAL '1 week' ) ) ) FOR UPDATE" ] > > > > > > SQLA - 1.63 > > > DBIC - 0.08120

> > > > So dbic should just drop the for attribute on ->count? Just

> double-checking. > > Yes, it should.

Fixed in [1]. Release coming in a couple of weeks. [1] http://dev.catalystframework.org/svnweb/bast/revision?rev=9297

Tue May 04 04:02:08 2010 ribasushi [...] leporine.io - Status changed from 'open' to 'patched'

Fri Jun 04 07:13:32 2010 ribasushi [...] leporine.io - Status changed from 'patched' to 'resolved'

Fri Jun 04 07:13:32 2010 ribasushi [...] leporine.io - Fixed in 0.08122 added