Attached patch allows DISTINCT queries to take advantage of an appropriate INDEX, if present.
For example, given: CREATE TABLE foo(a, b); CREATE INDEX foo_b on foo(b); explain query plan select distinct b from foo; sqlite 3.5.2 returns: 0|0|TABLE foo and sqlite 3.5.2 + patch returns: 0|0|TABLE foo WITH INDEX foo_b ORDER BY The query "select distinct b from foo" is transformed into "select b from foo group by b" by the patch. No regressions in "make test", although a few tests had to be altered in order to take into account the new ordering of DISTINCT rows. This is to be expected, as the order in which DISTINCT rows are returned were never guaranteed without an explicit ORDER BY clause. Please send any bug reports or comments to the list. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Index: src/select.c =================================================================== RCS file: /sqlite/sqlite/src/select.c,v retrieving revision 1.359 diff -u -3 -p -r1.359 select.c --- src/select.c 31 Aug 2007 17:42:48 -0000 1.359 +++ src/select.c 11 Nov 2007 23:49:01 -0000 @@ -3067,6 +3067,14 @@ int sqlite3Select( } #endif + /* Optimizion: convert DISTINCT to GROUP BY to potentially + ** take advantage of an INDEX + */ + if (p->isDistinct && !p->isAgg && !p->pGroupBy) { + pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, p->pEList); + isDistinct = p->isDistinct = 0; + } + /* If there is an ORDER BY clause, then this sorting ** index might end up being unused if the data can be ** extracted in pre-sorted order. If that is the case, then the Index: test/collate5.test =================================================================== RCS file: /sqlite/sqlite/test/collate5.test,v retrieving revision 1.5 diff -u -3 -p -r1.5 collate5.test --- test/collate5.test 7 Sep 2005 22:48:16 -0000 1.5 +++ test/collate5.test 11 Nov 2007 23:49:01 -0000 @@ -57,17 +57,17 @@ do_test collate5-1.1 { execsql { SELECT DISTINCT a FROM collate5t1; } -} {a b n} +} {A B N} do_test collate5-1.2 { execsql { SELECT DISTINCT b FROM collate5t1; } -} {apple Apple banana {}} +} {{} Apple apple banana} do_test collate5-1.3 { execsql { SELECT DISTINCT a, b FROM collate5t1; } -} {a apple A Apple b banana n {}} +} {A Apple a apple B banana N {}} # The remainder of this file tests compound SELECT statements. # Omit it if the library is compiled such that they are omitted. Index: test/insert4.test =================================================================== RCS file: /sqlite/sqlite/test/insert4.test,v retrieving revision 1.8 diff -u -3 -p -r1.8 insert4.test --- test/insert4.test 9 Oct 2007 08:29:32 -0000 1.8 +++ test/insert4.test 11 Nov 2007 23:49:01 -0000 @@ -112,7 +112,7 @@ do_test insert4-2.4.1 { INSERT INTO t3 SELECT DISTINCT * FROM t2; SELECT * FROM t3; } -} {9 1 1 9} +} {1 9 9 1} xferopt_test insert4-2.4.2 0 do_test insert4-2.4.3 { catchsql { Index: test/misc5.test =================================================================== RCS file: /sqlite/sqlite/test/misc5.test,v retrieving revision 1.17 diff -u -3 -p -r1.17 misc5.test --- test/misc5.test 12 Sep 2007 17:01:45 -0000 1.17 +++ test/misc5.test 11 Nov 2007 23:49:01 -0000 @@ -490,6 +490,7 @@ ifcapable subquery { LIMIT 10 ) WHERE artist <> '' + ORDER BY 1 DESC ) ) )
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------