From the SQL::Statement::Functions documentation:
Function syntax
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.
# valid for both parsing and executing
SELECT MyFunc(args);
SELECT * FROM MyFunc(args);
SELECT * FROM x WHERE MyFuncs(args);
SELECT * FROM x WHERE y < MyFuncs(args);
# valid only for parsing (won't work from a DBD)
SELECT MyFunc(args) FROM x WHERE y;
Reading this I would expect that the first SELECT-statement of my example shouldn't work and the second should but it is quite the contrary.
#!/usr/bin/env perl
use warnings; use strict;
use 5.010;
use DBI;
open my $fh, '>', 'test.csv' or die $!;
say $fh "id,name";
say $fh "1,Brown";
say $fh "2,Smith";
say $fh "7,Smith";
say $fh "8,Green";
close $fh;
my $dbh = DBI->connect ( 'dbi:CSV:', undef, undef, {
RaiseError => 1,
f_ext => '.csv',
});
my $table = 'test';
say "\nSELECT 1";
my $sth = $dbh->prepare ( "SELECT MAX( id ) FROM $table WHERE name LIKE 'Smith'" );
$sth->execute ();
$sth->dump_results();
say "\nSELECT 2";
$sth = $dbh->prepare ( "SELECT * FROM $table WHERE id = MAX( id )" );
$sth->execute ();
$sth->dump_results();
outputs:
SELECT 1
'7'
1 rows
SELECT 2
Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2893.
DBD::CSV::db prepare failed: Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2894.
[for Statement "SELECT * FROM test WHERE id = MAX( id )"] at ./so_3.pl line 30.
DBD::CSV::db prepare failed: Unknown function 'MAX' at /usr/lib/perl5/site_perl/5.10.0/SQL/Parser.pm line 2894.
[for Statement "SELECT * FROM test WHERE id = MAX( id )"] at ./so_3.pl line 30.
Could someone explaine me this behavior?
