From 135aae8ab53f6e51aa80e553e6a6bb0aa40392fb Mon Sep 17 00:00:00 2001 From: dan Date: Mon, 13 Sep 2010 18:58:33 +0000 Subject: [PATCH] Add tests for compound SELECT statements to e_select.test. FossilOrigin-Name: 8b0b009f292e0b52bffb08662aef67d9465e5a0a --- manifest | 12 +-- manifest.uuid | 2 +- test/e_select.test | 227 +++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 234 insertions(+), 7 deletions(-) diff --git a/manifest b/manifest index 162f6821e3..5040be621f 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Fix\sa\scouple\sof\stest\sfiles\sso\sthat\sthey\swork\swith\sDEFAULT_AUTOVACUUM. -D 2010-09-13T14:38:19 +C Add\stests\sfor\scompound\sSELECT\sstatements\sto\se_select.test. +D 2010-09-13T18:58:33 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in c599a15d268b1db2aeadea19df2adc3bf2eb6bee F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -350,7 +350,7 @@ F test/distinctagg.test 1a6ef9c87a58669438fc771450d7a72577417376 F test/e_expr.test 164e87c1d7b40ceb47c57c3bffa384c81d009aa7 F test/e_fkey.test 6721a741c6499b3ab7e5385923233343c8f1ad05 F test/e_fts3.test 75bb0aee26384ef586165e21018a17f7cd843469 -F test/e_select.test b033fb107c7f8becd68b688ec9c98329142de68d +F test/e_select.test d2c9d044811e93580f5743228a84f9627e7a6756 F test/enc.test e54531cd6bf941ee6760be041dff19a104c7acea F test/enc2.test 6d91a5286f59add0cfcbb2d0da913b76f2242398 F test/enc3.test 5c550d59ff31dccdba5d1a02ae11c7047d77c041 @@ -857,7 +857,7 @@ F tool/speedtest2.tcl ee2149167303ba8e95af97873c575c3e0fab58ff F tool/speedtest8.c 2902c46588c40b55661e471d7a86e4dd71a18224 F tool/speedtest8inst1.c 293327bc76823f473684d589a8160bde1f52c14e F tool/vdbe-compress.tcl d70ea6d8a19e3571d7ab8c9b75cba86d1173ff0f -P 3f5406e3231fbe84659712c9383b3e04cc6c11a8 -R c8fba6049d8d526de70cafa93adbbc04 +P 8cb39306f4bd9104c9d15d5ee98ec1252e798997 +R ea7890a7325b1ef483fb569c05bb662a U dan -Z 6e9bdacc0eb9bafd55e318a6d36e9d2e +Z 4d374c026bd8b69bfc76d182067e92a2 diff --git a/manifest.uuid b/manifest.uuid index 546aa3040b..d0960dd95a 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -8cb39306f4bd9104c9d15d5ee98ec1252e798997 \ No newline at end of file +8b0b009f292e0b52bffb08662aef67d9465e5a0a \ No newline at end of file diff --git a/test/e_select.test b/test/e_select.test index 3b2359b308..e65332607a 100644 --- a/test/e_select.test +++ b/test/e_select.test @@ -1631,4 +1631,231 @@ foreach {tn select res} { do_execsql_test e_select-5.$tn $select [list {*}$res] } +#------------------------------------------------------------------------- +# The following tests - e_select-7.* - test that statements made to do +# with compound SELECT statements are correct. +# + +# EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent +# SELECTs must return the same number of result columns. +# +# All the other tests in this section use compound SELECTs created +# using component SELECTs that do return the same number of columns. +# So the tests here just show that it is an error to attempt otherwise. +# +drop_all_tables +do_execsql_test e_select-7.1.0 { + CREATE TABLE j1(a, b, c); + CREATE TABLE j2(e, f); + CREATE TABLE j3(g); +} {} +foreach {tn select op} { + 1 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {UNION ALL} + 2 "SELECT * FROM j1 UNION ALL SELECT * FROM j3" {UNION ALL} + 3 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {UNION ALL} + 4 "SELECT a, b FROM j1 UNION ALL SELECT * FROM j3,j2" {UNION ALL} + 5 "SELECT * FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL} + + 6 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} + 7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION} + 8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} + 9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION} + 10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION} + + 11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} + 12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT} + 13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} + 14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT} + 15 "SELECT * FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT} + + 16 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} + 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j3" {EXCEPT} + 18 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} + 19 "SELECT a, b FROM j1 EXCEPT SELECT * FROM j3,j2" {EXCEPT} + 20 "SELECT * FROM j3,j2 EXCEPT SELECT a, b FROM j1" {EXCEPT} +} { + set err "SELECTs to the left and right of " + append err $op + append err " do not have the same number of result columns" + do_catchsql_test e_select-7.1.$tn $select [list 1 $err] +} + +# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must +# be simple SELECT statements, they may not contain ORDER BY or LIMIT +# clauses. +# +foreach {tn select op1 op2} { + 1 "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" + {ORDER BY} {UNION ALL} + 2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2" + {ORDER BY} {UNION ALL} + 3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2" + {ORDER BY} {UNION ALL} + 4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" + LIMIT {UNION ALL} + 5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" + LIMIT {UNION ALL} + 6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" + LIMIT {UNION ALL} + + 7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" + {ORDER BY} {UNION} + 8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2" + {ORDER BY} {UNION} + 9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2" + {ORDER BY} {UNION} + 10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" + LIMIT {UNION} + 11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" + LIMIT {UNION} + 12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" + LIMIT {UNION} + + 13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" + {ORDER BY} {EXCEPT} + 14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2" + {ORDER BY} {EXCEPT} + 15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2" + {ORDER BY} {EXCEPT} + 16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" + LIMIT {EXCEPT} + 17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" + LIMIT {EXCEPT} + 18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" + LIMIT {EXCEPT} + + 19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" + {ORDER BY} {INTERSECT} + 20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2" + {ORDER BY} {INTERSECT} + 21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2" + {ORDER BY} {INTERSECT} + 22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" + LIMIT {INTERSECT} + 23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" + LIMIT {INTERSECT} + 24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" + LIMIT {INTERSECT} +} { + set err "$op1 clause should come after $op2 not before" + do_catchsql_test e_select-7.2.$tn $select [list 1 $err] +} + +# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur +# at the end of the entire compound SELECT. +# +foreach {tn select} { + 1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a" + 2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1" + 3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3" + 4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" + 5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" + 6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" + + 7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a" + 8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1" + 9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3" + 10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" + 11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" + 12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" + + 13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a" + 14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1" + 15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3" + 16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" + 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" + 18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" + + 19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a" + 20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1" + 21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3" + 22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" + 23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" + 24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" +} { + do_test e_select-7.3.$tn { catch {execsql $select} msg } 0 +} + +# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL +# operator returns all the rows from the SELECT to the left of the UNION +# ALL operator, and all the rows from the SELECT to the right of it. +# +drop_all_tables +do_execsql_test e_select-7.4.0 { + CREATE TABLE q1(a TEXT, b INTEGER, c); + CREATE TABLE q2(d NUMBER, e BLOB); + + INSERT INTO q1 VALUES(16, -87.66, NULL); + INSERT INTO q1 VALUES('legible', 94, -42.47); + INSERT INTO q1 VALUES('beauty', 36, NULL); + + INSERT INTO q2 VALUES('legible', 1); + INSERT INTO q2 VALUES('beauty', 2); + INSERT INTO q2 VALUES(-65.91, 4); + INSERT INTO q2 VALUES('emanating', -16.56); + INSERT INTO q2 VALUES(NULL, -22.82); + INSERT INTO q2 VALUES(7.48, 'example'); +} {} +foreach {tn select res} { + 1 "SELECT a FROM q1 UNION ALL SELECT d FROM q2" + {16 legible beauty legible beauty -65.91 emanating {} 7.48} + + 2 "SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1" + {16 -87.66 {} x legible 1} + + 3 "SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2" {3 -22.82} +} { + do_execsql_test e_select-7.4.$tn $select [list {*}$res] +} + + +# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are +# connected into a compound SELECT, they group from left to right. In +# other words, if "A", "B" and "C" are all simple SELECT statements, (A +# op B op C) is processed as ((A op B) op C). +# +# e_select-7.X.1: Precedence of UNION vs. INTERSECT +# e_select-7.X.2: Precedence of UNION vs. UNION ALL +# e_select-7.X.3: Precedence of UNION vs. EXCEPT +# e_select-7.X.4: Precedence of INTERSECT vs. UNION ALL +# e_select-7.X.5: Precedence of INTERSECT vs. EXCEPT +# e_select-7.X.6: Precedence of UNION ALL vs. EXCEPT +# e_select-7.X.7: Check that "a EXCEPT b EXCEPT c" is processed as +# "(a EXCEPT b) EXCEPT c". +# +# The INTERSECT and EXCEPT operations are mutually commutative. So +# the e_select-7.X.5 test cases do not prove very much. +# +drop_all_tables +do_execsql_test e_select-7.X.0 { + CREATE TABLE t1(x); + INSERT INTO t1 VALUES(1); + INSERT INTO t1 VALUES(2); + INSERT INTO t1 VALUES(3); +} {} +foreach {tn select res} { + 1a "(1,2) INTERSECT (1) UNION (3)" {1 3} + 1b "(3) UNION (1,2) INTERSECT (1)" {1} + + 2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1} + 2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3} + + 3a "(1,2) UNION (3) EXCEPT (1)" {2 3} + 3b "(1,2) EXCEPT (3) UNION (1)" {1 2} + + 4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3} + 4b "(3) UNION (1,2) INTERSECT (1)" {1} + + 5a "(1,2) INTERSECT (2) EXCEPT (2)" {} + 5b "(2,3) EXCEPT (2) INTERSECT (2)" {} + + 6a "(2) UNION ALL (2) EXCEPT (2)" {} + 6b "(2) EXCEPT (2) UNION ALL (2)" {2} + + 7 "(2,3) EXCEPT (2) EXCEPT (3)" {} +} { + set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select] + do_execsql_test e_select-7.X.$tn $select [list {*}$res] +} + finish_test -- 2.47.2