1 # 2009 August 13 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 12 # focus of this file is testing WHERE clause conditions with 13 # subtle affinity issues. 14 # 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # For this set of tests: 20 # 21 # * t1.y holds an integer value with affinity NONE 22 # * t2.b holds a text value with affinity TEXT 23 # 24 # These values are not equal and because neither affinity is NUMERIC 25 # no type conversion occurs. 26 # 27 do_test whereB-1.1 { 28 db eval { 29 CREATE TABLE t1(x,y); -- affinity of t1.y is NONE 30 INSERT INTO t1 VALUES(1,99); 31 32 CREATE TABLE t2(a, b TEXT); -- affinity of t2.b is TEXT 33 CREATE INDEX t2b ON t2(b); 34 INSERT INTO t2 VALUES(2,99); 35 36 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; 37 } 38 } {1 2 0} 39 do_test whereB-1.2 { 40 db eval { 41 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 42 } 43 } {} 44 do_test whereB-1.3 { 45 db eval { 46 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 47 } 48 } {} 49 do_test whereB-1.4 { 50 db eval { 51 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 52 } 53 } {} 54 do_test whereB-1.100 { 55 db eval { 56 DROP INDEX t2b; 57 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 58 } 59 } {} 60 do_test whereB-1.101 { 61 db eval { 62 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 63 } 64 } {} 65 do_test whereB-1.102 { 66 db eval { 67 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 68 } 69 } {} 70 71 # For this set of tests: 72 # 73 # * t1.y holds a text value with affinity TEXT 74 # * t2.b holds an integer value with affinity NONE 75 # 76 # These values are not equal and because neither affinity is NUMERIC 77 # no type conversion occurs. 78 # 79 do_test whereB-2.1 { 80 db eval { 81 DROP TABLE t1; 82 DROP TABLE t2; 83 84 CREATE TABLE t1(x, y TEXT); -- affinity of t1.y is TEXT 85 INSERT INTO t1 VALUES(1,99); 86 87 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE 88 CREATE INDEX t2b ON t2(b); 89 INSERT INTO t2 VALUES(2,99); 90 91 SELECT x, a, y=b FROM t1, t2 ORDER BY +x, +a; 92 } 93 } {1 2 0} 94 do_test whereB-2.2 { 95 db eval { 96 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 97 } 98 } {} 99 do_test whereB-2.3 { 100 db eval { 101 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 102 } 103 } {} 104 do_test whereB-2.4 { 105 db eval { 106 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 107 } 108 } {} 109 do_test whereB-2.100 { 110 db eval { 111 DROP INDEX t2b; 112 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 113 } 114 } {} 115 do_test whereB-2.101 { 116 db eval { 117 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 118 } 119 } {} 120 do_test whereB-2.102 { 121 db eval { 122 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 123 } 124 } {} 125 126 # For this set of tests: 127 # 128 # * t1.y holds a text value with affinity NONE 129 # * t2.b holds an integer value with affinity NONE 130 # 131 # These values are not equal and because neither affinity is NUMERIC 132 # no type conversion occurs. 133 # 134 do_test whereB-3.1 { 135 db eval { 136 DROP TABLE t1; 137 DROP TABLE t2; 138 139 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE 140 INSERT INTO t1 VALUES(1,99); 141 142 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE 143 CREATE INDEX t2b ON t2(b); 144 INSERT INTO t2 VALUES(2,'99'); 145 146 SELECT x, a, y=b FROM t1, t2; 147 } 148 } {1 2 0} 149 do_test whereB-3.2 { 150 db eval { 151 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 152 } 153 } {} 154 do_test whereB-3.3 { 155 db eval { 156 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 157 } 158 } {} 159 do_test whereB-3.4 { 160 db eval { 161 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 162 } 163 } {} 164 do_test whereB-3.100 { 165 db eval { 166 DROP INDEX t2b; 167 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 168 } 169 } {} 170 do_test whereB-3.101 { 171 db eval { 172 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 173 } 174 } {} 175 do_test whereB-3.102 { 176 db eval { 177 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 178 } 179 } {} 180 181 182 # For this set of tests: 183 # 184 # * t1.y holds a text value with affinity NONE 185 # * t2.b holds an integer value with affinity NUMERIC 186 # 187 # Because t2.b has a numeric affinity, type conversion should occur 188 # and the two fields should be equal. 189 # 190 do_test whereB-4.1 { 191 db eval { 192 DROP TABLE t1; 193 DROP TABLE t2; 194 195 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE 196 INSERT INTO t1 VALUES(1,'99'); 197 198 CREATE TABLE t2(a, b NUMERIC); -- affinity of t2.b is NUMERIC 199 CREATE INDEX t2b ON t2(b); 200 INSERT INTO t2 VALUES(2,99); 201 202 SELECT x, a, y=b FROM t1, t2; 203 } 204 } {1 2 1} 205 do_test whereB-4.2 { 206 db eval { 207 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 208 } 209 } {1 2 1} 210 do_test whereB-4.3 { 211 db eval { 212 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 213 } 214 } {1 2 1} 215 do_test whereB-4.4 { 216 # In this case the unary "+" operator removes the column affinity so 217 # the columns compare false 218 db eval { 219 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 220 } 221 } {} 222 do_test whereB-4.100 { 223 db eval { 224 DROP INDEX t2b; 225 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 226 } 227 } {1 2 1} 228 do_test whereB-4.101 { 229 db eval { 230 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 231 } 232 } {1 2 1} 233 do_test whereB-4.102 { 234 # In this case the unary "+" operator removes the column affinity so 235 # the columns compare false 236 db eval { 237 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 238 } 239 } {} 240 241 242 243 # For this set of tests: 244 # 245 # * t1.y holds a text value with affinity NONE 246 # * t2.b holds an integer value with affinity INTEGER 247 # 248 # Because t2.b has a numeric affinity, type conversion should occur 249 # and the two fields should be equal. 250 # 251 do_test whereB-5.1 { 252 db eval { 253 DROP TABLE t1; 254 DROP TABLE t2; 255 256 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE 257 INSERT INTO t1 VALUES(1,'99'); 258 259 CREATE TABLE t2(a, b INT); -- affinity of t2.b is INTEGER 260 CREATE INDEX t2b ON t2(b); 261 INSERT INTO t2 VALUES(2,99); 262 263 SELECT x, a, y=b FROM t1, t2; 264 } 265 } {1 2 1} 266 do_test whereB-5.2 { 267 db eval { 268 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 269 } 270 } {1 2 1} 271 do_test whereB-5.3 { 272 db eval { 273 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 274 } 275 } {1 2 1} 276 do_test whereB-5.4 { 277 # In this case the unary "+" operator removes the column affinity so 278 # the columns compare false 279 db eval { 280 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 281 } 282 } {} 283 do_test whereB-5.100 { 284 db eval { 285 DROP INDEX t2b; 286 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 287 } 288 } {1 2 1} 289 do_test whereB-5.101 { 290 db eval { 291 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 292 } 293 } {1 2 1} 294 do_test whereB-5.102 { 295 # In this case the unary "+" operator removes the column affinity so 296 # the columns compare false 297 db eval { 298 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 299 } 300 } {} 301 302 303 # For this set of tests: 304 # 305 # * t1.y holds a text value with affinity NONE 306 # * t2.b holds an integer value with affinity REAL 307 # 308 # Because t2.b has a numeric affinity, type conversion should occur 309 # and the two fields should be equal. 310 # 311 do_test whereB-6.1 { 312 db eval { 313 DROP TABLE t1; 314 DROP TABLE t2; 315 316 CREATE TABLE t1(x, y BLOB); -- affinity of t1.y is NONE 317 INSERT INTO t1 VALUES(1,'99'); 318 319 CREATE TABLE t2(a, b REAL); -- affinity of t2.b is REAL 320 CREATE INDEX t2b ON t2(b); 321 INSERT INTO t2 VALUES(2,99.0); 322 323 SELECT x, a, y=b FROM t1, t2; 324 } 325 } {1 2 1} 326 do_test whereB-6.2 { 327 db eval { 328 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 329 } 330 } {1 2 1} 331 do_test whereB-6.3 { 332 db eval { 333 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 334 } 335 } {1 2 1} 336 do_test whereB-6.4 { 337 # In this case the unary "+" operator removes the column affinity so 338 # the columns compare false 339 db eval { 340 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 341 } 342 } {} 343 do_test whereB-6.100 { 344 db eval { 345 DROP INDEX t2b; 346 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 347 } 348 } {1 2 1} 349 do_test whereB-6.101 { 350 db eval { 351 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 352 } 353 } {1 2 1} 354 do_test whereB-6.102 { 355 # In this case the unary "+" operator removes the column affinity so 356 # the columns compare false 357 db eval { 358 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 359 } 360 } {} 361 362 363 # For this set of tests: 364 # 365 # * t1.y holds an integer value with affinity NUMERIC 366 # * t2.b holds a text value with affinity NONE 367 # 368 # Because t1.y has a numeric affinity, type conversion should occur 369 # and the two fields should be equal. 370 # 371 do_test whereB-7.1 { 372 db eval { 373 DROP TABLE t1; 374 DROP TABLE t2; 375 376 CREATE TABLE t1(x, y NUMERIC); -- affinity of t1.y is NUMERIC 377 INSERT INTO t1 VALUES(1,99); 378 379 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE 380 CREATE INDEX t2b ON t2(b); 381 INSERT INTO t2 VALUES(2,'99'); 382 383 SELECT x, a, y=b FROM t1, t2; 384 } 385 } {1 2 1} 386 do_test whereB-7.2 { 387 db eval { 388 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 389 } 390 } {1 2 1} 391 do_test whereB-7.3 { 392 db eval { 393 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 394 } 395 } {1 2 1} 396 do_test whereB-7.4 { 397 # In this case the unary "+" operator removes the column affinity so 398 # the columns compare false 399 db eval { 400 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 401 } 402 } {} 403 do_test whereB-7.100 { 404 db eval { 405 DROP INDEX t2b; 406 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 407 } 408 } {1 2 1} 409 do_test whereB-7.101 { 410 db eval { 411 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 412 } 413 } {1 2 1} 414 do_test whereB-7.102 { 415 # In this case the unary "+" operator removes the column affinity so 416 # the columns compare false 417 db eval { 418 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 419 } 420 } {} 421 422 # For this set of tests: 423 # 424 # * t1.y holds an integer value with affinity INTEGER 425 # * t2.b holds a text value with affinity NONE 426 # 427 # Because t1.y has a numeric affinity, type conversion should occur 428 # and the two fields should be equal. 429 # 430 do_test whereB-8.1 { 431 db eval { 432 DROP TABLE t1; 433 DROP TABLE t2; 434 435 CREATE TABLE t1(x, y INT); -- affinity of t1.y is INTEGER 436 INSERT INTO t1 VALUES(1,99); 437 438 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE 439 CREATE INDEX t2b ON t2(b); 440 INSERT INTO t2 VALUES(2,'99'); 441 442 SELECT x, a, y=b FROM t1, t2; 443 } 444 } {1 2 1} 445 do_test whereB-8.2 { 446 db eval { 447 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 448 } 449 } {1 2 1} 450 do_test whereB-8.3 { 451 db eval { 452 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 453 } 454 } {1 2 1} 455 do_test whereB-8.4 { 456 # In this case the unary "+" operator removes the column affinity so 457 # the columns compare false 458 db eval { 459 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 460 } 461 } {} 462 do_test whereB-8.100 { 463 db eval { 464 DROP INDEX t2b; 465 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 466 } 467 } {1 2 1} 468 do_test whereB-8.101 { 469 db eval { 470 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 471 } 472 } {1 2 1} 473 do_test whereB-8.102 { 474 # In this case the unary "+" operator removes the column affinity so 475 # the columns compare false 476 db eval { 477 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 478 } 479 } {} 480 481 # For this set of tests: 482 # 483 # * t1.y holds an integer value with affinity REAL 484 # * t2.b holds a text value with affinity NONE 485 # 486 # Because t1.y has a numeric affinity, type conversion should occur 487 # and the two fields should be equal. 488 # 489 do_test whereB-9.1 { 490 db eval { 491 DROP TABLE t1; 492 DROP TABLE t2; 493 494 CREATE TABLE t1(x, y REAL); -- affinity of t1.y is REAL 495 INSERT INTO t1 VALUES(1,99.0); 496 497 CREATE TABLE t2(a, b BLOB); -- affinity of t2.b is NONE 498 CREATE INDEX t2b ON t2(b); 499 INSERT INTO t2 VALUES(2,'99'); 500 501 SELECT x, a, y=b FROM t1, t2; 502 } 503 } {1 2 1} 504 do_test whereB-9.2 { 505 db eval { 506 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 507 } 508 } {1 2 1} 509 do_test whereB-9.3 { 510 db eval { 511 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 512 } 513 } {1 2 1} 514 do_test whereB-9.4 { 515 # In this case the unary "+" operator removes the column affinity so 516 # the columns compare false 517 db eval { 518 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 519 } 520 } {} 521 do_test whereB-9.100 { 522 db eval { 523 DROP INDEX t2b; 524 SELECT x, a, y=b FROM t1, t2 WHERE y=b; 525 } 526 } {1 2 1} 527 do_test whereB-9.101 { 528 db eval { 529 SELECT x, a, y=b FROM t1, t2 WHERE b=y; 530 } 531 } {1 2 1} 532 do_test whereB-9.102 { 533 # In this case the unary "+" operator removes the column affinity so 534 # the columns compare false 535 db eval { 536 SELECT x, a, y=b FROM t1, t2 WHERE +y=+b; 537 } 538 } {} 539 540 541 542 543 finish_test 544
