DBI:CSV SQL parsing problem

[rlewisuk]

Hello all.

I am having problems with DBI:CSV. I keep getting the following error:

"SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
DBD::CSV::db prepare failed: SQL ERROR: Bad table or column name 'col2 + col3' has chars not alphanumeric or underscore!
for Statement "SELECT AVG(col2 + col3) FROM mydata.csv" at test_script.pl line 11."

I have the following code to query a csv file.

#!/usr/bin/perl

use DBI;
use strict;
use warnings;

## Set up connection to csv data file as a database
my $dbh = DBI->connect("dbi:CSV:f_dir=./", "", "",
        {AutoCommit => 1, RaiseError => 1});

my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv");
$sth->execute();
my $value = $sth->fetchrow_array;

foreach (@value){
    print $_."\n";
}

$sth->finish();
$dbh->disconnect();

This works fine:

my $sth = $dbh->prepare("SELECT AVG(col3) FROM mydata.csv");

So does this:

my $sth = $dbh->prepare("SELECT (col2 + col3) FROM mydata.csv");

Just not this:

my $sth = $dbh->prepare("SELECT AVG(col2 + col3) FROM mydata.csv");

The mydata.csv file has the following in it:

col1,col2,col3,col4

575,480,192,5

388,485,194,5

379,447,210,5

504,465,215,0.8

356,439,219,0.7

481,435,225,0.6

Any idea what is going wrong? Any help would be appreciated.

[jZed]

The syntax that DBD::CSV supports is documented in SQL::Statement::Syntax. It clearly states that the supported argument for set functions is a column name, not a numeric expression:

set function ::=
             COUNT ( [DISTINCT|ALL] * )
           | COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] col_name )

So the short answer is, no, sorry, you can't currently do that like that in DBD::CSV. Depending on what you want to do, you may be able to build your own UDF (User-Defined-Function) to use here. See SQL::Statement::Function for details of building UDFs.

[erroneousBollock]

Any idea what is going wrong?

I'd say it's apparent that the SQL expression parser employed by DBD::CSV is unable to deal with such a complicated expression (either at all, or with the amount of type information at its disposal). Specifically, the parser expects a variable or a numeric literal as the argument to an aggregate function.

Assuming that's not the case, have you tried more parentheses ? some form of value coercion ?

-David

[jZed]

    > Assuming that's not the case, have you tried more parentheses ? 

Or perhaps sprinkling some chicken blood would help :-). The expected behavior is documented in this case so reading the docs is probably quicker (though perhaps not as much fun, depending on your definition of fun) than experimentation.

[ikegami]

Workaround:

my $sth = $dbh->prepare("SELECT AVG(col2), AVG(col3) FROM mydata.csv")
+;
$sth->execute();
my @avgs = $sth->fetchrow_array();
my $avg = $avgs[0] + $avgs[1];

[jZed]

Something like that might work. OTOH, I'm not totally sure what AVG(col1 +col2) should produce or whether it would be same as what you suggest. For example, since NULLs are eliminated, the two columns may have a different Count.

[tye]

Note that this gives different results if ever only one of col2 and col3 are NULL.

[rlewisuk]

No worries there, all columns have the same number of (non NULL) values.

[rlewisuk]

Thanks ikegami
This might be an option. I have tried

SELECT AVG(col2) + AVG(col3)...

Without success. If I use

my $avg = $avgs[0] + $avgs[1];

I'll first have to parse the SELECT statement for +,-,* and / and replace with commas and do the operation afterwards, but it certainly seems doable.