1# 2001 September 15 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 focus 12# of this file is testing the interaction of manifest types, type affinity 13# and comparison expressions. 14# 15# $Id: types2.test,v 1.1 2004/05/16 11:15:42 danielk1977 Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# Tests in this file are organized roughly as follows: 21# 22# types2-1.*: The '=' operator in the absence of an index. 23# types2-2.*: The '=' operator implemented using an index. 24# types2-2.*: The '<' operator implemented using an index. 25# types2-3.*: The '>' operator in the absense of an index. 26# 27 28execsql { 29 CREATE TABLE t1( 30 i1 INTEGER, 31 i2 INTEGER, 32 n1 NUMERIC, 33 n2 NUMERIC, 34 t1 TEXT, 35 t2 TEXT, 36 o1, 37 o2 38 ); 39 INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); 40} 41 42proc test_bool {testname vars expr res} { 43 if { $vars != "" } { 44 execsql "UPDATE t1 SET $vars" 45 } 46 47 foreach {t e r} [list $testname $expr $res] {} 48 49 do_test $t.1 "execsql {SELECT $e FROM t1}" $r 50 do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""] 51 do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"] 52} 53 54# Compare literals against literals 55test_bool types2-1.1 "" {500 = 500.0} 1 56test_bool types2-1.2 "" {'500' = 500.0} 1 57test_bool types2-1.3 "" {500 = '500.0'} 1 58test_bool types2-1.4 "" {'500' = '500.0'} 0 59 60# Compare literals against a column with TEXT affinity 61test_bool types2-1.5 {t1=500} {500 = t1} 1 62test_bool types2-1.6 {t1=500} {'500' = t1} 1 63test_bool types2-1.7 {t1=500} {500.0 = t1} 0 64test_bool types2-1.8 {t1=500} {'500.0' = t1} 0 65test_bool types2-1.9 {t1='500'} {500 = t1} 1 66test_bool types2-1.10 {t1='500'} {'500' = t1} 1 67test_bool types2-1.11 {t1='500'} {500.0 = t1} 0 68test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0 69 70# Compare literals against a column with NUMERIC affinity 71test_bool types2-1.13 {n1=500} {500 = n1} 1 72test_bool types2-1.14 {n1=500} {'500' = n1} 1 73test_bool types2-1.15 {n1=500} {500.0 = n1} 1 74test_bool types2-1.16 {n1=500} {'500.0' = n1} 1 75test_bool types2-1.17 {n1='500'} {500 = n1} 1 76test_bool types2-1.18 {n1='500'} {'500' = n1} 1 77test_bool types2-1.19 {n1='500'} {500.0 = n1} 1 78test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1 79 80# Compare literals against a column with affinity NONE 81test_bool types2-1.21 {o1=500} {500 = o1} 1 82test_bool types2-1.22 {o1=500} {'500' = o1} 0 83test_bool types2-1.23 {o1=500} {500.0 = o1} 1 84test_bool types2-1.24 {o1=500} {'500.0' = o1} 0 85test_bool types2-1.25 {o1='500'} {500 = o1} 0 86test_bool types2-1.26 {o1='500'} {'500' = o1} 1 87test_bool types2-1.27 {o1='500'} {500.0 = o1} 0 88test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0 89 90set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] 91# 1 2 3 4 5 6 7 8 9 10 11 12 92 93execsql { 94 CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o); 95 CREATE INDEX t2i1 ON t2(i); 96 CREATE INDEX t2i2 ON t2(n); 97 CREATE INDEX t2i3 ON t2(t); 98 CREATE INDEX t2i4 ON t2(o); 99} 100foreach v $vals { 101 execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);" 102} 103 104proc test_boolset {testname where set} { 105 set ::tb_sql "SELECT rowid FROM t2 WHERE $where" 106 do_test $testname { 107 lsort -integer [execsql $::tb_sql] 108 } $set 109} 110 111test_boolset types2-2.1 {i = 10} {1 2 3 4} 112test_boolset types2-2.2 {i = 10.0} {1 2 3 4} 113test_boolset types2-2.3 {i = '10'} {1 2 3 4} 114test_boolset types2-2.4 {i = '10.0'} {1 2 3 4} 115 116test_boolset types2-2.5 {n = 20} {5 6 7 8} 117test_boolset types2-2.6 {n = 20.0} {5 6 7 8} 118test_boolset types2-2.7 {n = '20'} {5 6 7 8} 119test_boolset types2-2.8 {n = '20.0'} {5 6 7 8} 120 121test_boolset types2-2.9 {t = 20} {5 7} 122test_boolset types2-2.10 {t = 20.0} {6 8} 123test_boolset types2-2.11 {t = '20'} {5 7} 124test_boolset types2-2.12 {t = '20.0'} {6 8} 125 126test_boolset types2-2.10 {o = 30} {9 10} 127test_boolset types2-2.11 {o = 30.0} {9 10} 128test_boolset types2-2.12 {o = '30'} 11 129test_boolset types2-2.13 {o = '30.0'} 12 130 131test_boolset types2-3.1 {i < 20} {1 2 3 4} 132test_boolset types2-3.2 {i < 20.0} {1 2 3 4} 133test_boolset types2-3.3 {i < '20'} {1 2 3 4} 134test_boolset types2-3.4 {i < '20.0'} {1 2 3 4} 135 136test_boolset types2-3.1 {n < 20} {1 2 3 4} 137test_boolset types2-3.2 {n < 20.0} {1 2 3 4} 138test_boolset types2-3.3 {n < '20'} {1 2 3 4} 139test_boolset types2-3.4 {n < '20.0'} {1 2 3 4} 140 141test_boolset types2-3.1 {t < 20} {1 2 3 4} 142test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7} 143test_boolset types2-3.3 {t < '20'} {1 2 3 4} 144test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7} 145 146test_boolset types2-3.1 {o < 20} {1 2} 147test_boolset types2-3.2 {o < 20.0} {1 2} 148test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10} 149test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10} 150 151# Compare literals against literals 152test_bool types2-4.1 "" {500 > 60.0} 1 153test_bool types2-4.2 "" {'500' > 60.0} 1 154test_bool types2-4.3 "" {500 > '60.0'} 1 155test_bool types2-4.4 "" {'500' > '60.0'} 0 156 157# Compare literals against a column with TEXT affinity 158test_bool types2-4.5 {t1=500.0} {t1 > 500} 1 159test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1 160test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0 161test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0 162test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1 163test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1 164test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0 165test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0 166 167# Compare literals against a column with NUMERIC affinity 168test_bool types2-4.13 {n1=400} {500 > n1} 1 169test_bool types2-4.14 {n1=400} {'500' > n1} 1 170test_bool types2-4.15 {n1=400} {500.0 > n1} 1 171test_bool types2-4.16 {n1=400} {'500.0' > n1} 1 172test_bool types2-4.17 {n1='400'} {500 > n1} 1 173test_bool types2-4.18 {n1='400'} {'500' > n1} 1 174test_bool types2-4.19 {n1='400'} {500.0 > n1} 1 175test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1 176 177# Compare literals against a column with affinity NONE 178test_bool types2-4.21 {o1=500} {500 > o1} 0 179test_bool types2-4.22 {o1=500} {'500' > o1} 1 180test_bool types2-4.23 {o1=500} {500.0 > o1} 0 181test_bool types2-4.24 {o1=500} {'500.0' > o1} 1 182test_bool types2-4.25 {o1='500'} {500 > o1} 0 183test_bool types2-4.26 {o1='500'} {'500' > o1} 0 184test_bool types2-4.27 {o1='500'} {500.0 > o1} 0 185test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1 186 187finish_test 188 189 190