diff options
| author | bernard <[email protected]> | 2017-11-02 13:50:42 +0800 |
|---|---|---|
| committer | bernard <[email protected]> | 2017-11-02 13:50:42 +0800 |
| commit | b2057f506a09e2adef81fc77efd697d16ae14bec (patch) | |
| tree | 3affefabfe376a0ec1b3ac4a5eef957c1dd58dc8 /components/external/SQLite-3.8.1/test/e_select.test | |
| parent | 23582c8129228b4961ad35319cfd2d270039345f (diff) | |
[External] Remove external from RT-Thread
Diffstat (limited to 'components/external/SQLite-3.8.1/test/e_select.test')
| -rw-r--r-- | components/external/SQLite-3.8.1/test/e_select.test | 2167 |
1 files changed, 0 insertions, 2167 deletions
diff --git a/components/external/SQLite-3.8.1/test/e_select.test b/components/external/SQLite-3.8.1/test/e_select.test deleted file mode 100644 index 61646a2a31..0000000000 --- a/components/external/SQLite-3.8.1/test/e_select.test +++ /dev/null @@ -1,2167 +0,0 @@ -# 2010 July 16 -# -# The author disclaims copyright to this source code. In place of -# a legal notice, here is a blessing: -# -# May you do good and not evil. -# May you find forgiveness for yourself and forgive others. -# May you share freely, never taking more than you give. -# -#*********************************************************************** -# -# This file implements tests to verify that the "testable statements" in -# the lang_select.html document are correct. -# - -set testdir [file dirname $argv0] -source $testdir/tester.tcl - -ifcapable !compound { - finish_test - return -} - -do_execsql_test e_select-1.0 { - CREATE TABLE t1(a, b); - INSERT INTO t1 VALUES('a', 'one'); - INSERT INTO t1 VALUES('b', 'two'); - INSERT INTO t1 VALUES('c', 'three'); - - CREATE TABLE t2(a, b); - INSERT INTO t2 VALUES('a', 'I'); - INSERT INTO t2 VALUES('b', 'II'); - INSERT INTO t2 VALUES('c', 'III'); - - CREATE TABLE t3(a, c); - INSERT INTO t3 VALUES('a', 1); - INSERT INTO t3 VALUES('b', 2); - - CREATE TABLE t4(a, c); - INSERT INTO t4 VALUES('a', NULL); - INSERT INTO t4 VALUES('b', 2); -} {} -set t1_cross_t2 [list \ - a one a I a one b II \ - a one c III b two a I \ - b two b II b two c III \ - c three a I c three b II \ - c three c III \ -] -set t1_cross_t1 [list \ - a one a one a one b two \ - a one c three b two a one \ - b two b two b two c three \ - c three a one c three b two \ - c three c three \ -] - - -# This proc is a specialized version of [do_execsql_test]. -# -# The second argument to this proc must be a SELECT statement that -# features a cross join of some time. Instead of the usual ",", -# "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be -# substituted. -# -# This test runs the SELECT three times - once with: -# -# * s/%JOIN%/,/ -# * s/%JOIN%/JOIN/ -# * s/%JOIN%/INNER JOIN/ -# * s/%JOIN%/CROSS JOIN/ -# -# and checks that each time the results of the SELECT are $res. -# -proc do_join_test {tn select res} { - foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { - set S [string map [list %JOIN% $joinop] $select] - uplevel do_execsql_test $tn.$tn2 [list $S] [list $res] - } -} - -#------------------------------------------------------------------------- -# The following tests check that all paths on the syntax diagrams on -# the lang_select.html page may be taken. -# -# -- syntax diagram join-constraint -# -do_join_test e_select-0.1.1 { - SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) -} {3} -do_join_test e_select-0.1.2 { - SELECT count(*) FROM t1 %JOIN% t2 USING (a) -} {3} -do_join_test e_select-0.1.3 { - SELECT count(*) FROM t1 %JOIN% t2 -} {9} -do_catchsql_test e_select-0.1.4 { - SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) -} {1 {cannot have both ON and USING clauses in the same join}} -do_catchsql_test e_select-0.1.5 { - SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) -} {1 {near "ON": syntax error}} - -# -- syntax diagram select-core -# -# 0: SELECT ... -# 1: SELECT DISTINCT ... -# 2: SELECT ALL ... -# -# 0: No FROM clause -# 1: Has FROM clause -# -# 0: No WHERE clause -# 1: Has WHERE clause -# -# 0: No GROUP BY clause -# 1: Has GROUP BY clause -# 2: Has GROUP BY and HAVING clauses -# -do_select_tests e_select-0.2 { - 0000.1 "SELECT 1, 2, 3 " {1 2 3} - 1000.1 "SELECT DISTINCT 1, 2, 3 " {1 2 3} - 2000.1 "SELECT ALL 1, 2, 3 " {1 2 3} - - 0100.1 "SELECT a, b, a||b FROM t1 " { - a one aone b two btwo c three cthree - } - 1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " { - a one aone b two btwo c three cthree - } - 1200.1 "SELECT ALL a, b, a||b FROM t1 " { - a one aone b two btwo c three cthree - } - - 0010.1 "SELECT 1, 2, 3 WHERE 1 " {1 2 3} - 0010.2 "SELECT 1, 2, 3 WHERE 0 " {} - 0010.3 "SELECT 1, 2, 3 WHERE NULL " {} - - 1010.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3} - - 2010.1 "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3} - - 0110.1 "SELECT a, b, a||b FROM t1 WHERE a!='x' " { - a one aone b two btwo c three cthree - } - 0110.2 "SELECT a, b, a||b FROM t1 WHERE a=='x'" {} - - 1110.1 "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " { - a one aone b two btwo c three cthree - } - - 2110.0 "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {} - - 0001.1 "SELECT 1, 2, 3 GROUP BY 2" {1 2 3} - 0002.1 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} - 0002.2 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} - - 1001.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3} - 1002.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} - 1002.2 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} - - 2001.1 "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3} - 2002.1 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} - 2002.2 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} - - 0101.1 "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} - 0102.1 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" { - 1 a 1 c 1 b - } - 0102.2 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { } - - 1101.1 "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} - 1102.1 "SELECT DISTINCT count(*), max(a) FROM t1 - GROUP BY b HAVING count(*)=1" { - 1 a 1 c 1 b - } - 1102.2 "SELECT DISTINCT count(*), max(a) FROM t1 - GROUP BY b HAVING count(*)=2" { - } - - 2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} - 2102.1 "SELECT ALL count(*), max(a) FROM t1 - GROUP BY b HAVING count(*)=1" { - 1 a 1 c 1 b - } - 2102.2 "SELECT ALL count(*), max(a) FROM t1 - GROUP BY b HAVING count(*)=2" { - } - - 0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} - 0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} - 0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {} - - 1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {} - 1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" - {1 2 3} - 1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {} - - 2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} - 2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} - 2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {} - - 0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a} - 0112.1 "SELECT count(*), max(a) FROM t1 - WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c} - 0112.2 "SELECT count(*), max(a) FROM t1 - WHERE 0 GROUP BY b HAVING count(*)=2" { } - 1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" - {1 a 1 b} - 1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a' - GROUP BY b HAVING count(*)=1" { - 1 c 1 b - } - 1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0 - GROUP BY b HAVING count(*)=2" { - } - - 2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" - {1 c 1 b} - 2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b' - GROUP BY b HAVING count(*)=1" { - 1 a 1 c - } - 2112.2 "SELECT ALL count(*), max(a) FROM t1 - WHERE 0 GROUP BY b HAVING count(*)=2" { } -} - - -# -- syntax diagram result-column -# -do_select_tests e_select-0.3 { - 1 "SELECT * FROM t1" {a one b two c three} - 2 "SELECT t1.* FROM t1" {a one b two c three} - 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx} - 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx} - 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx} -} - -# -- syntax diagram join-source -# -# -- syntax diagram join-op -# -do_select_tests e_select-0.4 { - 1 "SELECT t1.rowid FROM t1" {1 2 3} - 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3} - 3 "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} - - 4 "SELECT t1.rowid FROM t1" {1 2 3} - 5 "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3} - 6 "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" - {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} - - 7 "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2} - 8 "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3} - 9 "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3} - 10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2} - 11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2} - - 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} - 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} - 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} - 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} - 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} -} - -# -- syntax diagram compound-operator -# -do_select_tests e_select-0.5 { - 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4} - 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4} - 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3} - 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2} -} - -# -- syntax diagram ordering-term -# -do_select_tests e_select-0.6 { - 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob} - 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob} - 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob} - 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea} -} - -# -- syntax diagram select-stmt -# -do_select_tests e_select-0.7 { - 1 "SELECT * FROM t1" {a one b two c three} - 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two} - 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two} - - 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three} - 5 "SELECT * FROM t1 LIMIT 10 OFFSET 5" {} - 6 "SELECT * FROM t1 LIMIT 10, 5" {} - - 7 "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three} - 8 "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {} - 9 "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {} - - 10 "SELECT * FROM t1 UNION SELECT b, a FROM t1" - {a one b two c three one a three c two b} - 11 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" - {one a two b three c a one c three b two} - 12 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" - {one a two b three c a one c three b two} - 13 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" - {a one b two c three one a three c two b} - 14 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" - {two b} - 15 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" - {} - 16 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" - {a one b two c three one a three c two b} - 17 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" - {b two} - 18 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" - {} -} - -#------------------------------------------------------------------------- -# The following tests focus on FROM clause (join) processing. -# - -# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple -# SELECT statement, then the input data is implicitly a single row zero -# columns wide -# -do_select_tests e_select-1.1 { - 1 "SELECT 'abc'" {abc} - 2 "SELECT 'abc' WHERE NULL" {} - 3 "SELECT NULL" {{}} - 4 "SELECT count(*)" {1} - 5 "SELECT count(*) WHERE 0" {0} - 6 "SELECT count(*) WHERE 1" {1} -} - -# EVIDENCE-OF: R-48114-33255 If there is only a single table in the -# join-source following the FROM clause, then the input data used by the -# SELECT statement is the contents of the named table. -# -# The results of the SELECT queries suggest that they are operating on the -# contents of the table 'xx'. -# -do_execsql_test e_select-1.2.0 { - CREATE TABLE xx(x, y); - INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2'); - INSERT INTO xx VALUES(NULL, -16.87); - INSERT INTO xx VALUES(-17.89, 'linguistically'); -} {} -do_select_tests e_select-1.2 { - 1 "SELECT quote(x), quote(y) FROM xx" { - 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' - NULL -16.87 - -17.89 'linguistically' - } - - 2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3} - 3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87} -} - -# EVIDENCE-OF: R-23593-12456 If there is more than one table specified -# as part of the join-source following the FROM keyword, then the -# contents of each named table are joined into a single dataset for the -# simple SELECT statement to operate on. -# -# There are more detailed tests for subsequent requirements that add -# more detail to this idea. We just add a single test that shows that -# data is coming from each of the three tables following the FROM clause -# here to show that the statement, vague as it is, is not incorrect. -# -do_select_tests e_select-1.3 { - 1 "SELECT * FROM t1, t2, t3" { - a one a I a 1 a one a I b 2 a one b II a 1 - a one b II b 2 a one c III a 1 a one c III b 2 - b two a I a 1 b two a I b 2 b two b II a 1 - b two b II b 2 b two c III a 1 b two c III b 2 - c three a I a 1 c three a I b 2 c three b II a 1 - c three b II b 2 c three c III a 1 c three c III b 2 - } -} - -# -# The following block of tests - e_select-1.4.* - test that the description -# of cartesian joins in the SELECT documentation is consistent with SQLite. -# In doing so, we test the following three requirements as a side-effect: -# -# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER -# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, -# then the result of the join is simply the cartesian product of the -# left and right-hand datasets. -# -# The tests are built on this assertion. Really, they test that the output -# of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result -# of calculating the cartesian product of the left and right-hand datasets. -# -# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER -# JOIN", "JOIN" and "," join operators. -# -# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the -# same result as the "INNER JOIN", "JOIN" and "," operators -# -# All tests are run 4 times, with the only difference in each run being -# which of the 4 equivalent cartesian product join operators are used. -# Since the output data is the same in all cases, we consider that this -# qualifies as testing the two statements above. -# -do_execsql_test e_select-1.4.0 { - CREATE TABLE x1(a, b); - CREATE TABLE x2(c, d, e); - CREATE TABLE x3(f, g, h, i); - - -- x1: 3 rows, 2 columns - INSERT INTO x1 VALUES(24, 'converging'); - INSERT INTO x1 VALUES(NULL, X'CB71'); - INSERT INTO x1 VALUES('blonds', 'proprietary'); - - -- x2: 2 rows, 3 columns - INSERT INTO x2 VALUES(-60.06, NULL, NULL); - INSERT INTO x2 VALUES(-58, NULL, 1.21); - - -- x3: 5 rows, 4 columns - INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1); - INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified'); - INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL); - INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3); - INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL); -} {} - -# EVIDENCE-OF: R-59089-25828 The columns of the cartesian product -# dataset are, in order, all the columns of the left-hand dataset -# followed by all the columns of the right-hand dataset. -# -do_join_test e_select-1.4.1.1 { - SELECT * FROM x1 %JOIN% x2 LIMIT 1 -} [concat {24 converging} {-60.06 {} {}}] - -do_join_test e_select-1.4.1.2 { - SELECT * FROM x2 %JOIN% x1 LIMIT 1 -} [concat {-60.06 {} {}} {24 converging}] - -do_join_test e_select-1.4.1.3 { - SELECT * FROM x3 %JOIN% x2 LIMIT 1 -} [concat {-39.24 {} encompass -1} {-60.06 {} {}}] - -do_join_test e_select-1.4.1.4 { - SELECT * FROM x2 %JOIN% x3 LIMIT 1 -} [concat {-60.06 {} {}} {-39.24 {} encompass -1}] - -# EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product -# dataset formed by combining each unique combination of a row from the -# left-hand and right-hand datasets. -# -do_join_test e_select-1.4.2.1 { - SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f -} [list -60.06 {} {} -39.24 {} encompass -1 \ - -60.06 {} {} alerting {} -93.79 {} \ - -60.06 {} {} coldest -96 dramatists 82.3 \ - -60.06 {} {} conducting -87.24 37.56 {} \ - -60.06 {} {} presenting 51 reformation dignified \ - -58 {} 1.21 -39.24 {} encompass -1 \ - -58 {} 1.21 alerting {} -93.79 {} \ - -58 {} 1.21 coldest -96 dramatists 82.3 \ - -58 {} 1.21 conducting -87.24 37.56 {} \ - -58 {} 1.21 presenting 51 reformation dignified \ -] -# TODO: Come back and add a few more like the above. - -# EVIDENCE-OF: R-20659-43267 In other words, if the left-hand dataset -# consists of Nlhs rows of Mlhs columns, and the right-hand dataset of -# Nrhs rows of Mrhs columns, then the cartesian product is a dataset of -# Nlhs.Nrhs rows, each containing Mlhs+Mrhs columns. -# -# x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3) -do_join_test e_select-1.4.3.1 { - SELECT count(*) FROM x1 %JOIN% x2 -} [expr 3*2] -do_test e_select-1.4.3.2 { - expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6} -} [expr 2+3] - -# x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4) -do_join_test e_select-1.4.3.3 { - SELECT count(*) FROM x2 %JOIN% x3 -} [expr 2*5] -do_test e_select-1.4.3.4 { - expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10} -} [expr 3+4] - -# x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2) -do_join_test e_select-1.4.3.5 { - SELECT count(*) FROM x3 %JOIN% x1 -} [expr 5*3] -do_test e_select-1.4.3.6 { - expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15} -} [expr 4+2] - -# x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4) -do_join_test e_select-1.4.3.7 { - SELECT count(*) FROM x3 %JOIN% x3 -} [expr 5*5] -do_test e_select-1.4.3.8 { - expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25} -} [expr 4+4] - -# Some extra cartesian product tests using tables t1 and t2. -# -do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2 -do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 - -do_select_tests e_select-1.4.5 [list \ - 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ - 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ - 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ - 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ -] - - -# EVIDENCE-OF: R-22775-56496 If there is an ON clause specified, then -# the ON expression is evaluated for each row of the cartesian product -# as a boolean expression. All rows for which the expression evaluates -# to false are excluded from the dataset. -# -foreach {tn select res} [list \ - 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ - 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ - 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ - 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ - 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ - 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ - 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ - 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ - \ - 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ - {one I two II three III} \ - 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ - {one I one II one III} \ - 11 { SELECT t1.b, t2.b - FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ - {two I two II two III three I three II three III} \ -] { - do_join_test e_select-1.3.$tn $select $res -} - -# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as -# part of the join-constraint, then each of the column names specified -# must exist in the datasets to both the left and right of the join-op. -# -do_select_tests e_select-1.4 -error { - cannot join using column %s - column not present in both tables -} { - 1 { SELECT * FROM t1, t3 USING (b) } "b" - 2 { SELECT * FROM t3, t1 USING (c) } "c" - 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" -} - -# EVIDENCE-OF: R-55987-04584 For each pair of namesake columns, the -# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian -# product as a boolean expression. All rows for which one or more of the -# expressions evaluates to false are excluded from the result set. -# -do_select_tests e_select-1.5 { - 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} - 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} -} - -# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a -# USING clause, the normal rules for handling affinities, collation -# sequences and NULL values in comparisons apply. -# -# EVIDENCE-OF: R-35466-18578 The column from the dataset on the -# left-hand side of the join operator is considered to be on the -# left-hand side of the comparison operator (=) for the purposes of -# collation sequence and affinity precedence. -# -do_execsql_test e_select-1.6.0 { - CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); - INSERT INTO t5 VALUES('AA', 'cc'); - INSERT INTO t5 VALUES('BB', 'dd'); - INSERT INTO t5 VALUES(NULL, NULL); - CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); - INSERT INTO t6 VALUES('aa', 'cc'); - INSERT INTO t6 VALUES('bb', 'DD'); - INSERT INTO t6 VALUES(NULL, NULL); -} {} -foreach {tn select res} { - 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} - 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} - 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } - {aa cc cc bb DD dd} - 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} - 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} -} { - do_join_test e_select-1.6.$tn $select $res -} - -# EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a -# USING clause, the column from the right-hand dataset is omitted from -# the joined dataset. -# -# EVIDENCE-OF: R-56132-15700 This is the only difference between a USING -# clause and its equivalent ON constraint. -# -foreach {tn select res} { - 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } - {a one I b two II c three III} - 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } - {a one a I b two b II c three c III} - - 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } - {a 1 {} b 2 2} - 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } - {a 1 a {} b 2 b 2} - - 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} - 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} - - 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x - %JOIN% t5 USING (a) } - {aa cc cc bb DD dd} - 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x - %JOIN% t5 ON (x.a=t5.a) } - {aa cc AA cc bb DD BB dd} -} { - do_join_test e_select-1.7.$tn $select $res -} - -# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT -# OUTER JOIN", then after the ON or USING filtering clauses have been -# applied, an extra row is added to the output for each row in the -# original left-hand input dataset that corresponds to no rows at all in -# the composite dataset (if any). -# -do_execsql_test e_select-1.8.0 { - CREATE TABLE t7(a, b, c); - CREATE TABLE t8(a, d, e); - - INSERT INTO t7 VALUES('x', 'ex', 24); - INSERT INTO t7 VALUES('y', 'why', 25); - - INSERT INTO t8 VALUES('x', 'abc', 24); - INSERT INTO t8 VALUES('z', 'ghi', 26); -} {} - -do_select_tests e_select-1.8 { - 1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1} - 1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2} - 2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1} - 2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2} -} - - -# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the -# columns that would normally contain values copied from the right-hand -# input dataset. -# -do_select_tests e_select-1.9 { - 1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24} - 1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" - {x ex 24 x abc 24 y why 25 {} {} {}} - 2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} - 2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} -} - -# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of -# the join-ops, then an implicit USING clause is added to the -# join-constraints. The implicit USING clause contains each of the -# column names that appear in both the left and right-hand input -# datasets. -# -do_select_tests e_select-1-10 { - 1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} - 1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24} - - 2a "SELECT * FROM t8 JOIN t7 USING (a)" {x abc 24 ex 24} - 2b "SELECT * FROM t8 NATURAL JOIN t7" {x abc 24 ex 24} - - 3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} - 3b "SELECT * FROM t7 NATURAL LEFT JOIN t8" {x ex 24 abc 24 y why 25 {} {}} - - 4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)" {x abc 24 ex 24 z ghi 26 {} {}} - 4b "SELECT * FROM t8 NATURAL LEFT JOIN t7" {x abc 24 ex 24 z ghi 26 {} {}} - - 5a "SELECT * FROM t3 JOIN t4 USING (a,c)" {b 2} - 5b "SELECT * FROM t3 NATURAL JOIN t4" {b 2} - - 6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2} - 6b "SELECT * FROM t3 NATURAL LEFT JOIN t4" {a 1 b 2} -} - -# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets -# feature no common column names, then the NATURAL keyword has no effect -# on the results of the join. -# -do_execsql_test e_select-1.11.0 { - CREATE TABLE t10(x, y); - INSERT INTO t10 VALUES(1, 'true'); - INSERT INTO t10 VALUES(0, 'false'); -} {} -do_select_tests e_select-1-11 { - 1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} - 1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} -} - -# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a -# join that specifies the NATURAL keyword. -# -foreach {tn sql} { - 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} - 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} - 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} -} { - do_catchsql_test e_select-1.12.$tn " - $sql - " {1 {a NATURAL join may not have an ON or USING clause}} -} - -#------------------------------------------------------------------------- -# The next block of tests - e_select-3.* - concentrate on verifying -# statements made regarding WHERE clause processing. -# -drop_all_tables -do_execsql_test e_select-3.0 { - CREATE TABLE x1(k, x, y, z); - INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43); - INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81); - INSERT INTO x1 VALUES(3, -22, -27.57, NULL); - INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky'); - INSERT INTO x1 VALUES(5, NULL, 96.28, NULL); - INSERT INTO x1 VALUES(6, 0, 1, 2); - - CREATE TABLE x2(k, x, y2); - INSERT INTO x2 VALUES(1, 50, X'B82838'); - INSERT INTO x2 VALUES(5, 84.79, 65.88); - INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393'); - INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized'); -} {} - -# EVIDENCE-OF: R-06999-14330 If a WHERE clause is specified, the WHERE -# expression is evaluated for each row in the input data as a boolean -# expression. All rows for which the WHERE clause expression evaluates -# to false are excluded from the dataset before continuing. -# -do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3} -do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6} -do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6} -do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6} -do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5} -do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6} - -do_execsql_test e_select-3.2.1a { - SELECT k FROM x1 LEFT JOIN x2 USING(k) -} {1 2 3 4 5 6} -do_execsql_test e_select-3.2.1b { - SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k -} {1 3 5} -do_execsql_test e_select-3.2.2 { - SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL -} {2 4 6} - -do_execsql_test e_select-3.2.3 { - SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k -} {3} -do_execsql_test e_select-3.2.4 { - SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 -} {} - -#------------------------------------------------------------------------- -# Tests below this point are focused on verifying the testable statements -# related to caculating the result rows of a simple SELECT statement. -# - -drop_all_tables -do_execsql_test e_select-4.0 { - CREATE TABLE z1(a, b, c); - CREATE TABLE z2(d, e); - CREATE TABLE z3(a, b); - - INSERT INTO z1 VALUES(51.65, -59.58, 'belfries'); - INSERT INTO z1 VALUES(-5, NULL, 75); - INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters'); - INSERT INTO z1 VALUES(NULL, 67, 'quartets'); - INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen'); - INSERT INTO z1 VALUES(63, 'born', -26); - - INSERT INTO z2 VALUES(NULL, 21); - INSERT INTO z2 VALUES(36, 6); - - INSERT INTO z3 VALUES('subsistence', 'gauze'); - INSERT INTO z3 VALUES(49.17, -67); -} {} - -# EVIDENCE-OF: R-36327-17224 If a result expression is the special -# expression "*" then all columns in the input data are substituted for -# that one expression. -# -# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table -# or subquery in the FROM clause followed by ".*" then all columns from -# the named table or subquery are substituted for the single expression. -# -do_select_tests e_select-4.1 { - 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} - 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} - 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} - 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} - 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} - - 6 "SELECT count(*), * FROM z1" {6 63 born -26} - 7 "SELECT max(a), * FROM z1" {63 63 born -26} - 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} - - 9 "SELECT *,* FROM z1,z2 LIMIT 1" { - 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 - } - 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { - 51.65 -59.58 belfries 51.65 -59.58 belfries - } -} - -# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*" -# expression in any context other than than a result expression list. -# -# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or -# "alias.*" expression in a simple SELECT query that does not have a -# FROM clause. -# -foreach {tn select err} { - 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error} - 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error} - 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error} - 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error} - - 2.1 "SELECT *" {no tables specified} - 2.2 "SELECT * WHERE 1" {no tables specified} - 2.3 "SELECT * WHERE 0" {no tables specified} - 2.4 "SELECT count(*), *" {no tables specified} -} { - do_catchsql_test e_select-4.2.$tn $select [list 1 $err] -} - -# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned -# by a simple SELECT statement is equal to the number of expressions in -# the result expression list after substitution of * and alias.* -# expressions. -# -foreach {tn select nCol} { - 1 "SELECT * FROM z1" 3 - 2 "SELECT * FROM z1 NATURAL JOIN z3" 3 - 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3 - 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2 - 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5 - 6 "SELECT 1, 2, z1.* FROM z1" 5 - 7 "SELECT a, *, b, c FROM z1" 6 -} { - set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] - do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol - sqlite3_finalize $::stmt -} - - - -# In lang_select.html, a non-aggregate query is defined as any simple SELECT -# that has no GROUP BY clause and no aggregate expressions in the result -# expression list. Other queries are aggregate queries. Test cases -# e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of -# simple SELECT that is different for aggregate and non-aggregate queries -# verify (in a way) that these definitions are consistent: -# -# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate -# query if it contains either a GROUP BY clause or one or more aggregate -# functions in the result-set. -# -# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no -# aggregate functions or a GROUP BY clause, it is a non-aggregate query. -# - -# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate -# query, then each expression in the result expression list is evaluated -# for each row in the dataset filtered by the WHERE clause. -# -do_select_tests e_select-4.4 { - 1 "SELECT a, b FROM z1" - {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born} - - 2 "SELECT a IS NULL, b+1, * FROM z1" { - 0 -58.58 51.65 -59.58 belfries - 0 {} -5 {} 75 - 0 -22.18 -2.2 -23.18 suiters - 1 68 {} 67 quartets - 0 -31.3 -1.04 -32.3 aspen - 0 1 63 born -26 - } - - 3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366} -} - - -# Test cases e_select-4.5.* and e_select-4.6.* together show that: -# -# EVIDENCE-OF: R-51988-01124 The single row of result-set data created -# by evaluating the aggregate and non-aggregate expressions in the -# result-set forms the result of an aggregate query without a GROUP BY -# clause. -# - -# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate -# query without a GROUP BY clause, then each aggregate expression in the -# result-set is evaluated once across the entire dataset. -# -do_select_tests e_select-4.5 { - 1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} - 2 "SELECT count(*), max(1)" {1 1} - - 3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} - 4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} - 5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} -} - -# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the -# result-set is evaluated once for an arbitrarily selected row of the -# dataset. -# -# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used -# for each non-aggregate expression. -# -# Note: The results of many of the queries in this block of tests are -# technically undefined, as the documentation does not specify which row -# SQLite will arbitrarily select to use for the evaluation of the -# non-aggregate expressions. -# -drop_all_tables -do_execsql_test e_select-4.6.0 { - CREATE TABLE a1(one PRIMARY KEY, two); - INSERT INTO a1 VALUES(1, 1); - INSERT INTO a1 VALUES(2, 3); - INSERT INTO a1 VALUES(3, 6); - INSERT INTO a1 VALUES(4, 10); - - CREATE TABLE a2(one PRIMARY KEY, three); - INSERT INTO a2 VALUES(1, 1); - INSERT INTO a2 VALUES(3, 2); - INSERT INTO a2 VALUES(6, 3); - INSERT INTO a2 VALUES(10, 4); -} {} -do_select_tests e_select-4.6 { - 1 "SELECT one, two, count(*) FROM a1" {4 10 4} - 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} - 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} - 4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} - 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} - 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} - 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} -} - -# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then -# each non-aggregate expression is evaluated against a row consisting -# entirely of NULL values. -# -do_select_tests e_select-4.7 { - 1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} - 2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} - 3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { - 1 1 1 - } -} - -# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY -# clause always returns exactly one row of data, even if there are zero -# rows of input data. -# -foreach {tn select} { - 8.1 "SELECT count(*) FROM a1" - 8.2 "SELECT count(*) FROM a1 WHERE 0" - 8.3 "SELECT count(*) FROM a1 WHERE 1" - 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1" - 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0" -} { - # Set $nRow to the number of rows returned by $select: - set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] - set nRow 0 - while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow } - set rc [sqlite3_finalize $::stmt] - - # Test that $nRow==1 and that statement execution was successful - # (rc==SQLITE_OK). - do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1} -} - -drop_all_tables -do_execsql_test e_select-4.9.0 { - CREATE TABLE b1(one PRIMARY KEY, two); - INSERT INTO b1 VALUES(1, 'o'); - INSERT INTO b1 VALUES(4, 'f'); - INSERT INTO b1 VALUES(3, 't'); - INSERT INTO b1 VALUES(2, 't'); - INSERT INTO b1 VALUES(5, 'f'); - INSERT INTO b1 VALUES(7, 's'); - INSERT INTO b1 VALUES(6, 's'); - - CREATE TABLE b2(x, y); - INSERT INTO b2 VALUES(NULL, 0); - INSERT INTO b2 VALUES(NULL, 1); - INSERT INTO b2 VALUES('xyz', 2); - INSERT INTO b2 VALUES('abc', 3); - INSERT INTO b2 VALUES('xyz', 4); - - CREATE TABLE b3(a COLLATE nocase, b COLLATE binary); - INSERT INTO b3 VALUES('abc', 'abc'); - INSERT INTO b3 VALUES('aBC', 'aBC'); - INSERT INTO b3 VALUES('Def', 'Def'); - INSERT INTO b3 VALUES('dEF', 'dEF'); -} {} - -# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate -# query with a GROUP BY clause, then each of the expressions specified -# as part of the GROUP BY clause is evaluated for each row of the -# dataset. Each row is then assigned to a "group" based on the results; -# rows for which the results of evaluating the GROUP BY expressions are -# the same are assigned to the same group. -# -# These tests also show that the following is not untrue: -# -# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do -# not have to be expressions that appear in the result. -# -do_select_tests e_select-4.9 { - 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { - /#,# f 1 o #,# s #,# t/ - } - 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { - 1,2,3,4 10 5,6,7 18 - } - 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { - 4 1,5 2,6 3,7 - } - 4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { - 4,3,5,7,6 1,2 - } -} - -# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL -# values are considered equal. -# -do_select_tests e_select-4.10 { - 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} - 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} -} - -# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation -# sequence with which to compare text values apply when evaluating -# expressions in a GROUP BY clause. -# -do_select_tests e_select-4.11 { - 1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} - 2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} - 3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} - 4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} - 5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} - 6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} -} - -# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may -# not be aggregate expressions. -# -foreach {tn select} { - 12.1 "SELECT * FROM b3 GROUP BY count(*)" - 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)" - 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)" -} { - set res {1 {aggregate functions are not allowed in the GROUP BY clause}} - do_catchsql_test e_select-4.$tn $select $res -} - -# EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is -# evaluated once for each group of rows as a boolean expression. If the -# result of evaluating the HAVING clause is false, the group is -# discarded. -# -# This requirement is tested by all e_select-4.13.* tests. -# -# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate -# expression, it is evaluated across all rows in the group. -# -# Tested by e_select-4.13.1.* -# -# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate -# expression, it is evaluated with respect to an arbitrarily selected -# row from the group. -# -# Tested by e_select-4.13.2.* -# -# Tests in this block also show that this is not untrue: -# -# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values, -# even aggregate functions, that are not in the result. -# -do_execsql_test e_select-4.13.0 { - CREATE TABLE c1(up, down); - INSERT INTO c1 VALUES('x', 1); - INSERT INTO c1 VALUES('x', 2); - INSERT INTO c1 VALUES('x', 4); - INSERT INTO c1 VALUES('x', 8); - INSERT INTO c1 VALUES('y', 16); - INSERT INTO c1 VALUES('y', 32); - - CREATE TABLE c2(i, j); - INSERT INTO c2 VALUES(1, 0); - INSERT INTO c2 VALUES(2, 1); - INSERT INTO c2 VALUES(3, 3); - INSERT INTO c2 VALUES(4, 6); - INSERT INTO c2 VALUES(5, 10); - INSERT INTO c2 VALUES(6, 15); - INSERT INTO c2 VALUES(7, 21); - INSERT INTO c2 VALUES(8, 28); - INSERT INTO c2 VALUES(9, 36); - - CREATE TABLE c3(i PRIMARY KEY, k TEXT); - INSERT INTO c3 VALUES(1, 'hydrogen'); - INSERT INTO c3 VALUES(2, 'helium'); - INSERT INTO c3 VALUES(3, 'lithium'); - INSERT INTO c3 VALUES(4, 'beryllium'); - INSERT INTO c3 VALUES(5, 'boron'); - INSERT INTO c3 VALUES(94, 'plutonium'); -} {} - -do_select_tests e_select-4.13 { - 1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} - 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} - 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} - 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} - - 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} - 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} - - 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} -} - -# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then -# evaluated once for each group of rows. -# -# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate -# expression, it is evaluated across all rows in the group. -# -do_select_tests e_select-4.15 { - 1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} - 2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} - 3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} - 4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} - 5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" - {3 4.33 1 2.0} -} - -# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single -# arbitrarily chosen row from within the group. -# -# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate -# expression in the result-set, then all such expressions are evaluated -# for the same row. -# -do_select_tests e_select-4.15 { - 1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} - 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} - 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} - 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} - 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" - {2 5 boron 2 2 helium 1 3 lithium} -} - -# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows -# contributes a single row to the set of result rows. -# -# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the -# DISTINCT keyword, the number of rows returned by an aggregate query -# with a GROUP BY clause is the same as the number of groups of rows -# produced by applying the GROUP BY and HAVING clauses to the filtered -# input dataset. -# -do_select_tests e_select.4.16 -count { - 1 "SELECT i, j FROM c2 GROUP BY i%2" 2 - 2 "SELECT i, j FROM c2 GROUP BY i" 9 - 3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 -} - -#------------------------------------------------------------------------- -# The following tests attempt to verify statements made regarding the ALL -# and DISTINCT keywords. -# -drop_all_tables -do_execsql_test e_select-5.1.0 { - CREATE TABLE h1(a, b); - INSERT INTO h1 VALUES(1, 'one'); - INSERT INTO h1 VALUES(1, 'I'); - INSERT INTO h1 VALUES(1, 'i'); - INSERT INTO h1 VALUES(4, 'four'); - INSERT INTO h1 VALUES(4, 'IV'); - INSERT INTO h1 VALUES(4, 'iv'); - - CREATE TABLE h2(x COLLATE nocase); - INSERT INTO h2 VALUES('One'); - INSERT INTO h2 VALUES('Two'); - INSERT INTO h2 VALUES('Three'); - INSERT INTO h2 VALUES('Four'); - INSERT INTO h2 VALUES('one'); - INSERT INTO h2 VALUES('two'); - INSERT INTO h2 VALUES('three'); - INSERT INTO h2 VALUES('four'); - - CREATE TABLE h3(c, d); - INSERT INTO h3 VALUES(1, NULL); - INSERT INTO h3 VALUES(2, NULL); - INSERT INTO h3 VALUES(3, NULL); - INSERT INTO h3 VALUES(4, '2'); - INSERT INTO h3 VALUES(5, NULL); - INSERT INTO h3 VALUES(6, '2,3'); - INSERT INTO h3 VALUES(7, NULL); - INSERT INTO h3 VALUES(8, '2,4'); - INSERT INTO h3 VALUES(9, '3'); -} {} - -# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may -# follow the SELECT keyword in a simple SELECT statement. -# -do_select_tests e_select-5.1 { - 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} - 2 "SELECT DISTINCT a FROM h1" {1 4} -} - -# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then -# the entire set of result rows are returned by the SELECT. -# -# EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present, -# then the behavior is as if ALL were specified. -# -# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, -# then duplicate rows are removed from the set of result rows before it -# is returned. -# -# The three testable statements above are tested by e_select-5.2.*, -# 5.3.* and 5.4.* respectively. -# -do_select_tests e_select-5 { - 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} - 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} - - 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} - 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} - - 4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four} - 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four} -} - -# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate -# rows, two NULL values are considered to be equal. -# -do_select_tests e_select-5.5 { - 1 "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3} -} - -# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation -# sequence to compare text values with apply. -# -do_select_tests e_select-5.6 { - 1 "SELECT DISTINCT b FROM h1" {one I i four IV iv} - 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {one I four IV} - 3 "SELECT DISTINCT x FROM h2" {One Two Three Four} - 4 "SELECT DISTINCT x COLLATE binary FROM h2" { - One Two Three Four one two three four - } -} - -#------------------------------------------------------------------------- -# 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); -} {} -do_select_tests e_select-7.1 -error { - SELECTs to the left and right of %s do not have the same number of result columns -} { - 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} -} - -# 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); - CREATE TABLE q3(f REAL, g); - - 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 q3 VALUES('beauty', 2); - INSERT INTO q3 VALUES('beauty', 2); -} {} -do_select_tests e_select-7.4 { - 1 {SELECT a FROM q1 UNION ALL SELECT d FROM q2} - {16 legible beauty legible beauty -65.91 emanating} - - 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 -16.56} - - 4 {SELECT * FROM q2 UNION ALL SELECT * FROM q3} - {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2} -} - -# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as -# UNION ALL, except that duplicate rows are removed from the final -# result set. -# -do_select_tests e_select-7.5 { - 1 {SELECT a FROM q1 UNION SELECT d FROM q2} - {-65.91 16 beauty emanating legible} - - 2 {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1} - {16 -87.66 {} x legible 1} - - 3 {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} - {-16.56 3} - - 4 {SELECT * FROM q2 UNION SELECT * FROM q3} - {-65.91 4 beauty 2 emanating -16.56 legible 1} -} - -# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the -# intersection of the results of the left and right SELECTs. -# -do_select_tests e_select-7.6 { - 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} - 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} -} - -# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of -# rows returned by the left SELECT that are not also returned by the -# right-hand SELECT. -# -do_select_tests e_select-7.7 { - 1 {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16} - - 2 {SELECT * FROM q2 EXCEPT SELECT * FROM q3} - {-65.91 4 emanating -16.56 legible 1} -} - -# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results -# of INTERSECT and EXCEPT operators before the result set is returned. -# -do_select_tests e_select-7.8 { - 0 {SELECT * FROM q3} {beauty 2 beauty 2} - - 1 {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2} - 2 {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1} {beauty 2} -} - -# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate -# rows for the results of compound SELECT operators, NULL values are -# considered equal to other NULL values and distinct from all non-NULL -# values. -# -db nullvalue null -do_select_tests e_select-7.9 { - 1 {SELECT NULL UNION ALL SELECT NULL} {null null} - 2 {SELECT NULL UNION SELECT NULL} {null} - 3 {SELECT NULL INTERSECT SELECT NULL} {null} - 4 {SELECT NULL EXCEPT SELECT NULL} {} - - 5 {SELECT NULL UNION ALL SELECT 'ab'} {null ab} - 6 {SELECT NULL UNION SELECT 'ab'} {null ab} - 7 {SELECT NULL INTERSECT SELECT 'ab'} {} - 8 {SELECT NULL EXCEPT SELECT 'ab'} {null} - - 9 {SELECT NULL UNION ALL SELECT 0} {null 0} - 10 {SELECT NULL UNION SELECT 0} {null 0} - 11 {SELECT NULL INTERSECT SELECT 0} {} - 12 {SELECT NULL EXCEPT SELECT 0} {null} - - 13 {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2} - 14 {SELECT c FROM q1 UNION SELECT g FROM q3} {null -42.47 2} - 15 {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {} - 16 {SELECT c FROM q1 EXCEPT SELECT g FROM q3} {null -42.47} -} -db nullvalue {} - -# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two -# text values is determined as if the columns of the left and right-hand -# SELECT statements were the left and right-hand operands of the equals -# (=) operator, except that greater precedence is not assigned to a -# collation sequence specified with the postfix COLLATE operator. -# -drop_all_tables -do_execsql_test e_select-7.10.0 { - CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c); - INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); -} {} -do_select_tests e_select-7.10 { - 1 {SELECT 'abc' UNION SELECT 'ABC'} {ABC abc} - 2 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC} - 3 {SELECT 'abc' UNION SELECT 'ABC' COLLATE nocase} {ABC} - 4 {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc} - 5 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC} - - 6 {SELECT a FROM y1 UNION SELECT b FROM y1} {abc} - 7 {SELECT b FROM y1 UNION SELECT a FROM y1} {Abc abc} - 8 {SELECT a FROM y1 UNION SELECT c FROM y1} {aBC} - - 9 {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC} -} - -# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to -# any values when comparing rows as part of a compound SELECT. -# -drop_all_tables -do_execsql_test e_select-7.10.0 { - CREATE TABLE w1(a TEXT, b NUMBER); - CREATE TABLE w2(a, b TEXT); - - INSERT INTO w1 VALUES('1', 4.1); - INSERT INTO w2 VALUES(1, 4.1); -} {} - -do_select_tests e_select-7.11 { - 1 { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1} - 2 { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1} - 3 { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1} - 4 { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1} - - 5 { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {} - 6 { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {} - 7 { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {} - 8 { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {} - - 9 { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1} - 10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1} - 11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1} - 12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1} -} - - -# 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.12.1: Precedence of UNION vs. INTERSECT -# e_select-7.12.2: Precedence of UNION vs. UNION ALL -# e_select-7.12.3: Precedence of UNION vs. EXCEPT -# e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL -# e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT -# e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT -# e_select-7.12.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.12.5 test cases do not prove very much. -# -drop_all_tables -do_execsql_test e_select-7.12.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.12.$tn $select [list {*}$res] -} - - -#------------------------------------------------------------------------- -# ORDER BY clauses -# - -drop_all_tables -do_execsql_test e_select-8.1.0 { - CREATE TABLE d1(x, y, z); - - INSERT INTO d1 VALUES(1, 2, 3); - INSERT INTO d1 VALUES(2, 5, -1); - INSERT INTO d1 VALUES(1, 2, 8); - INSERT INTO d1 VALUES(1, 2, 7); - INSERT INTO d1 VALUES(2, 4, 93); - INSERT INTO d1 VALUES(1, 2, -20); - INSERT INTO d1 VALUES(1, 4, 93); - INSERT INTO d1 VALUES(1, 5, -1); - - CREATE TABLE d2(a, b); - INSERT INTO d2 VALUES('gently', 'failings'); - INSERT INTO d2 VALUES('commercials', 'bathrobe'); - INSERT INTO d2 VALUES('iterate', 'sexton'); - INSERT INTO d2 VALUES('babied', 'charitableness'); - INSERT INTO d2 VALUES('solemnness', 'annexed'); - INSERT INTO d2 VALUES('rejoicing', 'liabilities'); - INSERT INTO d2 VALUES('pragmatist', 'guarded'); - INSERT INTO d2 VALUES('barked', 'interrupted'); - INSERT INTO d2 VALUES('reemphasizes', 'reply'); - INSERT INTO d2 VALUES('lad', 'relenting'); -} {} - -# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results -# of evaluating the left-most expression in the ORDER BY list, then ties -# are broken by evaluating the second left-most expression and so on. -# -do_select_tests e_select-8.1 { - 1 "SELECT * FROM d1 ORDER BY x, y, z" { - 1 2 -20 1 2 3 1 2 7 1 2 8 - 1 4 93 1 5 -1 2 4 93 2 5 -1 - } -} - -# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally -# followed by one of the keywords ASC (smaller values are returned -# first) or DESC (larger values are returned first). -# -# Test cases e_select-8.2.* test the above. -# -# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows -# are sorted in ascending (smaller values first) order by default. -# -# Test cases e_select-8.3.* test the above. All 8.3 test cases are -# copies of 8.2 test cases with the explicit "ASC" removed. -# -do_select_tests e_select-8 { - 2.1 "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" { - 1 2 -20 1 2 3 1 2 7 1 2 8 - 1 4 93 1 5 -1 2 4 93 2 5 -1 - } - 2.2 "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" { - 2 5 -1 2 4 93 1 5 -1 1 4 93 - 1 2 8 1 2 7 1 2 3 1 2 -20 - } - 2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" { - 2 4 93 2 5 -1 1 2 8 1 2 7 - 1 2 3 1 2 -20 1 4 93 1 5 -1 - } - 2.4 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" { - 2 4 93 2 5 -1 1 2 -20 1 2 3 - 1 2 7 1 2 8 1 4 93 1 5 -1 - } - - 3.1 "SELECT * FROM d1 ORDER BY x, y, z" { - 1 2 -20 1 2 3 1 2 7 1 2 8 - 1 4 93 1 5 -1 2 4 93 2 5 -1 - } - 3.3 "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" { - 2 4 93 2 5 -1 1 2 8 1 2 7 - 1 2 3 1 2 -20 1 4 93 1 5 -1 - } - 3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" { - 2 4 93 2 5 -1 1 2 -20 1 2 3 - 1 2 7 1 2 8 1 4 93 1 5 -1 - } -} - -# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant -# integer K then the expression is considered an alias for the K-th -# column of the result set (columns are numbered from left to right -# starting with 1). -# -do_select_tests e_select-8.4 { - 1 "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" { - 1 2 -20 1 2 3 1 2 7 1 2 8 - 1 4 93 1 5 -1 2 4 93 2 5 -1 - } - 2 "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" { - 2 5 -1 2 4 93 1 5 -1 1 4 93 - 1 2 8 1 2 7 1 2 3 1 2 -20 - } - 3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" { - 2 4 93 2 5 -1 1 2 8 1 2 7 - 1 2 3 1 2 -20 1 4 93 1 5 -1 - } - 4 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" { - 2 4 93 2 5 -1 1 2 -20 1 2 3 - 1 2 7 1 2 8 1 4 93 1 5 -1 - } - 5 "SELECT * FROM d1 ORDER BY 1, 2, 3" { - 1 2 -20 1 2 3 1 2 7 1 2 8 - 1 4 93 1 5 -1 2 4 93 2 5 -1 - } - 6 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" { - 2 4 93 2 5 -1 1 2 8 1 2 7 - 1 2 3 1 2 -20 1 4 93 1 5 -1 - } - 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { - 2 4 93 2 5 -1 1 2 -20 1 2 3 - 1 2 7 1 2 8 1 4 93 1 5 -1 - } - 8 "SELECT z, x FROM d1 ORDER BY 2" { - /# 1 # 1 # 1 # 1 - # 1 # 1 # 2 # 2/ - } - 9 "SELECT z, x FROM d1 ORDER BY 1" { - /-20 1 -1 # -1 # 3 1 - 7 1 8 1 93 # 93 #/ - } -} - -# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier -# that corresponds to the alias of one of the output columns, then the -# expression is considered an alias for that column. -# -do_select_tests e_select-8.5 { - 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { - -19 0 0 4 8 9 94 94 - } - 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { - 94 94 9 8 4 0 0 -19 - } - 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { - /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ - } - 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { - /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ - } -} - -# EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is -# any other expression, it is evaluated and the returned value used to -# order the output rows. -# -# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, -# then an ORDER BY may contain any arbitrary expressions. -# -do_select_tests e_select-8.6 { - 1 "SELECT * FROM d1 ORDER BY x+y+z" { - 1 2 -20 1 5 -1 1 2 3 2 5 -1 - 1 2 7 1 2 8 1 4 93 2 4 93 - } - 2 "SELECT * FROM d1 ORDER BY x*z" { - 1 2 -20 2 5 -1 1 5 -1 1 2 3 - 1 2 7 1 2 8 1 4 93 2 4 93 - } - 3 "SELECT * FROM d1 ORDER BY y*z" { - 1 2 -20 2 5 -1 1 5 -1 1 2 3 - 1 2 7 1 2 8 2 4 93 1 4 93 - } -} - -# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound -# SELECT, then ORDER BY expressions that are not aliases to output -# columns must be exactly the same as an expression used as an output -# column. -# -do_select_tests e_select-8.7.1 -error { - %s ORDER BY term does not match any column in the result set -} { - 1 "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" 1st - 2 "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd -} - -do_select_tests e_select-8.7.2 { - 1 "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" { - -20 -2 -1 3 7 8 93 186 babied barked commercials gently - iterate lad pragmatist reemphasizes rejoicing solemnness - } - 2 "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" { - 1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 - babied charitableness barked interrupted commercials bathrobe gently - failings iterate sexton lad relenting pragmatist guarded reemphasizes reply - rejoicing liabilities solemnness annexed - } -} - -do_execsql_test e_select-8.8.0 { - CREATE TABLE d3(a); - INSERT INTO d3 VALUES('text'); - INSERT INTO d3 VALUES(14.1); - INSERT INTO d3 VALUES(13); - INSERT INTO d3 VALUES(X'78787878'); - INSERT INTO d3 VALUES(15); - INSERT INTO d3 VALUES(12.9); - INSERT INTO d3 VALUES(null); - - CREATE TABLE d4(x COLLATE nocase); - INSERT INTO d4 VALUES('abc'); - INSERT INTO d4 VALUES('ghi'); - INSERT INTO d4 VALUES('DEF'); - INSERT INTO d4 VALUES('JKL'); -} {} - -# EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values -# are compared in the same way as for comparison expressions. -# -# The following tests verify that values of different types are sorted -# correctly, and that mixed real and integer values are compared properly. -# -do_execsql_test e_select-8.8.1 { - SELECT a FROM d3 ORDER BY a -} {{} 12.9 13 14.1 15 text xxxx} -do_execsql_test e_select-8.8.2 { - SELECT a FROM d3 ORDER BY a DESC -} {xxxx text 15 14.1 13 12.9 {}} - - -# EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a -# collation sequence using the postfix COLLATE operator, then the -# specified collation sequence is used. -# -do_execsql_test e_select-8.9.1 { - SELECT x FROM d4 ORDER BY 1 COLLATE binary -} {DEF JKL abc ghi} -do_execsql_test e_select-8.9.2 { - SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase -} {abc DEF ghi JKL} - -# EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is -# an alias to an expression that has been assigned a collation sequence -# using the postfix COLLATE operator, then the collation sequence -# assigned to the aliased expression is used. -# -# In the test 8.10.2, the only result-column expression has no alias. So the -# ORDER BY expression is not a reference to it and therefore does not inherit -# the collation sequence. In test 8.10.3, "x" is the alias (as well as the -# column name), so the ORDER BY expression is interpreted as an alias and the -# collation sequence attached to the result column is used for sorting. -# -do_execsql_test e_select-8.10.1 { - SELECT x COLLATE binary FROM d4 ORDER BY 1 -} {DEF JKL abc ghi} -do_execsql_test e_select-8.10.2 { - SELECT x COLLATE binary FROM d4 ORDER BY x -} {abc DEF ghi JKL} -do_execsql_test e_select-8.10.3 { - SELECT x COLLATE binary AS x FROM d4 ORDER BY x -} {DEF JKL abc ghi} - -# EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a -# column or an alias of an expression that is a column, then the default -# collation sequence for the column is used. -# -do_execsql_test e_select-8.11.1 { - SELECT x AS y FROM d4 ORDER BY y -} {abc DEF ghi JKL} -do_execsql_test e_select-8.11.2 { - SELECT x||'' FROM d4 ORDER BY x -} {abc DEF ghi JKL} - -# EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is -# used. -# -do_execsql_test e_select-8.12.1 { - SELECT x FROM d4 ORDER BY x||'' -} {DEF JKL abc ghi} - -# EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer -# alias, then SQLite searches the left-most SELECT in the compound for a -# result column that matches either the second or third rules above. If -# a match is found, the search stops and the expression is handled as an -# alias for the result column that it has been matched against. -# Otherwise, the next SELECT to the right is tried, and so on. -# -do_execsql_test e_select-8.13.0 { - CREATE TABLE d5(a, b); - CREATE TABLE d6(c, d); - CREATE TABLE d7(e, f); - - INSERT INTO d5 VALUES(1, 'f'); - INSERT INTO d6 VALUES(2, 'e'); - INSERT INTO d7 VALUES(3, 'd'); - INSERT INTO d5 VALUES(4, 'c'); - INSERT INTO d6 VALUES(5, 'b'); - INSERT INTO d7 VALUES(6, 'a'); - - CREATE TABLE d8(x COLLATE nocase); - CREATE TABLE d9(y COLLATE nocase); - - INSERT INTO d8 VALUES('a'); - INSERT INTO d9 VALUES('B'); - INSERT INTO d8 VALUES('c'); - INSERT INTO d9 VALUES('D'); -} {} -do_select_tests e_select-8.13 { - 1 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 - ORDER BY a - } {1 2 3 4 5 6} - 2 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 - ORDER BY c - } {1 2 3 4 5 6} - 3 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 - ORDER BY e - } {1 2 3 4 5 6} - 4 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 - ORDER BY 1 - } {1 2 3 4 5 6} - - 5 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } - {f 1 c 4 4 c 1 f} - 6 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } - {f 1 c 4 4 c 1 f} - - 7 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } - {1 f 4 c c 4 f 1} - 8 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } - {1 f 4 c c 4 f 1} - - 9 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } - {f 2 c 5 4 c 1 f} - 10 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } - {f 2 c 5 4 c 1 f} - - 11 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } - {2 f 5 c c 5 f 2} - 12 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } - {2 f 5 c c 5 f 2} -} - -# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in -# the result columns of any constituent SELECT, it is an error. -# -do_select_tests e_select-8.14 -error { - %s ORDER BY term does not match any column in the result set -} { - 1 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 } 1st - 2 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 } 2nd - 3 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' } 1st - 4 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah } 1st - 5 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d } 3rd - 6 { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b } 4th -} - -# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is -# processed separately and may be matched against result columns from -# different SELECT statements in the compound. -# -do_select_tests e_select-8.15 { - 1 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d } - {1 e 1 f 4 b 4 c} - 2 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b } - {1 e 1 f 4 b 4 c} - 3 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 } - {1 e 1 f 4 b 4 c} -} - - -#------------------------------------------------------------------------- -# Tests related to statements made about the LIMIT/OFFSET clause. -# -do_execsql_test e_select-9.0 { - CREATE TABLE f1(a, b); - INSERT INTO f1 VALUES(26, 'z'); - INSERT INTO f1 VALUES(25, 'y'); - INSERT INTO f1 VALUES(24, 'x'); - INSERT INTO f1 VALUES(23, 'w'); - INSERT INTO f1 VALUES(22, 'v'); - INSERT INTO f1 VALUES(21, 'u'); - INSERT INTO f1 VALUES(20, 't'); - INSERT INTO f1 VALUES(19, 's'); - INSERT INTO f1 VALUES(18, 'r'); - INSERT INTO f1 VALUES(17, 'q'); - INSERT INTO f1 VALUES(16, 'p'); - INSERT INTO f1 VALUES(15, 'o'); - INSERT INTO f1 VALUES(14, 'n'); - INSERT INTO f1 VALUES(13, 'm'); - INSERT INTO f1 VALUES(12, 'l'); - INSERT INTO f1 VALUES(11, 'k'); - INSERT INTO f1 VALUES(10, 'j'); - INSERT INTO f1 VALUES(9, 'i'); - INSERT INTO f1 VALUES(8, 'h'); - INSERT INTO f1 VALUES(7, 'g'); - INSERT INTO f1 VALUES(6, 'f'); - INSERT INTO f1 VALUES(5, 'e'); - INSERT INTO f1 VALUES(4, 'd'); - INSERT INTO f1 VALUES(3, 'c'); - INSERT INTO f1 VALUES(2, 'b'); - INSERT INTO f1 VALUES(1, 'a'); -} {} - -# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the -# LIMIT clause, so long as it evaluates to an integer or a value that -# can be losslessly converted to an integer. -# -do_select_tests e_select-9.1 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e} - 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e} - 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } - {a b c d e} - 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e} - 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e} -} - -# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value -# or any other value that cannot be losslessly converted to an integer, -# an error is returned. -# - -do_select_tests e_select-9.2 -error "datatype mismatch" { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {} - 2 { SELECT b FROM f1 ORDER BY a LIMIT NULL } {} - 3 { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {} - 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {} - 5 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {} -} - -# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a -# negative value, then there is no upper bound on the number of rows -# returned. -# -do_select_tests e_select-9.4 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT -1 } - {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} - 2 { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } - {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} - 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 } - {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} -} - -# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N -# rows of its result set only, where N is the value that the LIMIT -# expression evaluates to. -# -do_select_tests e_select-9.5 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 0 } {} - 2 { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w} - 3 { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s} - 4 { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o} -} - -# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return -# less than N rows without a LIMIT clause, then the entire result set is -# returned. -# -do_select_tests e_select-9.6 { - 1 { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z} - 2 { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5} -} - - -# EVIDENCE-OF: R-24188-24349 The expression attached to the optional -# OFFSET clause that may follow a LIMIT clause must also evaluate to an -# integer, or a value that can be losslessly converted to an integer. -# -foreach {tn select} { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } - 2 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } - 3 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } - 4 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } - 5 { SELECT b FROM f1 ORDER BY a - LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) - } -} { - do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}} -} - -# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then -# the first M rows are omitted from the result set returned by the -# SELECT statement and the next N rows are returned, where M and N are -# the values that the OFFSET and LIMIT clauses evaluate to, -# respectively. -# -do_select_tests e_select-9.8 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o} - 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o} - 3 { SELECT b FROM f1 ORDER BY a - LIMIT (SELECT a FROM f1 WHERE b='j') - OFFSET (SELECT a FROM f1 WHERE b='b') - } {c d e f g h i j k l} - 4 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h} - 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e} - 6 { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {} - 7 { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r} -} - -# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than -# M+N rows if it did not have a LIMIT clause, then the first M rows are -# skipped and the remaining rows (if any) are returned. -# -do_select_tests e_select-9.9 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z} - 2 { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1} -} - - -# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a -# negative value, the results are the same as if it had evaluated to -# zero. -# -do_select_tests e_select-9.10 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e} - 2 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e} - 3 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 } {a b c d e} -} - -# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the -# LIMIT clause may specify two scalar expressions separated by a comma. -# -# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used -# as the OFFSET expression and the second as the LIMIT expression. -# -do_select_tests e_select-9.11 { - 1 { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o} - 2 { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o} - 3 { SELECT b FROM f1 ORDER BY a - LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') - } {c d e f g h i j k l} - 4 { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h} - 5 { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e} - 6 { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {} - 7 { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r} - - 8 { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z} - 9 { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1} - - 10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e} - 11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e} - 12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e} -} - -finish_test |