Does SELECT DISTINCT work with Perl's DBD::CSV?

Question

Hello,

I found a SELECT-example on the web. When I try it in my script I get this error-message:

Specifying DISTINCT when using aggregate functions isn't reasonable - ignored. at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 496.

#!/usr/bin/perl
use warnings;
use strict;
use DBI;

my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, AutoCommit => 1 } );
my $table = 'artikel';

my $array_ref = [   [ 'a_nr', 'a_name', 'a_preis' ],  
      [ 12, 'Oberhemd', 39.80, ],
      [ 22, 'Mantel', 360.00, ],
      [ 11, 'Oberhemd', 44.20, ],
      [ 13, 'Hose', 119.50, ],
     ];
$dbh->do( "CREATE TEMP TABLE $table AS IMPORT(?)", {}, $array_ref );

my $sth = $dbh->prepare( "SELECT DISTINCT a_name FROM $table" );
$sth->execute();
$sth->dump_results();
$dbh->disconnect();

Does SELECT DISTINCT not work with DBD::CSV or is something wrong with my script?

edit: The output is

'Oberhemd' 'Mantel' 'Oberhemd' 'Hose' 4 rows

I thought it should be

'Oberhemd' 'Mantel' 'Hose' 3 rows

Installed versions:

Perl : 5.010000 (x86_64-linux-thread-multi) OS : linux (2.6.31) DBI : 1.609 DBD::Sponge : 12.010002 DBD::SQLite : 1.25 DBD::Proxy : 0.2004 DBD::Gofer : 0.011565 DBD::File : 0.37 DBD::ExampleP : 12.010007 DBD::DBM : 0.03 DBD::CSV : 0.26

Answer 1

Note that the message about something being not reasonable is

1. Only a warning. Your script works nevertheless.
2. Confusing and non-sensical: you don't use any aggregate functions.

I smell a bug in either DBD::CSV or SQL::Statement.

Edit: DISTINCT is explicitly allowed in SQL::Statement

Answer 2

Hi This is an easily reproducable bug. SELECT data_display_mask FROM test.csv returns 200 plus rows. SELECT DISTINCT data_display_mask FROM test.csv returns the warning message and same 200 rows.

If i do an awk, sort -u for unique ( values of the row ) I get 36 values, which is what I would expect.

Certainly a bug in the code.

-Kanwar

perl -V Summary of my perl5 (revision 5 version 10 subversion 0) configuration: Platform: osname=linux, osvers=2.2.24-6.2.3, archname=i686-linux-thread-multi

DBD::CSV 0.26 SQL::Parser 1.23 DBI 1.609

example:

Specifying DISTINCT when using aggregate functions isn't reasonable - ignored. at /opt/perl2exe/perl5/lib/site_perl/5.10.0/SQL/Parser.pm line 496. 87060 87060 87060 87060

SQL used is SELECT DISTINCT entry_id FROM test.csv

Answer 3

Hi,

I met the same problem.

You can turn around this problem using a GROUP BY statement instead of DISTINCT.

This is just a turn around waiting for the resolution of a bug ...