test/types2.test

   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.7 2007/02/23 03:00:45 drh Exp $
  16
  17 set testdir [file dirname $argv0]
  18 source $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
  35 execsql {
  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
  49 proc 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.
  63 #
  64 # Changed by ticket #805:  Use no affinity for literal comparisons.
  65 #
  66 test_bool types2-1.1 "" {500 = 500.0} 1
  67 test_bool types2-1.2 "" {'500' = 500.0} 0
  68 test_bool types2-1.3 "" {500 = '500.0'} 0
  69 test_bool types2-1.4 "" {'500' = '500.0'} 0
  70
  71 # Compare literals against a column with TEXT affinity
  72 test_bool types2-1.5 {t1=500} {500 = t1} 1
  73 test_bool types2-1.6 {t1=500} {'500' = t1} 1
  74 test_bool types2-1.7 {t1=500} {500.0 = t1} 0
  75 test_bool types2-1.8 {t1=500} {'500.0' = t1} 0
  76 test_bool types2-1.9 {t1='500'} {500 = t1} 1
  77 test_bool types2-1.10 {t1='500'} {'500' = t1} 1
  78 test_bool types2-1.11 {t1='500'} {500.0 = t1} 0
  79 test_bool types2-1.12 {t1='500'} {'500.0' = t1} 0
  80
  81 # Compare literals against a column with NUMERIC affinity
  82 test_bool types2-1.13 {n1=500} {500 = n1} 1
  83 test_bool types2-1.14 {n1=500} {'500' = n1} 1
  84 test_bool types2-1.15 {n1=500} {500.0 = n1} 1
  85 test_bool types2-1.16 {n1=500} {'500.0' = n1} 1
  86 test_bool types2-1.17 {n1='500'} {500 = n1} 1
  87 test_bool types2-1.18 {n1='500'} {'500' = n1} 1
  88 test_bool types2-1.19 {n1='500'} {500.0 = n1} 1
  89 test_bool types2-1.20 {n1='500'} {'500.0' = n1} 1
  90
  91 # Compare literals against a column with affinity NONE
  92 test_bool types2-1.21 {o1=500} {500 = o1} 1
  93 test_bool types2-1.22 {o1=500} {'500' = o1} 0
  94 test_bool types2-1.23 {o1=500} {500.0 = o1} 1
  95 test_bool types2-1.24 {o1=500} {'500.0' = o1} 0
  96 test_bool types2-1.25 {o1='500'} {500 = o1} 0
  97 test_bool types2-1.26 {o1='500'} {'500' = o1} 1
  98 test_bool types2-1.27 {o1='500'} {500.0 = o1} 0
  99 test_bool types2-1.28 {o1='500'} {'500.0' = o1} 0
 100
 101 set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
 102 #              1  2    3    4      5  6    7    8      9  10   11   12
 103
 104 execsql {
 105   CREATE TABLE t2(i INTEGER, n NUMERIC, t TEXT, o XBLOBY);
 106   CREATE INDEX t2i1 ON t2(i);
 107   CREATE INDEX t2i2 ON t2(n);
 108   CREATE INDEX t2i3 ON t2(t);
 109   CREATE INDEX t2i4 ON t2(o);
 110 }
 111 foreach v $vals {
 112   execsql "INSERT INTO t2 VALUES($v, $v, $v, $v);"
 113 }
 114
 115 proc test_boolset {testname where set} {
 116   set ::tb_sql "SELECT rowid FROM t2 WHERE $where"
 117   do_test $testname {
 118     lsort -integer [execsql $::tb_sql]
 119   } $set
 120 }
 121
 122 test_boolset types2-2.1 {i = 10} {1 2 3 4}
 123 test_boolset types2-2.2 {i = 10.0} {1 2 3 4}
 124 test_boolset types2-2.3 {i = '10'} {1 2 3 4}
 125 test_boolset types2-2.4 {i = '10.0'} {1 2 3 4}
 126
 127 test_boolset types2-2.5 {n = 20} {5 6 7 8}
 128 test_boolset types2-2.6 {n = 20.0} {5 6 7 8}
 129 test_boolset types2-2.7 {n = '20'} {5 6 7 8}
 130 test_boolset types2-2.8 {n = '20.0'} {5 6 7 8}
 131
 132 test_boolset types2-2.9 {t = 20} {5 7}
 133 test_boolset types2-2.10 {t = 20.0} {6 8}
 134 test_boolset types2-2.11 {t = '20'} {5 7}
 135 test_boolset types2-2.12 {t = '20.0'} {6 8}
 136
 137 test_boolset types2-2.10 {o = 30} {9 10}
 138 test_boolset types2-2.11 {o = 30.0} {9 10}
 139 test_boolset types2-2.12 {o = '30'} 11
 140 test_boolset types2-2.13 {o = '30.0'} 12
 141
 142 test_boolset types2-3.1 {i < 20} {1 2 3 4}
 143 test_boolset types2-3.2 {i < 20.0} {1 2 3 4}
 144 test_boolset types2-3.3 {i < '20'} {1 2 3 4}
 145 test_boolset types2-3.4 {i < '20.0'} {1 2 3 4}
 146
 147 test_boolset types2-3.1 {n < 20} {1 2 3 4}
 148 test_boolset types2-3.2 {n < 20.0} {1 2 3 4}
 149 test_boolset types2-3.3 {n < '20'} {1 2 3 4}
 150 test_boolset types2-3.4 {n < '20.0'} {1 2 3 4}
 151
 152 test_boolset types2-3.1 {t < 20} {1 2 3 4}
 153 test_boolset types2-3.2 {t < 20.0} {1 2 3 4 5 7}
 154 test_boolset types2-3.3 {t < '20'} {1 2 3 4}
 155 test_boolset types2-3.4 {t < '20.0'} {1 2 3 4 5 7}
 156
 157 test_boolset types2-3.1 {o < 20} {1 2}
 158 test_boolset types2-3.2 {o < 20.0} {1 2}
 159 test_boolset types2-3.3 {o < '20'} {1 2 3 4 5 6 9 10}
 160 test_boolset types2-3.3 {o < '20.0'} {1 2 3 4 5 6 7 9 10}
 161
 162 # Compare literals against literals (always a numeric comparison).
 163 # Change (by ticket #805):  No affinity in comparisons
 164 test_bool types2-4.1 "" {500 > 60.0} 1
 165 test_bool types2-4.2 "" {'500' > 60.0} 1
 166 test_bool types2-4.3 "" {500 > '60.0'} 0
 167 test_bool types2-4.4 "" {'500' > '60.0'} 0
 168
 169 # Compare literals against a column with TEXT affinity
 170 test_bool types2-4.5 {t1=500.0} {t1 > 500} 1
 171 test_bool types2-4.6 {t1=500.0} {t1 > '500' } 1
 172 test_bool types2-4.7 {t1=500.0} {t1 > 500.0 } 0
 173 test_bool types2-4.8 {t1=500.0} {t1 > '500.0' } 0
 174 test_bool types2-4.9 {t1='500.0'} {t1 > 500 } 1
 175 test_bool types2-4.10 {t1='500.0'} {t1 > '500' } 1
 176 test_bool types2-4.11 {t1='500.0'} {t1 > 500.0 } 0
 177 test_bool types2-4.12 {t1='500.0'} {t1 > '500.0' } 0
 178
 179 # Compare literals against a column with NUMERIC affinity
 180 test_bool types2-4.13 {n1=400} {500 > n1} 1
 181 test_bool types2-4.14 {n1=400} {'500' > n1} 1
 182 test_bool types2-4.15 {n1=400} {500.0 > n1} 1
 183 test_bool types2-4.16 {n1=400} {'500.0' > n1} 1
 184 test_bool types2-4.17 {n1='400'} {500 > n1} 1
 185 test_bool types2-4.18 {n1='400'} {'500' > n1} 1
 186 test_bool types2-4.19 {n1='400'} {500.0 > n1} 1
 187 test_bool types2-4.20 {n1='400'} {'500.0' > n1} 1
 188
 189 # Compare literals against a column with affinity NONE
 190 test_bool types2-4.21 {o1=500} {500 > o1} 0
 191 test_bool types2-4.22 {o1=500} {'500' > o1} 1
 192 test_bool types2-4.23 {o1=500} {500.0 > o1} 0
 193 test_bool types2-4.24 {o1=500} {'500.0' > o1} 1
 194 test_bool types2-4.25 {o1='500'} {500 > o1} 0
 195 test_bool types2-4.26 {o1='500'} {'500' > o1} 0
 196 test_bool types2-4.27 {o1='500'} {500.0 > o1} 0
 197 test_bool types2-4.28 {o1='500'} {'500.0' > o1} 1
 198
 199 ifcapable subquery {
 200   # types2-5.* - The 'IN (x, y....)' operator with no index.
 201   #
 202   # Compare literals against literals (no affinity applied)
 203   test_bool types2-5.1 {} {(NULL IN ('10.0', 20)) ISNULL} 1
 204   test_bool types2-5.2 {} {10 IN ('10.0', 20)} 0
 205   test_bool types2-5.3 {} {'10' IN ('10.0', 20)} 0
 206   test_bool types2-5.4 {} {10 IN (10.0, 20)} 1
 207   test_bool types2-5.5 {} {'10.0' IN (10, 20)} 0
 208
 209   # Compare literals against a column with TEXT affinity
 210   test_bool types2-5.6 {t1='10.0'} {t1 IN (10.0, 20)} 1
 211   test_bool types2-5.7 {t1='10.0'} {t1 IN (10, 20)} 0
 212   test_bool types2-5.8 {t1='10'} {t1 IN (10.0, 20)} 0
 213   test_bool types2-5.9 {t1='10'} {t1 IN (20, '10.0')} 0
 214   test_bool types2-5.10 {t1=10} {t1 IN (20, '10')} 1
 215
 216   # Compare literals against a column with NUMERIC affinity
 217   test_bool types2-5.11 {n1='10.0'} {n1 IN (10.0, 20)} 1
 218   test_bool types2-5.12 {n1='10.0'} {n1 IN (10, 20)} 1
 219   test_bool types2-5.13 {n1='10'} {n1 IN (10.0, 20)} 1
 220   test_bool types2-5.14 {n1='10'} {n1 IN (20, '10.0')} 1
 221   test_bool types2-5.15 {n1=10} {n1 IN (20, '10')} 1
 222
 223   # Compare literals against a column with affinity NONE
 224   test_bool types2-5.16 {o1='10.0'} {o1 IN (10.0, 20)} 0
 225   test_bool types2-5.17 {o1='10.0'} {o1 IN (10, 20)} 0
 226   test_bool types2-5.18 {o1='10'} {o1 IN (10.0, 20)} 0
 227   test_bool types2-5.19 {o1='10'} {o1 IN (20, '10.0')} 0
 228   test_bool types2-5.20 {o1=10} {o1 IN (20, '10')} 0
 229   test_bool types2-5.21 {o1='10.0'} {o1 IN (10, 20, '10.0')} 1
 230   test_bool types2-5.22 {o1='10'} {o1 IN (10.0, 20, '10')} 1
 231   test_bool types2-5.23 {o1=10} {n1 IN (20, '10', 10)} 1
 232
 233   # Ticket #2248:  Comparisons of strings literals that look like
 234   # numbers.
 235   test_bool types2-5.24 {} {'1' IN ('1')} 1
 236   test_bool types2-5.25 {} {'2' IN (2)} 0
 237   test_bool types2-5.26 {} {3 IN ('3')} 0
 238   test_bool types2-5.27 {} {4 IN (4)} 1
 239
 240   # The affinity of columns on the right side of IN(...) is ignored.
 241   # All values in the expression list are treated as ordinary expressions,
 242   # even if they are columns with affinity.
 243   test_bool types2-5.30 {t1='10'} {10 IN (5,t1,'abc')} 0
 244   test_bool types2-5.31 {t1='10'} {10 IN ('abc',t1,5)} 0
 245   test_bool types2-5.32 {t1='010'} {10 IN (5,t1,'abc')} 0
 246   test_bool types2-5.33 {t1='010'} {10 IN ('abc',t1,5)} 0
 247   test_bool types2-5.34 {t1='10'} {'10' IN (5,t1,'abc')} 1
 248   test_bool types2-5.35 {t1='10'} {'10' IN ('abc',t1,5)} 1
 249   test_bool types2-5.36 {t1='010'} {'10' IN (5,t1,'abc')} 0
 250   test_bool types2-5.37 {t1='010'} {'10' IN ('abc',t1,5)} 0
 251
 252   # Columns on both the left and right of IN(...).  Only the column
 253   # on the left matters.  The all values on the right are treated like
 254   # expressions.
 255   test_bool types2-5.40 {t1='10',n1=10} {t1 IN (5,n1,11)} 1
 256   test_bool types2-5.41 {t1='010',n1=10} {t1 IN (5,n1,11)} 0
 257   test_bool types2-5.42 {t1='10',n1=10} {n1 IN (5,t1,11)} 1
 258   test_bool types2-5.43 {t1='010',n1=10} {n1 IN (5,t1,11)} 1
 259 }
 260
 261 # Tests named types2-6.* use the same infrastructure as the types2-2.*
 262 # tests. The contents of the vals array is repeated here for easy
 263 # reference.
 264 #
 265 # set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
 266 #                1  2    3    4      5  6    7    8      9  10   11   12
 267
 268 ifcapable subquery {
 269   test_boolset types2-6.1 {o IN ('10', 30)} {3 9 10}
 270   test_boolset types2-6.2 {o IN (20.0, 30.0)} {5 6 9 10}
 271   test_boolset types2-6.3 {t IN ('10', 30)} {1 3 9 11}
 272   test_boolset types2-6.4 {t IN (20.0, 30.0)} {6 8 10 12}
 273   test_boolset types2-6.5 {n IN ('10', 30)} {1 2 3 4 9 10 11 12}
 274   test_boolset types2-6.6 {n IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
 275   test_boolset types2-6.7 {i IN ('10', 30)} {1 2 3 4 9 10 11 12}
 276   test_boolset types2-6.8 {i IN (20.0, 30.0)} {5 6 7 8 9 10 11 12}
 277
 278   # Also test than IN(x, y, z) works on a rowid:
 279   test_boolset types2-6.9 {rowid IN (1, 6, 10)} {1 6 10}
 280 }
 281
 282 # Tests types2-7.* concentrate on expressions of the form
 283 # "x IN (SELECT...)" with no index.
 284 execsql {
 285   CREATE TABLE t3(i INTEGER, n NUMERIC, t TEXT, o BLOB);
 286   INSERT INTO t3 VALUES(1, 1, 1, 1);
 287   INSERT INTO t3 VALUES(2, 2, 2, 2);
 288   INSERT INTO t3 VALUES(3, 3, 3, 3);
 289   INSERT INTO t3 VALUES('1', '1', '1', '1');
 290   INSERT INTO t3 VALUES('1.0', '1.0', '1.0', '1.0');
 291 }
 292
 293 ifcapable subquery {
 294   test_bool types2-7.1 {i1=1} {i1 IN (SELECT i FROM t3)} 1
 295   test_bool types2-7.2 {i1='2.0'} {i1 IN (SELECT i FROM t3)} 1
 296   test_bool types2-7.3 {i1='2.0'} {i1 IN (SELECT n FROM t3)} 1
 297   test_bool types2-7.4 {i1='2.0'} {i1 IN (SELECT t FROM t3)} 1
 298   test_bool types2-7.5 {i1='2.0'} {i1 IN (SELECT o FROM t3)} 1
 299
 300   test_bool types2-7.6 {n1=1} {n1 IN (SELECT n FROM t3)} 1
 301   test_bool types2-7.7 {n1='2.0'} {n1 IN (SELECT i FROM t3)} 1
 302   test_bool types2-7.8 {n1='2.0'} {n1 IN (SELECT n FROM t3)} 1
 303   test_bool types2-7.9 {n1='2.0'} {n1 IN (SELECT t FROM t3)} 1
 304   test_bool types2-7.10 {n1='2.0'} {n1 IN (SELECT o FROM t3)} 1
 305
 306   test_bool types2-7.6 {t1=1} {t1 IN (SELECT t FROM t3)} 1
 307   test_bool types2-7.7 {t1='2.0'} {t1 IN (SELECT t FROM t3)} 0
 308   test_bool types2-7.8 {t1='2.0'} {t1 IN (SELECT n FROM t3)} 1
 309   test_bool types2-7.9 {t1='2.0'} {t1 IN (SELECT i FROM t3)} 1
 310   test_bool types2-7.10 {t1='2.0'} {t1 IN (SELECT o FROM t3)} 0
 311   test_bool types2-7.11 {t1='1.0'} {t1 IN (SELECT t FROM t3)} 1
 312   test_bool types2-7.12 {t1='1.0'} {t1 IN (SELECT o FROM t3)} 1
 313
 314   test_bool types2-7.13 {o1=2} {o1 IN (SELECT o FROM t3)} 1
 315   test_bool types2-7.14 {o1='2'} {o1 IN (SELECT o FROM t3)} 0
 316   test_bool types2-7.15 {o1='2'} {o1 IN (SELECT o||'' FROM t3)} 1
 317 }
 318
 319 # set vals [list 10 10.0 '10' '10.0' 20 20.0 '20' '20.0' 30 30.0 '30' '30.0']
 320 #                1  2    3    4      5  6    7    8      9  10   11   12
 321 execsql {
 322   CREATE TABLE t4(i INTEGER, n NUMERIC, t VARCHAR(20), o LARGE BLOB);
 323   INSERT INTO t4 VALUES(10, 20, 20, 30);
 324 }
 325 ifcapable subquery {
 326   test_boolset types2-8.1 {i IN (SELECT i FROM t4)} {1 2 3 4}
 327   test_boolset types2-8.2 {n IN (SELECT i FROM t4)} {1 2 3 4}
 328   test_boolset types2-8.3 {t IN (SELECT i FROM t4)} {1 2 3 4}
 329   test_boolset types2-8.4 {o IN (SELECT i FROM t4)} {1 2 3 4}
 330   test_boolset types2-8.5 {i IN (SELECT t FROM t4)} {5 6 7 8}
 331   test_boolset types2-8.6 {n IN (SELECT t FROM t4)} {5 6 7 8}
 332   test_boolset types2-8.7 {t IN (SELECT t FROM t4)} {5 7}
 333   test_boolset types2-8.8 {o IN (SELECT t FROM t4)} {7}
 334   test_boolset types2-8.9 {i IN (SELECT o FROM t4)} {9 10 11 12}
 335   test_boolset types2-8.6 {n IN (SELECT o FROM t4)} {9 10 11 12}
 336   test_boolset types2-8.7 {t IN (SELECT o FROM t4)} {}
 337   test_boolset types2-8.8 {o IN (SELECT o FROM t4)} {9 10}
 338 }
 339
 340 finish_test