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