Hello !
Today I found this unexpected behavior when using sqlite3 trunk:
When using views with joins sqlite3 is choosing expected plans except
for "LEFT JOIN", bellow is the snippet that shows this unexpected behavior.
===
create table if not exists a(id integer primary key, val text);
create table if not exists b(id integer primary key, a_id integer not
null, val text);
create view if not exists b_view as select b.*, a.* from b left join a
on b.a_id=a.id;
create table if not exists c(id integer primary key, b_id integer not
null, val text);
select 'good expected plan';
explain query plan select c.*, b_view.* from c, b_view where
c.b_id=b_view.id;
select 'good expected plan';
explain query plan select c.*, b_view.* from c join b_view on
c.b_id=b_view.id;
select 'bad unexpected plan';
explain query plan select c.*, b_view.* from c left join b_view on
c.b_id=b_view.id;
===
Output of sqlite3 < test-sqlte-bad-plan.sql :
===
good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
good expected plan
0|0|0|SCAN TABLE c
0|1|1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
bad unexpected plan
1|0|0|SCAN TABLE b
1|1|1|SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|SCAN TABLE c
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (id=?)
===
Cheers !
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
