Debian Codegraph @

test/distinct2.test

raw
1 # 2016-04-15
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 DISTINCT queries using the skip-ahead
13 # optimization.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 set testprefix distinct2
20
21 do_execsql_test 100 {
22 CREATE TABLE t1(x INTEGER PRIMARY KEY);
23 INSERT INTO t1 VALUES(0),(1),(2);
24 CREATE TABLE t2 AS
25 SELECT DISTINCT a.x AS aa, b.x AS bb
26 FROM t1 a, t1 b;
27 SELECT *, '|' FROM t2 ORDER BY aa, bb;
28 } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
29 do_execsql_test 110 {
30 DROP TABLE t2;
31 CREATE TABLE t2 AS
32 SELECT DISTINCT a.x AS aa, b.x AS bb
33 FROM t1 a, t1 b
34 WHERE a.x IN t1 AND b.x IN t1;
35 SELECT *, '|' FROM t2 ORDER BY aa, bb;
36 } {0 0 | 0 1 | 0 2 | 1 0 | 1 1 | 1 2 | 2 0 | 2 1 | 2 2 |}
37 do_execsql_test 120 {
38 CREATE TABLE t102 (i0 TEXT UNIQUE NOT NULL);
39 INSERT INTO t102 VALUES ('0'),('1'),('2');
40 DROP TABLE t2;
41 CREATE TABLE t2 AS
42 SELECT DISTINCT *
43 FROM t102 AS t0
44 JOIN t102 AS t4 ON (t2.i0 IN t102)
45 NATURAL JOIN t102 AS t3
46 JOIN t102 AS t1 ON (t0.i0 IN t102)
47 JOIN t102 AS t2 ON (t2.i0=+t0.i0 OR (t0.i0<>500 AND t2.i0=t1.i0));
48 SELECT *, '|' FROM t2 ORDER BY 1, 2, 3, 4, 5;
49 } {0 0 0 0 | 0 0 1 0 | 0 0 1 1 | 0 0 2 0 | 0 0 2 2 | 0 1 0 0 | 0 1 1 0 | 0 1 1 1 | 0 1 2 0 | 0 1 2 2 | 0 2 0 0 | 0 2 1 0 | 0 2 1 1 | 0 2 2 0 | 0 2 2 2 | 1 0 0 0 | 1 0 0 1 | 1 0 1 1 | 1 0 2 1 | 1 0 2 2 | 1 1 0 0 | 1 1 0 1 | 1 1 1 1 | 1 1 2 1 | 1 1 2 2 | 1 2 0 0 | 1 2 0 1 | 1 2 1 1 | 1 2 2 1 | 1 2 2 2 | 2 0 0 0 | 2 0 0 2 | 2 0 1 1 | 2 0 1 2 | 2 0 2 2 | 2 1 0 0 | 2 1 0 2 | 2 1 1 1 | 2 1 1 2 | 2 1 2 2 | 2 2 0 0 | 2 2 0 2 | 2 2 1 1 | 2 2 1 2 | 2 2 2 2 |}
50
51 do_execsql_test 400 {
52 CREATE TABLE t4(a,b,c,d,e,f,g,h,i,j);
53 INSERT INTO t4 VALUES(0,1,2,3,4,5,6,7,8,9);
54 INSERT INTO t4 SELECT * FROM t4;
55 INSERT INTO t4 SELECT * FROM t4;
56 CREATE INDEX t4x ON t4(c,d,e);
57 SELECT DISTINCT a,b,c FROM t4 WHERE a=0 AND b=1;
58 } {0 1 2}
59 do_execsql_test 410 {
60 SELECT DISTINCT a,b,c,d FROM t4 WHERE a=0 AND b=1;
61 } {0 1 2 3}
62 do_execsql_test 411 {
63 SELECT DISTINCT d,a,b,c FROM t4 WHERE a=0 AND b=1;
64 } {3 0 1 2}
65 do_execsql_test 420 {
66 SELECT DISTINCT a,b,c,d,e FROM t4 WHERE a=0 AND b=1;
67 } {0 1 2 3 4}
68 do_execsql_test 430 {
69 SELECT DISTINCT a,b,c,d,e,f FROM t4 WHERE a=0 AND b=1;
70 } {0 1 2 3 4 5}
71
72 do_execsql_test 500 {
73 CREATE TABLE t5(a INT, b INT);
74 CREATE UNIQUE INDEX t5x ON t5(a+b);
75 INSERT INTO t5(a,b) VALUES(0,0),(1,0),(1,1),(0,3);
76 CREATE TEMP TABLE out AS SELECT DISTINCT a+b FROM t5;
77 SELECT * FROM out ORDER BY 1;
78 } {0 1 2 3}
79
80 do_execsql_test 600 {
81 CREATE TABLE t6a(x INTEGER PRIMARY KEY);
82 INSERT INTO t6a VALUES(1);
83 CREATE TABLE t6b(y INTEGER PRIMARY KEY);
84 INSERT INTO t6b VALUES(2),(3);
85 SELECT DISTINCT x, x FROM t6a, t6b;
86 } {1 1}
87
88 do_execsql_test 700 {
89 CREATE TABLE t7(a, b, c);
90 WITH s(i) AS (
91 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<200
92 )
93 INSERT INTO t7 SELECT i/100, i/50, i FROM s;
94 }
95 do_execsql_test 710 {
96 SELECT DISTINCT a, b FROM t7;
97 } {
98 0 0 0 1
99 1 2 1 3
100 }
101 do_execsql_test 720 {
102 SELECT DISTINCT a, b+1 FROM t7;
103 } {
104 0 1 0 2
105 1 3 1 4
106 }
107 do_execsql_test 730 {
108 CREATE INDEX i7 ON t7(a, b+1);
109 ANALYZE;
110 SELECT DISTINCT a, b+1 FROM t7;
111 } {
112 0 1 0 2
113 1 3 1 4
114 }
115
116 do_execsql_test 800 {
117 CREATE TABLE t8(a, b, c);
118 WITH s(i) AS (
119 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE (i+1)<100
120 )
121 INSERT INTO t8 SELECT i/40, i/20, i/40 FROM s;
122 }
123
124 do_execsql_test 820 {
125 SELECT DISTINCT a, b, c FROM t8;
126 } {
127 0 0 0 0 1 0
128 1 2 1 1 3 1
129 2 4 2
130 }
131
132 do_execsql_test 820 {
133 SELECT DISTINCT a, b, c FROM t8 WHERE b=3;
134 } {1 3 1}
135
136 do_execsql_test 830 {
137 CREATE INDEX i8 ON t8(a, c);
138 ANALYZE;
139 SELECT DISTINCT a, b, c FROM t8 WHERE b=3;
140 } {1 3 1}
141
142 do_execsql_test 900 {
143 CREATE TABLE t9(v);
144 INSERT INTO t9 VALUES
145 ('abcd'), ('Abcd'), ('aBcd'), ('ABcd'), ('abCd'), ('AbCd'), ('aBCd'),
146 ('ABCd'), ('abcD'), ('AbcD'), ('aBcD'), ('ABcD'), ('abCD'), ('AbCD'),
147 ('aBCD'), ('ABCD'),
148 ('wxyz'), ('Wxyz'), ('wXyz'), ('WXyz'), ('wxYz'), ('WxYz'), ('wXYz'),
149 ('WXYz'), ('wxyZ'), ('WxyZ'), ('wXyZ'), ('WXyZ'), ('wxYZ'), ('WxYZ'),
150 ('wXYZ'), ('WXYZ');
151 }
152
153 do_execsql_test 910 {
154 SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v;
155 } {
156 ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD
157 AbCD AbCd AbCd AbcD AbcD Abcd Abcd
158 WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ
159 WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
160 aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
161 abCD abCd abCd abcD abcD abcd abcd
162 wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
163 wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
164 }
165
166 do_execsql_test 920 {
167 CREATE INDEX i9 ON t9(v COLLATE NOCASE, v);
168 ANALYZE;
169
170 SELECT DISTINCT v COLLATE NOCASE, v FROM t9 ORDER BY +v;
171 } {
172 ABCD ABCD ABCd ABCd ABcD ABcD ABcd ABcd AbCD
173 AbCD AbCd AbCd AbcD AbcD Abcd Abcd
174 WXYZ WXYZ WXYz WXYz WXyZ WXyZ WXyz WXyz WxYZ
175 WxYZ WxYz WxYz WxyZ WxyZ Wxyz Wxyz
176 aBCD aBCD aBCd aBCd aBcD aBcD aBcd aBcd abCD
177 abCD abCd abCd abcD abcD abcd abcd
178 wXYZ wXYZ wXYz wXYz wXyZ wXyZ wXyz wXyz wxYZ
179 wxYZ wxYz wxYz wxyZ wxyZ wxyz wxyz
180 }
181
182 # Ticket https://sqlite.org/src/info/ef9318757b152e3a on 2017-11-21
183 # Incorrect result due to a skip-ahead-distinct optimization on a
184 # join where no rows of the inner loop appear in the result set.
185 #
186 db close
187 sqlite3 db :memory:
188 do_execsql_test 1000 {
189 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
190 CREATE INDEX t1b ON t1(b);
191 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
192 CREATE INDEX t2y ON t2(y);
193 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
194 INSERT INTO t1(b) SELECT x/10 - 1 FROM c;
195 WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
196 INSERT INTO t2(x,y) SELECT x, 1 FROM c;
197 SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
198 ANALYZE;
199 SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>-1;
200 } {1 1}
201 db close
202 sqlite3 db :memory:
203 do_execsql_test 1010 {
204 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
205 CREATE INDEX t1b ON t1(b);
206 CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER);
207 CREATE INDEX t2y ON t2(y);
208 WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<49)
209 INSERT INTO t1(b) SELECT -(x/10 - 1) FROM c;
210 WITH RECURSIVE c(x) AS (VALUES(-1) UNION ALL SELECT x+1 FROM c WHERE x<19)
211 INSERT INTO t2(x,y) SELECT -x, 1 FROM c;
212 SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
213 ANALYZE;
214 SELECT DISTINCT y FROM t1, t2 WHERE b=x AND b<>1 ORDER BY y DESC;
215 } {1 1}
216 db close
217 sqlite3 db :memory:
218 do_execsql_test 1020 {
219 CREATE TABLE t1(a, b);
220 CREATE INDEX t1a ON t1(a, b);
221 -- Lots of rows of (1, 'no'), followed by a single (1, 'yes').
222 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
223 INSERT INTO t1(a, b) SELECT 1, 'no' FROM c;
224 INSERT INTO t1(a, b) VALUES(1, 'yes');
225 CREATE TABLE t2(x PRIMARY KEY);
226 INSERT INTO t2 VALUES('yes');
227 SELECT DISTINCT a FROM t1, t2 WHERE x=b;
228 ANALYZE;
229 SELECT DISTINCT a FROM t1, t2 WHERE x=b;
230 } {1 1}
231
232 #-------------------------------------------------------------------------
233 reset_db
234
235 do_execsql_test 2000 {
236 CREATE TABLE t0 (c0, c1, c2, PRIMARY KEY (c0, c1));
237 CREATE TABLE t1 (c2);
238 INSERT INTO t0(c2) VALUES (0),(1),(3),(4),(5),(6),(7),(8),(9),(10),(11);
239 INSERT INTO t0(c1) VALUES ('a');
240 INSERT INTO t1(c2) VALUES (0);
241 }
242 do_execsql_test 2010 {
243 SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
244 } {{} 1 {} {} 1 a}
245 do_execsql_test 1.2 {
246 ANALYZE;
247 }
248 do_execsql_test 2020 {
249 SELECT DISTINCT t0.c0, t1._rowid_, t0.c1 FROM t1 CROSS JOIN t0 ORDER BY t0.c0;
250 } {{} 1 {} {} 1 a}
251
252
253 do_execsql_test 2030 {
254 CREATE TABLE t2(a, b, c);
255 CREATE INDEX t2ab ON t2(a, b);
256
257 WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
258 INSERT INTO t2 SELECT 'one', i%2, 'one' FROM c;
259
260 WITH c(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM c WHERE i<64)
261 INSERT INTO t2 SELECT 'two', i%2, 'two' FROM c;
262
263 CREATE TABLE t3(x INTEGER PRIMARY KEY);
264 INSERT INTO t3 VALUES(1);
265
266 ANALYZE;
267 }
268 do_execsql_test 2040 {
269 SELECT DISTINCT a, b, x FROM t3 CROSS JOIN t2 ORDER BY a, +b;
270 } {
271 one 0 1
272 one 1 1
273 two 0 1
274 two 1 1
275 }
276
277 #-------------------------------------------------------------------------
278 #
279 reset_db
280 do_execsql_test 3000 {
281 CREATE TABLE t0 (c0, c1 NOT NULL DEFAULT 1, c2, PRIMARY KEY (c0, c1));
282 INSERT INTO t0(c2) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
283 INSERT INTO t0(c2) VALUES('a');
284 }
285
286 do_execsql_test 3010 {
287 SELECT DISTINCT * FROM t0 WHERE NULL IS t0.c0;
288 } {
289 {} 1 {}
290 {} 1 a
291 }
292
293 do_execsql_test 3020 {
294 ANALYZE;
295 }
296
297 do_execsql_test 3030 {
298 SELECT DISTINCT * FROM t0 WHERE NULL IS c0;
299 } {
300 {} 1 {}
301 {} 1 a
302 }
303
304 #-------------------------------------------------------------------------
305 #
306 reset_db
307
308 do_execsql_test 4010 {
309 CREATE TABLE t1(a, b COLLATE RTRIM);
310 INSERT INTO t1 VALUES(1, ''), (2, ' '), (3, ' ');
311 }
312 do_execsql_test 4020 {
313 SELECT b FROM t1 UNION SELECT 1;
314 } {1 {}}
315
316 #-------------------------------------------------------------------------
317 #
318 reset_db
319 do_execsql_test 5010 {
320 CREATE TABLE cnt(a);
321 WITH RECURSIVE cnt2(x) AS (
322 VALUES(1) UNION ALL SELECT x+1 FROM cnt2 WHERE x<50
323 )
324 INSERT INTO cnt SELECT x FROM cnt2;
325 }
326
327 do_execsql_test 5020 {
328 SELECT DISTINCT abs(random())%5 AS r FROM cnt ORDER BY r;
329 } {0 1 2 3 4}
330
331 do_execsql_test 5030 {
332 SELECT abs(random())%5 AS r FROM cnt GROUP BY 1 ORDER BY 1;
333 } {0 1 2 3 4}
334
335 do_execsql_test 5040 {
336 SELECT a FROM cnt WHERE a>45 GROUP BY 1;
337 } {46 47 48 49 50}
338
339
340 # 2024-06-03 dbsqlfuzz 8a44f675401a8b1f68a43bf813c4f4f72ad8f0ea
341 # Use of uninitialized bytecode register due to the call-function-once
342 # optimization at check-in 663f5dd32d9db832
343 #
344 db null NULL
345 do_execsql_test 5050 {
346 CREATE TABLE t0(a TEXT); INSERT INTO t0 VALUES('abcd');
347 CREATE TABLE t1(b TEXT);
348 CREATE TABLE t2(c TEXT);
349 CREATE TABLE t3(d TEXT); INSERT INTO t3 VALUES('wxyz');
350 CREATE VIEW v4(e) AS SELECT (SELECT t2.c FROM t0, t1 GROUP BY 1) FROM t2;
351 SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1;
352 } NULL
353 do_execsql_test 5060 {
354 DROP VIEW v4;
355 CREATE VIEW v4(e) AS SELECT (SELECT t2.c COLLATE nocase FROM t0, t1 GROUP BY 1) FROM t2;
356 SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1;
357 } NULL
358
359 do_execsql_test 5070 {
360 DROP VIEW v4;
361 CREATE VIEW v4(e) AS SELECT (SELECT unlikely(t2.c COLLATE nocase) FROM t0, t1 GROUP BY 1) FROM t2;
362 SELECT v4.e FROM t3 LEFT JOIN v4 ON true GROUP BY 1;
363 } NULL
364
365 # 2024-06-28 dbsqlfuzz 46343912848a603e32c6072cae792eb056bac897
366 # Do not call sqlite3ExprToRegister() on an expression that is already
367 # a register.
368 #
369 do_execsql_test 5080 {
370 CREATE TABLE dual(dummy TEXT);
371 INSERT INTO dual VALUES('X');
372 SELECT 11 = (
373 SELECT b
374 FROM (
375 SELECT a AS b
376 FROM dual
377 LEFT JOIN (SELECT 22 AS a FROM dual)
378 )
379 GROUP BY b, b
380 );
381 } 0
382
383 #-------------------------------------------------------------------------
384 # 2026-03-05 - do not "skip-ahead" of a null-row on the RHS of a
385 # LEFT JOIN.
386 #
387 reset_db
388
389 do_execsql_test 6000 {
390 CREATE TABLE t1(c1 UNIQUE NOT NULL);
391 INSERT INTO t1 VALUES(1);
392 CREATE TABLE t0(c0 UNIQUE);
393 INSERT INTO t0 VALUES(0);
394 WITH RECURSIVE c(n) AS (VALUES(1) UNION ALL SELECT n+1 FROM c WHERE n<1000)
395 INSERT INTO t0(c0) SELECT NULL FROM c;
396 } {}
397
398 do_execsql_test 6010 {
399 SELECT DISTINCT * FROM t1 LEFT OUTER JOIN t0 ON c0>c1;
400 } {1 {}}
401
402 do_execsql_test 6020 {
403 SELECT DISTINCT * FROM t1 FULL OUTER JOIN t0 ON c0>c1;
404 } {1 {} {} 0 {} {}}
405
406 do_execsql_test 6040 {
407 ANALYZE;
408 SELECT DISTINCT * FROM t1 LEFT OUTER JOIN t0 ON c0>c1;
409 } {1 {}}
410
411 do_execsql_test 6050 {
412 SELECT DISTINCT * FROM t1 FULL OUTER JOIN t0 ON c0>c1;
413 } {1 {} {} 0 {} {}}
414
415 finish_test