On Wed, Jun 25, 2008 at 05:33:04PM +0200, [EMAIL PROTECTED] wrote:
> mysql, postgres and mssql correctly return zero rows. SQLite
> however returns two rows, for id=1 and id=2.
>
> http://www.sqlite.org/nulls.html doesn't list it, so perhaps
> this has never come up before.
http://www.sqlite.org/
Dan wrote:
> Wow (applauds). That was incredible!
>
Thanks (bows). :-)
> So the upshot is that if a set used with "NOT IN" contains a NULL,
> the "NOT IN" operation will never evaluate to true. It may evaluate to
> false, it may evaluate to NULL. But never true.
>
> If we have the expression:
>
On Thu, Jun 26, 2008 at 04:35:20PM +0700, Dan wrote:
> Do we also have a similar problem with the regular 'IN' operator? In
> SQLite at the moment:
>
>SQLite version 3.6.0
>sqlite> select 1 IN (null, 2, 3), 2 IN (null, 2, 3);
>0, 1
>
> Should the leftmost column of the result row shou
On Jun 26, 2008, at 4:49 AM, Dennis Cote wrote:
> D. Richard Hipp wrote:
>>
>> If I understand Peter correctly, he is saying that NULL should mean
>> "unknown" in the context of the RHS of a NOT IN operator. SQLite
>> does
>> not currently operate this way. SQLite currently interprets a
>>
D. Richard Hipp wrote:
>
> If I understand Peter correctly, he is saying that NULL should mean
> "unknown" in the context of the RHS of a NOT IN operator. SQLite does
> not currently operate this way. SQLite currently interprets a NULL in
> the RHS of a NOT IN operator to mean "nothing".
>
Well, NULL is not a value, technically, it is the lack of a value.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Wilson, Ron P
Sent: Wednesday, June 25, 2008 11:12 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug with NULL in NOT
The lesson is very clear from the evidence. With SQL NULL is ambiguous
and subject to intepretation so good design requires that you completely
avoid it. Then you sidestep intractable implementation interptrations.
A project management technique dating back further than I can remember
was to
On Jun 25, 2008, at 12:48 PM, Wilson, Ron P wrote:
> It seems to me that using NULL ... could
> create a lot of confusion in queries.
Yes, yes. SQL-NULL excels at creating confusion!
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
ronics, 434.455.6453
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Wednesday, June 25, 2008 12:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug with NULL in NOT IN
On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrot
On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrote:
> Why should the second query return zero rows? Clearly ids 1 and 2
> don't
> exist in b.
The meaning of "NULL" in SQL is overloaded. In some contexts NULL
means "anything" or "unknown". In other contexts it means "nothing".
If we assu
. Richard Hipp
Sent: Wednesday, June 25, 2008 11:50 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] bug with NULL in NOT IN
On Jun 25, 2008, at 11:33 AM, [EMAIL PROTECTED] wrote:
> Hello,
>
> with the following schema and contents:
>
> BEGIN TRANSACTION;
On Jun 25, 2008, at 11:33 AM, [EMAIL PROTECTED] wrote:
> Hello,
>
> with the following schema and contents:
>
> BEGIN TRANSACTION;
> CREATE TABLE a(id INTEGER);
> INSERT INTO a VALUES(1);
> INSERT INTO a VALUES(2);
> INSERT INTO a VALUES(3);
> CREATE TABLE b(id INTEGER);
> INSERT INTO b VALUES(NU
Hello,
with the following schema and contents:
BEGIN TRANSACTION;
CREATE TABLE a(id INTEGER);
INSERT INTO a VALUES(1);
INSERT INTO a VALUES(2);
INSERT INTO a VALUES(3);
CREATE TABLE b(id INTEGER);
INSERT INTO b VALUES(NULL);
INSERT INTO b VALUES(3);
INSERT INTO b VALUES(4);
INSERT INTO b VALUES(5
13 matches
Mail list logo