test/nulls1.test

   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
 341
 342
 343 finish_test