Re: SQLite 3.3.16 nulls test results
am 03.05.2007 01:57:13 von Tim.Bunce
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:Tim.Bunce@pobox.com]
> Sent: Monday, April 30, 2007 5:08 AM
> To: Alex Teslik
> Cc: dbi-users@perl.org; msergeant@cpan.org
> 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 dbi-users\@perl.org. 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 dbi-users\@perl.org. Thank you.\n";
>
> exit 0;
Re: SQLite 3.3.16 nulls test results
am 03.05.2007 05:05:35 von Alex
Here are the results from Brian's script:
[root]/home/alex/DBI_testing# perl perl_dbi_nulls_test.pl
Using connect arguments, db version: 3.3.16
=> Drop table 'dbi__null_test_tmp', if it already exists...
=> Create table 'dbi__null_test_tmp'...
=> Insert 4 rows into the table...
Inserting values (1, 'Homer')
Inserting values (2, NULL)
Inserting values (3, 'Marge')
Inserting values (4, NULL)
(Driver bug: statement handle should not be Active after an INSERT.)
=> Testing clause style 0: WHERE mycol = ?...
Selecting row with Marge
Selecting rows with NULL
=> WHERE clause style 0 returned incorrect results.
Non-NULL test rows returned these row ids: 3
The NULL test rows returned these row ids:
=> Testing clause style 1: WHERE NVL(mycol, '-') = NVL(?, '-')...
DBD::SQLite::db prepare failed: no such function: NVL(1) at dbdimp.c line 271
at perl_dbi_nulls_test.pl line 118.
=> Testing clause style 2: WHERE ISNULL(mycol, '-') = ISNULL(?, '-')...
DBD::SQLite::db prepare failed: near "ISNULL": syntax error(1) at dbdimp.c
line 271 at perl_dbi_nulls_test.pl line 118.
=> Testing clause style 3: WHERE DECODE(mycol, ?, 1, 0) = 1...
DBD::SQLite::db prepare failed: no such function: DECODE(1) at dbdimp.c line
271 at perl_dbi_nulls_test.pl line 118.
=> Testing clause style 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)...
Selecting row with Marge
Selecting rows with NULL
=> WHERE clause style 4 is supported.
=> Testing clause style 5: WHERE mycol = ? OR (mycol IS NULL AND SP_ISNULL(?)
= 1)...
DBD::SQLite::db prepare failed: no such function: SP_ISNULL(1) at dbdimp.c
line 271 at perl_dbi_nulls_test.pl line 118.
=> Testing clause style 6: WHERE mycol = ? OR (mycol IS NULL AND ? = 1)...
Selecting row with Marge
Selecting rows with NULL
=> WHERE clause style 6 returned incorrect results.
Non-NULL test rows returned these row ids: 3
The NULL test rows returned these row ids:
closing dbh with active statement handles at perl_dbi_nulls_test.pl line 165.
------------------------------------------------------------ ------------
1 styles are supported:
Style 4: WHERE mycol = ? OR (mycol IS NULL AND ? IS NULL)
------------------------------------------------------------ ------------
If these results don't match what's in the 'Placeholders and Bind Values'
section of the DBI documentation, or are for a database that not already
listed, please email the results to dbi-users@perl.org. Thank you.
And some environment info========>
[root]/home/alex/DBI_testing# perl -V
Summary of my perl5 (revision 5 version 8 subversion 5) configuration:
Platform:
osname=freebsd, osvers=4.10-release-p5, archname=i386-freebsd-64int
uname='freebsd gouda.acatysmoof.com 4.10-release-p5 freebsd
4.10-release-p5 #1: sun dec 26 12:23:17 pst 2004
alex@gouda.acatysmoof.com:usrobjusrsrcsysdualp3-releng_4_10 i386 '
config_args='-sde -Dprefix=/usr/local
-Darchlib=/usr/local/lib/perl5/5.8.5/mach -Dprivlib=/usr/local/lib/perl5/5.8.5
-Dman3dir=/usr/local/lib/perl5/5.8.5/perl/man/man3
-Dman1dir=/usr/local/man/man1
-Dsitearch=/usr/local/lib/perl5/site_perl/5.8.5/mach
-Dsitelib=/usr/local/lib/perl5/site_perl/5.8.5 -Dscriptdir=/usr/local/bin
-Dsiteman3dir=/usr/local/lib/perl5/5.8.5/man/man3
-Dsiteman1dir=/usr/local/man/man1 -Ui_malloc -Ui_iconv -Uinstallusrbinperl
-Dcc=cc -Doptimize=-O -pipe -Duseshrplib
-Dccflags=-DAPPLLIB_EXP="/usr/local/lib/perl5/5.8.5/BSDPAN" -Dd_dosuid=define
-Ui_gdbm -Dusethreads=n -Dusemymalloc=y -Duse64bitint'
hint=recommended, useposix=true, d_sigaction=define
usethreads=undef use5005threads=undef useithreads=undef usemultiplicity=undef
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=define use64bitall=undef uselongdouble=undef
usemymalloc=y, bincompat5005=undef
Compiler:
cc='cc', ccflags ='-DAPPLLIB_EXP="/usr/local/lib/perl5/5.8.5/BSDPAN"
-DHAS_FPSETMASK -DHAS_FLOATINGPOINT_H -fno-strict-aliasing -pipe
-I/usr/local/include',
optimize='-O -pipe ',
cppflags='-DAPPLLIB_EXP="/usr/local/lib/perl5/5.8.5/BSDPAN"
-DHAS_FPSETMASK -DHAS_FLOATINGPOINT_H -fno-strict-aliasing -pipe
-I/usr/local/include'
ccversion='', gccversion='2.95.4 20020320 [FreeBSD]', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=12345678
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
ivtype='long long', ivsize=8, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=4, prototype=define
Linker and Libraries:
ld='cc', ldflags ='-Wl,-E -L/usr/local/lib'
libpth=/usr/lib /usr/local/lib
libs=-lgdbm -lm -lcrypt -lutil -lc
perllibs=-lm -lcrypt -lutil -lc
libc=, so=so, useshrplib=true, libperl=libperl.so
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='
-Wl,-R/usr/local/lib/perl5/5.8.5/mach/CORE'
cccdlflags='-DPIC -fPIC', lddlflags='-shared -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: USE_64_BIT_INT USE_LARGE_FILES
Built under freebsd
Compiled at Dec 26 2004 15:40:59
@INC:
/usr/local/lib/perl5/site_perl/5.8.5/mach
/usr/local/lib/perl5/site_perl/5.8.5
/usr/local/lib/perl5/site_perl/5.8.2
/usr/local/lib/perl5/site_perl/5.6.1
/usr/local/lib/perl5/site_perl/5.005
/usr/local/lib/perl5/site_perl
/usr/local/lib/perl5/5.8.5/BSDPAN
/usr/local/lib/perl5/5.8.5/mach
/usr/local/lib/perl5/5.8.5
.
[root]/home/alex/DBI_testing# uname -srm
FreeBSD 4.10-RELEASE-p5 i386
HTH,
Alex
On Thu, 3 May 2007 00:57:13 +0100, Tim Bunce wrote
> 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:Tim.Bunce@pobox.com]
> > Sent: Monday, April 30, 2007 5:08 AM
> > To: Alex Teslik
> > Cc: dbi-users@perl.org; msergeant@cpan.org
> > 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 dbi-users\@perl.org. 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 dbi-users\@perl.org. Thank
you.\n";
> >
> > exit 0;