Credit to Pete Hardman who posted the original thread:
http://thread.gmane.org/gmane.comp.db.sqlite.general/73931
Here's a much simpler reproduction of the query planner bug:
$ /var/tmp/sqlite3 testview.sq3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> SELECT SQLITE_SOURCE_ID();
2012-03-20 11:35:50 00bb9c9ce4f465e6ac321ced2a9d0062dc364669
sqlite> .schema
CREATE TABLE t1(regn_no, transfer_date);
CREATE TABLE t2(regn_no, transfer_date);
CREATE TABLE t3(regn_no, transfer_date);
CREATE VIEW v1 as select * from t1 union all select * from t2 union
all select * from t3;
CREATE INDEX t1_index on t1(regn_no);
CREATE INDEX t2_index on t2(regn_no);
CREATE INDEX t3_index on t3(regn_no);
sqlite> explain query plan select * from v1 where regn_no = '039540'
order by transfer_date;
2|0|0|SEARCH TABLE t1 USING INDEX t1_index (regn_no=?) (~10 rows)
2|0|0|USE TEMP B-TREE FOR ORDER BY
3|0|0|SEARCH TABLE t2 USING INDEX t2_index (regn_no=?) (~10 rows)
3|0|0|USE TEMP B-TREE FOR ORDER BY
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
4|0|0|SEARCH TABLE t3 USING INDEX t3_index (regn_no=?) (~10 rows)
4|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 4 (UNION ALL)
sqlite> explain query plan select regn_no, transfer_date from v1 where
regn_no = '039540' order by transfer_date;
3|0|0|SCAN TABLE t1 (~1000000 rows)
4|0|0|SCAN TABLE t2 (~1000000 rows)
2|0|0|COMPOUND SUBQUERIES 3 AND 4 (UNION ALL)
5|0|0|SCAN TABLE t3 (~1000000 rows)
1|0|0|COMPOUND SUBQUERIES 2 AND 5 (UNION ALL)
0|0|0|SCAN SUBQUERY 1 (~300000 rows)
0|0|0|USE TEMP B-TREE FOR ORDER BY
sqlite> .quit
$
The two queries would seem to be identical except one specifies * for
the columns and the other lists them out yet the first query results
in use of the indices whereas the second does not.
-- Kyle
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
