Thanks for the quick response! Yes, I understand the differences between querying with IS NULL vs. = NULL. But I had always thought that when using *parameter binding* a NULL query parameter would be treated like the IS NULL case when doing the comparison, not the equality case. Hmm, Sounds like I've had a misconception about this.
-----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of P Kishor Sent: Wednesday, March 11, 2009 10:25 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT queries and NULL value parameters On Wed, Mar 11, 2009 at 9:14 AM, Hynes, Tom <[email protected]> wrote: >> ... yes, this is expected. > > Can you explain that a bit more? I certainly would not have expected it. > Thanks. sqlite> CREATE TABLE foo (a); sqlite> INSERT INTO foo VALUES (1); sqlite> INSERT INTO foo VALUES ('ab'); sqlite> INSERT INTO foo VALUES (''); sqlite> INSERT INTO foo VALUES (NULL); sqlite> SELECT * FROM foo; 1 ab sqlite> SELECT Count(*) FROM foo; 4 sqlite> SELECT Count(*) FROM foo WHERE a = 1; 1 sqlite> SELECT Count(*) FROM foo WHERE a = ''; 1 sqlite> SELECT Count(*) FROM foo WHERE a = NULL; 0 sqlite> SELECT Count(*) FROM foo WHERE a IS NULL; 1 sqlite> SELECT Count(*) FROM foo WHERE a IS NULL OR a = ''; 2 sqlite> > > Tom > > -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Martin Engelschalk > Sent: Wednesday, March 11, 2009 8:59 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SELECT queries and NULL value parameters > > Hi, > > yes, this is expected. Note that you use the = - operator in WHERE > maybenullcolumn = @value > and NULL = NULL evaluates to false. > This is SQL standard. > > Martin > > [email protected] wrote: >> Hello, >> >> In my usage of SQLite, I found a behavior that might be considered a >> bug, but I would like others' input on it. >> >> Consider the following table with a single row, with one column >> containing a null value: >> >> CREATE TABLE MyTable (id integer primary key autoincrement, label >> char(255), maybenullcolumn integer); >> INSERT INTO MyTable (label) VALUES ('Label'); >> >> If one tries to retrieve that row with the "IS NULL" syntax, it works >> fine (the following statement returns 1): >> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL; >> >> ... but if one uses parameters, the statement returns 0: >> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value; >> ... >> sqlite3_bind_null(pStmt, 1); >> >> Is this expected? >> >> Thanks, >> >> Diego >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
