Optimization corner case with IS?

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Optimization corner case with IS?

wmertens
I noticed this, IS is not treated like = for optimization:

SQLITE> CREATE TABLE t(f INTEGER);
SQLITE> CREATE INDEX t_f ON t(f) WHERE f IS NOT NULL;
SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f = 1;
QUERY PLAN
`--SEARCH TABLE t USING COVERING INDEX t_f (f=?)
SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f IS 1;
QUERY PLAN
`--SCAN TABLE t
SQLITE> EXPLAIN QUERY PLAN SELECT * FROM t WHERE f IS 1 AND f IS NOT NULL;
QUERY PLAN
`--SEARCH TABLE t USING COVERING INDEX t_f (f=?)

Wout.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users