| Subject: | Bind parameter in GROUP BY fails |
Using a placeholder in a GROUP BY statement fails, as in the example below:
#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
unlink "/tmp/db";
my $dbh = DBI->connect( 'dbi:SQLite:dbname=/tmp/db', '', '',
{ PrintError => 0, RaiseError => 1 } );
$dbh->do( 'CREATE TABLE foo (bar TEXT, num INT)' );
for (1..5)
{
$dbh->do( 'INSERT INTO foo (bar, num) VALUES (?,?)',
undef, ($_%2 ? "odd" : "even"), $_ );
}
my ($v) = $dbh->selectrow_array( 'SELECT bar FROM foo WHERE num = ?',
undef, 3 );
print "1: $v\n";
DBI->trace(4);
my $ar = $dbh->selectall_arrayref( 'SELECT bar FROM foo GROUP BY bar HAVING count(*) >
1' );
print "2: @$_\n" for @$ar;
$ar = $dbh->selectall_arrayref( 'SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?',
undef, 1);
print "3: @$_\n" for @$ar;
DBI tracing is turned on in the output below and makes it a bit harder to see that the last
statement returns no results, even though it is identical to the penultimate statement:
1: odd
DBI 1.52-nothread default trace level set to 0x0/4 (pid 4608)
-> selectall_arrayref for DBD::SQLite::db (DBI::db=HASH(0x82e769c)~0x82ee7e8
'SELECT bar FROM foo GROUP BY bar HAVING count(*) > 1')
1 -> prepare for DBD::SQLite::db (DBI::db=HASH(0x82ee7e8)~INNER 'SELECT bar FR
OM foo GROUP BY bar HAVING count(*) > 1' undef)
New DBI::st (for DBD::SQLite::st, parent=DBI::db=HASH(0x82ee7e8), id=)
dbih_setup_handle(DBI::st=HASH(0x82eea28)=>DBI::st=HASH(0x82ee8fc), DBD::SQL
ite::st, 82eea7c, Null!)
dbih_make_com(DBI::db=HASH(0x82ee7e8), 824c3f0, DBD::SQLite::st, 132, 0) thr
#0
sqlite trace: prepare statement: SELECT bar FROM foo GROUP BY bar HAVING count(*
) > 1 at dbdimp.c line 258
1 <- prepare= DBI::st=HASH(0x82eea28) at /tmp/foo line 23
sqlite trace: execute at dbdimp.c line 325
sqlite trace: Execute returned 1 cols
at dbdimp.c line 395
dbih_setup_fbav for 1 fields => 0x82eebcc
<- selectall_arrayref= [ ARRAY(0x82eebe4) ARRAY(0x82eec08) ] at /tmp/foo lin
e 23
<> DESTROY(DBI::st=HASH(0x82eea28)) ignored for outer handle (inner DBI::st=
HASH(0x82ee8fc) has ref cnt 1)
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x82ee8fc)~INNER)
<- DESTROY= undef at /tmp/foo line 24 via at /tmp/foo line 24
dbih_clearcom 0x82ee8fc (com 0x82f1fe8, type 3) done.
2: even
2: odd
-> selectall_arrayref for DBD::SQLite::db (DBI::db=HASH(0x82e769c)~0x82ee7e8
'SELECT bar FROM foo GROUP BY bar HAVING count(*) > ?' undef 1)
1 -> prepare for DBD::SQLite::db (DBI::db=HASH(0x82ee7e8)~INNER 'SELECT bar FR
OM foo GROUP BY bar HAVING count(*) > ?' undef)
New DBI::st (for DBD::SQLite::st, parent=DBI::db=HASH(0x82ee7e8), id=)
dbih_setup_handle(DBI::st=HASH(0x82eeab8)=>DBI::st=HASH(0x82eea28), DBD::SQL
ite::st, 82eeb48, Null!)
dbih_make_com(DBI::db=HASH(0x82ee7e8), 824c3f0, DBD::SQLite::st, 132, 0) thr
#0
sqlite trace: prepare statement: SELECT bar FROM foo GROUP BY bar HAVING count(*
) > ? at dbdimp.c line 258
1 <- prepare= DBI::st=HASH(0x82eeab8) at /tmp/foo line 26
sqlite trace: bind into 0x82eeb0c: 1 => 1 (0) pos 0
at dbdimp.c line 464
sqlite trace: execute at dbdimp.c line 325
sqlite trace: params left in 0x82eeb0c: 0 at dbdimp.c line 343
sqlite trace: bind 0 type 0 as 1 at dbdimp.c line 344
sqlite trace: Execute returned 1 cols
at dbdimp.c line 395
<- selectall_arrayref= [ ] at /tmp/foo line 26
<> DESTROY(DBI::st=HASH(0x82eeab8)) ignored for outer handle (inner DBI::st=
HASH(0x82eea28) has ref cnt 1)
-> DESTROY for DBD::SQLite::st (DBI::st=HASH(0x82eea28)~INNER)
<- DESTROY= undef at /tmp/foo line 27 via at /tmp/foo line 27
dbih_clearcom 0x82eea28 (com 0x82f2208, type 3) done.
<> DESTROY(DBI::db=HASH(0x82e769c)) ignored for outer handle (inner DBI::db=
HASH(0x82ee7e8) has ref cnt 1)
-> DESTROY for DBD::SQLite::db (DBI::db=HASH(0x82ee7e8)~INNER)
<- DESTROY= undef
dbih_clearcom 0x82ee7e8 (com 0x824c3f0, type 2) done.
-- DBI::END
-> disconnect_all for DBD::SQLite::dr (DBI::dr=HASH(0x8284860)~0x82e76a8)
<- disconnect_all= '' at /usr/local/lib/perl5/site_perl/5.8.8/i686-linux/DBI
.pm line 700 via at /tmp/foo line 0
! -> DESTROY in DBD::_::common for DBD::SQLite::dr (DBI::dr=HASH(0x82e76a8)~IN
NER)
! <- DESTROY= undef during global destruction
dbih_clearcom 0x8284860 (com 0x824c200, type 1) done.
! <> DESTROY for DBI::dr=HASH(0x8284860) ignored (inner handle gone)