Re: SQLite 3.3.16 nulls test results

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@xxxxxxxxx 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@xxxxxxxxxxxxxxxxxxxx: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@xxxxxxxxx]
Sent: Monday, April 30, 2007 5:08 AM
To: Alex Teslik
Cc: dbi-users@xxxxxxxx; msergeant@xxxxxxxx
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;

.

Relevant Pages

  • cvs-src summary for June 14-21
    ... Intel PRO/10GbE driver MFC'ed ... Max responded, saying that since the script only adds lines to the output, ... Max later backed out the commit. ... Bruce Evans fixed a bug that allowed users to crash the system by ...
    (freebsd-current)
  • RE: TXT or HTML? -- IE NEW BUG
    ... Subject: TXT or HTML? ... -- IE NEW BUG ... vulnerability. ... However, the script would run in the web page's domain, so it ...
    (Bugtraq)
  • Re: IE javascript bug: global variable
    ... That's another aspect of this bug that puzzles me; ... code in the first block should "run to completion" before the ... instantiation of the second block. ... I see nothing to suggest that the first script ...
    (comp.lang.javascript)
  • Re: subprocess "handle is invalid" error
    ... So I switched to subprocess. ... I think this is a subprocess bug. ... It is often attributed to py2exe because ... Consider ths little script: ...
    (comp.lang.python)
  • Re: black lines around inserted image: workaround
    ... In order to keep doc files to a minimum size, I always edit graphics to size and resolution in an external graphics editor before Inserting them into Word. ... Common Bug - black lines around inserted image after saving. ...
    (microsoft.public.word.application.errors)