[Dbix-class] $rs->count fails with multiple group_by

[Noel Burton-Krahn]

If I search with multiple columns in a group_by clause, MySQL dies:

$rs = $schema->resultset('Measurement')->search({}, {
                select =>    [ {sum => 'value'}, qw(date type) ],
                as =>        [ 'value', qw(date type) ],
                group_by =>  [ qw(date type) ],
                order_by =>  [ qw(date type) ],
                                        });
$rs->count();

DBIx::Class::ResultSet::count(): DBI Exception: DBD::mysql::st execute
failed: Operand should contain 1 column(s) [for Statement "SELECT
COUNT( DISTINCT( date, type ) ) FROM measurement me"] at
./t/dbix_select_count_group_by.t line 115

I'll include a full test program below.

DBIx emulates count(*) by constructing a clause like
"count(distinct(col, ...))" which MySQL (and SQLite I believe) don't
like.  You could do a count(*) from those databases by doing the
original SQL as a subselect, like so:

select count(*) from (SELECT SUM( value ), date, type FROM measurement
me GROUP BY date, type ORDER BY date, type) x;

That is, generate the SQL you would use to select the rows, and wrap
it in a "select count(*) from ( ... ) x".

I've been digging through the DBIx source to find out where the
original SQL gets generated.  Any pointers?

~Noel

---------------------------------------------------------------------------------------------------------



#! /usr/bin/perl -w
=head1 NAME

 dbix_select_count_group_by.t - $rs->count fails with multiple group_by

=head1 AUTHOR

  Noel Burton-Krahn <n...@burton-krahn.com>

=cut

use strict;
use warnings;

#----------------------------------------------------------------------
package My::DBIx::Class;
use base qw/DBIx::Class/;

__PACKAGE__->load_components(qw/PK::Auto Core/);

use overload '""' => 'dump';

sub define {
        my($val, $def) = @_;
        return defined($val) ? $val : $def;
}

sub dump {
        my($self) = shift;
        return join(" ", map { "$_=" . define($self->get_column($_), "NULL")
} $self->columns);
}

#----------------------------------------------------------------------
package MySchema::Measurement;
use base qw/My::DBIx::Class/;
__PACKAGE__->table('measurement');
__PACKAGE__->add_columns(qw(id type date value));
__PACKAGE__->set_primary_key('id');

#----------------------------------------------------------------------
package MySchema;
use base qw/DBIx::Class::Schema/;

__PACKAGE__->load_classes({
        'MySchema' => [ qw(Measurement) ],
});

#----------------------------------------------------------------------
package MyTest;
use Test::More tests => 5;
use DateTime;
use Data::Dumper;

# create a mysql database to test with
system(<<'EOS');
mysqladmin -f drop mytest >/dev/null 2>&1

mysqladmin create mytest

mysql mytest <<ESQL
create table measurement (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  ,type VARCHAR(64) NOT NULL
  ,date DATETIME NOT NULL
  ,value DECIMAL(16,4) NOT NULL
) ENGINE=INNODB;
ESQL

#mysql mytest <<ESQL
#show tables;
#show create table person;
#show create table address;
#ESQL

EOS
        ;
is($?, 0, "create database");

# connect
my $schema = MySchema->connect("dbi:mysql:mytest", 'xxx', 'yyy')
        or die("connect: $!");
ok($schema, "connect to db");

my $rs;
my $measurement;
my $count = 100;
my @types = qw(red green blue);
foreach (1..$count) {
        my $date = DateTime->now();
        $date->add(days => rand(60)-30);
        my $value = rand(100);
        my $type = $types[rand(@types)];
        $measurement = $schema->resultset('Measurement')->create({
                type => $type,
                date => $date,
                value => $value});
}
ok($measurement, "created count=$count measurements");

$rs = $schema->resultset('Measurement')->search({}, {
                select =>    [ {sum => 'value'}, qw(date type) ],
                as =>        [ 'value', qw(date type) ],
                group_by =>  [ qw(date type) ],
                order_by =>  [ qw(date type) ],
                                        });
$schema->storage->debug(1);

while( my $row = $rs->next() ) {
        $measurement = $row;
}
ok($rs, "found Measurement: $measurement");

$count = $rs->count();
ok($count, "counted measurements: count=$count");

_______________________________________________
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/dbix-class@lists.scsys.co.uk