gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/istrue.test (about) 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