AryaWu/sqlite
0
1# 2018-02-262#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing expressions of the form13#14# x IS TRUE15# x IS FALSE16# x IS NOT TRUE17# x IS NOT FALSE18#19# Tests are also included for the use of TRUE and FALSE as20# literal values.21 22set testdir [file dirname $argv0]23source $testdir/tester.tcl24 25do_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}30do_execsql_test istrue-110 {31 SELECT x FROM t1 WHERE y IS FALSE;32} {2}33do_execsql_test istrue-120 {34 SELECT x FROM t1 WHERE y IS NULL;35} {3}36do_execsql_test istrue-130 {37 SELECT x FROM t1 WHERE y IS NOT TRUE;38} {2 3}39do_execsql_test istrue-140 {40 SELECT x FROM t1 WHERE y IS NOT FALSE;41} {1 3}42do_execsql_test istrue-150 {43 SELECT x FROM t1 WHERE y IS NOT NULL;44} {1 2}45unset -nocomplain X46set X 947do_execsql_test istrue-160 {48 SELECT x FROM t1 WHERE y IS TRUE OR (8==$X)49} {1}50do_execsql_test istrue-170 {51 SELECT x FROM t1 WHERE y IS FALSE OR (8==$X)52} {2}53do_execsql_test istrue-180 {54 SELECT x FROM t1 WHERE y IS NULL OR (8==$X);55} {3}56do_execsql_test istrue-190 {57 SELECT x FROM t1 WHERE y IS NOT TRUE OR (8==$X);58} {2 3}59do_execsql_test istrue-200 {60 SELECT x FROM t1 WHERE y IS NOT FALSE OR (8==$X);61} {1 3}62do_execsql_test istrue-210 {63 SELECT x FROM t1 WHERE y IS NOT NULL OR (8==$X);64} {1 2}65 66do_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 73do_execsql_test istrue-400 {74 SELECT x FROM t1 WHERE true;75} {1 2 3}76do_execsql_test istrue-410 {77 SELECT x FROM t1 WHERE false;78} {}79 80do_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}91do_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}101do_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 {} {}}113do_catchsql_test istrue-521 {114 INSERT INTO t2 VALUES(2,false,false,null,null);115} {1 {CHECK constraint failed: b IS TRUE}}116do_catchsql_test istrue-522 {117 INSERT INTO t2 VALUES(2,true,true,null,null);118} {1 {CHECK constraint failed: c IS FALSE}}119do_catchsql_test istrue-523 {120 INSERT INTO t2 VALUES(2,true,false,true,null);121} {1 {CHECK constraint failed: d IS NOT TRUE}}122do_catchsql_test istrue-524 {123 INSERT INTO t2 VALUES(2,true,false,null,false);124} {1 {CHECK constraint failed: e IS NOT FALSE}}125 126foreach {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 $val134 sqlite3_step $::STMT135 sqlite3_reset $::STMT136 sqlite3_finalize $::STMT137 } {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 146ifcapable 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 true152 );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 163do_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 Chromium178# https://bugs.chromium.org/p/chromium/issues/detail?id=1094247179do_catchsql_test istrue-800 {180 SELECT 9 IN (false.false);181} {1 {no such column: false.false}}182do_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}187do_catchsql_test istrue-820 {188 SELECT 9 IN (false.false) FROM t8;189} {1 {no such column: false.false}}190do_execsql_test istrue-830 {191 CREATE TABLE false(true INT, false INT, x INT CHECK (5 IN (false.false)));192} {}193do_execsql_test istrue-840 {194 INSERT INTO False VALUES(4,5,6);195} {}196do_catchsql_test istrue-841 {197 INSERT INTO False VALUES(5,6,7);198} {1 {CHECK constraint failed: 5 IN (false.false)}}199do_execsql_test istrue-850 {200 SELECT 9 IN (false.false) FROM false;201} {0}202do_execsql_test istrue-851 {203 SELECT 5 IN (false.false) FROM false;204} {1}205 206finish_test207