DBD::CSV: Problem with userdefined functions

Question

From the SQL::Statement::Functions documentation:

Creating User-Defined Functions
...
More complex functions can make use of a number of arguments always passed to functions automatically. Functions always receive these values in @_:
sub FOO { my( $self, $sth, $rowhash, @params ); }

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

my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, } );
my $table = 'wages';
my $array_ref = [   [ 'id', 'number' ],  
            [ 0, 6900 ], 
            [ 1, 3200 ], 
            [ 2, 1800 ],    ];

$dbh->do( "CREATE TEMP TABLE $table AS import( ? )", {}, $array_ref );

sub routine {
    my $self = shift;
    my $sth = shift;
    my $rowhash = shift; #
    return $_[0] / 30; 
};

$dbh->do( "CREATE FUNCTION routine" );
my $sth = $dbh->prepare( "SELECT id, routine( number ) AS result FROM  $table" );
$sth->execute();
$sth->dump_results();

When I try this I get an error-message:

DBD::CSV::st execute failed: Use of uninitialized value $_[0] in division (/) at ./so.pl line 27.
[for Statement "SELECT id, routine( number ) AS result FROM "wages""] at ./so.pl line 34.

When I comment out the third argument I works as expected ( because it looks as if the third argument is missing ):

#!/usr/bin/env perl
...  
sub routine {
    my $self = shift;
    my $sth = shift;
    #my $rowhash = shift;
    return $_[0] / 30; 
};
...

0, 230
1, 106.667
2, 60
3 rows

Is this a bug?

Answer 1

Also from the SQL::Statement::Functions documentation:

When using SQL::Statement/SQL::Parser directly to parse SQL, functions (either built-in or user-defined) may occur anywhere in a SQL statement that values, column names, table names, or predicates may occur. When using the modules through a DBD or in any other context in which the SQL is both parsed and executed, functions can occur in the same places except that they can not occur in the column selection clause of a SELECT statement that contains a FROM clause.

i.e.

SELECT id, routine( number ) AS result FROM  wages

will not work.

Rewrite your function to return a table, something like this:

sub routine {
    my($self,$sth,$rowhash,@params) = @_;
    return [ [qw(id result)],
             map { [ $_->[0], $_->[1] / 30 ] } @$array_ref ];
};

$dbh->do( "CREATE FUNCTION routine" );
my $sth = $dbh->prepare( "SELECT * FROM routine()" );
$sth->execute();
$sth->dump_results();

With the expected results:

$ perl dl.pl 
0, 230
1, 106.667
2, 60
3 rows