Hi Tom, yes, you are right. It does not matter how you put the NULL in your query. The bind variable does not change the operator from = to IS. However, if you do not want to change the SQL text, and know a value which your field will never have, and do not use an index, then you can write
--- where coalesce(maybenullcolumn, 'nullvalue') = coalesce(?, 'nullvalue') Martin Hynes, Tom wrote: > 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: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] 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 <tom.hy...@inin.com> 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: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] 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 >> >> diego.d...@bentley.com 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 >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
