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]
-----------------------------------------------------------------------------
