| Subject: | Indirectly Defined Columns and Prepared Queries |
If a column is defined indirectly, like so:
CREATE TABLE master ( id INTEGER PRIMARY KEY );
CREATE TABLE test2 ( id REFERENCES master, str VARCHAR(10) );
where 'id' in table 'test2' is derived from 'id' in 'master',
a prepared query may falsely return an undefined row.
E.g. let table 'test2' contain the row (25, 'hello!').
Then the following code should not return an undefined row:
$sth = $dbh->prepare('select * from test2 where id = ?');
$sth->execute(25);
$row = $sth->fetchrow_arrayref;
See the attached code for a more detailed example.
(This may be a bug from upstream, but I don't know how to differentiate
that, sorry. :-/)
| Subject: | sqlite-bug.pl |
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=","","", { RaiseError => 1 } );
map { $dbh->do($_) } split /;/, <<SCHEMA;
CREATE TABLE master ( id INTEGER PRIMARY KEY );
CREATE TABLE test1 ( id INTEGER REFERENCES master, str VARCHAR(10) );
CREATE TABLE test2 ( id REFERENCES master, str VARCHAR(10) );
INSERT INTO test1 VALUES ( 25, 'hello!');
INSERT INTO test2 VALUES ( 25, 'hello!');
SCHEMA
my ($sth, $row);
# query 1
$sth = $dbh->prepare('select * from test1 where id = ?');
$sth->execute(25);
$row = $sth->fetchrow_arrayref;
print defined $row ? "row: @$row" : 'row is undefined!';
print "\n";
# query 2
$sth = $dbh->prepare('select * from test2 where id = 25');
$sth->execute();
$row = $sth->fetchrow_arrayref;
print defined $row ? "row: @$row" : 'row is undefined!';
print "\n";
# query 3
$sth = $dbh->prepare('select * from test2 where id = ?');
$sth->execute(25);
$row = $sth->fetchrow_arrayref;
print defined $row ? "row: @$row" : 'row is undefined!';
print "\n";