On Tue, May 01, 2007 at 05:09:50PM -0500, CAMPBELL, BRIAN D (BRIAN) wrote: > Some points... > > 1. My response to the style 6 failure > > I am also puzzled why 6 failed. The non-null case worked, but the NULL > case did not: no rows with null values were selected. After > "substitution" in: > WHERE mycol = ? OR (mycol IS NULL AND ? = 1) > Effectively, you get this for the NULL case: > WHERE mycol = NULL OR (mycol IS NULL AND 1 = 1) > In this case, the LHS of the OR operator usually fails for most DB > engines, but the RHS should succeed. A couple of possible explanations > (but not good ones): > A. Placeholder is not supported on LHS of = operator. But I'd expect a > driver error during prepare or execute, and there apparently is none. > And a parameter on the LHS of the IS operator works because "? IS NULL" > in style 4 worked. > B. Even though the SQL literal and placeholder parameter are apparently > both int, there is some type of float vs. integer comparison issue. > Seems unlikely though. > > > 2. Not the latest script? > > This appears to be a slightly older version of the script. A current > version is at: > http://svn.perl.org/modules/dbi/trunk/ex/perl_dbi_nulls_test.pl > as referenced in the DBI perl doc, in the "Placeholders and Bind Values" > section.
There aren't any changes to what's tested - the results would be the same. I just modified the test to make it easier to see what was going on. > 3. How about running a better script? > > But that current version has a couple of bugs. Attached is a revision I > came up with, that fixes the bugs and improves the output format just a > bit. Also attached is a diff listing. Tim, are YOU maintaining files > at the link location above? Can you apply the attached revision? BTW, > I feel somewhat qualified to submit a revision, because I was the > primary contributor to the original. Patch applied and checked in (r9490). Thanks Brian. Alex, please run Brian's script and post the results. Thanks. Tim. > -----Original Message----- > From: Tim Bunce [mailto:[EMAIL PROTECTED] > Sent: Monday, April 30, 2007 5:08 AM > To: Alex Teslik > Cc: [email protected]; [EMAIL PROTECTED] > Subject: Re: SQLite 3.3.16 nulls test results > > On Fri, Apr 27, 2007 at 08:52:21AM -0700, Alex Teslik wrote: > > as requested by the DBI man page: > > > > > > [root]/home/alex# perl perl_dbi_nulls_test.pl Using connect arguments, > > > db version: 3.3.16 => Drop table 'dbi__null_test_tmp', if it already > > exists... > > DBD::SQLite::db do failed: no such table: dbi__null_test_tmp(1) at > > dbdimp.c line 271 at perl_dbi_nulls_test.pl line 92. > > => Create table 'dbi__null_test_tmp'... > > => Insert 4 rows into the table... > > Values 1 Homer > > Values 2 > > Values 3 Marge > > Values 4 > > > => Testing clause style 6: WHERE mycol = ? OR (mycol IS NULL AND ? = > > 1) => WHERE clause style 6 returned incorrect results. > > Non-Null test rows returned: 3 > > Null test rows returned: > > It's unfortunate and surprising that style 6 isn't supported as that's > the only style that every other database supports. > > Could you look into that some more. Perhaps there's a bug somewhere. > > > closing dbh with active statement handles at perl_dbi_nulls_test.pl > line 167. > > Looks like a bug in DBD::SQLite. Executing a prepared NON-select > statement (like a CREATE TABLE or INSERT) should not leave > $sth->{Active} true. > > > 1 styles are supported > > 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL) > > I've updated the docs. Thanks. > > Tim. Content-Description: diff_out.txt > *** perl_dbi_nulls_test.pl.orig Tue May 1 13:27:11 2007 > --- perl_dbi_nulls_test.pl Tue May 1 13:59:51 2007 > *************** > *** 102,108 **** > for my $i (0..$#char_column_values) > { > my $val = $char_column_values[$i]; > ! printf "Inserting values (%d, %s)\n", $i+1, $dbh->quote($val); > $sth->execute($i+1, $val); > } > print "(Driver bug: statement handle should not be Active after an > INSERT.)\n" > --- 102,108 ---- > for my $i (0..$#char_column_values) > { > my $val = $char_column_values[$i]; > ! printf " Inserting values (%d, %s)\n", $i+1, $dbh->quote($val); > $sth->execute($i+1, $val); > } > print "(Driver bug: statement handle should not be Active after an > INSERT.)\n" > *************** > *** 113,123 **** > for my $i (0..$#select_clauses) > { > my $sel = $select_clauses[$i]; > ! print "\n=> Testing clause style $i: ".$sel->{clause}." to match > $marge\n"; > > $sth = $dbh->prepare("SELECT myid,mycol FROM $tablename > ".$sel->{clause}) > or next; > > $sth->execute(@{$sel->{nonnull}}) > or next; > my $r1 = $sth->fetchall_arrayref(); > --- 113,124 ---- > for my $i (0..$#select_clauses) > { > my $sel = $select_clauses[$i]; > ! print "\n=> Testing clause style $i: ".$sel->{clause}."...\n"; > > $sth = $dbh->prepare("SELECT myid,mycol FROM $tablename > ".$sel->{clause}) > or next; > > + print " Selecting row with $marge\n"; > $sth->execute(@{$sel->{nonnull}}) > or next; > my $r1 = $sth->fetchall_arrayref(); > *************** > *** 124,129 **** > --- 125,131 ---- > my $n1_rows = $sth->rows; > my $n1 = @$r1; > > + print " Selecting rows with NULL\n"; > $sth->execute(@{$sel->{null}}) > or next; > my $r2 = $sth->fetchall_arrayref(); > *************** > *** 152,172 **** > print "=> WHERE clause style $i returned incorrect results.\n"; > if ($n1 > 0 || $n2 > 0) > { > ! print " Non-NULL test rows returned these row ids: ". > join(", ", map { $r1->[$_][0] } (0..$#{$r1}))."\n"; > ! print " The NULL test rows returned these row ids: ". > ! join(", ", map { $r2->[$_][0] } (0..$#{$r1}))."\n"; > } > } > } > > $dbh->disconnect(); > ! > ! printf "\n%d styles are supported $tag:\n", scalar @ok; > print "$_\n" for @ok; > print "\n"; > print "If these results don't match what's in the 'Placeholders and Bind > Values'\n"; > ! print "section of the DBI documentation, or are for a database that not > already listed,\n"; > ! print "please email the results to [EMAIL PROTECTED] Thank you.\n"; > > exit 0; > --- 154,176 ---- > print "=> WHERE clause style $i returned incorrect results.\n"; > if ($n1 > 0 || $n2 > 0) > { > ! print " Non-NULL test rows returned these row ids: ". > join(", ", map { $r1->[$_][0] } (0..$#{$r1}))."\n"; > ! print " The NULL test rows returned these row ids: ". > ! join(", ", map { $r2->[$_][0] } (0..$#{$r2}))."\n"; > } > } > } > > $dbh->disconnect(); > ! print "\n"; > ! print "-" x 72, "\n"; > ! printf "%d styles are supported:\n", scalar @ok; > print "$_\n" for @ok; > + print "-" x 72, "\n"; > print "\n"; > print "If these results don't match what's in the 'Placeholders and Bind > Values'\n"; > ! print "section of the DBI documentation, or are for a database that not > already\n"; > ! print "listed, please email the results to [EMAIL PROTECTED] Thank you.\n"; > > exit 0;
