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.3 2004/05/20 12:41:20 drh 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-3.*: The '<' operator implemented using an index. 25# types2-4.*: The '>' operator in the absence of an index. 26# types2-5.*: The 'IN(x, y...)' operator in the absence of an index. 27# types2-6.*: The 'IN(x, y...)' operator with an index. 28# types2-7.*: The 'IN(SELECT...)' operator in the absence of an index. 29# types2-8.*: The 'IN(SELECT...)' operator with an index. 30# 31# All tests test the operators using literals and columns, but no 32# other types of expressions. All expressions except columns are 33# handled similarly in the implementation. 34 35execsql { 36 CREATE TABLE t1( 37 i1 INTEGER, 38 i2 INTEGER, 39 n1 NUMERIC, 40 n2 NUMERIC, 41 t1 TEXT, 42 t2 TEXT, 43 o1 BLOB, 44 o2 BLOB 45 ); 46 INSERT INTO t1 VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); 47} 48 49proc test_bool {testname vars expr res} { 50 if { $vars != "" } { 51 execsql "UPDATE t1 SET $vars" 52 } 53 54 foreach {t e r} [list $testname $expr $res] {} 55 56 do_test $t.1 "execsql {SELECT $e FROM t1}" $r 57 do_test $t.2 "execsql {SELECT 1 FROM t1 WHERE $expr}" [expr $r?"1":""] 58 do_test $t.3 "execsql {SELECT 1 FROM t1 WHERE NOT ($e)}" [expr $r?"":"1"] 59} 60 61# Compare literals against literals. This should always use a numeric 62# comparison. 63test_bool types2-1.1 "" {500 = 500.0} 1 64test_bool types2-1.2 "" {'500' = 500.0} 1 65test_bool types2-1.3 "" {500 = '500.0'} 1 66test_bool types2-1.4 "" {'500' = '500.0'} 1 67 68# Compare literals against a column with TEXT affinity 69test_bool types2-1.5 {t1=500} {500 = t1} 1 70test_bool types2-1.6 {t1=500} {'500' = t1} 1 71test_bool types2-1.7 {t1=500} {500.0 = t1} 0 72test_bool types2-1.8 {t1=500} {'500.0' = t1} 0 73test_bool types2-1.9 {t1='500'} {500 = t1} 1 74test_bool types2-1.10 {t1='500'} {'500' = t1} 1 75test_bool types2-1.11 {t1='500'} {500.0 = t1} 0 76test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0 77 78# Compare literals against a column with NUMERIC affinity 79test_bool types2-1.13 {n1=500} {500 = n1} 1 80test_bool types2-1.14 {n1=500} {'500' = n1} 1 81test_bool types2-1.15 {n1=500} {500.0 = n1} 1 82test_bool types2-1.16 {n1=500} {'500.0' = n1} 1 83test_bool types2-1.17 {n1='500'} {500 = n1} 1 84test_bool types2-1.18 {n1='500'} {'500' = n1} 1 85test_bool types2-1.19 {n1='500'} {500.0 = n1} 1 86test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1 87 88# Compare literals against a column with affinity NONE 89test_bool types2-1.21 {o1=500} {500 = o1} 1 90test_bool types2-1.22 {o1=500} {'500' = o1} 0 91test_bool types2-1.23 {o1=500} {500.0 = o1} 1 92test_bool types2-1.24 {o1=500} {'500.0' = o1} 0 93test_bool types2-1.25 {o1='500'} {500 = o1} 0 94test_bool types2-1.26 {o1='500'} {'500' = o1} 1 95test_bool types2-1.27 {o1='500'} {500.0 = o1} 0 96test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0 97 98set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] 99# 1 2 3 4 5 6 7 8 9 10 11 12 100 101execsql { 102 CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY); 103 CREATE INDEX t2i1 ON t2(i); 104 CREATE INDEX t2i2 ON t2(n); 105 CREATE INDEX t2i3 ON t2(t); 106 CREATE INDEX t2i4 ON t2(o); 107} 108foreach v $vals { 109 execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);" 110} 111 112proc test_boolset {testname where set} { 113 set ::tb_sql "SELECT rowid FROM t2 WHERE $where" 114 do_test $testname { 115 lsort -integer [execsql $::tb_sql] 116 } $set 117} 118 119test_boolset types2-2.1 {i = 10} {1 2 3 4} 120test_boolset types2-2.2 {i = 10.0} {1 2 3 4} 121test_boolset types2-2.3 {i = '10'} {1 2 3 4} 122test_boolset types2-2.4 {i = '10.0'} {1 2 3 4} 123 124test_boolset types2-2.5 {n = 20} {5 6 7 8} 125test_boolset types2-2.6 {n = 20.0} {5 6 7 8} 126test_boolset types2-2.7 {n = '20'} {5 6 7 8} 127test_boolset types2-2.8 {n = '20.0'} {5 6 7 8} 128 129test_boolset types2-2.9 {t = 20} {5 7} 130test_boolset types2-2.10 {t = 20.0} {6 8} 131test_boolset types2-2.11 {t = '20'} {5 7} 132test_boolset types2-2.12 {t = '20.0'} {6 8} 133 134test_boolset types2-2.10 {o = 30} {9 10} 135test_boolset types2-2.11 {o = 30.0} {9 10} 136test_boolset types2-2.12 {o = '30'} 11 137test_boolset types2-2.13 {o = '30.0'} 12 138 139test_boolset types2-3.1 {i < 20} {1 2 3 4} 140test_boolset types2-3.2 {i < 20.0} {1 2 3 4} 141test_boolset types2-3.3 {i < '20'} {1 2 3 4} 142test_boolset types2-3.4 {i < '20.0'} {1 2 3 4} 143 144test_boolset types2-3.1 {n < 20} {1 2 3 4} 145test_boolset types2-3.2 {n < 20.0} {1 2 3 4} 146test_boolset types2-3.3 {n < '20'} {1 2 3 4} 147test_boolset types2-3.4 {n < '20.0'} {1 2 3 4} 148 149test_boolset types2-3.1 {t < 20} {1 2 3 4} 150test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7} 151test_boolset types2-3.3 {t < '20'} {1 2 3 4} 152test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7} 153 154test_boolset types2-3.1 {o < 20} {1 2} 155test_boolset types2-3.2 {o < 20.0} {1 2} 156test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10} 157test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10} 158 159# Compare literals against literals (always a numeric comparison). 160test_bool types2-4.1 "" {500 > 60.0} 1 161test_bool types2-4.2 "" {'500' > 60.0} 1 162test_bool types2-4.3 "" {500 > '60.0'} 1 163test_bool types2-4.4 "" {'500' > '60.0'} 1 164 165# Compare literals against a column with TEXT affinity 166test_bool types2-4.5 {t1=500.0} {t1 > 500} 1 167test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1 168test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0 169test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0 170test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1 171test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1 172test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0 173test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0 174 175# Compare literals against a column with NUMERIC affinity 176test_bool types2-4.13 {n1=400} {500 > n1} 1 177test_bool types2-4.14 {n1=400} {'500' > n1} 1 178test_bool types2-4.15 {n1=400} {500.0 > n1} 1 179test_bool types2-4.16 {n1=400} {'500.0' > n1} 1 180test_bool types2-4.17 {n1='400'} {500 > n1} 1 181test_bool types2-4.18 {n1='400'} {'500' > n1} 1 182test_bool types2-4.19 {n1='400'} {500.0 > n1} 1 183test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1 184 185# Compare literals against a column with affinity NONE 186test_bool types2-4.21 {o1=500} {500 > o1} 0 187test_bool types2-4.22 {o1=500} {'500' > o1} 1 188test_bool types2-4.23 {o1=500} {500.0 > o1} 0 189test_bool types2-4.24 {o1=500} {'500.0' > o1} 1 190test_bool types2-4.25 {o1='500'} {500 > o1} 0 191test_bool types2-4.26 {o1='500'} {'500' > o1} 0 192test_bool types2-4.27 {o1='500'} {500.0 > o1} 0 193test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1 194 195# types2-5.* - The 'IN (x, y....)' operator with no index. 196# 197# Compare literals against literals (always a numeric comparison). 198test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1 199test_bool types2-5.2 {} {10 IN ('10.0', 20)} 1 200test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 1 201test_bool types2-5.4 {} {10 IN (10.0, 20)} 1 202test_bool types2-5.5 {} {'10.0' IN (10, 20)} 1 203 204# Compare literals against a column with TEXT affinity 205test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1 206test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0 207test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0 208test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0 209test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1 210 211# Compare literals against a column with NUMERIC affinity 212test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1 213test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1 214test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1 215test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1 216test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1 217 218# Compare literals against a column with affinity NONE 219test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0 220test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0 221test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0 222test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0 223test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0 224test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1 225test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1 226test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1 227 228# Tests named types2-6.* use the same infrastructure as the types2-2.* 229# tests. The contents of the vals array is repeated here for easy 230# reference. 231# 232# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] 233# 1 2 3 4 5 6 7 8 9 10 11 12 234 235test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10} 236test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10} 237test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11} 238test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12} 239test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12} 240test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12} 241test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12} 242test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12} 243 244# Also test than IN(x, y, z) works on a rowid: 245test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10} 246 247# Tests types2-7.* concentrate on expressions of the form 248# "x IN (SELECT...)" with no index. 249execsql { 250 CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB); 251 INSERT INTO t3 VALUES(1, 1, 1, 1); 252 INSERT INTO t3 VALUES(2, 2, 2, 2); 253 INSERT INTO t3 VALUES(3, 3, 3, 3); 254 INSERT INTO t3 VALUES('1', '1', '1', '1'); 255 INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0'); 256} 257 258test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1 259test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1 260test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1 261test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1 262test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1 263 264test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1 265test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1 266test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1 267test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1 268test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1 269 270test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1 271test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0 272test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1 273test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1 274test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0 275test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1 276test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1 277 278test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1 279test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0 280test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1 281 282# set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0'] 283# 1 2 3 4 5 6 7 8 9 10 11 12 284execsql { 285 CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB); 286 INSERT INTO t4 VALUES(10, 20, 20, 30); 287} 288test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4} 289test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4} 290test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4} 291test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4} 292test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8} 293test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8} 294test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7} 295test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7} 296test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12} 297test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12} 298test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {9 11} 299test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10} 300 301finish_test 302