Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 44860
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: mfawcett [...] bigpond.net.au
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08103



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



Download (untitled) / with headers
text/plain 1014b
On Wed Apr 08 01:40:09 2009, mfawcett@bigpond.net.au wrote: Show quoted text
> 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. > > 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 >
Hi Mike, The version you are currently using is unfortunately rather old. You would want to at least upgrade to 08013 which will pull in a rewritten SQL::Abstract, which may very well solve your problem. In any case I would encourage you to try our developer release 08099_08 aka 08100 RC2. Although we are not aware of any issues with it, we are in great need of field testers to validate our assumptions about the quality of this release. The sooner I receive *some* feedback from anyone, the faster 08100 will become a reality.
Subject: Re: [rt.cpan.org #44860] DBIx generates incorrect SQL with DISTINCT keyword
Date: Thu, 09 Apr 2009 17:34:35 +1000
To: bug-DBIx-Class [...] rt.cpan.org
From: Mike Fawcett <mfawcett [...] bigpond.net.au>
Hi Peter,

I have installed DBIx 08013 and  identified the source of my problem  in the module DBIx::Class::Storage::DBI.pm.
When I make the change indicated in red below my problem is resolved.
I'm not sure of the wider implications of the change. I can't offhand think of a situation in SQL in which the brackets would be required, but presumably they were put there for some good reason initially.

I'd be happy to try your developer release, but I'm not quite sure how to get hold of it.

regards
Mike Fawcett

sub _recurse_fields {
  my ($self, $fields, $params) = @_;
  my $ref = ref $fields;
  return $self->_quote($fields) unless $ref;
  return $$fields if $ref eq 'SCALAR';

  if ($ref eq 'ARRAY') {
    return join(', ', map {
      $self->_recurse_fields($_)
        .(exists $self->{rownum_hack_count} && !($params && $params->{no_rownum_hack})
          ? ' AS col'.$self->{rownum_hack_count}++
          : '')
      } @$fields);
  } elsif ($ref eq 'HASH') {
    foreach my $func (keys %$fields) {
      return $self->_sqlcase($func)
        . ' ' .$self->_recurse_fields($fields->{$func}). ' ' if $func =~ /distinct/i ; # Mike Fawcett
      return $self->_sqlcase($func)
        .'( '.$self->_recurse_fields($fields->{$func}).' )';
    }
  }
}


Peter Rabbitson via RT wrote: Show quoted text
<URL: https://rt.cpan.org/Ticket/Display.html?id=44860 >

On Wed Apr 08 01:40:09 2009, mfawcett@bigpond.net.au wrote:
  
 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.

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

    
Hi Mike,

The version you are currently using is unfortunately rather old. You
would want to at least upgrade to 08013 which will pull in a rewritten
SQL::Abstract, which may very well solve your problem.

In any case I would encourage you to try our developer release 08099_08
aka 08100 RC2. Although we are not aware of any issues with it, we are
in great need of field testers to validate our assumptions about the
quality of this release. The sooner I receive *some* feedback from
anyone, the faster 08100 will become a reality.

  
Download (untitled) / with headers
text/plain 920b
On Thu Apr 09 03:34:51 2009, mfawcett@bigpond.net.au wrote: Show quoted text
> Hi Peter, > > I have installed DBIx 08013 and identified the source of my problem in > the > module DBIx::Class::Storage::DBI.pm. > When I make the change indicated in red below my problem is resolved. > I'm not sure of the wider implications of the change. I can't offhand > think of > a situation in SQL in which the brackets would be required, but > presumably they > were put there for some good reason initially.
This pat of the code needs heavy refactoring. See below. Show quoted text
> I'd be happy to try your developer release, but I'm not quite sure how > to get > hold of it. >
Please download and install http://search.cpan.org/CPAN/authors/id/R/RI/RIBASUSHI/DBIx-Class-0.08099_08.tar.gz. If ou have never installed a modul manually, `cpanp <above url>` should do it for you. It is very possible that this problem was already identified and fixed. Cheers
Subject: Re: [rt.cpan.org #44860] DBIx generates incorrect SQL with DISTINCT keyword
Date: Thu, 09 Apr 2009 20:41:18 +1000
To: bug-DBIx-Class [...] rt.cpan.org
From: Mike Fawcett <mfawcett [...] bigpond.net.au>
Hi Peter,

I've installed the 0.08099_08 development release and the problem I reported is not resolved. The relevant lines in DBI.pm are unaltered.
I can certainly live with the minor hack I have made that solves my immediate problem, and of course I will report any other issues I come across. If you'd like me top be more systematic in testing this release, please let me know how I can help.

cheers
Mike Fawcett

Peter Rabbitson via RT wrote: Show quoted text
<URL: https://rt.cpan.org/Ticket/Display.html?id=44860 >

On Thu Apr 09 03:34:51 2009, mfawcett@bigpond.net.au wrote:
  
 Hi Peter,

I have installed DBIx 08013 and identified the source of my problem in
the
module DBIx::Class::Storage::DBI.pm.
When I make the change indicated in red below my problem is resolved.
I'm not sure of the wider implications of the change. I can't offhand
think of
a situation in SQL in which the brackets would be required, but
presumably they
were put there for some good reason initially.
    
This pat of the code needs heavy refactoring. See below. 

  
I'd be happy to try your developer release, but I'm not quite sure how
to get
hold of it.

    
Please download and install
http://search.cpan.org/CPAN/authors/id/R/RI/RIBASUSHI/DBIx-Class-0.08099_08.tar.gz.
If ou have never installed a modul manually, `cpanp <above url>` should
do it for you. It is very possible that this problem was already
identified and fixed.

Cheers

  
Download (untitled) / with headers
text/plain 787b
On Thu Apr 09 06:42:34 2009, mfawcett@bigpond.net.au wrote: Show quoted text
> Hi Peter, > > I've installed the 0.08099_08 development release and the problem I > reported is > not resolved. The relevant lines in DBI.pm are unaltered. > I can certainly live with the minor hack I have made that solves my > immediate > problem, and of course I will report any other issues I come across.
Hi, There is ongoing work to reimplement DISTINCT internally as to not use it at all, so all of our discussion above is moot. This should hit trunk in a month or so. In the meantime this is what the author had to say: <arcanez> you might also tell Mike Fawcett he can select => ['member_id', 'last_name'], group_by => ['member_id', 'last_name'] to emulate DISTINCT <arcanez> it very well may be faster Cheers
Subject: Re: [rt.cpan.org #44860] DBIx generates incorrect SQL with DISTINCT keyword
Date: Mon, 27 Apr 2009 18:48:29 +1000
To: bug-DBIx-Class [...] rt.cpan.org
From: Mike Fawcett <mfawcett [...] bigpond.net.au>
Thanks Peter. I'll keep an eye on new releases.
cheers
Mike

Peter Rabbitson via RT wrote: Show quoted text
<URL: https://rt.cpan.org/Ticket/Display.html?id=44860 >

On Thu Apr 09 06:42:34 2009, mfawcett@bigpond.net.au wrote:
  
 Hi Peter,

I've installed the 0.08099_08 development release and the problem I
reported is
not resolved. The relevant lines in DBI.pm are unaltered.
I can certainly live with the minor hack I have made that solves my
immediate
problem, and of course I will report any other issues I come across.
    
Hi,

There is ongoing work to reimplement DISTINCT internally as to not use
it at all, so all of our discussion above is moot. This should hit trunk
in a month or so. In the meantime this is what the author had to say:

<arcanez> you might also tell Mike Fawcett he can select =>
['member_id', 'last_name'], group_by => ['member_id', 'last_name'] to
emulate DISTINCT
<arcanez> it very well may be faster

Cheers



  
Stalling until branches/count_distinct merges
Download (untitled) / with headers
text/plain 1.2k
On Mon Apr 27 04:48:45 2009, mfawcett@bigpond.net.au wrote: Show quoted text
> Thanks Peter. I'll keep an eye on new releases. > cheers > Mike > > Peter Rabbitson via RT wrote: > > <URL: https://rt.cpan.org/Ticket/Display.html?id=44860 > > > On Thu Apr 09 06:42:34 2009, mfawcett@bigpond.net.au wrote: > > > Hi Peter, > > I've installed the 0.08099_08 development release and the problem I > reported is > not resolved. The relevant lines in DBI.pm are unaltered. > I can certainly live with the minor hack I have made that solves my > immediate > problem, and of course I will report any other issues I come across. > > > Hi, > > There is ongoing work to reimplement DISTINCT internally as to not use > it at all, so all of our discussion above is moot. This should hit trunk > in a month or so. In the meantime this is what the author had to say: > > <arcanez> you might also tell Mike Fawcett he can select => > ['member_id', 'last_name'], group_by => ['member_id', 'last_name'] to > emulate DISTINCT > <arcanez> it very well may be faster > > Cheers
Please checkout and try: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/joined_count This should be the final fix, but is not in trunk yet, due to lack of testing volunteers.
Download (untitled) / with headers
text/plain 203b
DBIx::Class 0.08103 just released on CPAN fixes this RT. The multi-distinct syntax is deprecated in favor of group_by or distinct => 1. Refer to the documentation of DBIx::Class::ResultSet for more info.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.