[Dbix-class] Error when using distinct => 1 with select => ...

Octavian Rasnita orasnita at gmail.com
Mon Jan 12 06:54:36 GMT 2015


Hello,

I tried to use the following select query, but it gives an error:

use TB::Schema;
$ENV{DBIC_TRACE}++;

my $schema = TB::Schema->connect( "dbi:mysql:intranet", "root" );

my $rs = $schema->resultset('Performance')->search( {}, {
    select => [ { date => 'date_time' } ],
    as => [ 'date' ],
    distinct => 1,
} );

$rs->all;

This gives the following result:

SELECT DATE( date_time ) FROM performance me GROUP BY :
DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st 
execute failed: You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near '' 
at line 1 [for Statement "SELECT DATE( date_time ) FROM performance me GROUP 
BY "] at D:\test_dbic\test.pl line 18

If I replace the line:
    distinct => 1,

with:
    group_by => [ { date => 'date_time' } ],

Then it works fine and it prints the query:
SELECT DATE( date_time ) FROM performance me GROUP BY DATE( date_time ):

Isn't the first way using distinct => 1 recommended, or there is a bug in 
DBIC?

The Result class with POD and other non-important columns removed is:

package TB::Schema::Result::Performance;

use Moose;
use MooseX::NonMoose;
use MooseX::MarkAsMethods autoclean => 1;
extends 'DBIx::Class::Core';

__PACKAGE__->load_components("InflateColumn::DateTime");
__PACKAGE__->table("performance");

__PACKAGE__->add_columns(
  "id",
  {
    data_type => "integer",
    extra => { unsigned => 1 },
    is_auto_increment => 1,
    is_nullable => 0,
  },
  "date_time",
  {
    data_type => "datetime",
    datetime_undef_if_invalid => 1,
    is_nullable => 0,
  },
#... other fields
);

__PACKAGE__->set_primary_key("id");
__PACKAGE__->meta->make_immutable;
1;

Thanks

--Octavian




More information about the DBIx-Class mailing list