AryaWu/sqlite
0
1# 2008 Feb 62#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#12# This file is to test that ticket #2927 is fixed.13#14# $Id: tkt2927.test,v 1.4 2008/08/04 03:51:24 danielk1977 Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !compound {21 finish_test22 return23}24 25# Create a database.26#27do_test tkt2927-1.1 {28 db eval {29 CREATE TABLE t1(a, b);30 INSERT INTO t1 VALUES(1,11);31 INSERT INTO t1 VALUES(2,22);32 INSERT INTO t1 VALUES(3,33);33 INSERT INTO t1 VALUES(4,44);34 INSERT INTO t1 VALUES(5,55);35 SELECT * FROM t1;36 }37} {1 11 2 22 3 33 4 44 5 55}38 39 40do_test tkt2927-2.1 {41 db eval {42 SELECT a, b FROM t143 UNION ALL44 SELECT a, b FROM t145 }46} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}47do_test tkt2927-2.2 {48#set sqlite_addop_trace 149 db eval {50 SELECT a, b FROM t151 UNION ALL52 SELECT a, abs(b) FROM t153 }54} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}55do_test tkt2927-2.3 {56 db eval {57 SELECT a, b FROM t158 UNION ALL59 SELECT abs(a), b FROM t160 }61} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}62do_test tkt2927-2.4 {63 db eval {64 SELECT a, b FROM t165 UNION ALL66 SELECT abs(a), abs(b) FROM t167 }68} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}69do_test tkt2927-2.5 {70 db eval {71 SELECT a, abs(b) FROM t172 UNION ALL73 SELECT a, b FROM t174 }75} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}76do_test tkt2927-2.6 {77 db eval {78 SELECT a, abs(b) FROM t179 UNION ALL80 SELECT a, abs(b) FROM t181 }82} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}83do_test tkt2927-2.7 {84 db eval {85 SELECT a, abs(b) FROM t186 UNION ALL87 SELECT abs(a), b FROM t188 }89} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}90do_test tkt2927-2.8 {91 db eval {92 SELECT a, abs(b) FROM t193 UNION ALL94 SELECT abs(a), abs(b) FROM t195 }96} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}97do_test tkt2927-2.9 {98 db eval {99 SELECT abs(a), b FROM t1100 UNION ALL101 SELECT a, b FROM t1102 }103} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}104do_test tkt2927-2.10 {105 db eval {106 SELECT abs(a), b FROM t1107 UNION ALL108 SELECT a, abs(b) FROM t1109 }110} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}111do_test tkt2927-2.11 {112 db eval {113 SELECT abs(a), b FROM t1114 UNION ALL115 SELECT abs(a), b FROM t1116 }117} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}118do_test tkt2927-2.12 {119 db eval {120 SELECT abs(a), b FROM t1121 UNION ALL122 SELECT abs(a), abs(b) FROM t1123 }124} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}125do_test tkt2927-2.13 {126 db eval {127 SELECT abs(a), abs(b) FROM t1128 UNION ALL129 SELECT a, b FROM t1130 }131} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}132do_test tkt2927-2.14 {133 db eval {134 SELECT abs(a), abs(b) FROM t1135 UNION ALL136 SELECT a, abs(b) FROM t1137 }138} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}139do_test tkt2927-2.15 {140 db eval {141 SELECT abs(a), abs(b) FROM t1142 UNION ALL143 SELECT abs(a), b FROM t1144 }145} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}146do_test tkt2927-2.16 {147 db eval {148 SELECT abs(a), abs(b) FROM t1149 UNION ALL150 SELECT abs(a), abs(b) FROM t1151 }152} {1 11 2 22 3 33 4 44 5 55 1 11 2 22 3 33 4 44 5 55}153 154 155do_test tkt2927-3.1 {156 db eval {157 SELECT a, b FROM t1158 UNION 159 SELECT a, b FROM t1160 ORDER BY 1161 }162} {1 11 2 22 3 33 4 44 5 55}163do_test tkt2927-3.2 {164 db eval {165 SELECT a, b FROM t1166 UNION 167 SELECT a, abs(b) FROM t1168 ORDER BY 1169 }170} {1 11 2 22 3 33 4 44 5 55}171do_test tkt2927-3.3 {172 db eval {173 SELECT a, b FROM t1174 UNION 175 SELECT abs(a), b FROM t1176 ORDER BY 1177 }178} {1 11 2 22 3 33 4 44 5 55}179do_test tkt2927-3.4 {180 db eval {181 SELECT a, b FROM t1182 UNION 183 SELECT abs(a), abs(b) FROM t1184 ORDER BY 1185 }186} {1 11 2 22 3 33 4 44 5 55}187do_test tkt2927-3.5 {188 db eval {189 SELECT a, abs(b) FROM t1190 UNION 191 SELECT a, b FROM t1192 ORDER BY 1193 }194} {1 11 2 22 3 33 4 44 5 55}195do_test tkt2927-3.6 {196 db eval {197 SELECT a, abs(b) FROM t1198 UNION 199 SELECT a, abs(b) FROM t1200 ORDER BY 1201 }202} {1 11 2 22 3 33 4 44 5 55}203do_test tkt2927-3.7 {204 db eval {205 SELECT a, abs(b) FROM t1206 UNION 207 SELECT abs(a), b FROM t1208 ORDER BY 1209 }210} {1 11 2 22 3 33 4 44 5 55}211do_test tkt2927-3.8 {212 db eval {213 SELECT a, abs(b) FROM t1214 UNION 215 SELECT abs(a), abs(b) FROM t1216 ORDER BY 1217 }218} {1 11 2 22 3 33 4 44 5 55}219do_test tkt2927-3.9 {220 db eval {221 SELECT abs(a), b FROM t1222 UNION 223 SELECT a, b FROM t1224 ORDER BY 1225 }226} {1 11 2 22 3 33 4 44 5 55}227do_test tkt2927-3.10 {228 db eval {229 SELECT abs(a), b FROM t1230 UNION 231 SELECT a, abs(b) FROM t1232 ORDER BY 1233 }234} {1 11 2 22 3 33 4 44 5 55}235do_test tkt2927-3.11 {236 db eval {237 SELECT abs(a), b FROM t1238 UNION 239 SELECT abs(a), b FROM t1240 ORDER BY 1241 }242} {1 11 2 22 3 33 4 44 5 55}243do_test tkt2927-3.12 {244 db eval {245 SELECT abs(a), b FROM t1246 UNION 247 SELECT abs(a), abs(b) FROM t1248 ORDER BY 1249 }250} {1 11 2 22 3 33 4 44 5 55}251do_test tkt2927-3.13 {252 db eval {253 SELECT abs(a), abs(b) FROM t1254 UNION 255 SELECT a, b FROM t1256 ORDER BY 1257 }258} {1 11 2 22 3 33 4 44 5 55}259do_test tkt2927-3.14 {260 db eval {261 SELECT abs(a), abs(b) FROM t1262 UNION 263 SELECT a, abs(b) FROM t1264 ORDER BY 1265 }266} {1 11 2 22 3 33 4 44 5 55}267do_test tkt2927-3.15 {268 db eval {269 SELECT abs(a), abs(b) FROM t1270 UNION 271 SELECT abs(a), b FROM t1272 ORDER BY 1273 }274} {1 11 2 22 3 33 4 44 5 55}275do_test tkt2927-3.16 {276 db eval {277 SELECT abs(a), abs(b) FROM t1278 UNION 279 SELECT abs(a), abs(b) FROM t1280 ORDER BY 1281 }282} {1 11 2 22 3 33 4 44 5 55}283 284 285do_test tkt2927-4.1 {286 db eval {287 SELECT a+b, a-b, a, b FROM t1288 UNION ALL289 SELECT a+b, a-b, a, b FROM t1290 }291} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}292do_test tkt2927-4.2 {293 db eval {294 SELECT a+b, a-b, a, b FROM t1295 UNION ALL296 SELECT a+b, a-b, a, abs(b) FROM t1297 }298} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}299do_test tkt2927-4.3 {300 db eval {301 SELECT a+b, a-b, a, b FROM t1302 UNION ALL303 SELECT a+b, a-b, abs(a), b FROM t1304 }305} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}306do_test tkt2927-4.4 {307 db eval {308 SELECT a+b, a-b, a, b FROM t1309 UNION ALL310 SELECT a+b, a-b, abs(a), abs(b) FROM t1311 }312} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}313do_test tkt2927-4.5 {314 db eval {315 SELECT a+b, a-b, a, abs(b) FROM t1316 UNION ALL317 SELECT a+b, a-b, a, b FROM t1318 }319} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}320do_test tkt2927-4.6 {321 db eval {322 SELECT a+b, a-b, a, abs(b) FROM t1323 UNION ALL324 SELECT a+b, a-b, a, abs(b) FROM t1325 }326} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}327do_test tkt2927-4.7 {328 db eval {329 SELECT a+b, a-b, a, abs(b) FROM t1330 UNION ALL331 SELECT a+b, a-b, abs(a), b FROM t1332 }333} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}334do_test tkt2927-4.8 {335 db eval {336 SELECT a+b, a-b, a, abs(b) FROM t1337 UNION ALL338 SELECT a+b, a-b, abs(a), abs(b) FROM t1339 }340} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}341do_test tkt2927-4.9 {342 db eval {343 SELECT a+b, a-b, abs(a), b FROM t1344 UNION ALL345 SELECT a+b, a-b, a, b FROM t1346 }347} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}348do_test tkt2927-4.10 {349 db eval {350 SELECT a+b, a-b, abs(a), b FROM t1351 UNION ALL352 SELECT a+b, a-b, a, abs(b) FROM t1353 }354} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}355do_test tkt2927-4.11 {356 db eval {357 SELECT a+b, a-b, abs(a), b FROM t1358 UNION ALL359 SELECT a+b, a-b, abs(a), b FROM t1360 }361} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}362do_test tkt2927-4.12 {363 db eval {364 SELECT a+b, a-b, abs(a), b FROM t1365 UNION ALL366 SELECT a+b, a-b, abs(a), abs(b) FROM t1367 }368} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}369do_test tkt2927-4.13 {370 db eval {371 SELECT a+b, a-b, abs(a), abs(b) FROM t1372 UNION ALL373 SELECT a+b, a-b, a, b FROM t1374 }375} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}376do_test tkt2927-4.14 {377 db eval {378 SELECT a+b, a-b, abs(a), abs(b) FROM t1379 UNION ALL380 SELECT a+b, a-b, a, abs(b) FROM t1381 }382} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}383do_test tkt2927-4.15 {384 db eval {385 SELECT a+b, a-b, abs(a), abs(b) FROM t1386 UNION ALL387 SELECT a+b, a-b, abs(a), b FROM t1388 }389} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}390do_test tkt2927-4.16 {391 db eval {392 SELECT a+b, a-b, abs(a), abs(b) FROM t1393 UNION ALL394 SELECT a+b, a-b, abs(a), abs(b) FROM t1395 }396} {12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55 12 -10 1 11 24 -20 2 22 36 -30 3 33 48 -40 4 44 60 -50 5 55}397 398 399do_test tkt2927-5.1 {400 db eval {401 SELECT a, b FROM t1402 EXCEPT403 SELECT a, b FROM t1404 }405} {}406do_test tkt2927-5.2 {407 db eval {408 SELECT a, b FROM t1409 EXCEPT410 SELECT a, abs(b) FROM t1411 }412} {}413do_test tkt2927-5.3 {414 db eval {415 SELECT a, b FROM t1416 EXCEPT417 SELECT abs(a), b FROM t1418 }419} {}420do_test tkt2927-5.4 {421 db eval {422 SELECT a, b FROM t1423 EXCEPT424 SELECT abs(a), abs(b) FROM t1425 }426} {}427do_test tkt2927-5.5 {428 db eval {429 SELECT a, abs(b) FROM t1430 EXCEPT431 SELECT a, b FROM t1432 }433} {}434do_test tkt2927-5.6 {435 db eval {436 SELECT a, abs(b) FROM t1437 EXCEPT438 SELECT a, abs(b) FROM t1439 }440} {}441do_test tkt2927-5.7 {442 db eval {443 SELECT a, abs(b) FROM t1444 EXCEPT445 SELECT abs(a), b FROM t1446 }447} {}448do_test tkt2927-5.8 {449 db eval {450 SELECT a, abs(b) FROM t1451 EXCEPT452 SELECT abs(a), abs(b) FROM t1453 }454} {}455do_test tkt2927-5.9 {456 db eval {457 SELECT abs(a), b FROM t1458 EXCEPT459 SELECT a, b FROM t1460 }461} {}462do_test tkt2927-5.10 {463 db eval {464 SELECT abs(a), b FROM t1465 EXCEPT466 SELECT a, abs(b) FROM t1467 }468} {}469do_test tkt2927-5.11 {470 db eval {471 SELECT abs(a), b FROM t1472 EXCEPT473 SELECT abs(a), b FROM t1474 }475} {}476do_test tkt2927-5.12 {477 db eval {478 SELECT abs(a), b FROM t1479 EXCEPT480 SELECT abs(a), abs(b) FROM t1481 }482} {}483do_test tkt2927-5.13 {484 db eval {485 SELECT abs(a), abs(b) FROM t1486 EXCEPT487 SELECT a, b FROM t1488 }489} {}490do_test tkt2927-5.14 {491 db eval {492 SELECT abs(a), abs(b) FROM t1493 EXCEPT494 SELECT a, abs(b) FROM t1495 }496} {}497do_test tkt2927-5.15 {498 db eval {499 SELECT abs(a), abs(b) FROM t1500 EXCEPT501 SELECT abs(a), b FROM t1502 }503} {}504do_test tkt2927-5.16 {505 db eval {506 SELECT abs(a), abs(b) FROM t1507 EXCEPT508 SELECT abs(a), abs(b) FROM t1509 }510} {}511 512 513do_test tkt2927-6.1 {514 db eval {515 SELECT a, b FROM t1516 INTERSECT517 SELECT a, b FROM t1518 ORDER BY 1519 }520} {1 11 2 22 3 33 4 44 5 55}521do_test tkt2927-6.2 {522 db eval {523 SELECT a, b FROM t1524 INTERSECT525 SELECT a, abs(b) FROM t1526 ORDER BY 1527 }528} {1 11 2 22 3 33 4 44 5 55}529do_test tkt2927-6.3 {530 db eval {531 SELECT a, b FROM t1532 INTERSECT533 SELECT abs(a), b FROM t1534 ORDER BY 1535 }536} {1 11 2 22 3 33 4 44 5 55}537do_test tkt2927-6.4 {538 db eval {539 SELECT a, b FROM t1540 INTERSECT541 SELECT abs(a), abs(b) FROM t1542 ORDER BY 1543 }544} {1 11 2 22 3 33 4 44 5 55}545do_test tkt2927-6.5 {546 db eval {547 SELECT a, abs(b) FROM t1548 INTERSECT549 SELECT a, b FROM t1550 ORDER BY 1551 }552} {1 11 2 22 3 33 4 44 5 55}553do_test tkt2927-6.6 {554 db eval {555 SELECT a, abs(b) FROM t1556 INTERSECT557 SELECT a, abs(b) FROM t1558 ORDER BY 1559 }560} {1 11 2 22 3 33 4 44 5 55}561do_test tkt2927-6.7 {562 db eval {563 SELECT a, abs(b) FROM t1564 INTERSECT565 SELECT abs(a), b FROM t1566 ORDER BY 1567 }568} {1 11 2 22 3 33 4 44 5 55}569do_test tkt2927-6.8 {570 db eval {571 SELECT a, abs(b) FROM t1572 INTERSECT573 SELECT abs(a), abs(b) FROM t1574 ORDER BY 1575 }576} {1 11 2 22 3 33 4 44 5 55}577do_test tkt2927-6.9 {578 db eval {579 SELECT abs(a), b FROM t1580 INTERSECT581 SELECT a, b FROM t1582 ORDER BY 1583 }584} {1 11 2 22 3 33 4 44 5 55}585do_test tkt2927-6.10 {586 db eval {587 SELECT abs(a), b FROM t1588 INTERSECT589 SELECT a, abs(b) FROM t1590 ORDER BY 1591 }592} {1 11 2 22 3 33 4 44 5 55}593do_test tkt2927-6.11 {594 db eval {595 SELECT abs(a), b FROM t1596 INTERSECT597 SELECT abs(a), b FROM t1598 ORDER BY 1599 }600} {1 11 2 22 3 33 4 44 5 55}601do_test tkt2927-6.12 {602 db eval {603 SELECT abs(a), b FROM t1604 INTERSECT605 SELECT abs(a), abs(b) FROM t1606 ORDER BY 1607 }608} {1 11 2 22 3 33 4 44 5 55}609do_test tkt2927-6.13 {610 db eval {611 SELECT abs(a), abs(b) FROM t1612 INTERSECT613 SELECT a, b FROM t1614 ORDER BY 1615 }616} {1 11 2 22 3 33 4 44 5 55}617do_test tkt2927-6.14 {618 db eval {619 SELECT abs(a), abs(b) FROM t1620 INTERSECT621 SELECT a, abs(b) FROM t1622 ORDER BY 1623 }624} {1 11 2 22 3 33 4 44 5 55}625do_test tkt2927-6.15 {626 db eval {627 SELECT abs(a), abs(b) FROM t1628 INTERSECT629 SELECT abs(a), b FROM t1630 ORDER BY 1631 }632} {1 11 2 22 3 33 4 44 5 55}633do_test tkt2927-6.16 {634 db eval {635 SELECT abs(a), abs(b) FROM t1636 INTERSECT637 SELECT abs(a), abs(b) FROM t1638 ORDER BY 1639 }640} {1 11 2 22 3 33 4 44 5 55}641 642# Ticket #3092 is the same bug. But another test case never hurts.643#644do_test tkt2927-7.1 {645 db eval {646 CREATE TABLE host (647 hostname text not null primary key,648 consoleHost text,649 consolePort text650 );651 INSERT INTO "host" VALUES('aald04','aalp03','4');652 INSERT INTO "host" VALUES('aald17','aalp01','1');653 CREATE VIEW consolemap1a as654 select hostname, consolehost, '/dev/cuaD0.' || (consoleport-1) consoleport655 from host where consolehost='aalp01';656 CREATE VIEW consolemap1b as657 select hostname hostname, consolehost consolehost, '/dev/cuaD' ||658 substr('01',1+((consoleport-1)/16),1) ||659 substr('0123456789abcdef',1+((consoleport-1)%16),1) consoleport660 from host where consolehost='aalp03';661 CREATE VIEW consolemap1 as662 select * from consolemap1a663 union664 select * from consolemap1b;665 SELECT * from consolemap1b;666 }667} {aald04 aalp03 /dev/cuaD03}668do_test tkt2927-7.2 {669 db eval {670 SELECT * FROM consolemap1671 }672} {aald04 aalp03 /dev/cuaD03 aald17 aalp01 /dev/cuaD0.0}673 674finish_test675