| Subject: | DBIx generates incorrect SQL with DISTINCT keyword |
| Date: | Wed, 08 Apr 2009 15:39:52 +1000 |
| To: | bug-DBIx-Class [...] rt.cpan.org |
| From: | Mike Fawcett <mfawcett [...] bigpond.net.au> |
The following appears the be a bug in generating SQL where the select
list contains the DISTINCT keyword, inserting invalid brackets around
the select list columns.
Postgresql database: (postgres version 8.1.5-13)
create table member(
member_id integer primary key,
last_name text not null,
first_name text not null
) ;
Perl program: (perl version is 5.8.8, DBIx::Class version is .08010)
use strict;
use warnings;
use FindBin;
use lib "$FindBin::Bin/../lib";
use Fred::Schema ;
my $schema = Fred::Schema->connect("dbi:Pg:dbname=fred","fred","") ;
my $storage = $schema->storage ;
$storage->debug(1) ;
my @rows = $schema->resultset('Member')->search(
{},
{ select => [{ distinct => ['member_id','last_name']}],
as => ['member_id','last_name']
}
) ;
Program debugging output:
SELECT DISTINCT( member_id, last_name ) FROM member me:
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: ERROR: could not identify an ordering operator for type record
HINT: Use an explicit ordering operator or modify the query.
[for Statement "SELECT DISTINCT( member_id, last_name ) FROM member me"] at ./script/distinct.pl line 13
Analysis:
The brackets surrounding the select list column names are invalid according to the SQL definition.
SELECT DISTINCT ( member_id, last_name ) FROM member me:
The correct SQL is:
SELECT DISTINCT member_id, last_name FROM member me
Regards
Mike Fawcett
Postgresql database: (postgres version 8.1.5-13)
create table member(
member_id integer primary key,
last_name text not null,
first_name text not null
) ;
Perl program: (perl version is 5.8.8, DBIx::Class version is .08010)
use strict;
use warnings;
use FindBin;
use lib "$FindBin::Bin/../lib";
use Fred::Schema ;
my $schema = Fred::Schema->connect("dbi:Pg:dbname=fred","fred","") ;
my $storage = $schema->storage ;
$storage->debug(1) ;
my @rows = $schema->resultset('Member')->search(
{},
{ select => [{ distinct => ['member_id','last_name']}],
as => ['member_id','last_name']
}
) ;
Program debugging output:
SELECT DISTINCT( member_id, last_name ) FROM member me:
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Pg::st execute failed: ERROR: could not identify an ordering operator for type record
HINT: Use an explicit ordering operator or modify the query.
[for Statement "SELECT DISTINCT( member_id, last_name ) FROM member me"] at ./script/distinct.pl line 13
Analysis:
The brackets surrounding the select list column names are invalid according to the SQL definition.
SELECT DISTINCT ( member_id, last_name ) FROM member me:
The correct SQL is:
SELECT DISTINCT member_id, last_name FROM member me
Regards
Mike Fawcett