Debian Codegraph @

test/distinct.test

raw
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 ifcapable !compound {
19 finish_test
20 return
21 }
22
23 set testprefix distinct
24
25
26 proc is_distinct_noop {sql} {
27 set sql1 $sql
28 set sql2 [string map {DISTINCT ""} $sql]
29
30 set program1 [list]
31 set program2 [list]
32 db eval "EXPLAIN $sql1" {
33 if {$opcode != "Noop" && $opcode != "Explain"} { lappend program1 $opcode }
34 }
35 db eval "EXPLAIN $sql2" {
36 if {$opcode != "Noop" && $opcode != "Explain"} { lappend program2 $opcode }
37 }
38 return [expr {$program1==$program2}]
39 }
40
41 proc do_distinct_noop_test {tn sql} {
42 uplevel [list do_test $tn [list is_distinct_noop $sql] 1]
43 }
44 proc do_distinct_not_noop_test {tn sql} {
45 uplevel [list do_test $tn [list is_distinct_noop $sql] 0]
46 }
47
48 proc do_temptables_test {tn sql temptables} {
49 uplevel [list do_test $tn [subst -novar {
50 set ret ""
51 db eval "EXPLAIN [set sql]" {
52 if {$opcode == "OpenEphemeral" || $opcode == "SorterOpen"} {
53 if {$p5!=8 && $p5!=0} { error "p5 = $p5" }
54 if {$p5==8} {
55 lappend ret hash
56 } else {
57 lappend ret btree
58 }
59 }
60 }
61 set ret
62 }] $temptables]
63 }
64
65
66 #-------------------------------------------------------------------------
67 # The following tests - distinct-1.* - check that the planner correctly
68 # detects cases where a UNIQUE index means that a DISTINCT clause is
69 # redundant. Currently the planner only detects such cases when there
70 # is a single table in the FROM clause.
71 #
72 do_execsql_test 1.0 {
73 CREATE TABLE t1(a, b, c, d);
74 CREATE UNIQUE INDEX i1 ON t1(b, c);
75 CREATE UNIQUE INDEX i2 ON t1(d COLLATE nocase);
76
77 CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
78
79 CREATE TABLE t3(c1 PRIMARY KEY NOT NULL, c2 NOT NULL);
80 CREATE INDEX i3 ON t3(c2);
81
82 CREATE TABLE t4(a, b NOT NULL, c NOT NULL, d NOT NULL);
83 CREATE UNIQUE INDEX t4i1 ON t4(b, c);
84 CREATE UNIQUE INDEX t4i2 ON t4(d COLLATE nocase);
85 }
86 foreach {tn noop sql} {
87
88 1.1 0 "SELECT DISTINCT b, c FROM t1"
89 1.2 1 "SELECT DISTINCT b, c FROM t4"
90 2.1 0 "SELECT DISTINCT c FROM t1 WHERE b = ?"
91 2.2 1 "SELECT DISTINCT c FROM t4 WHERE b = ?"
92 3 1 "SELECT DISTINCT rowid FROM t1"
93 4 1 "SELECT DISTINCT rowid, a FROM t1"
94 5 1 "SELECT DISTINCT x FROM t2"
95 6 1 "SELECT DISTINCT * FROM t2"
96 7 1 "SELECT DISTINCT * FROM (SELECT * FROM t2)"
97
98 8.1 0 "SELECT DISTINCT * FROM t1"
99 8.2 1 "SELECT DISTINCT * FROM t4"
100
101 8 0 "SELECT DISTINCT a, b FROM t1"
102
103 9 0 "SELECT DISTINCT c FROM t1 WHERE b IN (1,2)"
104 10 0 "SELECT DISTINCT c FROM t1"
105 11 0 "SELECT DISTINCT b FROM t1"
106
107 12.1 0 "SELECT DISTINCT a, d FROM t1"
108 12.2 0 "SELECT DISTINCT a, d FROM t4"
109 13.1 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t1"
110 13.2 0 "SELECT DISTINCT a, b, c COLLATE nocase FROM t4"
111 14.1 0 "SELECT DISTINCT a, d COLLATE nocase FROM t1"
112 14.2 1 "SELECT DISTINCT a, d COLLATE nocase FROM t4"
113
114 15 0 "SELECT DISTINCT a, d COLLATE binary FROM t1"
115 16.1 0 "SELECT DISTINCT a, b, c COLLATE binary FROM t1"
116 16.2 1 "SELECT DISTINCT a, b, c COLLATE binary FROM t4"
117
118 16 0 "SELECT DISTINCT t1.rowid FROM t1, t2"
119 17 0 { /* Technically, it would be possible to detect that DISTINCT
120 ** is a no-op in cases like the following. But SQLite does not
121 ** do so. */
122 SELECT DISTINCT t1.rowid FROM t1, t2 WHERE t1.rowid=t2.rowid }
123
124 18 1 "SELECT DISTINCT c1, c2 FROM t3"
125 19 1 "SELECT DISTINCT c1 FROM t3"
126 20 1 "SELECT DISTINCT * FROM t3"
127 21 0 "SELECT DISTINCT c2 FROM t3"
128
129 22 0 "SELECT DISTINCT * FROM (SELECT 1, 2, 3 UNION SELECT 4, 5, 6)"
130
131 24 0 "SELECT DISTINCT rowid/2 FROM t1"
132 25 1 "SELECT DISTINCT rowid/2, rowid FROM t1"
133 26.1 0 "SELECT DISTINCT rowid/2, b FROM t1 WHERE c = ?"
134 26.2 1 "SELECT DISTINCT rowid/2, b FROM t4 WHERE c = ?"
135 } {
136 if {$noop} {
137 do_distinct_noop_test 1.$tn $sql
138 } else {
139 do_distinct_not_noop_test 1.$tn $sql
140 }
141 }
142
143 #-------------------------------------------------------------------------
144 # The following tests - distinct-2.* - test cases where an index is
145 # used to deliver results in order of the DISTINCT expressions.
146 #
147 drop_all_tables
148 do_execsql_test 2.0 {
149 CREATE TABLE t1(a, b, c);
150
151 CREATE INDEX i1 ON t1(a, b);
152 CREATE INDEX i2 ON t1(b COLLATE nocase, c COLLATE nocase);
153
154 INSERT INTO t1 VALUES('a', 'b', 'c');
155 INSERT INTO t1 VALUES('A', 'B', 'C');
156 INSERT INTO t1 VALUES('a', 'b', 'c');
157 INSERT INTO t1 VALUES('A', 'B', 'C');
158 }
159
160 foreach {tn sql temptables res} {
161 1 "a, b FROM t1" {} {A B a b}
162 2 "b, a FROM t1" {} {B A b a}
163 3 "a, b, c FROM t1" {hash} {A B C a b c}
164 4 "a, b, c FROM t1 ORDER BY a, b, c" {btree} {A B C a b c}
165 5 "b FROM t1 WHERE a = 'a'" {} {b}
166 6 "b FROM t1 ORDER BY +b COLLATE binary" {btree hash} {B b}
167 7 "a FROM t1" {} {A a}
168 8 "b COLLATE nocase FROM t1" {} {b}
169 9 "b COLLATE nocase FROM t1 ORDER BY b COLLATE nocase" {} {b}
170 } {
171 do_execsql_test 2.$tn.1 "SELECT DISTINCT $sql" $res
172 do_temptables_test 2.$tn.2 "SELECT DISTINCT $sql" $temptables
173 }
174
175 do_execsql_test 2.A {
176 SELECT (SELECT DISTINCT o.a FROM t1 AS i) FROM t1 AS o ORDER BY rowid;
177 } {a A a A}
178
179 do_test 3.0 {
180 db eval {
181 CREATE TABLE t3(a INTEGER, b INTEGER, c, UNIQUE(a,b));
182 INSERT INTO t3 VALUES
183 (null, null, 1),
184 (null, null, 2),
185 (null, 3, 4),
186 (null, 3, 5),
187 (6, null, 7),
188 (6, null, 8);
189 SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
190 }
191 } {{} {} {} 3 6 {}}
192 do_test 3.1 {
193 regexp {OpenEphemeral} [db eval {
194 EXPLAIN SELECT DISTINCT a, b FROM t3 ORDER BY +a, +b;
195 }]
196 } {0}
197
198 #-------------------------------------------------------------------------
199 # Ticket [fccbde530a6583bf2748400919f1603d5425995c] (2014-01-08)
200 # The logic that computes DISTINCT sometimes thinks that a zeroblob()
201 # and a blob of all zeros are different when they should be the same.
202 #
203 do_execsql_test 4.1 {
204 DROP TABLE IF EXISTS t1;
205 DROP TABLE IF EXISTS t2;
206 CREATE TABLE t1(a INTEGER);
207 INSERT INTO t1 VALUES(3);
208 INSERT INTO t1 VALUES(2);
209 INSERT INTO t1 VALUES(1);
210 INSERT INTO t1 VALUES(2);
211 INSERT INTO t1 VALUES(3);
212 INSERT INTO t1 VALUES(1);
213 CREATE TABLE t2(x);
214 INSERT INTO t2
215 SELECT DISTINCT
216 CASE a WHEN 1 THEN x'0000000000'
217 WHEN 2 THEN zeroblob(5)
218 ELSE 'xyzzy' END
219 FROM t1;
220 SELECT quote(x) FROM t2 ORDER BY 1;
221 } {'xyzzy' X'0000000000'}
222
223 #----------------------------------------------------------------------------
224 # Ticket [c5ea805691bfc4204b1cb9e9aa0103bd48bc7d34] (2014-12-04)
225 # Make sure that DISTINCT works together with ORDER BY and descending
226 # indexes.
227 #
228 do_execsql_test 5.1 {
229 DROP TABLE IF EXISTS t1;
230 CREATE TABLE t1(x);
231 INSERT INTO t1(x) VALUES(3),(1),(5),(2),(6),(4),(5),(1),(3);
232 CREATE INDEX t1x ON t1(x DESC);
233 SELECT DISTINCT x FROM t1 ORDER BY x ASC;
234 } {1 2 3 4 5 6}
235 do_execsql_test 5.2 {
236 SELECT DISTINCT x FROM t1 ORDER BY x DESC;
237 } {6 5 4 3 2 1}
238 do_execsql_test 5.3 {
239 SELECT DISTINCT x FROM t1 ORDER BY x;
240 } {1 2 3 4 5 6}
241 do_execsql_test 5.4 {
242 DROP INDEX t1x;
243 CREATE INDEX t1x ON t1(x ASC);
244 SELECT DISTINCT x FROM t1 ORDER BY x ASC;
245 } {1 2 3 4 5 6}
246 do_execsql_test 5.5 {
247 SELECT DISTINCT x FROM t1 ORDER BY x DESC;
248 } {6 5 4 3 2 1}
249 do_execsql_test 5.6 {
250 SELECT DISTINCT x FROM t1 ORDER BY x;
251 } {1 2 3 4 5 6}
252
253 #-------------------------------------------------------------------------
254 # 2015-11-23. Problem discovered by Kostya Serebryany using libFuzzer
255 #
256 db close
257 sqlite3 db :memory:
258 do_execsql_test 6.1 {
259 CREATE TABLE jjj(x);
260 SELECT (SELECT 'mmm' UNION SELECT DISTINCT max(name) ORDER BY 1)
261 FROM sqlite_master;
262 } {jjj}
263 do_execsql_test 6.2 {
264 CREATE TABLE nnn(x);
265 SELECT (SELECT 'mmm' UNION SELECT DISTINCT max(name) ORDER BY 1)
266 FROM sqlite_master;
267 } {mmm}
268
269 #-------------------------------------------------------------------------
270 # Ticket [9c944882]
271 #
272 reset_db
273 do_execsql_test 7.0 {
274 CREATE TABLE t1(a INTEGER PRIMARY KEY);
275 CREATE TABLE t3(a INTEGER PRIMARY KEY);
276
277 CREATE TABLE t4(x);
278 CREATE TABLE t5(y);
279
280 INSERT INTO t5 VALUES(1), (2), (2);
281 INSERT INTO t1 VALUES(2);
282 INSERT INTO t3 VALUES(2);
283 INSERT INTO t4 VALUES(2);
284 }
285
286 do_execsql_test 7.1 {
287 WITH t2(b) AS (
288 SELECT DISTINCT y FROM t5 ORDER BY y
289 )
290 SELECT * FROM
291 t4 CROSS JOIN t3 CROSS JOIN t1
292 WHERE (t1.a=t3.a) AND (SELECT count(*) FROM t2 AS y WHERE t4.x!='abc')=t1.a
293 } {2 2 2}
294
295 # 2021-04-06 forum post https://sqlite.org/forum/forumpost/66954e9ece
296 reset_db
297 do_execsql_test 8.0 {
298 CREATE TABLE person ( pid INT) ;
299 CREATE UNIQUE INDEX idx ON person ( pid ) WHERE pid == 1;
300 INSERT INTO person VALUES (1), (10), (10);
301 SELECT DISTINCT pid FROM person where pid = 10;
302 } {10}
303
304 #-------------------------------------------------------------------------
305 reset_db
306 do_execsql_test 9.0 {
307 CREATE TABLE t1(a, b);
308 INSERT INTO t1 VALUES('a', 'a');
309 INSERT INTO t1 VALUES('a', 'b');
310 INSERT INTO t1 VALUES('a', 'c');
311
312 INSERT INTO t1 VALUES('b', 'a');
313 INSERT INTO t1 VALUES('b', 'b');
314 INSERT INTO t1 VALUES('b', 'c');
315
316 INSERT INTO t1 VALUES('a', 'a');
317 INSERT INTO t1 VALUES('b', 'b');
318
319 INSERT INTO t1 VALUES('A', 'A');
320 INSERT INTO t1 VALUES('B', 'B');
321 }
322
323 foreach {tn idx} {
324 1 { }
325 2 { CREATE INDEX i1 ON t1(a, b); }
326 3 { CREATE INDEX i1 ON t1(b, a); }
327 4 { CREATE INDEX i1 ON t1(a COLLATE nocase, b COLLATE nocase); }
328 5 { CREATE INDEX i1 ON t1(b COLLATE nocase, a COLLATE nocase); }
329 } {
330
331 execsql { DROP INDEX IF EXISTS i1 }
332 execsql $idx
333
334 do_execsql_test 9.$tn.1 {
335 SELECT DISTINCT a, b FROM t1 ORDER BY a, b
336 } {
337 A A B B
338 a a a b a c
339 b a b b b c
340 }
341
342 do_execsql_test 9.$tn.1 {
343 SELECT DISTINCT a COLLATE nocase, b COLLATE nocase FROM t1
344 ORDER BY a COLLATE nocase, b COLLATE nocase
345 } {
346 a a a b a c
347 b a b b b c
348 }
349 }
350
351 # 2023-03-16
352 # https://sqlite.org/forum/forumpost/16ce2bb7a639e29b
353 # ticket c36cdb4afd504dc1
354 # ticket 4051a7f931d9ba24
355 # ticket d6fd512f50513ab7
356 #
357 do_execsql_test 10.1 {
358 SELECT DISTINCT
359 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
360 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
361 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
362 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
363 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
364 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
365 1, 1, 1, 1, 1
366 ORDER BY
367 'x','x','x','x','x','x','x','x','x','x',
368 'x','x','x','x','x','x','x','x','x','x',
369 'x','x','x','x','x','x','x','x','x','x',
370 'x','x','x','x','x','x','x','x','x','x',
371 'x','x','x','x','x','x','x','x','x','x',
372 'x','x','x','x','x','x','x','x','x','x',
373 'x','x','x','x';
374 } {1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1}
375 do_execsql_test 10.2 {
376 EXPLAIN
377 SELECT DISTINCT
378 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
379 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
380 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
381 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
382 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
383 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
384 1, 1, 1, 1, 1
385 ORDER BY
386 'x','x','x','x','x','x','x','x','x','x',
387 'x','x','x','x','x','x','x','x','x','x',
388 'x','x','x','x','x','x','x','x','x','x',
389 'x','x','x','x','x','x','x','x','x','x',
390 'x','x','x','x','x','x','x','x','x','x',
391 'x','x','x','x','x','x','x','x','x','x',
392 'x','x','x','x';
393 } {/0 Init 0 /}
394 do_execsql_test 10.3 {
395 EXPLAIN CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;
396 } {/0 Init 0/}
397 do_execsql_test 10.4 {
398 DROP TABLE IF EXISTS t0;
399 CREATE TABLE t0 AS SELECT DISTINCT 0xda, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 0xda-0xda-42e-300, 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0', 'lit0' ORDER BY '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%Y-%m-%d', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'lit0', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', 'auto', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', ':memory:', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '%%', '';
400 SELECT count(*) FROM t0;
401 } {1}
402 do_execsql_test 10.5 {
403 DROP TABLE IF EXISTS t2;
404 CREATE TABLE t2 AS SELECT DISTINCT ':memory:', 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 0.0*7/0, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7 ORDER BY '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', '%J%j%w%s', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 'unixepoch', 42e-300, 'unixepoch', 'unixepoch', 'unixepoch' LIMIT 0xda;
405 SELECT count(*) FROM t2;
406 } {1}
407
408 finish_test