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 licitacao_modalidades AS b ON a.modalidade_lic=b.id LEFT JOIN credores AS c ON a.credor_id=c.id LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id;
On Fri, Jan 6, 2017 at 3:27 AM, Domingo Alvarez Duarte <mingo...@gmail.com> wrote: > 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: > > === > > 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, > name varchar not null unique collate nocase_slna > ); > > create table if not exists municipios_poder( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists credores( > id integer primary key, > cnpj_cpf varchar collate nocase_slna, > name varchar not null collate nocase_slna, > unique(cnpj_cpf, name) > ); > > create table if not exists tipos_despesa( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists funcoes_governo( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists subfuncoes_governo( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists programas_governo( > id integer primary key, > code integer, > name varchar not null collate nocase_slna, > unique(code, name) > ); > > create table if not exists acoes_governo( > id integer primary key, > code integer, > name varchar not null collate nocase_slna, > unique(code, name) > ); > > create table if not exists fontes_recursos( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists aplicacoes_fixo( > id integer primary key, > code integer, > name varchar not null collate nocase_slna, > unique(code, name) > ); > > create table if not exists aplicacoes_variavel( > id integer primary key, > code integer, > name varchar not null collate nocase_slna, > unique(code, name) > ); > > create table if not exists licitacao_modalidades( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > create table if not exists elementos_despesa( > id integer primary key, > name varchar not null unique collate nocase_slna > ); > > -- > -- the table bellow has 6M records > -- > CREATE TABLE if not exists empenhos( > id integer primary key, > ano_exercicio integer not null, > nr_empenho varchar not null, > valor decimal, > municipio_id integer not null, > orgao_id integer not null, > funcao_governo_id integer, > subfuncao_governo_id integer, > cd_programa integer, > cd_acao integer, > fonte_recurso_id integer, > cd_aplicacao_fixo integer, > elemento_id integer, > unique(municipio_id, nr_empenho) > ); > > > -- > -- the table bellow has 6M records > -- > CREATE TABLE if not exists despesas_master( > id integer primary key, > empenho_id integer not null, > modalidade_lic integer not null, > credor_id integer not null, > historico_despesa varchar collate nocase_slna, > unique(credor_id, empenho_id) > ); > > -- > -- the table bellow has 24M records > -- > CREATE TABLE if not exists despesas_detalhe( > id integer primary key, > id_despesa_detalhe integer not null, > despesa_id integer not null, > mes_referencia integer not null, > tp_despesa_id integer not null, > dt_emissao_despesa date not null, > vl_despesa decimal > ); > > CREATE VIEW if not exists "empenhos_list_view" AS > SELECT > a."id", > a."ano_exercicio", > a."nr_empenho", > a."valor", > d.name as municipio, > e.name as orgao, > f.name as funcao, > g.name as subfuncao, > h.name as programa, > i.name as acao, > k.name as fonte_recurso, > l.name as aplicacao_fixo, > n.name as elemento, > a."municipio_id", > a."orgao_id", > a."funcao_governo_id", > a."subfuncao_governo_id", > a."cd_programa", > a."cd_acao", > a."fonte_recurso_id", > a."cd_aplicacao_fixo", > a."elemento_id" > FROM "empenhos" AS a > LEFT JOIN municipios AS d ON a.municipio_id=d.id > LEFT JOIN municipios_orgaos AS e ON a.orgao_id=e.id > LEFT JOIN funcoes_governo AS f ON a.funcao_governo_id=f.id > LEFT JOIN subfuncoes_governo AS g ON a.subfuncao_governo_id=g.id > LEFT JOIN programas_governo AS h ON a.cd_programa=h.id > LEFT JOIN acoes_governo AS i ON a.cd_acao=i.id > LEFT JOIN fontes_recursos AS k ON a.fonte_recurso_id=k.id > LEFT JOIN aplicacoes_fixo AS l ON a.cd_aplicacao_fixo=l.id > LEFT JOIN elementos_despesa AS n ON a.elemento_id=n.id; > -- > -- 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 licitacao_modalidades AS b ON a.modalidade_lic=b.id > LEFT JOIN credores AS c ON a.credor_id=c.id > LEFT JOIN empenhos_list_view AS d ON a.empenho_id=d.id; > > -- > -- But to overcome the sqlite3 planer bug you suggest to do this > -- > CREATE VIEW if not exists "despesas_master_list_view_dirty" AS > SELECT > a."id", > d."ano_exercicio", > d."nr_empenho", > d."valor", > e.name as municipio, > f.name as orgao, > g.name as funcao, > h.name as subfuncao, > i.name as programa, > k.name as acao, > l.name as fonte_recurso, > m.name as aplicacao_fixo, > n.name as elemento, > a."historico_despesa", > a."credor_id", > a."modalidade_lic", > a.empenho_id, > d."municipio_id", > d."orgao_id", > d."funcao_governo_id", > d."subfuncao_governo_id", > d."cd_programa", > d."cd_acao", > d."fonte_recurso_id", > d."cd_aplicacao_fixo", > d."elemento_id" > FROM "despesas_master" AS a > LEFT JOIN licitacao_modalidades AS b ON a.modalidade_lic=b.id > LEFT JOIN credores AS c ON a.credor_id=c.id > LEFT JOIN empenhos AS d ON a.empenho_id=d.id > LEFT JOIN municipios AS e ON d.municipio_id=e.id > LEFT JOIN municipios_orgaos AS f ON d.orgao_id=f.id > LEFT JOIN funcoes_governo AS g ON d.funcao_governo_id=g.id > LEFT JOIN subfuncoes_governo AS h ON d.subfuncao_governo_id=h.id > LEFT JOIN programas_governo AS i ON d.cd_programa=i.id > LEFT JOIN acoes_governo AS k ON d.cd_acao=k.id > LEFT JOIN fontes_recursos AS l ON d.fonte_recurso_id=l.id > LEFT JOIN aplicacoes_fixo AS m ON d.cd_aplicacao_fixo=m.id > LEFT JOIN elementos_despesa AS n ON d.elemento_id=n.id; > > -- > -- Ideally I want to write the next query using the previous view > -- > CREATE VIEW if not exists "despesas_detalhe_list_view_tidy" AS > SELECT > a.*, > ab.name AS despesa_tipo, > b.* > FROM "despesas_detalhe" AS a > LEFT JOIN tipos_despesa AS ab ON a.tp_despesa_id=ab.id > LEFT JOIN despesas_master_list_view AS b ON a.despesa_id=b.id; > > -- > -- But to overcome the sqlite3 planer bug you suggest to do this > -- > CREATE VIEW if not exists "despesas_detalhe_list_view_dirty" AS > SELECT > a.id, > a.id_despesa_detalhe, > ab.name as tp_despesa, > ac.name as modalidade_lic, > d."ano_exercicio", > a.mes_referencia, > d."nr_empenho", > a.dt_emissao_despesa, > a.vl_despesa, > d."valor", > e.name as municipio, > f.name as orgao, > g.name as funcao, > h.name as subfuncao, > i.name as programa, > k.name as acao, > l.name as fonte_recurso, > m.name as aplicacao_fixo, > n.name as elemento, > b."historico_despesa", > a.despesa_id, > a.tp_despesa_id, > b."credor_id", > b."modalidade_lic", > b.empenho_id, > d."municipio_id", > d."orgao_id", > d."funcao_governo_id", > d."subfuncao_governo_id", > d."cd_programa", > d."cd_acao", > d."fonte_recurso_id", > d."cd_aplicacao_fixo", > d."elemento_id" > FROM "despesas_detalhe" AS a > LEFT JOIN tipos_despesa AS ab ON a.tp_despesa_id=ab.id > LEFT JOIN despesas_master AS b ON a.despesa_id=b.id > LEFT JOIN licitacao_modalidades AS ac ON b.modalidade_lic=ac.id > LEFT JOIN credores AS c ON b.credor_id=c.id > LEFT JOIN empenhos AS d ON b.empenho_id=d.id > LEFT JOIN municipios AS e ON d.municipio_id=e.id > LEFT JOIN municipios_orgaos AS f ON d.orgao_id=f.id > LEFT JOIN funcoes_governo AS g ON d.funcao_governo_id=g.id > LEFT JOIN subfuncoes_governo AS h ON d.subfuncao_governo_id=h.id > LEFT JOIN programas_governo AS i ON d.cd_programa=i.id > LEFT JOIN acoes_governo AS k ON d.cd_acao=k.id > LEFT JOIN fontes_recursos AS l ON d.fonte_recurso_id=l.id > LEFT JOIN aplicacoes_fixo AS m ON d.cd_aplicacao_fixo=m.id > LEFT JOIN elementos_despesa AS n ON d.elemento_id=n.id; > === > > Cheers ! > > On 05/01/17 23:16, Richard Hipp wrote: > >> On 1/5/17, Domingo Alvarez Duarte <mingo...@gmail.com> 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; >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users