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,
name varchar not null unique collate nocase_slna
);
create table if not exists tipos_despesa(
id integer primary key,
name varchar not null unique collate nocase_slna
);
CREATE TABLE if not exists despesas_master(
id integer primary key,
ano_exercicio integer not null,
municipio_id integer not null,
orgao_id integer not null,
unique(municipio_id, orgao_id)
);
CREATE TABLE if not exists despesas_detalhe(
id integer primary key,
despesa_id integer not null,
tp_despesa_id integer not null
);
create index if not exists despesas_detalhe_despesa_idx on
despesas_detalhe(despesa_id);
select 'expected good plan';
select '===';
explain query plan
select * from (
SELECT
a."municipio_id",
a."ano_exercicio",
c.name as municipio,
b.name as orgao,
-- count(*) count,
a."orgao_id"
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
--GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;
select '';
select 'unexpected bad plan';
select '===';
explain query plan
select * from (
SELECT
a.municipio_id,
a.ano_exercicio,
c.name as municipio,
b.name as orgao,
count(*) count,
a.orgao_id
FROM despesas_master AS a
JOIN despesas_detalhe d ON a.id=d.despesa_id
and d.tp_despesa_id=(select id from tipos_despesa where name='any')
JOIN municipios c ON a.municipio_id=c.id
JOIN municipios_orgaos b ON a.orgao_id=b.id
GROUP BY municipio_id, orgao_id
) tbl
WHERE municipio_id=2;
===
sqlite3 < the_above_sql_file
===
expected good plan
===
0|0|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INDEX
sqlite_autoindex_despesas_master_1 (municipio_id=?)
0|2|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY
(rowid=?)
0|3|1|SEARCH TABLE despesas_detalhe AS d USING INDEX
despesas_detalhe_despesa_idx (despesa_id=?)
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX
sqlite_autoindex_tipos_despesa_1 (name=?)
unexpected bad plan
===
0|0|1|SCAN TABLE despesas_detalhe AS d
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE tipos_despesa USING COVERING INDEX
sqlite_autoindex_tipos_despesa_1 (name=?)
0|1|0|SEARCH TABLE despesas_master AS a USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE municipios AS c USING INTEGER PRIMARY KEY (rowid=?)
0|3|3|SEARCH TABLE municipios_orgaos AS b USING INTEGER PRIMARY KEY
(rowid=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
===
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
