# 2018-02-26 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing expressions of the form # # x IS TRUE # x IS FALSE # x IS NOT TRUE # x IS NOT FALSE # # Tests are also included for the use of TRUE and FALSE as # literal values. set testdir [file dirname $argv0] source $testdir/tester.tcl do_execsql_test istrue-100 { CREATE TABLE t1(x INTEGER PRIMARY KEY, y BOOLEAN); INSERT INTO t1 VALUES(1, true),(2, false),(3, null); SELECT x FROM t1 WHERE y IS TRUE; } {1} do_execsql_test istrue-110 { SELECT x FROM t1 WHERE y IS FALSE; } {2} do_execsql_test istrue-120 { SELECT x FROM t1 WHERE y IS NULL; } {3} do_execsql_test istrue-130 { SELECT x FROM t1 WHERE y IS NOT TRUE; } {2 3} do_execsql_test istrue-140 { SELECT x FROM t1 WHERE y IS NOT FALSE; } {1 3} do_execsql_test istrue-150 { SELECT x FROM t1 WHERE y IS NOT NULL; } {1 2} unset -nocomplain X set X 9 do_execsql_test istrue-160 { SELECT x FROM t1 WHERE y IS TRUE OR (8==$X) } {1} do_execsql_test istrue-170 { SELECT x FROM t1 WHERE y IS FALSE OR (8==$X) } {2} do_execsql_test istrue-180 { SELECT x FROM t1 WHERE y IS NULL OR (8==$X); } {3} do_execsql_test istrue-190 { SELECT x FROM t1 WHERE y IS NOT TRUE OR (8==$X); } {2 3} do_execsql_test istrue-200 { SELECT x FROM t1 WHERE y IS NOT FALSE OR (8==$X); } {1 3} do_execsql_test istrue-210 { SELECT x FROM t1 WHERE y IS NOT NULL OR (8==$X); } {1 2} do_execsql_test istrue-300 { SELECT x, y IS TRUE, y IS FALSE, y is NULL, y IS NOT TRUE, y IS NOT FALSE, y IS NOT NULL, '|' FROM t1 ORDER BY x; } {1 1 0 0 0 1 1 | 2 0 1 0 1 0 1 | 3 0 0 1 1 1 0 |} do_execsql_test istrue-400 { SELECT x FROM t1 WHERE true; } {1 2 3} do_execsql_test istrue-410 { SELECT x FROM t1 WHERE false; } {} do_execsql_test istrue-500 { CREATE TABLE t2( a INTEGER PRIMARY KEY, b BOOLEAN DEFAULT true, c BOOLEAN DEFAULT(true), d BOOLEAN DEFAULT false, e BOOLEAN DEFAULT(false) ); INSERT INTO t2 DEFAULT VALUES; SELECT * FROM t2; } {1 1 1 0 0} do_execsql_test istrue-510 { DROP TABLE t2; CREATE TABLE t2( a INTEGER PRIMARY KEY, b BOOLEAN DEFAULT(not true), c BOOLEAN DEFAULT(not false) ); INSERT INTO t2(a) VALUES(99); SELECT * FROM t2; } {99 0 1} do_execsql_test istrue-520 { DROP TABLE t2; CREATE TABLE t2( a INTEGER PRIMARY KEY, b BOOLEAN CHECK(b IS TRUE), c BOOLEAN CHECK(c IS FALSE), d BOOLEAN CHECK(d IS NOT TRUE), e BOOLEAN CHECK(e IS NOT FALSE) ); INSERT INTO t2 VALUES(1,true,false,null,null); SELECT * FROM t2; } {1 1 0 {} {}} do_catchsql_test istrue-521 { INSERT INTO t2 VALUES(2,false,false,null,null); } {1 {CHECK constraint failed: t2}} do_catchsql_test istrue-522 { INSERT INTO t2 VALUES(2,true,true,null,null); } {1 {CHECK constraint failed: t2}} do_catchsql_test istrue-523 { INSERT INTO t2 VALUES(2,true,false,true,null); } {1 {CHECK constraint failed: t2}} do_catchsql_test istrue-524 { INSERT INTO t2 VALUES(2,true,false,null,false); } {1 {CHECK constraint failed: t2}} foreach {tn val} [list 1 NaN 2 -NaN 3 NaN0 4 -NaN0 5 Inf 6 -Inf] { do_execsql_test istrue-600.$tn.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x); } do_test istrue-600.$tn.2 { set ::STMT [sqlite3_prepare db "INSERT INTO t1 VALUES(?)" -1 TAIL] sqlite3_bind_double $::STMT 1 $val sqlite3_step $::STMT sqlite3_reset $::STMT sqlite3_finalize $::STMT } {SQLITE_OK} do_execsql_test istrue-600.$tn.3 { SELECT x IS TRUE FROM t1; } [expr {$tn in [list 5 6] ? {1} : {0}}] do_execsql_test istrue-600.$tn.4 { SELECT x IS FALSE FROM t1; } {0} } do_execsql_test istrue-700 { CREATE TABLE t7( a INTEGER PRIMARY KEY, b BOOLEAN DEFAULT false, c BOOLEAN DEFAULT true ); INSERT INTO t7(a) VALUES(1); INSERT INTO t7(a,b,c) VALUES(2,true,false); ALTER TABLE t7 ADD COLUMN d BOOLEAN DEFAULT false; ALTER TABLE t7 ADD COLUMN e BOOLEAN DEFAULT true; INSERT INTO t7(a,b,c) VALUES(3,true,false); INSERT INTO t7 VALUES(4,false,true,true,false); SELECT *,'x' FROM t7 ORDER BY a; } {1 0 1 0 1 x 2 1 0 0 1 x 3 1 0 0 1 x 4 0 1 1 0 x} do_execsql_test istrue-710 { SELECT 0.5 IS TRUE COLLATE NOCASE; SELECT 0.5 IS TRUE COLLATE RTRIM; SELECT 0.5 IS TRUE COLLATE BINARY; SELECT 0.5 IS TRUE; SELECT 0.5 COLLATE NOCASE IS TRUE; SELECT 0.0 IS FALSE; SELECT 0.0 IS FALSE COLLATE NOCASE; SELECT 0.0 IS FALSE COLLATE RTRIM; SELECT 0.0 IS FALSE COLLATE BINARY; } {1 1 1 1 1 1 1 1 1} finish_test