[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 <noel at 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");