On Thu, Apr 26, 2012 at 7:05 PM, Kyle McKay <[email protected]> wrote:
> Credit to Pete Hardman who posted the original thread: > > > http://thread.gmane.org/gmane.**comp.db.sqlite.general/73931<http://thread.gmane.org/gmane.comp.db.sqlite.general/73931> > > Here's a much simpler reproduction of the query planner bug: > Fixed here: http://www.sqlite.org/src/info/a49e909c87 A minor point of nomenclature: I really prefer to reserve the use of the word "bug" for cases where the software gets the wrong answer (or crashes, which is always the wrong answer). By that definition, this issue is not a bug but merely an inefficiency or an "opportunity for performance improvement". By reserving the use of "bug" for cases where the wrong answer appears, we make the word "bug" stronger, so that when you see a phrase like "a bug in SQLite" you know you ought to pay attention. Calling obscure inefficiencies "bugs" tends to weaken the term and make it less helpful for describing serious problems. > > $ /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 00bb9c9ce4f465e6ac321ced2a9d00**62dc364669 > 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 > [email protected] > http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users> > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
