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
