AryaWu/sqlite
0
1# 2005 September 192#3# The author disclaims copyright to this source code. In place of4# 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.12#13# This file implements tests to verify that ticket #1473 has been14# fixed. 15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !compound {21 finish_test22 return 23}24 25do_test tkt1473-1.1 {26 execsql {27 CREATE TABLE t1(a,b);28 INSERT INTO t1 VALUES(1,2);29 INSERT INTO t1 VALUES(3,4);30 SELECT * FROM t131 }32} {1 2 3 4}33 34do_test tkt1473-1.2 {35 execsql {36 SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=037 }38} {1}39do_test tkt1473-1.3 {40 execsql {41 SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=042 }43} {1}44do_test tkt1473-1.4 {45 execsql {46 SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=447 }48} {1 2}49do_test tkt1473-1.5 {50 execsql {51 SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=452 }53} {1 2}54do_test tkt1473-1.6 {55 execsql {56 SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=457 }58} {2}59do_test tkt1473-1.7 {60 execsql {61 SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=462 }63} {2}64do_test tkt1473-1.8 {65 execsql {66 SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=067 }68} {}69do_test tkt1473-1.9 {70 execsql {71 SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=072 }73} {}74 75# Everything from this point on depends on sub-queries. So skip it76# if sub-queries are not available.77ifcapable !subquery {78 finish_test79 return80}81 82do_test tkt1473-2.2 {83 execsql {84 SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0)85 }86} {1}87do_test tkt1473-2.3 {88 execsql {89 SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0)90 }91} {1}92do_test tkt1473-2.4 {93 execsql {94 SELECT (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4)95 }96} {1}97do_test tkt1473-2.5 {98 execsql {99 SELECT (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4)100 }101} {1}102do_test tkt1473-2.6 {103 execsql {104 SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4)105 }106} {2}107do_test tkt1473-2.7 {108 execsql {109 SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4)110 }111} {2}112do_test tkt1473-2.8 {113 execsql {114 SELECT (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0)115 }116} {{}}117do_test tkt1473-2.9 {118 execsql {119 SELECT (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0)120 }121} {{}}122 123do_test tkt1473-3.2 {124 execsql {125 SELECT EXISTS126 (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=0)127 }128} {1}129do_test tkt1473-3.3 {130 execsql {131 SELECT EXISTS132 (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=0)133 }134} {1}135do_test tkt1473-3.4 {136 execsql {137 SELECT EXISTS138 (SELECT 1 FROM t1 WHERE a=1 UNION ALL SELECT 2 FROM t1 WHERE b=4)139 }140} {1}141do_test tkt1473-3.5 {142 execsql {143 SELECT EXISTS144 (SELECT 1 FROM t1 WHERE a=1 UNION SELECT 2 FROM t1 WHERE b=4)145 }146} {1}147do_test tkt1473-3.6 {148 execsql {149 SELECT EXISTS150 (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=4)151 }152} {1}153do_test tkt1473-3.7 {154 execsql {155 SELECT EXISTS156 (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=4)157 }158} {1}159do_test tkt1473-3.8 {160 execsql {161 SELECT EXISTS162 (SELECT 1 FROM t1 WHERE a=0 UNION ALL SELECT 2 FROM t1 WHERE b=0)163 }164} {0}165do_test tkt1473-3.9 {166 execsql {167 SELECT EXISTS168 (SELECT 1 FROM t1 WHERE a=0 UNION SELECT 2 FROM t1 WHERE b=0)169 }170} {0}171 172do_test tkt1473-4.1 {173 execsql {174 CREATE TABLE t2(x,y);175 INSERT INTO t2 VALUES(1,2);176 INSERT INTO t2 SELECT x+2, y+2 FROM t2;177 INSERT INTO t2 SELECT x+4, y+4 FROM t2;178 INSERT INTO t2 SELECT x+8, y+8 FROM t2;179 INSERT INTO t2 SELECT x+16, y+16 FROM t2;180 INSERT INTO t2 SELECT x+32, y+32 FROM t2;181 INSERT INTO t2 SELECT x+64, y+64 FROM t2;182 SELECT count(*), sum(x), sum(y) FROM t2;183 }184} {64 4096 4160}185do_test tkt1473-4.2 {186 execsql {187 SELECT 1 FROM t2 WHERE x=0188 UNION ALL189 SELECT 2 FROM t2 WHERE x=1190 UNION ALL191 SELECT 3 FROM t2 WHERE x=2192 UNION ALL193 SELECT 4 FROM t2 WHERE x=3194 UNION ALL195 SELECT 5 FROM t2 WHERE x=4196 UNION ALL197 SELECT 6 FROM t2 WHERE y=0198 UNION ALL199 SELECT 7 FROM t2 WHERE y=1200 UNION ALL201 SELECT 8 FROM t2 WHERE y=2202 UNION ALL203 SELECT 9 FROM t2 WHERE y=3204 UNION ALL205 SELECT 10 FROM t2 WHERE y=4206 }207} {2 4 8 10}208do_test tkt1473-4.3 {209 execsql {210 SELECT (211 SELECT 1 FROM t2 WHERE x=0212 UNION ALL213 SELECT 2 FROM t2 WHERE x=1214 UNION ALL215 SELECT 3 FROM t2 WHERE x=2216 UNION ALL217 SELECT 4 FROM t2 WHERE x=3218 UNION ALL219 SELECT 5 FROM t2 WHERE x=4220 UNION ALL221 SELECT 6 FROM t2 WHERE y=0222 UNION ALL223 SELECT 7 FROM t2 WHERE y=1224 UNION ALL225 SELECT 8 FROM t2 WHERE y=2226 UNION ALL227 SELECT 9 FROM t2 WHERE y=3228 UNION ALL229 SELECT 10 FROM t2 WHERE y=4230 )231 }232} {2}233do_test tkt1473-4.4 {234 execsql {235 SELECT (236 SELECT 1 FROM t2 WHERE x=0237 UNION ALL238 SELECT 2 FROM t2 WHERE x=-1239 UNION ALL240 SELECT 3 FROM t2 WHERE x=2241 UNION ALL242 SELECT 4 FROM t2 WHERE x=3243 UNION ALL244 SELECT 5 FROM t2 WHERE x=4245 UNION ALL246 SELECT 6 FROM t2 WHERE y=0247 UNION ALL248 SELECT 7 FROM t2 WHERE y=1249 UNION ALL250 SELECT 8 FROM t2 WHERE y=2251 UNION ALL252 SELECT 9 FROM t2 WHERE y=3253 UNION ALL254 SELECT 10 FROM t2 WHERE y=4255 )256 }257} {4}258do_test tkt1473-4.5 {259 execsql {260 SELECT (261 SELECT 1 FROM t2 WHERE x=0262 UNION ALL263 SELECT 2 FROM t2 WHERE x=-1264 UNION ALL265 SELECT 3 FROM t2 WHERE x=2266 UNION ALL267 SELECT 4 FROM t2 WHERE x=-1268 UNION ALL269 SELECT 5 FROM t2 WHERE x=4270 UNION ALL271 SELECT 6 FROM t2 WHERE y=0272 UNION ALL273 SELECT 7 FROM t2 WHERE y=1274 UNION ALL275 SELECT 8 FROM t2 WHERE y=2276 UNION ALL277 SELECT 9 FROM t2 WHERE y=3278 UNION ALL279 SELECT 10 FROM t2 WHERE y=-4280 )281 }282} {8}283do_test tkt1473-4.6 {284 execsql {285 SELECT (286 SELECT 1 FROM t2 WHERE x=0287 UNION ALL288 SELECT 2 FROM t2 WHERE x=-1289 UNION ALL290 SELECT 3 FROM t2 WHERE x=2291 UNION ALL292 SELECT 4 FROM t2 WHERE x=-2293 UNION ALL294 SELECT 5 FROM t2 WHERE x=4295 UNION ALL296 SELECT 6 FROM t2 WHERE y=0297 UNION ALL298 SELECT 7 FROM t2 WHERE y=1299 UNION ALL300 SELECT 8 FROM t2 WHERE y=-3301 UNION ALL302 SELECT 9 FROM t2 WHERE y=3303 UNION ALL304 SELECT 10 FROM t2 WHERE y=4305 )306 }307} {10}308do_test tkt1473-4.7 {309 execsql {310 SELECT (311 SELECT 1 FROM t2 WHERE x=0312 UNION ALL313 SELECT 2 FROM t2 WHERE x=-1314 UNION ALL315 SELECT 3 FROM t2 WHERE x=2316 UNION ALL317 SELECT 4 FROM t2 WHERE x=-2318 UNION ALL319 SELECT 5 FROM t2 WHERE x=4320 UNION ALL321 SELECT 6 FROM t2 WHERE y=0322 UNION ALL323 SELECT 7 FROM t2 WHERE y=1324 UNION ALL325 SELECT 8 FROM t2 WHERE y=-3326 UNION ALL327 SELECT 9 FROM t2 WHERE y=3328 UNION ALL329 SELECT 10 FROM t2 WHERE y=-4330 )331 }332} {{}}333 334do_test tkt1473-5.3 {335 execsql {336 SELECT EXISTS (337 SELECT 1 FROM t2 WHERE x=0338 UNION ALL339 SELECT 2 FROM t2 WHERE x=1340 UNION ALL341 SELECT 3 FROM t2 WHERE x=2342 UNION ALL343 SELECT 4 FROM t2 WHERE x=3344 UNION ALL345 SELECT 5 FROM t2 WHERE x=4346 UNION ALL347 SELECT 6 FROM t2 WHERE y=0348 UNION ALL349 SELECT 7 FROM t2 WHERE y=1350 UNION ALL351 SELECT 8 FROM t2 WHERE y=2352 UNION ALL353 SELECT 9 FROM t2 WHERE y=3354 UNION ALL355 SELECT 10 FROM t2 WHERE y=4356 )357 }358} {1}359do_test tkt1473-5.4 {360 execsql {361 SELECT EXISTS (362 SELECT 1 FROM t2 WHERE x=0363 UNION ALL364 SELECT 2 FROM t2 WHERE x=-1365 UNION ALL366 SELECT 3 FROM t2 WHERE x=2367 UNION ALL368 SELECT 4 FROM t2 WHERE x=3369 UNION ALL370 SELECT 5 FROM t2 WHERE x=4371 UNION ALL372 SELECT 6 FROM t2 WHERE y=0373 UNION ALL374 SELECT 7 FROM t2 WHERE y=1375 UNION ALL376 SELECT 8 FROM t2 WHERE y=2377 UNION ALL378 SELECT 9 FROM t2 WHERE y=3379 UNION ALL380 SELECT 10 FROM t2 WHERE y=4381 )382 }383} {1}384 385do_test tkt1473-5.5 {386 execsql {387 SELECT EXISTS (388 SELECT 1 FROM t2 WHERE x=0389 UNION ALL390 SELECT 2 FROM t2 WHERE x=-1391 UNION ALL392 SELECT 3 FROM t2 WHERE x=2393 UNION ALL394 SELECT 4 FROM t2 WHERE x=-1395 UNION ALL396 SELECT 5 FROM t2 WHERE x=4397 UNION ALL398 SELECT 6 FROM t2 WHERE y=0399 UNION ALL400 SELECT 7 FROM t2 WHERE y=1401 UNION ALL402 SELECT 8 FROM t2 WHERE y=2403 UNION ALL404 SELECT 9 FROM t2 WHERE y=3405 UNION ALL406 SELECT 10 FROM t2 WHERE y=-4407 )408 }409} {1}410do_test tkt1473-5.6 {411 execsql {412 SELECT EXISTS (413 SELECT 1 FROM t2 WHERE x=0414 UNION ALL415 SELECT 2 FROM t2 WHERE x=-1416 UNION ALL417 SELECT 3 FROM t2 WHERE x=2418 UNION ALL419 SELECT 4 FROM t2 WHERE x=-2420 UNION ALL421 SELECT 5 FROM t2 WHERE x=4422 UNION ALL423 SELECT 6 FROM t2 WHERE y=0424 UNION ALL425 SELECT 7 FROM t2 WHERE y=1426 UNION ALL427 SELECT 8 FROM t2 WHERE y=-3428 UNION ALL429 SELECT 9 FROM t2 WHERE y=3430 UNION ALL431 SELECT 10 FROM t2 WHERE y=4432 )433 }434} {1}435do_test tkt1473-5.7 {436 execsql {437 SELECT EXISTS (438 SELECT 1 FROM t2 WHERE x=0439 UNION ALL440 SELECT 2 FROM t2 WHERE x=-1441 UNION ALL442 SELECT 3 FROM t2 WHERE x=2443 UNION ALL444 SELECT 4 FROM t2 WHERE x=-2445 UNION ALL446 SELECT 5 FROM t2 WHERE x=4447 UNION ALL448 SELECT 6 FROM t2 WHERE y=0449 UNION ALL450 SELECT 7 FROM t2 WHERE y=1451 UNION ALL452 SELECT 8 FROM t2 WHERE y=-3453 UNION ALL454 SELECT 9 FROM t2 WHERE y=3455 UNION ALL456 SELECT 10 FROM t2 WHERE y=-4457 )458 }459} {0}460 461do_test tkt1473-6.3 {462 execsql {463 SELECT EXISTS (464 SELECT 1 FROM t2 WHERE x=0465 UNION466 SELECT 2 FROM t2 WHERE x=1467 UNION468 SELECT 3 FROM t2 WHERE x=2469 UNION470 SELECT 4 FROM t2 WHERE x=3471 UNION472 SELECT 5 FROM t2 WHERE x=4473 UNION474 SELECT 6 FROM t2 WHERE y=0475 UNION476 SELECT 7 FROM t2 WHERE y=1477 UNION478 SELECT 8 FROM t2 WHERE y=2479 UNION480 SELECT 9 FROM t2 WHERE y=3481 UNION482 SELECT 10 FROM t2 WHERE y=4483 )484 }485} {1}486do_test tkt1473-6.4 {487 execsql {488 SELECT EXISTS (489 SELECT 1 FROM t2 WHERE x=0490 UNION491 SELECT 2 FROM t2 WHERE x=-1492 UNION493 SELECT 3 FROM t2 WHERE x=2494 UNION495 SELECT 4 FROM t2 WHERE x=3496 UNION497 SELECT 5 FROM t2 WHERE x=4498 UNION499 SELECT 6 FROM t2 WHERE y=0500 UNION501 SELECT 7 FROM t2 WHERE y=1502 UNION503 SELECT 8 FROM t2 WHERE y=2504 UNION505 SELECT 9 FROM t2 WHERE y=3506 UNION507 SELECT 10 FROM t2 WHERE y=4508 )509 }510} {1}511 512do_test tkt1473-6.5 {513 execsql {514 SELECT EXISTS (515 SELECT 1 FROM t2 WHERE x=0516 UNION517 SELECT 2 FROM t2 WHERE x=-1518 UNION519 SELECT 3 FROM t2 WHERE x=2520 UNION521 SELECT 4 FROM t2 WHERE x=-1522 UNION523 SELECT 5 FROM t2 WHERE x=4524 UNION525 SELECT 6 FROM t2 WHERE y=0526 UNION527 SELECT 7 FROM t2 WHERE y=1528 UNION529 SELECT 8 FROM t2 WHERE y=2530 UNION531 SELECT 9 FROM t2 WHERE y=3532 UNION533 SELECT 10 FROM t2 WHERE y=-4534 )535 }536} {1}537do_test tkt1473-6.6 {538 execsql {539 SELECT EXISTS (540 SELECT 1 FROM t2 WHERE x=0541 UNION542 SELECT 2 FROM t2 WHERE x=-1543 UNION544 SELECT 3 FROM t2 WHERE x=2545 UNION546 SELECT 4 FROM t2 WHERE x=-2547 UNION548 SELECT 5 FROM t2 WHERE x=4549 UNION550 SELECT 6 FROM t2 WHERE y=0551 UNION552 SELECT 7 FROM t2 WHERE y=1553 UNION554 SELECT 8 FROM t2 WHERE y=-3555 UNION556 SELECT 9 FROM t2 WHERE y=3557 UNION558 SELECT 10 FROM t2 WHERE y=4559 )560 }561} {1}562do_test tkt1473-6.7 {563 execsql {564 SELECT EXISTS (565 SELECT 1 FROM t2 WHERE x=0566 UNION567 SELECT 2 FROM t2 WHERE x=-1568 UNION569 SELECT 3 FROM t2 WHERE x=2570 UNION571 SELECT 4 FROM t2 WHERE x=-2572 UNION573 SELECT 5 FROM t2 WHERE x=4574 UNION575 SELECT 6 FROM t2 WHERE y=0576 UNION577 SELECT 7 FROM t2 WHERE y=1578 UNION579 SELECT 8 FROM t2 WHERE y=-3580 UNION581 SELECT 9 FROM t2 WHERE y=3582 UNION583 SELECT 10 FROM t2 WHERE y=-4584 )585 }586} {0}587do_test tkt1473-6.8 {588 execsql {589 SELECT EXISTS (590 SELECT 1 FROM t2 WHERE x=0591 UNION592 SELECT 2 FROM t2 WHERE x=-1593 UNION594 SELECT 3 FROM t2 WHERE x=2595 UNION596 SELECT 4 FROM t2 WHERE x=-2597 UNION598 SELECT 5 FROM t2 WHERE x=4599 UNION ALL600 SELECT 6 FROM t2 WHERE y=0601 UNION602 SELECT 7 FROM t2 WHERE y=1603 UNION604 SELECT 8 FROM t2 WHERE y=-3605 UNION606 SELECT 9 FROM t2 WHERE y=3607 UNION608 SELECT 10 FROM t2 WHERE y=4609 )610 }611} {1}612do_test tkt1473-6.9 {613 execsql {614 SELECT EXISTS (615 SELECT 1 FROM t2 WHERE x=0616 UNION617 SELECT 2 FROM t2 WHERE x=-1618 UNION619 SELECT 3 FROM t2 WHERE x=2620 UNION621 SELECT 4 FROM t2 WHERE x=-2622 UNION623 SELECT 5 FROM t2 WHERE x=4624 UNION ALL625 SELECT 6 FROM t2 WHERE y=0626 UNION627 SELECT 7 FROM t2 WHERE y=1628 UNION629 SELECT 8 FROM t2 WHERE y=-3630 UNION631 SELECT 9 FROM t2 WHERE y=3632 UNION633 SELECT 10 FROM t2 WHERE y=-4634 )635 }636} {0}637 638do_test tkt1473-7.1 {639 execsql {640 SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2641 }642} {1}643do_test tkt1473-7.2 {644 execsql {645 SELECT (646 SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2647 )648 }649} {1}650do_test tkt1473-7.3 {651 execsql {652 SELECT EXISTS (653 SELECT 1 FROM t2 WHERE x=1 EXCEPT SELECT 2 FROM t2 WHERE y=2654 )655 }656} {1}657do_test tkt1473-7.4 {658 execsql {659 SELECT (660 SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2661 )662 }663} {{}}664do_test tkt1473-7.5 {665 execsql {666 SELECT EXISTS (667 SELECT 1 FROM t2 WHERE x=0 EXCEPT SELECT 2 FROM t2 WHERE y=2668 )669 }670} {0}671 672do_test tkt1473-8.1 {673 execsql {674 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2675 }676} {}677do_test tkt1473-8.1 {678 execsql {679 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2680 }681} {1}682do_test tkt1473-8.3 {683 execsql {684 SELECT (685 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2686 )687 }688} {{}}689do_test tkt1473-8.4 {690 execsql {691 SELECT (692 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2693 )694 }695} {1}696do_test tkt1473-8.5 {697 execsql {698 SELECT EXISTS (699 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 2 FROM t2 WHERE y=2700 )701 }702} {0}703do_test tkt1473-8.6 {704 execsql {705 SELECT EXISTS (706 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=2707 )708 }709} {1}710do_test tkt1473-8.7 {711 execsql {712 SELECT (713 SELECT 1 FROM t2 WHERE x=0 INTERSECT SELECT 1 FROM t2 WHERE y=2714 )715 }716} {{}}717do_test tkt1473-8.8 {718 execsql {719 SELECT EXISTS (720 SELECT 1 FROM t2 WHERE x=1 INTERSECT SELECT 1 FROM t2 WHERE y=0721 )722 }723} {0}724 725 726 727 728finish_test729