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 14set testdir [file dirname $argv0] 15source $testdir/tester.tcl 16set testprefix nulls1 17 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 $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#------------------------------------------------------------------------- 57reset_db 58do_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 LAST 69} { 70 1 1 1 1 4 4 1 {} 2 1 {} 3 71} 72 73do_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# 84reset_db 85do_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) } 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 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 100 117 300 200 118 400 300 119} 120 121#------------------------------------------------------------------------- 122# 123ifcapable 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# 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 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} 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 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} 183do_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 TABLE 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 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} 197do_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 TABLE 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<200 210 ) 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} $res1 223do_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 TABLE 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} $res2 232do_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 TABLE 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 find 252# 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_db 260do_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 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 286do_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 294do_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 TABLE v0 USING COVERING INDEX v3 (ANY(c1) AND c2=?)} 298 299 300 301finish_test 302