Subject: | Bug in binding parameters in HAVING clauses |
The attached failing test (tested both on 1.25 and latest development
version, 1.26_06) shows a bug in binding parameters in 'HAVING' clauses.
Subject: | fail-test.pl |
#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
use Test::More;
my $dbh = DBI->connect("dbi:SQLite::memory:");
Creation: { # Database creation and population
my $sth = $dbh->prepare('create table foo ( bar text, quux number )');
$sth->execute;
my %data = (
apple => [ 20, 20, 30, 40 ],
orange => [ -2, -3, -5, 0 ],
pineapple => [1000, 23000, 9000, 42 ],
);
my $sql = "insert into foo (bar, quux) values (?, ?)";
$sth = $dbh->prepare($sql);
foreach my $field (keys %data) {
$sth->execute($field, $_) for @{$data{$field}};
}
}
Hardcoded: { # a select query with hardcoded HAVING clause
my $sql_hardcoded =
"SELECT foo.bar FROM foo GROUP BY bar HAVING AVG(foo.quux) > 10";
my $result = $dbh->selectall_arrayref($sql_hardcoded);
$result = [ map { $_->[0] } @$result ];
is_deeply($result, ['apple', 'pineapple']);
}
Bind: { # same query, using binding
my $sql_bind =
"SELECT foo.bar FROM foo GROUP BY bar HAVING AVG(foo.quux) > ?";
my $result = $dbh->selectall_arrayref($sql_bind, undef, ['10']);
$result = [ map { $_->[0] } @$result ];
is_deeply($result, ['apple', 'pineapple']);
}
done_testing;
__END__
Output:
ok 1
not ok 2
# Failed test at fail-test.pl line 51.
# Structures begin differing at:
# $got->[0] = Does not exist
# $expected->[0] = 'apple'
1..2
# Looks like you failed 1 test of 2.