In SQLite null is not equal to anything, including null.
I'm not sure what the best solution for your application is. With help from
the others here, I have learned to use coalesce() to convert nulls into a
value, and to not allow null in key fields.
select
tableA.path,
tableA.value
from
tableA,
tableB
where
tableA.path = tableB.path
and coalesce(tableA.value, '' ) = coalesce(tableB.value, '' );
yields
alpha|1
gamma|
And it appears to use the myIndexB index for the join.
-Clark
----- Original Message ----
From: Sean Cunningham <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, June 15, 2007 1:26:49 PM
Subject: [sqlite] Optimization of equality comparison when NULL involved
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple inserts:
insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');
The following select statement gets what you'd expect, and it uses
the index.
sqlite> select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3
However, if your data has NULL's:
insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);
The same select above neglects to report the tuple ('gamma',NULL) as
the equal. This is correct given SQL's treatment of NULL, and is
easily fixed:
sqlite> select tableA.path, tableA.value from tableA,tableB where
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|
However, the above statement has the unfortunate side effect of
bypassing the index:
sqlite> explain select tableA.path, tableA.value from tableA,tableB
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|
My question is: Is there another way to write such a select
statement which can solve both problems of treating NULL==NULL and
using the index.
Thanks,
Sean
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
