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
