Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello again ! I did some changes and got a better plan but it takes for ever, it seems that sqlite3 can not merge the where clause before the "group by" scan the whole tables: === CREATE VIEW despesas_municipio_orgao_list_view AS SELECT a."municipio_id" rowid, a."ano_exercicio",

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello Richard ! It seems that sqlite query plan is having trouble with this query too: === create table if not exists municipios( id integer primary key, name varchar not null unique collate nocase_slna ); create table if not exists municipios_orgaos( id integer primary key,

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello Don ! Thanks for reply ! It's my fault I wrote it without actually testing it for the purpose of show my use case: === -- -- Ideally I want to write the next query using the previous view -- CREATE VIEW if not exists "despesas_master_list_view_tidy" AS SELECT a.*, b.name as

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

In the below view, what is "a"? A FROM is not defined. -- -- Ideally I want to write the next query using the previous view -- CREATE VIEW if not exists "despesas_master_list_view_tidy" AS SELECT a.*, b.name as modalidade_licitacao, c.name as credor, d.* LEFT JOIN

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

Hello Richard ! The simple example I've sent is the minimal to show the problem, the real database schema where I found this problem has a lot of small tables joined and I was using the views to simplify (not duplicate) code, so on that case it'll result in bloat and repetition. see bellow:

Re: [sqlite] Bad query plan selection only with "LEFT JOIN"

On 1/5/17, Domingo Alvarez Duarte 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. > >

[sqlite] Bad query plan selection only with "LEFT JOIN"

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);