On 1/5/17, Domingo Alvarez Duarte <[email protected]> wrote:
> 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 'bad unexpected plan';
> explain query plan select c.*, b_view.* from c left join b_view on
> c.b_id=b_view.id;
Can you rewrite your query as:
SELECT *
FROM c LEFT JOIN b ON c.b_id=b.id
LEFT JOIN a ON b.id=a.id;
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
