AryaWu/sqlite
0
1# 2001 September 152#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. The12# focus of this file is testing UNION, INTERSECT and EXCEPT operators13# in SELECT statements.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# Most tests in this file depend on compound-select. But there are a couple20# right at the end that test DISTINCT, so we cannot omit the entire file.21#22ifcapable compound {23 24# Build some test data25#26execsql {27 CREATE TABLE t1(n int, log int);28 BEGIN;29}30for {set i 1} {$i<32} {incr i} {31 for {set j 0} {(1<<$j)<$i} {incr j} {}32 execsql "INSERT INTO t1 VALUES($i,$j)"33}34execsql {35 COMMIT;36}37 38do_test select4-1.0 {39 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}40} {0 1 2 3 4 5}41 42# Union All operator43#44do_test select4-1.1a {45 lsort [execsql {SELECT DISTINCT log FROM t1}]46} {0 1 2 3 4 5}47do_test select4-1.1b {48 lsort [execsql {SELECT n FROM t1 WHERE log=3}]49} {5 6 7 8}50do_test select4-1.1c {51 execsql {52 SELECT DISTINCT log FROM t153 UNION ALL54 SELECT n FROM t1 WHERE log=355 ORDER BY log;56 }57} {0 1 2 3 4 5 5 6 7 8}58do_test select4-1.1d {59 execsql {60 CREATE TABLE t2 AS61 SELECT DISTINCT log FROM t162 UNION ALL63 SELECT n FROM t1 WHERE log=364 ORDER BY log;65 SELECT * FROM t2;66 }67} {0 1 2 3 4 5 5 6 7 8}68execsql {DROP TABLE t2}69do_test select4-1.1e {70 execsql {71 CREATE TABLE t2 AS72 SELECT DISTINCT log FROM t173 UNION ALL74 SELECT n FROM t1 WHERE log=375 ORDER BY log DESC;76 SELECT * FROM t2;77 }78} {8 7 6 5 5 4 3 2 1 0}79execsql {DROP TABLE t2}80do_test select4-1.1f {81 execsql {82 SELECT DISTINCT log FROM t183 UNION ALL84 SELECT n FROM t1 WHERE log=285 }86} {0 1 2 3 4 5 3 4}87do_test select4-1.1g {88 execsql {89 CREATE TABLE t2 AS 90 SELECT DISTINCT log FROM t191 UNION ALL92 SELECT n FROM t1 WHERE log=2;93 SELECT * FROM t2;94 }95} {0 1 2 3 4 5 3 4}96execsql {DROP TABLE t2}97ifcapable subquery {98 do_test select4-1.2 {99 execsql {100 SELECT log FROM t1 WHERE n IN 101 (SELECT DISTINCT log FROM t1 UNION ALL102 SELECT n FROM t1 WHERE log=3)103 ORDER BY log;104 }105 } {0 1 2 2 3 3 3 3}106}107 108# EVIDENCE-OF: R-02644-22131 In a compound SELECT statement, only the109# last or right-most simple SELECT may have an ORDER BY clause.110#111do_test select4-1.3 {112 set v [catch {execsql {113 SELECT DISTINCT log FROM t1 ORDER BY log114 UNION ALL115 SELECT n FROM t1 WHERE log=3116 ORDER BY log;117 }} msg]118 lappend v $msg119} {1 {ORDER BY clause should come after UNION ALL not before}}120do_catchsql_test select4-1.4 {121 SELECT (VALUES(0) INTERSECT SELECT(0) UNION SELECT(0) ORDER BY 1 UNION122 SELECT 0 UNION SELECT 0 ORDER BY 1);123} {1 {ORDER BY clause should come after UNION not before}}124 125# Union operator126#127do_test select4-2.1 {128 execsql {129 SELECT DISTINCT log FROM t1130 UNION131 SELECT n FROM t1 WHERE log=3132 ORDER BY log;133 }134} {0 1 2 3 4 5 6 7 8}135ifcapable subquery {136 do_test select4-2.2 {137 execsql {138 SELECT log FROM t1 WHERE n IN 139 (SELECT DISTINCT log FROM t1 UNION140 SELECT n FROM t1 WHERE log=3)141 ORDER BY log;142 }143 } {0 1 2 2 3 3 3 3}144}145do_test select4-2.3 {146 set v [catch {execsql {147 SELECT DISTINCT log FROM t1 ORDER BY log148 UNION149 SELECT n FROM t1 WHERE log=3150 ORDER BY log;151 }} msg]152 lappend v $msg153} {1 {ORDER BY clause should come after UNION not before}}154do_test select4-2.4 {155 set v [catch {execsql {156 SELECT 0 ORDER BY (SELECT 0) UNION SELECT 0;157 }} msg]158 lappend v $msg159} {1 {ORDER BY clause should come after UNION not before}}160do_execsql_test select4-2.5 {161 SELECT 123 AS x ORDER BY (SELECT x ORDER BY 1);162} {123}163 164# Except operator165#166do_test select4-3.1.1 {167 execsql {168 SELECT DISTINCT log FROM t1169 EXCEPT170 SELECT n FROM t1 WHERE log=3171 ORDER BY log;172 }173} {0 1 2 3 4}174do_test select4-3.1.2 {175 execsql {176 CREATE TABLE t2 AS 177 SELECT DISTINCT log FROM t1178 EXCEPT179 SELECT n FROM t1 WHERE log=3180 ORDER BY log;181 SELECT * FROM t2;182 }183} {0 1 2 3 4}184execsql {DROP TABLE t2}185do_test select4-3.1.3 {186 execsql {187 CREATE TABLE t2 AS 188 SELECT DISTINCT log FROM t1189 EXCEPT190 SELECT n FROM t1 WHERE log=3191 ORDER BY log DESC;192 SELECT * FROM t2;193 }194} {4 3 2 1 0}195execsql {DROP TABLE t2}196ifcapable subquery {197 do_test select4-3.2 {198 execsql {199 SELECT log FROM t1 WHERE n IN 200 (SELECT DISTINCT log FROM t1 EXCEPT201 SELECT n FROM t1 WHERE log=3)202 ORDER BY log;203 }204 } {0 1 2 2}205}206do_test select4-3.3 {207 set v [catch {execsql {208 SELECT DISTINCT log FROM t1 ORDER BY log209 EXCEPT210 SELECT n FROM t1 WHERE log=3211 ORDER BY log;212 }} msg]213 lappend v $msg214} {1 {ORDER BY clause should come after EXCEPT not before}}215 216# Intersect operator217#218do_test select4-4.1.1 {219 execsql {220 SELECT DISTINCT log FROM t1221 INTERSECT222 SELECT n FROM t1 WHERE log=3223 ORDER BY log;224 }225} {5}226 227do_test select4-4.1.2 {228 execsql {229 SELECT DISTINCT log FROM t1230 UNION ALL231 SELECT 6232 INTERSECT233 SELECT n FROM t1 WHERE log=3234 ORDER BY t1.log;235 }236} {5 6}237 238do_test select4-4.1.3 {239 execsql {240 CREATE TABLE t2 AS241 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6242 INTERSECT243 SELECT n FROM t1 WHERE log=3244 ORDER BY log;245 SELECT * FROM t2;246 }247} {5 6}248execsql {DROP TABLE t2}249do_test select4-4.1.4 {250 execsql {251 CREATE TABLE t2 AS252 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6253 INTERSECT254 SELECT n FROM t1 WHERE log=3255 ORDER BY log DESC;256 SELECT * FROM t2;257 }258} {6 5}259execsql {DROP TABLE t2}260ifcapable subquery {261 do_test select4-4.2 {262 execsql {263 SELECT log FROM t1 WHERE n IN 264 (SELECT DISTINCT log FROM t1 INTERSECT265 SELECT n FROM t1 WHERE log=3)266 ORDER BY log;267 }268 } {3}269}270do_test select4-4.3 {271 set v [catch {execsql {272 SELECT DISTINCT log FROM t1 ORDER BY log273 INTERSECT274 SELECT n FROM t1 WHERE log=3275 ORDER BY log;276 }} msg]277 lappend v $msg278} {1 {ORDER BY clause should come after INTERSECT not before}}279do_catchsql_test select4-4.4 {280 SELECT 3 IN (281 SELECT 0 ORDER BY 1282 INTERSECT283 SELECT 1284 INTERSECT 285 SELECT 2286 ORDER BY 1287 );288} {1 {ORDER BY clause should come after INTERSECT not before}}289 290# Various error messages while processing UNION or INTERSECT291#292do_test select4-5.1 {293 set v [catch {execsql {294 SELECT DISTINCT log FROM t2295 UNION ALL296 SELECT n FROM t1 WHERE log=3297 ORDER BY log;298 }} msg]299 lappend v $msg300} {1 {no such table: t2}}301do_test select4-5.2 {302 set v [catch {execsql {303 SELECT DISTINCT log AS "xyzzy" FROM t1304 UNION ALL305 SELECT n FROM t1 WHERE log=3306 ORDER BY xyzzy;307 }} msg]308 lappend v $msg309} {0 {0 1 2 3 4 5 5 6 7 8}}310do_test select4-5.2b {311 set v [catch {execsql {312 SELECT DISTINCT log AS xyzzy FROM t1313 UNION ALL314 SELECT n FROM t1 WHERE log=3315 ORDER BY "xyzzy";316 }} msg]317 lappend v $msg318} {0 {0 1 2 3 4 5 5 6 7 8}}319do_test select4-5.2c {320 set v [catch {execsql {321 SELECT DISTINCT log FROM t1322 UNION ALL323 SELECT n FROM t1 WHERE log=3324 ORDER BY "xyzzy";325 }} msg]326 lappend v $msg327} {1 {1st ORDER BY term does not match any column in the result set}}328do_test select4-5.2d {329 set v [catch {execsql {330 SELECT DISTINCT log FROM t1331 INTERSECT332 SELECT n FROM t1 WHERE log=3333 ORDER BY "xyzzy";334 }} msg]335 lappend v $msg336} {1 {1st ORDER BY term does not match any column in the result set}}337do_test select4-5.2e {338 set v [catch {execsql {339 SELECT DISTINCT log FROM t1340 UNION ALL341 SELECT n FROM t1 WHERE log=3342 ORDER BY n;343 }} msg]344 lappend v $msg345} {0 {0 1 2 3 4 5 5 6 7 8}}346do_test select4-5.2f {347 catchsql {348 SELECT DISTINCT log FROM t1349 UNION ALL350 SELECT n FROM t1 WHERE log=3351 ORDER BY log;352 }353} {0 {0 1 2 3 4 5 5 6 7 8}}354do_test select4-5.2g {355 catchsql {356 SELECT DISTINCT log FROM t1357 UNION ALL358 SELECT n FROM t1 WHERE log=3359 ORDER BY 1;360 }361} {0 {0 1 2 3 4 5 5 6 7 8}}362do_test select4-5.2h {363 catchsql {364 SELECT DISTINCT log FROM t1365 UNION ALL366 SELECT n FROM t1 WHERE log=3367 ORDER BY 2;368 }369} {1 {1st ORDER BY term out of range - should be between 1 and 1}}370do_test select4-5.2i {371 catchsql {372 SELECT DISTINCT 1, log FROM t1373 UNION ALL374 SELECT 2, n FROM t1 WHERE log=3375 ORDER BY 2, 1;376 }377} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}378do_test select4-5.2j {379 catchsql {380 SELECT DISTINCT 1, log FROM t1381 UNION ALL382 SELECT 2, n FROM t1 WHERE log=3383 ORDER BY 1, 2 DESC;384 }385} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}386do_test select4-5.2k {387 catchsql {388 SELECT DISTINCT 1, log FROM t1389 UNION ALL390 SELECT 2, n FROM t1 WHERE log=3391 ORDER BY n, 1;392 }393} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}394do_test select4-5.3 {395 set v [catch {execsql {396 SELECT DISTINCT log, n FROM t1397 UNION ALL398 SELECT n FROM t1 WHERE log=3399 ORDER BY log;400 }} msg]401 lappend v $msg402} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}403do_test select4-5.3-3807-1 {404 catchsql {405 SELECT 1 UNION SELECT 2, 3 UNION SELECT 4, 5 ORDER BY 1;406 }407} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}408do_test select4-5.4 {409 set v [catch {execsql {410 SELECT log FROM t1 WHERE n=2411 UNION ALL412 SELECT log FROM t1 WHERE n=3413 UNION ALL414 SELECT log FROM t1 WHERE n=4415 UNION ALL416 SELECT log FROM t1 WHERE n=5417 ORDER BY log;418 }} msg]419 lappend v $msg420} {0 {1 2 2 3}}421 422do_test select4-6.1 {423 execsql {424 SELECT log, count(*) as cnt FROM t1 GROUP BY log425 UNION426 SELECT log, n FROM t1 WHERE n=7427 ORDER BY cnt, log;428 }429} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}430do_test select4-6.2 {431 execsql {432 SELECT log, count(*) FROM t1 GROUP BY log433 UNION434 SELECT log, n FROM t1 WHERE n=7435 ORDER BY count(*), log;436 }437} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}438 439# NULLs are indistinct for the UNION operator.440# Make sure the UNION operator recognizes this441#442do_test select4-6.3 {443 execsql {444 SELECT NULL UNION SELECT NULL UNION445 SELECT 1 UNION SELECT 2 AS 'x'446 ORDER BY x;447 }448} {{} 1 2}449do_test select4-6.3.1 {450 execsql {451 SELECT NULL UNION ALL SELECT NULL UNION ALL452 SELECT 1 UNION ALL SELECT 2 AS 'x'453 ORDER BY x;454 }455} {{} {} 1 2}456 457# Make sure the DISTINCT keyword treats NULLs as indistinct.458#459ifcapable subquery {460 do_test select4-6.4 {461 execsql {462 SELECT * FROM (463 SELECT NULL, 1 UNION ALL SELECT NULL, 1464 );465 }466 } {{} 1 {} 1}467 do_test select4-6.5 {468 execsql {469 SELECT DISTINCT * FROM (470 SELECT NULL, 1 UNION ALL SELECT NULL, 1471 );472 }473 } {{} 1}474 do_test select4-6.6 {475 execsql {476 SELECT DISTINCT * FROM (477 SELECT 1,2 UNION ALL SELECT 1,2478 );479 }480 } {1 2}481}482 483# Test distinctness of NULL in other ways.484#485do_test select4-6.7 {486 execsql {487 SELECT NULL EXCEPT SELECT NULL488 }489} {}490 491 492# Make sure column names are correct when a compound select appears as493# an expression in the WHERE clause.494#495do_test select4-7.1 {496 execsql {497 CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;498 SELECT * FROM t2 ORDER BY x;499 }500} {0 1 1 1 2 2 3 4 4 8 5 15} 501ifcapable subquery {502 do_test select4-7.2 {503 execsql2 {504 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)505 ORDER BY n506 }507 } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}508 do_test select4-7.3 {509 execsql2 {510 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)511 ORDER BY n LIMIT 2512 }513 } {n 6 log 3 n 7 log 3}514 do_test select4-7.4 {515 execsql2 {516 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)517 ORDER BY n LIMIT 2518 }519 } {n 1 log 0 n 2 log 1}520} ;# ifcapable subquery521 522} ;# ifcapable compound523 524# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.525do_test select4-8.1 {526 execsql {527 BEGIN;528 CREATE TABLE t3(a text, b float, c text);529 INSERT INTO t3 VALUES(1, 1.1, '1.1');530 INSERT INTO t3 VALUES(2, 1.10, '1.10');531 INSERT INTO t3 VALUES(3, 1.10, '1.1');532 INSERT INTO t3 VALUES(4, 1.1, '1.10');533 INSERT INTO t3 VALUES(5, 1.2, '1.2');534 INSERT INTO t3 VALUES(6, 1.3, '1.3');535 COMMIT;536 }537 execsql {538 SELECT DISTINCT b FROM t3 ORDER BY c;539 }540} {1.1 1.2 1.3}541do_test select4-8.2 {542 execsql {543 SELECT DISTINCT c FROM t3 ORDER BY c;544 }545} {1.1 1.10 1.2 1.3}546 547# Make sure the names of columns are taken from the right-most subquery548# right in a compound query. Ticket #1721549#550ifcapable compound {551 552do_test select4-9.1 {553 execsql2 {554 SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1555 }556} {x 0 y 1}557do_test select4-9.2 {558 execsql2 {559 SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1560 }561} {x 0 y 1}562do_test select4-9.3 {563 execsql2 {564 SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1565 }566} {x 0 y 1}567do_test select4-9.4 {568 execsql2 {569 SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;570 }571} {x 0 y 1}572do_test select4-9.5 {573 execsql2 {574 SELECT 0 AS x, 1 AS y575 UNION576 SELECT 2 AS p, 3 AS q577 UNION578 SELECT 4 AS a, 5 AS b579 ORDER BY x LIMIT 1580 }581} {x 0 y 1}582 583ifcapable subquery {584do_test select4-9.6 {585 execsql2 {586 SELECT * FROM (587 SELECT 0 AS x, 1 AS y588 UNION589 SELECT 2 AS p, 3 AS q590 UNION591 SELECT 4 AS a, 5 AS b592 ) ORDER BY 1 LIMIT 1;593 }594} {x 0 y 1}595do_test select4-9.7 {596 execsql2 {597 SELECT * FROM (598 SELECT 0 AS x, 1 AS y599 UNION600 SELECT 2 AS p, 3 AS q601 UNION602 SELECT 4 AS a, 5 AS b603 ) ORDER BY x LIMIT 1;604 }605} {x 0 y 1}606} ;# ifcapable subquery607 608do_test select4-9.8 {609 execsql {610 SELECT 0 AS x, 1 AS y611 UNION612 SELECT 2 AS y, -3 AS x613 ORDER BY x LIMIT 1;614 }615} {0 1}616 617do_test select4-9.9.1 {618 execsql2 {619 SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a620 }621} {a 1 b 2 a 3 b 4}622 623ifcapable subquery {624do_test select4-9.9.2 {625 execsql2 {626 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)627 WHERE b=3628 }629} {}630do_test select4-9.10 {631 execsql2 {632 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)633 WHERE b=2634 }635} {a 1 b 2}636do_test select4-9.11 {637 execsql2 {638 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)639 WHERE b=2640 }641} {a 1 b 2}642do_test select4-9.12 {643 execsql2 {644 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)645 WHERE b>0646 }647} {a 1 b 2 a 3 b 4}648} ;# ifcapable subquery649 650# Try combining DISTINCT, LIMIT, and OFFSET. Make sure they all work651# together.652#653do_test select4-10.1 {654 execsql {655 SELECT DISTINCT log FROM t1 ORDER BY log656 }657} {0 1 2 3 4 5}658do_test select4-10.2 {659 execsql {660 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4661 }662} {0 1 2 3}663do_test select4-10.3 {664 execsql {665 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0666 }667} {}668do_test select4-10.4 {669 execsql {670 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1671 }672} {0 1 2 3 4 5}673do_test select4-10.5 {674 execsql {675 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2676 }677} {2 3 4 5}678do_test select4-10.6 {679 execsql {680 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2681 }682} {2 3 4}683do_test select4-10.7 {684 execsql {685 SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20686 }687} {}688do_test select4-10.8 {689 execsql {690 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3691 }692} {}693do_test select4-10.9 {694 execsql {695 SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1696 }697} {31 5}698 699# Make sure compound SELECTs with wildly different numbers of columns700# do not cause assertion faults due to register allocation issues.701#702do_test select4-11.1 {703 catchsql {704 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2705 UNION706 SELECT x FROM t2707 }708} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}709do_test select4-11.2 {710 catchsql {711 SELECT x FROM t2712 UNION713 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2714 }715} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}716do_test select4-11.3 {717 catchsql {718 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2719 UNION ALL720 SELECT x FROM t2721 }722} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}723do_test select4-11.4 {724 catchsql {725 SELECT x FROM t2726 UNION ALL727 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2728 }729} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}730do_test select4-11.5 {731 catchsql {732 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2733 EXCEPT734 SELECT x FROM t2735 }736} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}737do_test select4-11.6 {738 catchsql {739 SELECT x FROM t2740 EXCEPT741 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2742 }743} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}744do_test select4-11.7 {745 catchsql {746 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2747 INTERSECT748 SELECT x FROM t2749 }750} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}751do_test select4-11.8 {752 catchsql {753 SELECT x FROM t2754 INTERSECT755 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2756 }757} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}758 759do_test select4-11.11 {760 catchsql {761 SELECT x FROM t2762 UNION763 SELECT x FROM t2764 UNION ALL765 SELECT x FROM t2766 EXCEPT767 SELECT x FROM t2768 INTERSECT769 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2770 }771} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}772do_test select4-11.12 {773 catchsql {774 SELECT x FROM t2775 UNION776 SELECT x FROM t2777 UNION ALL778 SELECT x FROM t2779 EXCEPT780 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2781 EXCEPT782 SELECT x FROM t2783 }784} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}785do_test select4-11.13 {786 catchsql {787 SELECT x FROM t2788 UNION789 SELECT x FROM t2790 UNION ALL791 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2792 UNION ALL793 SELECT x FROM t2794 EXCEPT795 SELECT x FROM t2796 }797} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}798do_test select4-11.14 {799 catchsql {800 SELECT x FROM t2801 UNION802 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2803 UNION804 SELECT x FROM t2805 UNION ALL806 SELECT x FROM t2807 EXCEPT808 SELECT x FROM t2809 }810} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}811do_test select4-11.15 {812 catchsql {813 SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2814 UNION815 SELECT x FROM t2816 INTERSECT817 SELECT x FROM t2818 UNION ALL819 SELECT x FROM t2820 EXCEPT821 SELECT x FROM t2822 }823} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}824do_test select4-11.16 {825 catchsql {826 INSERT INTO t2(rowid) VALUES(2) UNION SELECT 3,4 UNION SELECT 5,6 ORDER BY 1;827 }828} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}829 830do_test select4-12.1 {831 sqlite3 db2 :memory:832 catchsql {833 SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1;834 } db2835} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}836 837} ;# ifcapable compound838 839 840# Ticket [3557ad65a076c] - Incorrect DISTINCT processing with an841# indexed query using IN.842#843do_test select4-13.1 {844 sqlite3 db test.db845 db eval {846 CREATE TABLE t13(a,b);847 INSERT INTO t13 VALUES(1,1);848 INSERT INTO t13 VALUES(2,1);849 INSERT INTO t13 VALUES(3,1);850 INSERT INTO t13 VALUES(2,2);851 INSERT INTO t13 VALUES(3,2);852 INSERT INTO t13 VALUES(4,2);853 CREATE INDEX t13ab ON t13(a,b);854 SELECT DISTINCT b from t13 WHERE a IN (1,2,3);855 }856} {1 2}857 858# 2014-02-18: Make sure compound SELECTs work with VALUES clauses859#860do_execsql_test select4-14.1 {861 CREATE TABLE t14(a,b,c);862 INSERT INTO t14 VALUES(1,2,3),(4,5,6);863 SELECT * FROM t14 INTERSECT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);864} {1 2 3}865do_execsql_test select4-14.2 {866 SELECT * FROM t14 INTERSECT VALUES(1,2,3);867} {1 2 3}868do_execsql_test select4-14.3 {869 SELECT * FROM t14870 UNION VALUES(3,2,1),(2,3,1),(1,2,3),(7,8,9),(4,5,6)871 UNION SELECT * FROM t14 ORDER BY 1, 2, 3872} {1 2 3 2 3 1 3 2 1 4 5 6 7 8 9}873do_execsql_test select4-14.4 {874 SELECT * FROM t14875 UNION VALUES(3,2,1)876 UNION SELECT * FROM t14 ORDER BY 1, 2, 3877} {1 2 3 3 2 1 4 5 6}878do_execsql_test select4-14.5 {879 SELECT * FROM t14 EXCEPT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);880} {4 5 6}881do_execsql_test select4-14.6 {882 SELECT * FROM t14 EXCEPT VALUES(1,2,3)883} {4 5 6}884do_execsql_test select4-14.7 {885 SELECT * FROM t14 EXCEPT VALUES(1,2,3) EXCEPT VALUES(4,5,6)886} {}887do_execsql_test select4-14.8 {888 SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)889} {1 2 3}890do_execsql_test select4-14.9 {891 SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);892} {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}893do_execsql_test select4-14.10 {894 SELECT (VALUES(1),(2),(3),(4))895} {1}896do_execsql_test select4-14.11 {897 SELECT (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)898} {1}899do_execsql_test select4-14.12 {900 VALUES(1) UNION VALUES(2);901} {1 2}902do_execsql_test select4-14.13 {903 VALUES(1),(2),(3) EXCEPT VALUES(2);904} {1 3}905do_execsql_test select4-14.14 {906 VALUES(1),(2),(3) EXCEPT VALUES(1),(3);907} {2}908do_execsql_test select4-14.15 {909 SELECT * FROM (SELECT 123), (SELECT 456) ON likely(0 OR 1) OR 0;910} {123 456}911do_execsql_test select4-14.16 {912 VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 99;913} {1 2 3 4 5}914do_execsql_test select4-14.17 {915 VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 3;916} {1 2 3}917 918# Ticket https://sqlite.org/src/info/d06a25c84454a372919# Incorrect answer due to two co-routines using the same registers and expecting920# those register values to be preserved across a Yield.921#922do_execsql_test select4-15.1 {923 DROP TABLE IF EXISTS tx;924 CREATE TABLE tx(id INTEGER PRIMARY KEY, a, b);925 INSERT INTO tx(a,b) VALUES(33,456);926 INSERT INTO tx(a,b) VALUES(33,789);927 928 SELECT DISTINCT t0.id, t0.a, t0.b929 FROM tx AS t0, tx AS t1930 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456931 UNION932 SELECT DISTINCT t0.id, t0.a, t0.b933 FROM tx AS t0, tx AS t1934 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789935 ORDER BY 1;936} {1 33 456 2 33 789}937 938# Enhancement (2016-03-15): Use a co-routine for subqueries if the939# subquery is guaranteed to be the outer-most query940#941do_execsql_test select4-16.1 {942 DROP TABLE IF EXISTS t1;943 CREATE TABLE t1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,944 PRIMARY KEY(a,b DESC)) WITHOUT ROWID;945 946 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)947 INSERT INTO t1(a,b,c,d)948 SELECT x%10, x/10, x, printf('xyz%dabc',x) FROM c;949 950 SELECT t3.c FROM 951 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2952 JOIN t1 AS t3953 WHERE t2.a=t3.a AND t2.m=t3.b954 ORDER BY t3.a;955} {95 96 97 98 99}956do_execsql_test select4-16.2 {957 SELECT t3.c FROM 958 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2959 CROSS JOIN t1 AS t3960 WHERE t2.a=t3.a AND t2.m=t3.b961 ORDER BY t3.a;962} {95 96 97 98 99}963do_execsql_test select4-16.3 {964 SELECT t3.c FROM 965 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2966 LEFT JOIN t1 AS t3967 WHERE t2.a=t3.a AND t2.m=t3.b968 ORDER BY t3.a;969} {95 96 97 98 99}970 971# Ticket https://sqlite.org/src/tktview/f7f8c97e975978d45 on 2016-04-25972#973# The where push-down optimization from 2015-06-02 is suppose to disable974# on aggregate subqueries. But if the subquery is a compound where the975# last SELECT is non-aggregate but some other SELECT is an aggregate, the976# test is incomplete and the optimization is not properly disabled.977# 978# The following test cases verify that the fix works.979#980do_execsql_test select4-17.1 {981 DROP TABLE IF EXISTS t1;982 CREATE TABLE t1(a int, b int);983 INSERT INTO t1 VALUES(1,2),(1,18),(2,19);984 SELECT x, y FROM (985 SELECT 98 AS x, 99 AS y986 UNION987 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a988 ) AS w WHERE y>=20989 ORDER BY +x;990} {1 20 98 99}991do_execsql_test select4-17.2 {992 SELECT x, y FROM (993 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a994 UNION995 SELECT 98 AS x, 99 AS y996 ) AS w WHERE y>=20997 ORDER BY +x;998} {1 20 98 99}999do_catchsql_test select4-17.3 {1000 SELECT x, y FROM (1001 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 31002 UNION1003 SELECT 98 AS x, 99 AS y1004 ) AS w WHERE y>=201005 ORDER BY +x;1006} {1 {LIMIT clause should come after UNION not before}}1007 1008# 2020-04-03 ticket 51166be0159fd2ce from Yong Heng.1009# Adverse interaction between the constant propagation and push-down1010# optimizations.1011#1012reset_db1013do_execsql_test select4-18.1 {1014 CREATE VIEW v0(v0) AS WITH v0 AS(SELECT 0 v0) SELECT(SELECT min(v0) OVER()) FROM v0 GROUP BY v0;1015 SELECT *FROM v0 v1 JOIN v0 USING(v0) WHERE datetime(v0) = (v0.v0)AND v0 = 10;1016} {}1017do_execsql_test select4-18.2 {1018 CREATE VIEW t1(aa) AS1019 WITH t2(bb) AS (SELECT 123)1020 SELECT (SELECT min(bb) OVER()) FROM t2 GROUP BY bb;1021 SELECT * FROM t1;1022} {123}1023do_execsql_test select4-18.3 {1024 SELECT * FROM t1 AS z1 JOIN t1 AS z2 USING(aa)1025 WHERE abs(z1.aa)=z2.aa AND z1.aa=123;1026} {123}1027 1028# 2021-03-31 Fix an assert() problem in the logic at the end of sqlite3Select()1029# that validates AggInfo. The checks to ensure that AggInfo.aCol[].pCExpr1030# references a valid expression was looking at an expression that had been1031# deleted by the truth optimization in sqlite3ExprAnd() which was invoked by1032# the push-down optimization. This is harmless in delivery builds, as that code1033# only runs with SQLITE_DEBUG. But it should still be fixed. The problem1034# was discovered by dbsqlfuzz (crash-dece7b67a3552ed7e571a7bda903afd1f7bd9b21)1035#1036reset_db1037do_execsql_test select4-19.1 {1038 CREATE TABLE t1(x);1039 INSERT INTO t1 VALUES(99);1040 SELECT sum((SELECT 1 FROM (SELECT 2 WHERE x IS NULL) WHERE 0)) FROM t1;1041} {{}}1042 1043finish_test1044