000001 # 2001 September 15
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library. The
000012 # focus of this file is testing UNION, INTERSECT and EXCEPT operators
000013 # in SELECT statements.
000014 #
000015
000016 set testdir [file dirname $argv0]
000017 source $testdir/tester.tcl
000018
000019 # Most tests in this file depend on compound-select. But there are a couple
000020 # right at the end that test DISTINCT, so we cannot omit the entire file.
000021 #
000022 ifcapable compound {
000023
000024 # Build some test data
000025 #
000026 execsql {
000027 CREATE TABLE t1(n int, log int);
000028 BEGIN;
000029 }
000030 for {set i 1} {$i<32} {incr i} {
000031 for {set j 0} {(1<<$j)<$i} {incr j} {}
000032 execsql "INSERT INTO t1 VALUES($i,$j)"
000033 }
000034 execsql {
000035 COMMIT;
000036 }
000037
000038 do_test select4-1.0 {
000039 execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
000040 } {0 1 2 3 4 5}
000041
000042 # Union All operator
000043 #
000044 do_test select4-1.1a {
000045 lsort [execsql {SELECT DISTINCT log FROM t1}]
000046 } {0 1 2 3 4 5}
000047 do_test select4-1.1b {
000048 lsort [execsql {SELECT n FROM t1 WHERE log=3}]
000049 } {5 6 7 8}
000050 do_test select4-1.1c {
000051 execsql {
000052 SELECT DISTINCT log FROM t1
000053 UNION ALL
000054 SELECT n FROM t1 WHERE log=3
000055 ORDER BY log;
000056 }
000057 } {0 1 2 3 4 5 5 6 7 8}
000058 do_test select4-1.1d {
000059 execsql {
000060 CREATE TABLE t2 AS
000061 SELECT DISTINCT log FROM t1
000062 UNION ALL
000063 SELECT n FROM t1 WHERE log=3
000064 ORDER BY log;
000065 SELECT * FROM t2;
000066 }
000067 } {0 1 2 3 4 5 5 6 7 8}
000068 execsql {DROP TABLE t2}
000069 do_test select4-1.1e {
000070 execsql {
000071 CREATE TABLE t2 AS
000072 SELECT DISTINCT log FROM t1
000073 UNION ALL
000074 SELECT n FROM t1 WHERE log=3
000075 ORDER BY log DESC;
000076 SELECT * FROM t2;
000077 }
000078 } {8 7 6 5 5 4 3 2 1 0}
000079 execsql {DROP TABLE t2}
000080 do_test select4-1.1f {
000081 execsql {
000082 SELECT DISTINCT log FROM t1
000083 UNION ALL
000084 SELECT n FROM t1 WHERE log=2
000085 }
000086 } {0 1 2 3 4 5 3 4}
000087 do_test select4-1.1g {
000088 execsql {
000089 CREATE TABLE t2 AS
000090 SELECT DISTINCT log FROM t1
000091 UNION ALL
000092 SELECT n FROM t1 WHERE log=2;
000093 SELECT * FROM t2;
000094 }
000095 } {0 1 2 3 4 5 3 4}
000096 execsql {DROP TABLE t2}
000097 ifcapable subquery {
000098 do_test select4-1.2 {
000099 execsql {
000100 SELECT log FROM t1 WHERE n IN
000101 (SELECT DISTINCT log FROM t1 UNION ALL
000102 SELECT n FROM t1 WHERE log=3)
000103 ORDER BY log;
000104 }
000105 } {0 1 2 2 3 3 3 3}
000106 }
000107
000108 # EVIDENCE-OF: R-02644-22131 In a compound SELECT statement, only the
000109 # last or right-most simple SELECT may have an ORDER BY clause.
000110 #
000111 do_test select4-1.3 {
000112 set v [catch {execsql {
000113 SELECT DISTINCT log FROM t1 ORDER BY log
000114 UNION ALL
000115 SELECT n FROM t1 WHERE log=3
000116 ORDER BY log;
000117 }} msg]
000118 lappend v $msg
000119 } {1 {ORDER BY clause should come after UNION ALL not before}}
000120 do_catchsql_test select4-1.4 {
000121 SELECT (VALUES(0) INTERSECT SELECT(0) UNION SELECT(0) ORDER BY 1 UNION
000122 SELECT 0 UNION SELECT 0 ORDER BY 1);
000123 } {1 {ORDER BY clause should come after UNION not before}}
000124
000125 # Union operator
000126 #
000127 do_test select4-2.1 {
000128 execsql {
000129 SELECT DISTINCT log FROM t1
000130 UNION
000131 SELECT n FROM t1 WHERE log=3
000132 ORDER BY log;
000133 }
000134 } {0 1 2 3 4 5 6 7 8}
000135 ifcapable subquery {
000136 do_test select4-2.2 {
000137 execsql {
000138 SELECT log FROM t1 WHERE n IN
000139 (SELECT DISTINCT log FROM t1 UNION
000140 SELECT n FROM t1 WHERE log=3)
000141 ORDER BY log;
000142 }
000143 } {0 1 2 2 3 3 3 3}
000144 }
000145 do_test select4-2.3 {
000146 set v [catch {execsql {
000147 SELECT DISTINCT log FROM t1 ORDER BY log
000148 UNION
000149 SELECT n FROM t1 WHERE log=3
000150 ORDER BY log;
000151 }} msg]
000152 lappend v $msg
000153 } {1 {ORDER BY clause should come after UNION not before}}
000154 do_test select4-2.4 {
000155 set v [catch {execsql {
000156 SELECT 0 ORDER BY (SELECT 0) UNION SELECT 0;
000157 }} msg]
000158 lappend v $msg
000159 } {1 {ORDER BY clause should come after UNION not before}}
000160 do_execsql_test select4-2.5 {
000161 SELECT 123 AS x ORDER BY (SELECT x ORDER BY 1);
000162 } {123}
000163
000164 # Except operator
000165 #
000166 do_test select4-3.1.1 {
000167 execsql {
000168 SELECT DISTINCT log FROM t1
000169 EXCEPT
000170 SELECT n FROM t1 WHERE log=3
000171 ORDER BY log;
000172 }
000173 } {0 1 2 3 4}
000174 do_test select4-3.1.2 {
000175 execsql {
000176 CREATE TABLE t2 AS
000177 SELECT DISTINCT log FROM t1
000178 EXCEPT
000179 SELECT n FROM t1 WHERE log=3
000180 ORDER BY log;
000181 SELECT * FROM t2;
000182 }
000183 } {0 1 2 3 4}
000184 execsql {DROP TABLE t2}
000185 do_test select4-3.1.3 {
000186 execsql {
000187 CREATE TABLE t2 AS
000188 SELECT DISTINCT log FROM t1
000189 EXCEPT
000190 SELECT n FROM t1 WHERE log=3
000191 ORDER BY log DESC;
000192 SELECT * FROM t2;
000193 }
000194 } {4 3 2 1 0}
000195 execsql {DROP TABLE t2}
000196 ifcapable subquery {
000197 do_test select4-3.2 {
000198 execsql {
000199 SELECT log FROM t1 WHERE n IN
000200 (SELECT DISTINCT log FROM t1 EXCEPT
000201 SELECT n FROM t1 WHERE log=3)
000202 ORDER BY log;
000203 }
000204 } {0 1 2 2}
000205 }
000206 do_test select4-3.3 {
000207 set v [catch {execsql {
000208 SELECT DISTINCT log FROM t1 ORDER BY log
000209 EXCEPT
000210 SELECT n FROM t1 WHERE log=3
000211 ORDER BY log;
000212 }} msg]
000213 lappend v $msg
000214 } {1 {ORDER BY clause should come after EXCEPT not before}}
000215
000216 # Intersect operator
000217 #
000218 do_test select4-4.1.1 {
000219 execsql {
000220 SELECT DISTINCT log FROM t1
000221 INTERSECT
000222 SELECT n FROM t1 WHERE log=3
000223 ORDER BY log;
000224 }
000225 } {5}
000226
000227 do_test select4-4.1.2 {
000228 execsql {
000229 SELECT DISTINCT log FROM t1
000230 UNION ALL
000231 SELECT 6
000232 INTERSECT
000233 SELECT n FROM t1 WHERE log=3
000234 ORDER BY t1.log;
000235 }
000236 } {5 6}
000237
000238 do_test select4-4.1.3 {
000239 execsql {
000240 CREATE TABLE t2 AS
000241 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
000242 INTERSECT
000243 SELECT n FROM t1 WHERE log=3
000244 ORDER BY log;
000245 SELECT * FROM t2;
000246 }
000247 } {5 6}
000248 execsql {DROP TABLE t2}
000249 do_test select4-4.1.4 {
000250 execsql {
000251 CREATE TABLE t2 AS
000252 SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
000253 INTERSECT
000254 SELECT n FROM t1 WHERE log=3
000255 ORDER BY log DESC;
000256 SELECT * FROM t2;
000257 }
000258 } {6 5}
000259 execsql {DROP TABLE t2}
000260 ifcapable subquery {
000261 do_test select4-4.2 {
000262 execsql {
000263 SELECT log FROM t1 WHERE n IN
000264 (SELECT DISTINCT log FROM t1 INTERSECT
000265 SELECT n FROM t1 WHERE log=3)
000266 ORDER BY log;
000267 }
000268 } {3}
000269 }
000270 do_test select4-4.3 {
000271 set v [catch {execsql {
000272 SELECT DISTINCT log FROM t1 ORDER BY log
000273 INTERSECT
000274 SELECT n FROM t1 WHERE log=3
000275 ORDER BY log;
000276 }} msg]
000277 lappend v $msg
000278 } {1 {ORDER BY clause should come after INTERSECT not before}}
000279 do_catchsql_test select4-4.4 {
000280 SELECT 3 IN (
000281 SELECT 0 ORDER BY 1
000282 INTERSECT
000283 SELECT 1
000284 INTERSECT
000285 SELECT 2
000286 ORDER BY 1
000287 );
000288 } {1 {ORDER BY clause should come after INTERSECT not before}}
000289
000290 # Various error messages while processing UNION or INTERSECT
000291 #
000292 do_test select4-5.1 {
000293 set v [catch {execsql {
000294 SELECT DISTINCT log FROM t2
000295 UNION ALL
000296 SELECT n FROM t1 WHERE log=3
000297 ORDER BY log;
000298 }} msg]
000299 lappend v $msg
000300 } {1 {no such table: t2}}
000301 do_test select4-5.2 {
000302 set v [catch {execsql {
000303 SELECT DISTINCT log AS "xyzzy" FROM t1
000304 UNION ALL
000305 SELECT n FROM t1 WHERE log=3
000306 ORDER BY xyzzy;
000307 }} msg]
000308 lappend v $msg
000309 } {0 {0 1 2 3 4 5 5 6 7 8}}
000310 do_test select4-5.2b {
000311 set v [catch {execsql {
000312 SELECT DISTINCT log AS xyzzy FROM t1
000313 UNION ALL
000314 SELECT n FROM t1 WHERE log=3
000315 ORDER BY "xyzzy";
000316 }} msg]
000317 lappend v $msg
000318 } {0 {0 1 2 3 4 5 5 6 7 8}}
000319 do_test select4-5.2c {
000320 set v [catch {execsql {
000321 SELECT DISTINCT log FROM t1
000322 UNION ALL
000323 SELECT n FROM t1 WHERE log=3
000324 ORDER BY "xyzzy";
000325 }} msg]
000326 lappend v $msg
000327 } {1 {1st ORDER BY term does not match any column in the result set}}
000328 do_test select4-5.2d {
000329 set v [catch {execsql {
000330 SELECT DISTINCT log FROM t1
000331 INTERSECT
000332 SELECT n FROM t1 WHERE log=3
000333 ORDER BY "xyzzy";
000334 }} msg]
000335 lappend v $msg
000336 } {1 {1st ORDER BY term does not match any column in the result set}}
000337 do_test select4-5.2e {
000338 set v [catch {execsql {
000339 SELECT DISTINCT log FROM t1
000340 UNION ALL
000341 SELECT n FROM t1 WHERE log=3
000342 ORDER BY n;
000343 }} msg]
000344 lappend v $msg
000345 } {0 {0 1 2 3 4 5 5 6 7 8}}
000346 do_test select4-5.2f {
000347 catchsql {
000348 SELECT DISTINCT log FROM t1
000349 UNION ALL
000350 SELECT n FROM t1 WHERE log=3
000351 ORDER BY log;
000352 }
000353 } {0 {0 1 2 3 4 5 5 6 7 8}}
000354 do_test select4-5.2g {
000355 catchsql {
000356 SELECT DISTINCT log FROM t1
000357 UNION ALL
000358 SELECT n FROM t1 WHERE log=3
000359 ORDER BY 1;
000360 }
000361 } {0 {0 1 2 3 4 5 5 6 7 8}}
000362 do_test select4-5.2h {
000363 catchsql {
000364 SELECT DISTINCT log FROM t1
000365 UNION ALL
000366 SELECT n FROM t1 WHERE log=3
000367 ORDER BY 2;
000368 }
000369 } {1 {1st ORDER BY term out of range - should be between 1 and 1}}
000370 do_test select4-5.2i {
000371 catchsql {
000372 SELECT DISTINCT 1, log FROM t1
000373 UNION ALL
000374 SELECT 2, n FROM t1 WHERE log=3
000375 ORDER BY 2, 1;
000376 }
000377 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
000378 do_test select4-5.2j {
000379 catchsql {
000380 SELECT DISTINCT 1, log FROM t1
000381 UNION ALL
000382 SELECT 2, n FROM t1 WHERE log=3
000383 ORDER BY 1, 2 DESC;
000384 }
000385 } {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
000386 do_test select4-5.2k {
000387 catchsql {
000388 SELECT DISTINCT 1, log FROM t1
000389 UNION ALL
000390 SELECT 2, n FROM t1 WHERE log=3
000391 ORDER BY n, 1;
000392 }
000393 } {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
000394 do_test select4-5.3 {
000395 set v [catch {execsql {
000396 SELECT DISTINCT log, n FROM t1
000397 UNION ALL
000398 SELECT n FROM t1 WHERE log=3
000399 ORDER BY log;
000400 }} msg]
000401 lappend v $msg
000402 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000403 do_test select4-5.3-3807-1 {
000404 catchsql {
000405 SELECT 1 UNION SELECT 2, 3 UNION SELECT 4, 5 ORDER BY 1;
000406 }
000407 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000408 do_test select4-5.4 {
000409 set v [catch {execsql {
000410 SELECT log FROM t1 WHERE n=2
000411 UNION ALL
000412 SELECT log FROM t1 WHERE n=3
000413 UNION ALL
000414 SELECT log FROM t1 WHERE n=4
000415 UNION ALL
000416 SELECT log FROM t1 WHERE n=5
000417 ORDER BY log;
000418 }} msg]
000419 lappend v $msg
000420 } {0 {1 2 2 3}}
000421
000422 do_test select4-6.1 {
000423 execsql {
000424 SELECT log, count(*) as cnt FROM t1 GROUP BY log
000425 UNION
000426 SELECT log, n FROM t1 WHERE n=7
000427 ORDER BY cnt, log;
000428 }
000429 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
000430 do_test select4-6.2 {
000431 execsql {
000432 SELECT log, count(*) FROM t1 GROUP BY log
000433 UNION
000434 SELECT log, n FROM t1 WHERE n=7
000435 ORDER BY count(*), log;
000436 }
000437 } {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
000438
000439 # NULLs are indistinct for the UNION operator.
000440 # Make sure the UNION operator recognizes this
000441 #
000442 do_test select4-6.3 {
000443 execsql {
000444 SELECT NULL UNION SELECT NULL UNION
000445 SELECT 1 UNION SELECT 2 AS 'x'
000446 ORDER BY x;
000447 }
000448 } {{} 1 2}
000449 do_test select4-6.3.1 {
000450 execsql {
000451 SELECT NULL UNION ALL SELECT NULL UNION ALL
000452 SELECT 1 UNION ALL SELECT 2 AS 'x'
000453 ORDER BY x;
000454 }
000455 } {{} {} 1 2}
000456
000457 # Make sure the DISTINCT keyword treats NULLs as indistinct.
000458 #
000459 ifcapable subquery {
000460 do_test select4-6.4 {
000461 execsql {
000462 SELECT * FROM (
000463 SELECT NULL, 1 UNION ALL SELECT NULL, 1
000464 );
000465 }
000466 } {{} 1 {} 1}
000467 do_test select4-6.5 {
000468 execsql {
000469 SELECT DISTINCT * FROM (
000470 SELECT NULL, 1 UNION ALL SELECT NULL, 1
000471 );
000472 }
000473 } {{} 1}
000474 do_test select4-6.6 {
000475 execsql {
000476 SELECT DISTINCT * FROM (
000477 SELECT 1,2 UNION ALL SELECT 1,2
000478 );
000479 }
000480 } {1 2}
000481 }
000482
000483 # Test distinctness of NULL in other ways.
000484 #
000485 do_test select4-6.7 {
000486 execsql {
000487 SELECT NULL EXCEPT SELECT NULL
000488 }
000489 } {}
000490
000491
000492 # Make sure column names are correct when a compound select appears as
000493 # an expression in the WHERE clause.
000494 #
000495 do_test select4-7.1 {
000496 execsql {
000497 CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
000498 SELECT * FROM t2 ORDER BY x;
000499 }
000500 } {0 1 1 1 2 2 3 4 4 8 5 15}
000501 ifcapable subquery {
000502 do_test select4-7.2 {
000503 execsql2 {
000504 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
000505 ORDER BY n
000506 }
000507 } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
000508 do_test select4-7.3 {
000509 execsql2 {
000510 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
000511 ORDER BY n LIMIT 2
000512 }
000513 } {n 6 log 3 n 7 log 3}
000514 do_test select4-7.4 {
000515 execsql2 {
000516 SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
000517 ORDER BY n LIMIT 2
000518 }
000519 } {n 1 log 0 n 2 log 1}
000520 } ;# ifcapable subquery
000521
000522 } ;# ifcapable compound
000523
000524 # Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
000525 do_test select4-8.1 {
000526 execsql {
000527 BEGIN;
000528 CREATE TABLE t3(a text, b float, c text);
000529 INSERT INTO t3 VALUES(1, 1.1, '1.1');
000530 INSERT INTO t3 VALUES(2, 1.10, '1.10');
000531 INSERT INTO t3 VALUES(3, 1.10, '1.1');
000532 INSERT INTO t3 VALUES(4, 1.1, '1.10');
000533 INSERT INTO t3 VALUES(5, 1.2, '1.2');
000534 INSERT INTO t3 VALUES(6, 1.3, '1.3');
000535 COMMIT;
000536 }
000537 execsql {
000538 SELECT DISTINCT b FROM t3 ORDER BY c;
000539 }
000540 } {1.1 1.2 1.3}
000541 do_test select4-8.2 {
000542 execsql {
000543 SELECT DISTINCT c FROM t3 ORDER BY c;
000544 }
000545 } {1.1 1.10 1.2 1.3}
000546
000547 # Make sure the names of columns are taken from the right-most subquery
000548 # right in a compound query. Ticket #1721
000549 #
000550 ifcapable compound {
000551
000552 do_test select4-9.1 {
000553 execsql2 {
000554 SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
000555 }
000556 } {x 0 y 1}
000557 do_test select4-9.2 {
000558 execsql2 {
000559 SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
000560 }
000561 } {x 0 y 1}
000562 do_test select4-9.3 {
000563 execsql2 {
000564 SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
000565 }
000566 } {x 0 y 1}
000567 do_test select4-9.4 {
000568 execsql2 {
000569 SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
000570 }
000571 } {x 0 y 1}
000572 do_test select4-9.5 {
000573 execsql2 {
000574 SELECT 0 AS x, 1 AS y
000575 UNION
000576 SELECT 2 AS p, 3 AS q
000577 UNION
000578 SELECT 4 AS a, 5 AS b
000579 ORDER BY x LIMIT 1
000580 }
000581 } {x 0 y 1}
000582
000583 ifcapable subquery {
000584 do_test select4-9.6 {
000585 execsql2 {
000586 SELECT * FROM (
000587 SELECT 0 AS x, 1 AS y
000588 UNION
000589 SELECT 2 AS p, 3 AS q
000590 UNION
000591 SELECT 4 AS a, 5 AS b
000592 ) ORDER BY 1 LIMIT 1;
000593 }
000594 } {x 0 y 1}
000595 do_test select4-9.7 {
000596 execsql2 {
000597 SELECT * FROM (
000598 SELECT 0 AS x, 1 AS y
000599 UNION
000600 SELECT 2 AS p, 3 AS q
000601 UNION
000602 SELECT 4 AS a, 5 AS b
000603 ) ORDER BY x LIMIT 1;
000604 }
000605 } {x 0 y 1}
000606 } ;# ifcapable subquery
000607
000608 do_test select4-9.8 {
000609 execsql {
000610 SELECT 0 AS x, 1 AS y
000611 UNION
000612 SELECT 2 AS y, -3 AS x
000613 ORDER BY x LIMIT 1;
000614 }
000615 } {0 1}
000616
000617 do_test select4-9.9.1 {
000618 execsql2 {
000619 SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
000620 }
000621 } {a 1 b 2 a 3 b 4}
000622
000623 ifcapable subquery {
000624 do_test select4-9.9.2 {
000625 execsql2 {
000626 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
000627 WHERE b=3
000628 }
000629 } {}
000630 do_test select4-9.10 {
000631 execsql2 {
000632 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
000633 WHERE b=2
000634 }
000635 } {a 1 b 2}
000636 do_test select4-9.11 {
000637 execsql2 {
000638 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
000639 WHERE b=2
000640 }
000641 } {a 1 b 2}
000642 do_test select4-9.12 {
000643 execsql2 {
000644 SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
000645 WHERE b>0
000646 }
000647 } {a 1 b 2 a 3 b 4}
000648 } ;# ifcapable subquery
000649
000650 # Try combining DISTINCT, LIMIT, and OFFSET. Make sure they all work
000651 # together.
000652 #
000653 do_test select4-10.1 {
000654 execsql {
000655 SELECT DISTINCT log FROM t1 ORDER BY log
000656 }
000657 } {0 1 2 3 4 5}
000658 do_test select4-10.2 {
000659 execsql {
000660 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
000661 }
000662 } {0 1 2 3}
000663 do_test select4-10.3 {
000664 execsql {
000665 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
000666 }
000667 } {}
000668 do_test select4-10.4 {
000669 execsql {
000670 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
000671 }
000672 } {0 1 2 3 4 5}
000673 do_test select4-10.5 {
000674 execsql {
000675 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
000676 }
000677 } {2 3 4 5}
000678 do_test select4-10.6 {
000679 execsql {
000680 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
000681 }
000682 } {2 3 4}
000683 do_test select4-10.7 {
000684 execsql {
000685 SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
000686 }
000687 } {}
000688 do_test select4-10.8 {
000689 execsql {
000690 SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
000691 }
000692 } {}
000693 do_test select4-10.9 {
000694 execsql {
000695 SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
000696 }
000697 } {31 5}
000698
000699 # Make sure compound SELECTs with wildly different numbers of columns
000700 # do not cause assertion faults due to register allocation issues.
000701 #
000702 do_test select4-11.1 {
000703 catchsql {
000704 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 t2
000705 UNION
000706 SELECT x FROM t2
000707 }
000708 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000709 do_test select4-11.2 {
000710 catchsql {
000711 SELECT x FROM t2
000712 UNION
000713 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 t2
000714 }
000715 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000716 do_test select4-11.3 {
000717 catchsql {
000718 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 t2
000719 UNION ALL
000720 SELECT x FROM t2
000721 }
000722 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000723 do_test select4-11.4 {
000724 catchsql {
000725 SELECT x FROM t2
000726 UNION ALL
000727 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 t2
000728 }
000729 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000730 do_test select4-11.5 {
000731 catchsql {
000732 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 t2
000733 EXCEPT
000734 SELECT x FROM t2
000735 }
000736 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000737 do_test select4-11.6 {
000738 catchsql {
000739 SELECT x FROM t2
000740 EXCEPT
000741 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 t2
000742 }
000743 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000744 do_test select4-11.7 {
000745 catchsql {
000746 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 t2
000747 INTERSECT
000748 SELECT x FROM t2
000749 }
000750 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000751 do_test select4-11.8 {
000752 catchsql {
000753 SELECT x FROM t2
000754 INTERSECT
000755 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 t2
000756 }
000757 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000758
000759 do_test select4-11.11 {
000760 catchsql {
000761 SELECT x FROM t2
000762 UNION
000763 SELECT x FROM t2
000764 UNION ALL
000765 SELECT x FROM t2
000766 EXCEPT
000767 SELECT x FROM t2
000768 INTERSECT
000769 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 t2
000770 }
000771 } {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
000772 do_test select4-11.12 {
000773 catchsql {
000774 SELECT x FROM t2
000775 UNION
000776 SELECT x FROM t2
000777 UNION ALL
000778 SELECT x FROM t2
000779 EXCEPT
000780 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 t2
000781 EXCEPT
000782 SELECT x FROM t2
000783 }
000784 } {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
000785 do_test select4-11.13 {
000786 catchsql {
000787 SELECT x FROM t2
000788 UNION
000789 SELECT x FROM t2
000790 UNION ALL
000791 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 t2
000792 UNION ALL
000793 SELECT x FROM t2
000794 EXCEPT
000795 SELECT x FROM t2
000796 }
000797 } {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
000798 do_test select4-11.14 {
000799 catchsql {
000800 SELECT x FROM t2
000801 UNION
000802 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 t2
000803 UNION
000804 SELECT x FROM t2
000805 UNION ALL
000806 SELECT x FROM t2
000807 EXCEPT
000808 SELECT x FROM t2
000809 }
000810 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000811 do_test select4-11.15 {
000812 catchsql {
000813 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 t2
000814 UNION
000815 SELECT x FROM t2
000816 INTERSECT
000817 SELECT x FROM t2
000818 UNION ALL
000819 SELECT x FROM t2
000820 EXCEPT
000821 SELECT x FROM t2
000822 }
000823 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000824 do_test select4-11.16 {
000825 catchsql {
000826 INSERT INTO t2(rowid) VALUES(2) UNION SELECT 3,4 UNION SELECT 5,6 ORDER BY 1;
000827 }
000828 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000829
000830 do_test select4-12.1 {
000831 sqlite3 db2 :memory:
000832 catchsql {
000833 SELECT 1 UNION SELECT 2,3 UNION SELECT 4,5 ORDER BY 1;
000834 } db2
000835 } {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
000836
000837 } ;# ifcapable compound
000838
000839
000840 # Ticket [3557ad65a076c] - Incorrect DISTINCT processing with an
000841 # indexed query using IN.
000842 #
000843 do_test select4-13.1 {
000844 sqlite3 db test.db
000845 db eval {
000846 CREATE TABLE t13(a,b);
000847 INSERT INTO t13 VALUES(1,1);
000848 INSERT INTO t13 VALUES(2,1);
000849 INSERT INTO t13 VALUES(3,1);
000850 INSERT INTO t13 VALUES(2,2);
000851 INSERT INTO t13 VALUES(3,2);
000852 INSERT INTO t13 VALUES(4,2);
000853 CREATE INDEX t13ab ON t13(a,b);
000854 SELECT DISTINCT b from t13 WHERE a IN (1,2,3);
000855 }
000856 } {1 2}
000857
000858 # 2014-02-18: Make sure compound SELECTs work with VALUES clauses
000859 #
000860 do_execsql_test select4-14.1 {
000861 CREATE TABLE t14(a,b,c);
000862 INSERT INTO t14 VALUES(1,2,3),(4,5,6);
000863 SELECT * FROM t14 INTERSECT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000864 } {1 2 3}
000865 do_execsql_test select4-14.2 {
000866 SELECT * FROM t14 INTERSECT VALUES(1,2,3);
000867 } {1 2 3}
000868 do_execsql_test select4-14.3 {
000869 SELECT * FROM t14
000870 UNION VALUES(3,2,1),(2,3,1),(1,2,3),(7,8,9),(4,5,6)
000871 UNION SELECT * FROM t14 ORDER BY 1, 2, 3
000872 } {1 2 3 2 3 1 3 2 1 4 5 6 7 8 9}
000873 do_execsql_test select4-14.4 {
000874 SELECT * FROM t14
000875 UNION VALUES(3,2,1)
000876 UNION SELECT * FROM t14 ORDER BY 1, 2, 3
000877 } {1 2 3 3 2 1 4 5 6}
000878 do_execsql_test select4-14.5 {
000879 SELECT * FROM t14 EXCEPT VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000880 } {4 5 6}
000881 do_execsql_test select4-14.6 {
000882 SELECT * FROM t14 EXCEPT VALUES(1,2,3)
000883 } {4 5 6}
000884 do_execsql_test select4-14.7 {
000885 SELECT * FROM t14 EXCEPT VALUES(1,2,3) EXCEPT VALUES(4,5,6)
000886 } {}
000887 do_execsql_test select4-14.8 {
000888 SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6)
000889 } {1 2 3}
000890 do_execsql_test select4-14.9 {
000891 SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3);
000892 } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3}
000893 do_execsql_test select4-14.10 {
000894 SELECT (VALUES(1),(2),(3),(4))
000895 } {1}
000896 do_execsql_test select4-14.11 {
000897 SELECT (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4)
000898 } {1}
000899 do_execsql_test select4-14.12 {
000900 VALUES(1) UNION VALUES(2);
000901 } {1 2}
000902 do_execsql_test select4-14.13 {
000903 VALUES(1),(2),(3) EXCEPT VALUES(2);
000904 } {1 3}
000905 do_execsql_test select4-14.14 {
000906 VALUES(1),(2),(3) EXCEPT VALUES(1),(3);
000907 } {2}
000908 do_execsql_test select4-14.15 {
000909 SELECT * FROM (SELECT 123), (SELECT 456) ON likely(0 OR 1) OR 0;
000910 } {123 456}
000911 do_execsql_test select4-14.16 {
000912 VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 99;
000913 } {1 2 3 4 5}
000914 do_execsql_test select4-14.17 {
000915 VALUES(1),(2),(3),(4) UNION ALL SELECT 5 LIMIT 3;
000916 } {1 2 3}
000917
000918 # Ticket https://www.sqlite.org/src/info/d06a25c84454a372
000919 # Incorrect answer due to two co-routines using the same registers and expecting
000920 # those register values to be preserved across a Yield.
000921 #
000922 do_execsql_test select4-15.1 {
000923 DROP TABLE IF EXISTS tx;
000924 CREATE TABLE tx(id INTEGER PRIMARY KEY, a, b);
000925 INSERT INTO tx(a,b) VALUES(33,456);
000926 INSERT INTO tx(a,b) VALUES(33,789);
000927
000928 SELECT DISTINCT t0.id, t0.a, t0.b
000929 FROM tx AS t0, tx AS t1
000930 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456
000931 UNION
000932 SELECT DISTINCT t0.id, t0.a, t0.b
000933 FROM tx AS t0, tx AS t1
000934 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789
000935 ORDER BY 1;
000936 } {1 33 456 2 33 789}
000937
000938 # Enhancement (2016-03-15): Use a co-routine for subqueries if the
000939 # subquery is guaranteed to be the outer-most query
000940 #
000941 do_execsql_test select4-16.1 {
000942 DROP TABLE IF EXISTS t1;
000943 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,
000944 PRIMARY KEY(a,b DESC)) WITHOUT ROWID;
000945
000946 WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
000947 INSERT INTO t1(a,b,c,d)
000948 SELECT x%10, x/10, x, printf('xyz%dabc',x) FROM c;
000949
000950 SELECT t3.c FROM
000951 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000952 JOIN t1 AS t3
000953 WHERE t2.a=t3.a AND t2.m=t3.b
000954 ORDER BY t3.a;
000955 } {95 96 97 98 99}
000956 do_execsql_test select4-16.2 {
000957 SELECT t3.c FROM
000958 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000959 CROSS JOIN t1 AS t3
000960 WHERE t2.a=t3.a AND t2.m=t3.b
000961 ORDER BY t3.a;
000962 } {95 96 97 98 99}
000963 do_execsql_test select4-16.3 {
000964 SELECT t3.c FROM
000965 (SELECT a,max(b) AS m FROM t1 WHERE a>=5 GROUP BY a) AS t2
000966 LEFT JOIN t1 AS t3
000967 WHERE t2.a=t3.a AND t2.m=t3.b
000968 ORDER BY t3.a;
000969 } {95 96 97 98 99}
000970
000971 # Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45 on 2016-04-25
000972 #
000973 # The where push-down optimization from 2015-06-02 is suppose to disable
000974 # on aggregate subqueries. But if the subquery is a compound where the
000975 # last SELECT is non-aggregate but some other SELECT is an aggregate, the
000976 # test is incomplete and the optimization is not properly disabled.
000977 #
000978 # The following test cases verify that the fix works.
000979 #
000980 do_execsql_test select4-17.1 {
000981 DROP TABLE IF EXISTS t1;
000982 CREATE TABLE t1(a int, b int);
000983 INSERT INTO t1 VALUES(1,2),(1,18),(2,19);
000984 SELECT x, y FROM (
000985 SELECT 98 AS x, 99 AS y
000986 UNION
000987 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
000988 ) AS w WHERE y>=20
000989 ORDER BY +x;
000990 } {1 20 98 99}
000991 do_execsql_test select4-17.2 {
000992 SELECT x, y FROM (
000993 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a
000994 UNION
000995 SELECT 98 AS x, 99 AS y
000996 ) AS w WHERE y>=20
000997 ORDER BY +x;
000998 } {1 20 98 99}
000999 do_catchsql_test select4-17.3 {
001000 SELECT x, y FROM (
001001 SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3
001002 UNION
001003 SELECT 98 AS x, 99 AS y
001004 ) AS w WHERE y>=20
001005 ORDER BY +x;
001006 } {1 {LIMIT clause should come after UNION not before}}
001007
001008 # 2020-04-03 ticket 51166be0159fd2ce from Yong Heng.
001009 # Adverse interaction between the constant propagation and push-down
001010 # optimizations.
001011 #
001012 reset_db
001013 do_execsql_test select4-18.1 {
001014 CREATE VIEW v0(v0) AS WITH v0 AS(SELECT 0 v0) SELECT(SELECT min(v0) OVER()) FROM v0 GROUP BY v0;
001015 SELECT *FROM v0 v1 JOIN v0 USING(v0) WHERE datetime(v0) = (v0.v0)AND v0 = 10;
001016 } {}
001017 do_execsql_test select4-18.2 {
001018 CREATE VIEW t1(aa) AS
001019 WITH t2(bb) AS (SELECT 123)
001020 SELECT (SELECT min(bb) OVER()) FROM t2 GROUP BY bb;
001021 SELECT * FROM t1;
001022 } {123}
001023 do_execsql_test select4-18.3 {
001024 SELECT * FROM t1 AS z1 JOIN t1 AS z2 USING(aa)
001025 WHERE abs(z1.aa)=z2.aa AND z1.aa=123;
001026 } {123}
001027
001028 # 2021-03-31 Fix an assert() problem in the logic at the end of sqlite3Select()
001029 # that validates AggInfo. The checks to ensure that AggInfo.aCol[].pCExpr
001030 # references a valid expression was looking at an expression that had been
001031 # deleted by the truth optimization in sqlite3ExprAnd() which was invoked by
001032 # the push-down optimization. This is harmless in delivery builds, as that code
001033 # only runs with SQLITE_DEBUG. But it should still be fixed. The problem
001034 # was discovered by dbsqlfuzz (crash-dece7b67a3552ed7e571a7bda903afd1f7bd9b21)
001035 #
001036 reset_db
001037 do_execsql_test select4-19.1 {
001038 CREATE TABLE t1(x);
001039 INSERT INTO t1 VALUES(99);
001040 SELECT sum((SELECT 1 FROM (SELECT 2 WHERE x IS NULL) WHERE 0)) FROM t1;
001041 } {{}}
001042
001043 finish_test