Debian Codegraph @

test/nulls1.test

raw
1 # 2019 August 10
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.
12 #
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix nulls1
17
18 do_execsql_test 1.0 {
19 DROP TABLE IF EXISTS t3;
20 CREATE TABLE t3(a INTEGER);
21 INSERT INTO t3 VALUES(NULL), (10), (30), (20), (NULL);
22 } {}
23
24 for {set a 0} {$a < 3} {incr a} {
25 foreach {tn limit} {
26 1 ""
27 2 "LIMIT 10"
28 } {
29 do_execsql_test 1.$a.$tn.1 "
30 SELECT a FROM t3 ORDER BY a nULLS FIRST $limit
31 " {{} {} 10 20 30}
32
33 do_execsql_test 1.$a.$tn.2 "
34 SELECT a FROM t3 ORDER BY a nULLS LAST $limit
35 " {10 20 30 {} {}}
36
37 do_execsql_test 1.$a.$tn.3 "
38 SELECT a FROM t3 ORDER BY a DESC nULLS FIRST $limit
39 " {{} {} 30 20 10}
40
41 do_execsql_test 1.$a.$tn.4 "
42 SELECT a FROM t3 ORDER BY a DESC nULLS LAST $limit
43 " {30 20 10 {} {}}
44 }
45
46 switch $a {
47 0 {
48 execsql { CREATE INDEX i1 ON t3(a) }
49 }
50 1 {
51 execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t3(a DESC) }
52 }
53 }
54 }
55
56 #-------------------------------------------------------------------------
57 reset_db
58 do_execsql_test 2.0 {
59 CREATE TABLE t2(a, b, c);
60 CREATE INDEX i2 ON t2(a, b);
61 INSERT INTO t2 VALUES(1, 1, 1);
62 INSERT INTO t2 VALUES(1, NULL, 2);
63 INSERT INTO t2 VALUES(1, NULL, 3);
64 INSERT INTO t2 VALUES(1, 4, 4);
65 }
66
67 do_execsql_test 2.1 {
68 SELECT * FROM t2 WHERE a=1 ORDER BY b NULLS LAST
69 } {
70 1 1 1 1 4 4 1 {} 2 1 {} 3
71 }
72
73 do_execsql_test 2.2 {
74 SELECT * FROM t2 WHERE a=1 ORDER BY b DESC NULLS FIRST
75 } {
76 1 {} 3
77 1 {} 2
78 1 4 4
79 1 1 1
80 }
81
82 #-------------------------------------------------------------------------
83 #
84 reset_db
85 do_execsql_test 3.0 {
86 CREATE TABLE t1(a, b, c, d, UNIQUE (b));
87 }
88 foreach {tn sql err} {
89 1 { CREATE INDEX i1 ON t1(a ASC NULLS LAST) } LAST
90 2 { CREATE INDEX i1 ON t1(a ASC NULLS FIRST) } FIRST
91 3 { CREATE INDEX i1 ON t1(a, b ASC NULLS LAST) } LAST
92 4 { CREATE INDEX i1 ON t1(a, b ASC NULLS FIRST) } FIRST
93 5 { CREATE INDEX i1 ON t1(a DESC NULLS LAST) } LAST
94 6 { CREATE INDEX i1 ON t1(a DESC NULLS FIRST) } FIRST
95 7 { CREATE INDEX i1 ON t1(a, b DESC NULLS LAST) } LAST
96 8 { CREATE INDEX i1 ON t1(a, b DESC NULLS FIRST) } FIRST
97 9 { CREATE TABLE t2(a, b, PRIMARY KEY(a DESC, b NULLS FIRST)) } FIRST
98 10 { CREATE TABLE t2(a, b, UNIQUE(a DESC NULLS FIRST, b)) } FIRST
99 11 { INSERT INTO t1 VALUES(1, 2, 3, 4)
100 ON CONFLICT (b DESC NULLS LAST) DO UPDATE SET a = a+1 } LAST
101 12 {
102 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
103 INSERT INTO t1 VALUES(1, 2, 3, 4)
104 ON CONFLICT (b DESC NULLS FIRST) DO UPDATE SET a = a+1;
105 END
106 } FIRST
107 } {
108 do_catchsql_test 3.1.$tn $sql "1 {unsupported use of NULLS $err}"
109 }
110
111 do_execsql_test 3.2 {
112 CREATE TABLE first(nulls, last);
113 INSERT INTO first(last, nulls) VALUES(100,200), (300,400), (200,300);
114 SELECT * FROM first ORDER BY nulls;
115 } {
116 200 100
117 300 200
118 400 300
119 }
120
121 #-------------------------------------------------------------------------
122 #
123 ifcapable vtab {
124 register_echo_module db
125 do_execsql_test 4.0 {
126 CREATE TABLE tx(a INTEGER PRIMARY KEY, b, c);
127 CREATE INDEX i1 ON tx(b);
128 INSERT INTO tx VALUES(1, 1, 1);
129 INSERT INTO tx VALUES(2, NULL, 2);
130 INSERT INTO tx VALUES(3, 3, 3);
131 INSERT INTO tx VALUES(4, NULL, 4);
132 INSERT INTO tx VALUES(5, 5, 5);
133 CREATE VIRTUAL TABLE te USING echo(tx);
134 }
135
136 do_execsql_test 4.1 {
137 SELECT * FROM tx ORDER BY b NULLS FIRST;
138 } {2 {} 2 4 {} 4 1 1 1 3 3 3 5 5 5}
139 do_execsql_test 4.2 {
140 SELECT * FROM te ORDER BY b NULLS FIRST;
141 } {2 {} 2 4 {} 4 1 1 1 3 3 3 5 5 5}
142
143 do_execsql_test 4.3 {
144 SELECT * FROM tx ORDER BY b NULLS LAST;
145 } {1 1 1 3 3 3 5 5 5 2 {} 2 4 {} 4}
146 do_execsql_test 4.4 {
147 SELECT * FROM te ORDER BY b NULLS LAST;
148 } {1 1 1 3 3 3 5 5 5 2 {} 2 4 {} 4}
149 }
150
151 #-------------------------------------------------------------------------
152 #
153 do_execsql_test 5.0 {
154 CREATE TABLE t4(a, b, c);
155 INSERT INTO t4 VALUES(1, 1, 11);
156 INSERT INTO t4 VALUES(1, 2, 12);
157 INSERT INTO t4 VALUES(1, NULL, 1);
158
159 INSERT INTO t4 VALUES(2, NULL, 1);
160 INSERT INTO t4 VALUES(2, 2, 12);
161 INSERT INTO t4 VALUES(2, 1, 11);
162
163 INSERT INTO t4 VALUES(3, NULL, 1);
164 INSERT INTO t4 VALUES(3, 2, 12);
165 INSERT INTO t4 VALUES(3, NULL, 3);
166 }
167
168 do_execsql_test 5.1 {
169 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a, b NULLS LAST
170 } {
171 1 1 11 1 2 12 1 {} 1
172 2 1 11 2 2 12 2 {} 1
173 3 2 12 3 {} 1 3 {} 3
174 }
175 do_execsql_test 5.2 {
176 CREATE INDEX t4ab ON t4(a, b);
177 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a, b NULLS LAST
178 } {
179 1 1 11 1 2 12 1 {} 1
180 2 1 11 2 2 12 2 {} 1
181 3 2 12 3 {} 1 3 {} 3
182 }
183 do_eqp_test 5.3 {
184 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a, b NULLS LAST
185 } {
186 QUERY PLAN
187 `--SEARCH t4 USING INDEX t4ab (a=?)
188 }
189
190 do_execsql_test 5.4 {
191 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a DESC, b DESC NULLS FIRST
192 } {
193 3 {} 3 3 {} 1 3 2 12
194 2 {} 1 2 2 12 2 1 11
195 1 {} 1 1 2 12 1 1 11
196 }
197 do_eqp_test 5.5 {
198 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a DESC, b DESC NULLS FIRST
199 } {
200 QUERY PLAN
201 `--SEARCH t4 USING INDEX t4ab (a=?)
202 }
203
204 #-------------------------------------------------------------------------
205 #
206 do_execsql_test 6.0 {
207 CREATE TABLE t5(a, b, c);
208 WITH s(i) AS (
209 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<200
210 )
211 INSERT INTO t5 SELECT i%2, CASE WHEN (i%10)==0 THEN NULL ELSE i END, i FROM s;
212 }
213
214 set res1 [db eval { SELECT a,b FROM t5 WHERE a=1 ORDER BY b NULLS LAST, c }]
215 set res2 [db eval {
216 SELECT a,b FROM t5 WHERE a=1 ORDER BY b DESC NULLS FIRST, c DESC
217 }]
218
219 do_execsql_test 6.1.1 {
220 CREATE INDEX t5ab ON t5(a, b, c);
221 SELECT a,b FROM t5 WHERE a=1 ORDER BY b NULLS LAST, c;
222 } $res1
223 do_eqp_test 6.1.2 {
224 SELECT a,b FROM t5 WHERE a=1 ORDER BY b NULLS LAST, c;
225 } {
226 QUERY PLAN
227 `--SEARCH t5 USING COVERING INDEX t5ab (a=?)
228 }
229 do_execsql_test 6.2.1 {
230 SELECT a,b FROM t5 WHERE a=1 ORDER BY b DESC NULLS FIRST, c DESC
231 } $res2
232 do_eqp_test 6.2.2 {
233 SELECT a,b FROM t5 WHERE a=1 ORDER BY b DESC NULLS FIRST, c DESC
234 } {
235 QUERY PLAN
236 `--SEARCH t5 USING COVERING INDEX t5ab (a=?)
237 }
238
239 #-------------------------------------------------------------------------
240 do_execsql_test 7.0 {
241 CREATE TABLE t71(a, b, c);
242 CREATE INDEX t71abc ON t71(a, b, c);
243
244 SELECT * FROM t71 WHERE a=1 AND b=2 ORDER BY c NULLS LAST;
245 SELECT * FROM t71 WHERE a=1 AND b=2 ORDER BY c DESC NULLS FIRST;
246
247 SELECT * FROM t71 ORDER BY a NULLS LAST;
248 SELECT * FROM t71 ORDER BY a DESC NULLS FIRST;
249 }
250
251 # 2019-12-18 gramfuzz1 find
252 # NULLS LAST not allows on an INTEGER PRIMARY KEY.
253 #
254 do_catchsql_test 8.0 {
255 CREATE TABLE t80(a, b INTEGER, PRIMARY KEY(b NULLS LAST)) WITHOUT ROWID;
256 } {1 {unsupported use of NULLS LAST}}
257
258 #-------------------------------------------------------------------------
259 reset_db
260 do_execsql_test 9.0 {
261 CREATE TABLE v0 (c1, c2, c3);
262 CREATE INDEX v3 ON v0 (c1, c2, c3);
263 }
264 do_execsql_test 9.1 {
265 ANALYZE sqlite_master;
266 INSERT INTO sqlite_stat1 VALUES('v0','v3','648 324 81');
267 ANALYZE sqlite_master;
268 }
269
270 do_execsql_test 9.2 {
271 INSERT INTO v0 VALUES
272 (1, 10, 'b'),
273 (1, 10, 'd'),
274 (1, 10, NULL),
275 (2, 10, 'a'),
276 (2, 10, NULL),
277 (1, 10, 'c'),
278 (2, 10, 'b'),
279 (1, 10, 'a'),
280 (1, 10, NULL),
281 (2, 10, NULL),
282 (2, 10, 'd'),
283 (2, 10, 'c');
284 }
285
286 do_execsql_test 9.3 {
287 SELECT c1, c2, ifnull(c3, 'NULL') FROM v0
288 WHERE c2=10 ORDER BY c1, c3 NULLS LAST
289 } {
290 1 10 a 1 10 b 1 10 c 1 10 d 1 10 NULL 1 10 NULL
291 2 10 a 2 10 b 2 10 c 2 10 d 2 10 NULL 2 10 NULL
292 }
293
294 do_eqp_test 9.4 {
295 SELECT c1, c2, ifnull(c3, 'NULL') FROM v0
296 WHERE c2=10 ORDER BY c1, c3 NULLS LAST
297 } {SEARCH v0 USING COVERING INDEX v3 (ANY(c1) AND c2=?)}
298
299
300 # 2020-03-01 ticket e12a0ae526bb51c7
301 # NULLS LAST on a LEFT JOIN
302 #
303 reset_db
304 do_execsql_test 10.10 {
305 CREATE TABLE t1(x);
306 INSERT INTO t1(x) VALUES('X');
307 CREATE TABLE t2(c, d);
308 CREATE INDEX t2dc ON t2(d, c);
309 SELECT c FROM t1 LEFT JOIN t2 ON d=NULL ORDER BY d, c NULLS LAST;
310 } {{}}
311 do_execsql_test 10.20 {
312 INSERT INTO t2(c,d) VALUES(5,'X'),(6,'Y'),(7,'Z'),(3,'A'),(4,'B');
313 SELECT c FROM t1 LEFT JOIN t2 ON d=x ORDER BY d, c NULLS LAST;
314 } {5}
315 do_execsql_test 10.30 {
316 UPDATE t2 SET d='X';
317 UPDATE t2 SET c=NULL WHERE c=6;
318 SELECT c FROM t1 LEFT JOIN t2 ON d=x ORDER BY d NULLS FIRST, c NULLS FIRST;
319 } {{} 3 4 5 7}
320 do_execsql_test 10.40 {
321 SELECT c FROM t1 LEFT JOIN t2 ON d=x ORDER BY d NULLS LAST, c NULLS LAST;
322 } {3 4 5 7 {}}
323 do_execsql_test 10.41 {
324 SELECT c FROM t1 LEFT JOIN t2 ON d=x ORDER BY c NULLS LAST;
325 } {3 4 5 7 {}}
326 do_execsql_test 10.42 {
327 SELECT c FROM t1 LEFT JOIN t2 ON d=x ORDER BY +d NULLS LAST, +c NULLS LAST;
328 } {3 4 5 7 {}}
329 do_execsql_test 10.50 {
330 INSERT INTO t1(x) VALUES(NULL),('Y');
331 SELECT x, c, d, '|' FROM t1 LEFT JOIN t2 ON d=x
332 ORDER BY d NULLS LAST, c NULLS LAST;
333 } {X 3 X | X 4 X | X 5 X | X 7 X | X {} X | {} {} {} | Y {} {} |}
334 do_execsql_test 10.51 {
335 SELECT x, c, d, '|' FROM t1 LEFT JOIN t2 ON d=x
336 ORDER BY +d NULLS LAST, +c NULLS LAST;
337 } {X 3 X | X 4 X | X 5 X | X 7 X | X {} X | {} {} {} | Y {} {} |}
338
339 #-------------------------------------------------------------------------
340 reset_db
341
342 do_execsql_test 11.1 {
343 CREATE TABLE t1(a TEXT COLLATE NOCASE, b TEXT);
344 INSERT INTO t1 VALUES('Hello', 'world');
345 }
346
347 foreach {tn sql res} {
348 0 "SELECT null" NULL
349
350 1 "SELECT ('hello', NULL) IN (SELECT a, b FROM t1)" NULL
351 2 "SELECT NOT ('hello', NULL) IN (SELECT a, b FROM t1)" NULL
352
353 3 "SELECT ('Hello', NULL) IN (SELECT a, b FROM t1)" NULL
354 4 "SELECT ('Hello' COLLATE NOCASE, NULL) IN (SELECT a, b FROM t1)" NULL
355 5 "SELECT ('hello', 'world') IN (SELECT a, b FROM t1)" 1
356
357 6 "SELECT ('hi', NULL) IN (SELECT a, b FROM t1)" 0
358
359 7 "SELECT ('hello', NULL) IN ((a, b), (3, 4), (5, 6)) FROM t1" 0
360 8 "SELECT (a, b) IN (('hello', NULL), (3, 4), (5, 6)) FROM t1" NULL
361
362 9 "SELECT ('Hello', NULL) IN ((a, b), (3, 4), (5, 6)) FROM t1" NULL
363 } {
364 db nullvalue NULL
365 do_execsql_test 11.2.$tn $sql $res
366 }
367
368
369
370
371 finish_test