Annotation of embedaddon/sqlite3/test/distinct.test, revision 1.1.1.1
1.1 misho 1: # 2011 July 1
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. The
12: # focus of this script is the DISTINCT modifier.
13: #
14:
15: set testdir [file dirname $argv0]
16: source $testdir/tester.tcl
17:
18: set testprefix distinct
19:
20:
21: proc is_distinct_noop {sql} {
22: set sql1 $sql
23: set sql2 [string map {DISTINCT ""} $sql]
24:
25: set program1 [list]
26: set program2 [list]
27: db eval "EXPLAIN $sql1" {
28: if {$opcode != "Noop"} { lappend program1 $opcode }
29: }
30: db eval "EXPLAIN $sql2" {
31: if {$opcode != "Noop"} { lappend program2 $opcode }
32: }
33:
34: return [expr {$program1==$program2}]
35: }
36:
37: proc do_distinct_noop_test {tn sql} {
38: uplevel [list do_test $tn [list is_distinct_noop $sql] 1]
39: }
40: proc do_distinct_not_noop_test {tn sql} {
41: uplevel [list do_test $tn [list is_distinct_noop $sql] 0]
42: }
43:
44: proc do_temptables_test {tn sql temptables} {
45: uplevel [list do_test $tn [subst -novar {
46: set ret ""
47: db eval "EXPLAIN [set sql]" {
48: if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} {
49: if {$p5 != "10" && $p5!="00"} { error "p5 = $p5" }
50: if {$p5 == "10"} {
51: lappend ret hash
52: } else {
53: lappend ret btree
54: }
55: }
56: }
57: set ret
58: }] $temptables]
59: }
60:
61:
62: #-------------------------------------------------------------------------
63: # The following tests - distinct-1.* - check that the planner correctly
64: # detects cases where a UNIQUE index means that a DISTINCT clause is
65: # redundant. Currently the planner only detects such cases when there
66: # is a single table in the FROM clause.
67: #
68: do_execsql_test 1.0 {
69: CREATE TABLE t1(a, b, c, d);
70: CREATE UNIQUE INDEX i1 ON t1(b, c);
71: CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase);
72:
73: CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
74:
75: CREATE TABLE t3(c1 PRIMARY KEY, c2);
76: CREATE INDEX i3 ON t3(c2);
77: }
78: foreach {tn noop sql} {
79:
80: 1 1 "SELECT DISTINCT b, c FROM t1"
81: 2 1 "SELECT DISTINCT c FROM t1 WHERE b = ?"
82: 3 1 "SELECT DISTINCT rowid FROM t1"
83: 4 1 "SELECT DISTINCT rowid, a FROM t1"
84: 5 1 "SELECT DISTINCT x FROM t2"
85: 6 1 "SELECT DISTINCT * FROM t2"
86: 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)"
87:
88: 8 1 "SELECT DISTINCT * FROM t1"
89:
90: 8 0 "SELECT DISTINCT a, b FROM t1"
91:
92: 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)"
93: 10 0 "SELECT DISTINCT c FROM t1"
94: 11 0 "SELECT DISTINCT b FROM t1"
95:
96: 12 0 "SELECT DISTINCT a, d FROM t1"
97: 13 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
98: 14 1 "SELECT DISTINCT a, d COLLATE nocase FROM t1"
99: 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1"
100: 16 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
101:
102: 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2"
103: 17 0 { /* Technically, it would be possible to detect that DISTINCT
104: ** is a no-op in cases like the following. But SQLite does not
105: ** do so. */
106: SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid }
107:
108: 18 1 "SELECT DISTINCT c1, c2 FROM t3"
109: 19 1 "SELECT DISTINCT c1 FROM t3"
110: 20 1 "SELECT DISTINCT * FROM t3"
111: 21 0 "SELECT DISTINCT c2 FROM t3"
112:
113: 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
114: 23 1 "SELECT DISTINCT rowid FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
115:
116: 24 0 "SELECT DISTINCT rowid/2 FROM t1"
117: 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1"
118: 26 1 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
119: } {
120: if {$noop} {
121: do_distinct_noop_test 1.$tn $sql
122: } else {
123: do_distinct_not_noop_test 1.$tn $sql
124: }
125: }
126:
127: #-------------------------------------------------------------------------
128: # The following tests - distinct-2.* - test cases where an index is
129: # used to deliver results in order of the DISTINCT expressions.
130: #
131: drop_all_tables
132: do_execsql_test 2.0 {
133: CREATE TABLE t1(a, b, c);
134:
135: CREATE INDEX i1 ON t1(a, b);
136: CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase);
137:
138: INSERT INTO t1 VALUES('a', 'b', 'c');
139: INSERT INTO t1 VALUES('A', 'B', 'C');
140: INSERT INTO t1 VALUES('a', 'b', 'c');
141: INSERT INTO t1 VALUES('A', 'B', 'C');
142: }
143:
144: foreach {tn sql temptables res} {
145: 1 "a, b FROM t1" {} {A B a b}
146: 2 "b, a FROM t1" {} {B A b a}
147: 3 "a, b, c FROM t1" {hash} {a b c A B C}
148: 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c}
149: 5 "b FROM t1 WHERE a = 'a'" {} {b}
150: 6 "b FROM t1" {hash} {b B}
151: 7 "a FROM t1" {} {A a}
152: 8 "b COLLATE nocase FROM t1" {} {b}
153: 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {B}
154: } {
155: do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res
156: do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
157: }
158:
159: do_execsql_test 2.A {
160: SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o;
161: } {a A a A}
162:
163:
164:
165:
166: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to distinct.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test