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 <[email protected]>
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 <[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;
>>
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
