Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 81127
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: pmcdermott98 [...] gsb.columbia.edu
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08250



Subject: Results of get_column('xxx')->min incorrect on resultset using group_by
Date: Mon, 12 Nov 2012 21:07:27 +0000
To: bug-DBIx-Class [...] rt.cpan.org
From: Paul McDermott <pmcdermott98 [...] gsb.columbia.edu>
Download (untitled) / with headers
text/plain 2.1k
Bit wary about submitting this as DBIx::Class is pretty complex and I'm not expert, but I think the following is a bug (as it creates and SQL syntax error), and at least is idiosyncratic behaviour. Given a table 'prices' with columns (product_id, date, price): $schema->resultset('Prices')->search({ product_id => 1 }, { select => [ {year => 'date', -as => 'year'}, {count => 'date', -as => 'count'} ], as => [qw/year count/], group_by => ['year'], having => {count => {'>=' => 250}} }); corresponds to: SELECT YEAR(date) AS year, COUNT(date) AS count FROM prices WHERE product_id = 1 GROUP BY year HAVING count > 250; This is a table year count ---------------- 1984 253 1985 255 1986 251 1987 254 ... etc. applying ->get_column('year')->min (as I understand it) should extract the year column from the resultset and find the minimum, which in this example is 1984. However, $schema->resultset('Prices')->search({ product_id => 1 }, { select => [ {year => 'date', -as => 'year'}, {count => 'date', -as => 'count'} ], as => [qw/year count/], group_by => ['year'], having => {count => {'>=' => 250}} })->get_column('year')->min; produces the following SQL SELECT MIN(YEAR(date) AS year), COUNT(date) AS count FROM prices WHERE product_id = 1 GROUP BY year HAVING count > 250; which results in a syntax error in MySQL. I believe it should be generating something along the lines of: SELECT MIN(year) FROM (SELECT YEAR(date) AS year, COUNT(date) AS count FROM prices WHERE product_id = 1 GROUP BY year HAVING count > 250) AS A; More generally, while for a simple search of table (X,Y,Z) the strategy of doing SELECT MIN(X) to implement get_column('X')->min works, for any query using a group_by the SELECT MIN(X') FROM (subquery) format us needed. I'm sure the same would apply to other aggregation functions: SUM, MIN, COUNT, etc. Platform is linunx kernel 3.2.12 with MySQL 5.1 and DBIx::Class 0.81.960 The SQL examples above are not taken literally from DBIx:Class--I have simplified to make the point more clearly. Regards, Paul
Download (untitled) / with headers
text/plain 1.9k
On Mon Nov 12 16:07:40 2012, pmcdermott98@gsb.columbia.edu wrote: Show quoted text
> Bit wary about submitting this as DBIx::Class is pretty complex and I'm > not expert, but I think the following is a bug
This is definitely a bug, yes. Show quoted text
> > Given a table 'prices' with columns (product_id, date, price): > > $schema->resultset('Prices')->search({ > product_id => 1 > }, { > select => [ > {year => 'date', -as => 'year'}, > {count => 'date', -as => 'count'} > ], > as => [qw/year count/], > group_by => ['year'], > having => {count => {'>=' => 250}} > }); > > corresponds to: > > SELECT YEAR(date) AS year, COUNT(date) AS count FROM prices WHERE > product_id = 1 GROUP BY year HAVING count > 250; > > This is a table > > year count > ---------------- > 1984 253 > 1985 255 > 1986 251 > 1987 254 > ... etc. > > applying ->get_column('year')->min (as I understand it) should extract > the year column from the resultset and find the minimum, which in this > example is 1984. > > However, > > $schema->resultset('Prices')->search({ > product_id => 1 > }, { > select => [ > {year => 'date', -as => 'year'}, > {count => 'date', -as => 'count'} > ], > as => [qw/year count/], > group_by => ['year'], > having => {count => {'>=' => 250}} > })->get_column('year')->min; > > produces the following SQL > > SELECT MIN(YEAR(date) AS year), COUNT(date) AS count FROM prices WHERE > product_id = 1 GROUP BY year HAVING count > 250; > > which results in a syntax error in MySQL. > > I believe it should be generating something along the lines of: > > SELECT MIN(year) FROM (SELECT YEAR(date) AS year, COUNT(date) AS count > FROM prices WHERE product_id = 1 GROUP BY year HAVING count > 250) AS A;
Can you please turn this into a test - add one to t/88result_set_column.t (use any of the existing ones as a base). Then if you feel adventurous I can point out where the fix actually should live. Cheers
Subject: Re: [rt.cpan.org #81127] Results of get_column('xxx')->min incorrect on resultset using group_by
Date: Mon, 03 Dec 2012 19:16:33 +0000
To: bug-DBIx-Class [...] rt.cpan.org
From: Paul McDermott <pmcdermott98 [...] gsb.columbia.edu>
Download (untitled) / with headers
text/plain 1.1k
On 03/12/12 14:17, Peter Rabbitson via RT wrote: Show quoted text
> Can you please turn this into a test - add one to > t/88result_set_column.t (use any of the existing ones as a base). Then > if you feel adventurous I can point out where the fix actually should live.
Try this test mod to 88result_set_column.t: 155a156,186 Show quoted text
> ############## > # Bug # 81127 > ############## > $rs->reset; > > # This should work > my $q_rs = $schema->resultset("Track"); > my $max1 = $q_rs->get_column('cd')->max; > is($max1, 5, "Correct: Max cd in Track is 5"); > > # Max of a get_column() of an intermediate table -- this is the bug > # Generate an intermediate summary of track counts by CD. i.e. > # CD count > # 1 3 > # 2 3 > # 3 3 > # 4 3 > # 5 3 > # (Some more variety here might be nicer!) > my $r_rs = $q_rs->search({}, { > select => [ > 'cd', > {count => 'trackid', -as => 'count'} > ], > as => [qw/cd count/], > group_by => ['cd'] > }); > # Get the maximum number of tracks on a CD (i.e. 3) > my $max2 = $r_rs->get_column('count')->max; > is($max2, 3, "Correct: Max # of track per CD is 3"); >
Download (untitled) / with headers
text/plain 247b
Finally patched up: https://github.com/dbsrgits/dbix-class/commit/3214abc71 . Note that it went into the experimental series, I am not so sure about it going into current stable. 0.08242-TRIAL will hit CPAN shortly and you can try it out. Cheers!
And production version resolving this is now on CPAN.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.