Debian Codegraph @

test/istrue.test

raw
1 # 2018-02-26
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. The
12 # focus of this file is testing expressions of the form
13 #
14 # x IS TRUE
15 # x IS FALSE
16 # x IS NOT TRUE
17 # x IS NOT FALSE
18 #
19 # Tests are also included for the use of TRUE and FALSE as
20 # literal values.
21
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
24
25 do_execsql_test istrue-100 {
26 CREATE TABLE t1(x INTEGER PRIMARY KEY, y BOOLEAN);
27 INSERT INTO t1 VALUES(1, true),(2, false),(3, null);
28 SELECT x FROM t1 WHERE y IS TRUE;
29 } {1}
30 do_execsql_test istrue-110 {
31 SELECT x FROM t1 WHERE y IS FALSE;
32 } {2}
33 do_execsql_test istrue-120 {
34 SELECT x FROM t1 WHERE y IS NULL;
35 } {3}
36 do_execsql_test istrue-130 {
37 SELECT x FROM t1 WHERE y IS NOT TRUE;
38 } {2 3}
39 do_execsql_test istrue-140 {
40 SELECT x FROM t1 WHERE y IS NOT FALSE;
41 } {1 3}
42 do_execsql_test istrue-150 {
43 SELECT x FROM t1 WHERE y IS NOT NULL;
44 } {1 2}
45 unset -nocomplain X
46 set X 9
47 do_execsql_test istrue-160 {
48 SELECT x FROM t1 WHERE y IS TRUE OR (8==$X)
49 } {1}
50 do_execsql_test istrue-170 {
51 SELECT x FROM t1 WHERE y IS FALSE OR (8==$X)
52 } {2}
53 do_execsql_test istrue-180 {
54 SELECT x FROM t1 WHERE y IS NULL OR (8==$X);
55 } {3}
56 do_execsql_test istrue-190 {
57 SELECT x FROM t1 WHERE y IS NOT TRUE OR (8==$X);
58 } {2 3}
59 do_execsql_test istrue-200 {
60 SELECT x FROM t1 WHERE y IS NOT FALSE OR (8==$X);
61 } {1 3}
62 do_execsql_test istrue-210 {
63 SELECT x FROM t1 WHERE y IS NOT NULL OR (8==$X);
64 } {1 2}
65
66 do_execsql_test istrue-300 {
67 SELECT x,
68 y IS TRUE, y IS FALSE, y is NULL,
69 y IS NOT TRUE, y IS NOT FALSE, y IS NOT NULL, '|'
70 FROM t1 ORDER BY x;
71 } {1 1 0 0 0 1 1 | 2 0 1 0 1 0 1 | 3 0 0 1 1 1 0 |}
72
73 do_execsql_test istrue-400 {
74 SELECT x FROM t1 WHERE true;
75 } {1 2 3}
76 do_execsql_test istrue-410 {
77 SELECT x FROM t1 WHERE false;
78 } {}
79
80 do_execsql_test istrue-500 {
81 CREATE TABLE t2(
82 a INTEGER PRIMARY KEY,
83 b BOOLEAN DEFAULT true,
84 c BOOLEAN DEFAULT(true),
85 d BOOLEAN DEFAULT false,
86 e BOOLEAN DEFAULT(false)
87 );
88 INSERT INTO t2 DEFAULT VALUES;
89 SELECT * FROM t2;
90 } {1 1 1 0 0}
91 do_execsql_test istrue-510 {
92 DROP TABLE t2;
93 CREATE TABLE t2(
94 a INTEGER PRIMARY KEY,
95 b BOOLEAN DEFAULT(not true),
96 c BOOLEAN DEFAULT(not false)
97 );
98 INSERT INTO t2(a) VALUES(99);
99 SELECT * FROM t2;
100 } {99 0 1}
101 do_execsql_test istrue-520 {
102 DROP TABLE t2;
103 CREATE TABLE t2(
104 a INTEGER PRIMARY KEY,
105 b BOOLEAN CHECK(b IS TRUE),
106 c BOOLEAN CHECK(c IS FALSE),
107 d BOOLEAN CHECK(d IS NOT TRUE),
108 e BOOLEAN CHECK(e IS NOT FALSE)
109 );
110 INSERT INTO t2 VALUES(1,true,false,null,null);
111 SELECT * FROM t2;
112 } {1 1 0 {} {}}
113 do_catchsql_test istrue-521 {
114 INSERT INTO t2 VALUES(2,false,false,null,null);
115 } {1 {CHECK constraint failed: b IS TRUE}}
116 do_catchsql_test istrue-522 {
117 INSERT INTO t2 VALUES(2,true,true,null,null);
118 } {1 {CHECK constraint failed: c IS FALSE}}
119 do_catchsql_test istrue-523 {
120 INSERT INTO t2 VALUES(2,true,false,true,null);
121 } {1 {CHECK constraint failed: d IS NOT TRUE}}
122 do_catchsql_test istrue-524 {
123 INSERT INTO t2 VALUES(2,true,false,null,false);
124 } {1 {CHECK constraint failed: e IS NOT FALSE}}
125
126 foreach {tn val} [list 1 NaN 2 -NaN 3 NaN0 4 -NaN0 5 Inf 6 -Inf] {
127 do_execsql_test istrue-600.$tn.1 {
128 DROP TABLE IF EXISTS t1;
129 CREATE TABLE t1(x);
130 }
131 do_test istrue-600.$tn.2 {
132 set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL]
133 sqlite3_bind_double $::STMT 1 $val
134 sqlite3_step $::STMT
135 sqlite3_reset $::STMT
136 sqlite3_finalize $::STMT
137 } {SQLITE_OK}
138 do_execsql_test istrue-600.$tn.3 {
139 SELECT x IS TRUE FROM t1;
140 } [expr {$tn in [list 5 6] ? {1} : {0}}]
141 do_execsql_test istrue-600.$tn.4 {
142 SELECT x IS FALSE FROM t1;
143 } {0}
144 }
145
146 ifcapable altertable {
147 do_execsql_test istrue-700 {
148 CREATE TABLE t7(
149 a INTEGER PRIMARY KEY,
150 b BOOLEAN DEFAULT false,
151 c BOOLEAN DEFAULT true
152 );
153 INSERT INTO t7(a) VALUES(1);
154 INSERT INTO t7(a,b,c) VALUES(2,true,false);
155 ALTER TABLE t7 ADD COLUMN d BOOLEAN DEFAULT false;
156 ALTER TABLE t7 ADD COLUMN e BOOLEAN DEFAULT true;
157 INSERT INTO t7(a,b,c) VALUES(3,true,false);
158 INSERT INTO t7 VALUES(4,false,true,true,false);
159 SELECT *,'x' FROM t7 ORDER BY a;
160 } {1 0 1 0 1 x 2 1 0 0 1 x 3 1 0 0 1 x 4 0 1 1 0 x}
161 }
162
163 do_execsql_test istrue-710 {
164 SELECT 0.5 IS TRUE COLLATE NOCASE;
165 SELECT 0.5 IS TRUE COLLATE RTRIM;
166 SELECT 0.5 IS TRUE COLLATE BINARY;
167
168 SELECT 0.5 IS TRUE;
169 SELECT 0.5 COLLATE NOCASE IS TRUE;
170 SELECT 0.0 IS FALSE;
171
172 SELECT 0.0 IS FALSE COLLATE NOCASE;
173 SELECT 0.0 IS FALSE COLLATE RTRIM;
174 SELECT 0.0 IS FALSE COLLATE BINARY;
175 } {1 1 1 1 1 1 1 1 1}
176
177 # 2020-06-12 bug report from Chromium
178 # https://bugs.chromium.org/p/chromium/issues/detail?id=1094247
179 do_catchsql_test istrue-800 {
180 SELECT 9 IN (false.false);
181 } {1 {no such column: false.false}}
182 do_execsql_test istrue-810 {
183 CREATE TABLE t8(a INT, true INT, false INT, d INT);
184 INSERT INTO t8(a,true,false,d) VALUES(5,6,7,8),(4,3,2,1),('a','b','c','d');
185 SELECT * FROM t8 ORDER BY false;
186 } {4 3 2 1 5 6 7 8 a b c d}
187 do_catchsql_test istrue-820 {
188 SELECT 9 IN (false.false) FROM t8;
189 } {1 {no such column: false.false}}
190 do_execsql_test istrue-830 {
191 CREATE TABLE false(true INT, false INT, x INT CHECK (5 IN (false.false)));
192 } {}
193 do_execsql_test istrue-840 {
194 INSERT INTO False VALUES(4,5,6);
195 } {}
196 do_catchsql_test istrue-841 {
197 INSERT INTO False VALUES(5,6,7);
198 } {1 {CHECK constraint failed: 5 IN (false.false)}}
199 do_execsql_test istrue-850 {
200 SELECT 9 IN (false.false) FROM false;
201 } {0}
202 do_execsql_test istrue-851 {
203 SELECT 5 IN (false.false) FROM false;
204 } {1}
205
206 finish_test