xref: /sqlite-3.40.0/test/nulls1.test (revision 4fcb9caa) 
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#-------------------------------------------------------------------------
122ifcapable vtab {
123  register_echo_module db
124  do_execsql_test 4.0 {
125    CREATE TABLE tx(a INTEGER PRIMARY KEY, b, c);
126    CREATE INDEX i1 ON tx(b);
127    INSERT INTO tx VALUES(1, 1, 1);
128    INSERT INTO tx VALUES(2, NULL, 2);
129    INSERT INTO tx VALUES(3, 3, 3);
130    INSERT INTO tx VALUES(4, NULL, 4);
131    INSERT INTO tx VALUES(5, 5, 5);
132    CREATE VIRTUAL TABLE te USING echo(tx);
133  }
134
135  do_execsql_test 4.1 {
136    SELECT * FROM tx ORDER BY b NULLS FIRST;
137  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}
138  do_execsql_test 4.2 {
139    SELECT * FROM te ORDER BY b NULLS FIRST;
140  } {2 {} 2  4 {} 4  1 1 1  3 3 3  5 5 5}
141
142  do_execsql_test 4.3 {
143    SELECT * FROM tx ORDER BY b NULLS LAST;
144  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
145  do_execsql_test 4.4 {
146    SELECT * FROM te ORDER BY b NULLS LAST;
147  } {1 1 1  3 3 3  5 5 5  2 {} 2  4 {} 4}
148}
149
150finish_test
151