AryaWu/sqlite
0
1# 2019 August 102#3# The author disclaims copyright to this source code. In place of4# 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 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16set testprefix nulls117 18do_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 24for {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 $limit31 " {{} {} 10 20 30}32 33 do_execsql_test 1.$a.$tn.2 "34 SELECT a FROM t3 ORDER BY a nULLS LAST $limit35 " {10 20 30 {} {}}36 37 do_execsql_test 1.$a.$tn.3 "38 SELECT a FROM t3 ORDER BY a DESC nULLS FIRST $limit39 " {{} {} 30 20 10}40 41 do_execsql_test 1.$a.$tn.4 "42 SELECT a FROM t3 ORDER BY a DESC nULLS LAST $limit43 " {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#-------------------------------------------------------------------------57reset_db58do_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 67do_execsql_test 2.1 {68 SELECT * FROM t2 WHERE a=1 ORDER BY b NULLS LAST69} {70 1 1 1 1 4 4 1 {} 2 1 {} 371}72 73do_execsql_test 2.2 {74 SELECT * FROM t2 WHERE a=1 ORDER BY b DESC NULLS FIRST75} {76 1 {} 377 1 {} 2 78 1 4 4 79 1 1 180}81 82#-------------------------------------------------------------------------83#84reset_db85do_execsql_test 3.0 {86 CREATE TABLE t1(a, b, c, d, UNIQUE (b));87}88foreach {tn sql err} {89 1 { CREATE INDEX i1 ON t1(a ASC NULLS LAST) } LAST90 2 { CREATE INDEX i1 ON t1(a ASC NULLS FIRST) } FIRST91 3 { CREATE INDEX i1 ON t1(a, b ASC NULLS LAST) } LAST92 4 { CREATE INDEX i1 ON t1(a, b ASC NULLS FIRST) } FIRST93 5 { CREATE INDEX i1 ON t1(a DESC NULLS LAST) } LAST94 6 { CREATE INDEX i1 ON t1(a DESC NULLS FIRST) } FIRST95 7 { CREATE INDEX i1 ON t1(a, b DESC NULLS LAST) } LAST96 8 { CREATE INDEX i1 ON t1(a, b DESC NULLS FIRST) } FIRST97 9 { CREATE TABLE t2(a, b, PRIMARY KEY(a DESC, b NULLS FIRST)) } FIRST98 10 { CREATE TABLE t2(a, b, UNIQUE(a DESC NULLS FIRST, b)) } FIRST99 11 { INSERT INTO t1 VALUES(1, 2, 3, 4)100 ON CONFLICT (b DESC NULLS LAST) DO UPDATE SET a = a+1 } LAST101 12 {102 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN103 INSERT INTO t1 VALUES(1, 2, 3, 4)104 ON CONFLICT (b DESC NULLS FIRST) DO UPDATE SET a = a+1;105 END106 } FIRST107} {108 do_catchsql_test 3.1.$tn $sql "1 {unsupported use of NULLS $err}"109}110 111do_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 100117 300 200118 400 300119}120 121#-------------------------------------------------------------------------122#123ifcapable vtab {124 register_echo_module db125 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#153do_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 168do_execsql_test 5.1 {169 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a, b NULLS LAST170} {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 {} 3174}175do_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 LAST178} {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 {} 3182}183do_eqp_test 5.3 {184 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a, b NULLS LAST185} {186 QUERY PLAN187 `--SEARCH t4 USING INDEX t4ab (a=?)188}189 190do_execsql_test 5.4 {191 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a DESC, b DESC NULLS FIRST192} {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}197do_eqp_test 5.5 {198 SELECT * FROM t4 WHERE a IN (1, 2, 3) ORDER BY a DESC, b DESC NULLS FIRST199} {200 QUERY PLAN201 `--SEARCH t4 USING INDEX t4ab (a=?)202}203 204#-------------------------------------------------------------------------205#206do_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<200210 ) 211 INSERT INTO t5 SELECT i%2, CASE WHEN (i%10)==0 THEN NULL ELSE i END, i FROM s;212}213 214set res1 [db eval { SELECT a,b FROM t5 WHERE a=1 ORDER BY b NULLS LAST, c }]215set res2 [db eval { 216 SELECT a,b FROM t5 WHERE a=1 ORDER BY b DESC NULLS FIRST, c DESC 217}]218 219do_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} $res1223do_eqp_test 6.1.2 {224 SELECT a,b FROM t5 WHERE a=1 ORDER BY b NULLS LAST, c;225} {226 QUERY PLAN227 `--SEARCH t5 USING COVERING INDEX t5ab (a=?)228}229do_execsql_test 6.2.1 {230 SELECT a,b FROM t5 WHERE a=1 ORDER BY b DESC NULLS FIRST, c DESC 231} $res2232do_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 PLAN236 `--SEARCH t5 USING COVERING INDEX t5ab (a=?)237}238 239#-------------------------------------------------------------------------240do_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 find252# NULLS LAST not allows on an INTEGER PRIMARY KEY.253#254do_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#-------------------------------------------------------------------------259reset_db260do_execsql_test 9.0 {261 CREATE TABLE v0 (c1, c2, c3);262 CREATE INDEX v3 ON v0 (c1, c2, c3);263}264do_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 270do_execsql_test 9.2 {271 INSERT INTO v0 VALUES272 (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 286do_execsql_test 9.3 {287 SELECT c1, c2, ifnull(c3, 'NULL') FROM v0 288 WHERE c2=10 ORDER BY c1, c3 NULLS LAST289} {290 1 10 a 1 10 b 1 10 c 1 10 d 1 10 NULL 1 10 NULL291 2 10 a 2 10 b 2 10 c 2 10 d 2 10 NULL 2 10 NULL292}293 294do_eqp_test 9.4 {295 SELECT c1, c2, ifnull(c3, 'NULL') FROM v0 296 WHERE c2=10 ORDER BY c1, c3 NULLS LAST297} {SEARCH v0 USING COVERING INDEX v3 (ANY(c1) AND c2=?)}298 299 300# 2020-03-01 ticket e12a0ae526bb51c7301# NULLS LAST on a LEFT JOIN302#303reset_db304do_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} {{}}311do_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}315do_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}320do_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 {}}323do_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 {}}326do_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 {}}329do_execsql_test 10.50 {330 INSERT INTO t1(x) VALUES(NULL),('Y');331 SELECT x, c, d, '|' FROM t1 LEFT JOIN t2 ON d=x332 ORDER BY d NULLS LAST, c NULLS LAST;333} {X 3 X | X 4 X | X 5 X | X 7 X | X {} X | {} {} {} | Y {} {} |}334do_execsql_test 10.51 {335 SELECT x, c, d, '|' FROM t1 LEFT JOIN t2 ON d=x336 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 341 342 343finish_test344