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 258finish_test 259