# 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 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. # # EVIDENCE-OF: R-18428-22111 -- 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}} #------------------------------------------------------------------------- # 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_execsql_test e_select-1.1.1 { SELECT 'abc' } {abc} do_execsql_test e_select-1.1.2 { SELECT 'abc' WHERE NULL } {} do_execsql_test e_select-1.1.3 { SELECT NULL } {{}} do_execsql_test e_select-1.1.4 { SELECT count(*) } {1} do_execsql_test e_select-1.1.5 { SELECT count(*) WHERE 0 } {0} do_execsql_test e_select-1.1.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.1 { 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_execsql_test e_select-1.2.2 { SELECT quote(x), quote(y) FROM xx } [list \ 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' \ NULL -16.87 \ -17.89 'linguistically' \ ] do_execsql_test e_select-1.2.3 { SELECT count(*), count(x), count(y) FROM xx } {3 2 3} do_execsql_test e_select-1.2.4 { 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_execsql_test e_select-1.3.1 { SELECT * FROM t1, t2, t3 } [list 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-07544-24155 The "CROSS JOIN" join operator produces the # same data 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 } [list -60.06 {} {} -39.24 {} encompass -1 \ -60.06 {} {} presenting 51 reformation dignified \ -60.06 {} {} conducting -87.24 37.56 {} \ -60.06 {} {} coldest -96 dramatists 82.3 \ -60.06 {} {} alerting {} -93.79 {} \ -58 {} 1.21 -39.24 {} encompass -1 \ -58 {} 1.21 presenting 51 reformation dignified \ -58 {} 1.21 conducting -87.24 37.56 {} \ -58 {} 1.21 coldest -96 dramatists 82.3 \ -58 {} 1.21 alerting {} -93.79 {} \ ] # 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 foreach {tn select res} [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 \ ] { do_execsql_test e_select-1.4.5.$tn $select $res } # EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then # the ON expression is evaluated for each row of the cartesian product # and the result cast to a numeric value as if by a CAST expression. All # rows for which the expression evaluates to NULL or zero (integer value # 0 or real value 0.0) 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. # foreach {tn select col} { 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" } { set err "cannot join using column $col - column not present in both tables" do_catchsql_test e_select-1.4.$tn $select [list 1 $err] } # EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian # product and the result cast to a numeric value. All rows for which one # or more of the expressions evaluates to NULL or zero are excluded from # the result set. # foreach {tn select res} { 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} } { do_execsql_test e_select-1.5.$tn $select $res } # 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_execsql_test e_select-1.8.1a { SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a) } {1} do_execsql_test e_select-1.8.1b { SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) } {2} do_execsql_test e_select-1.8.2a { SELECT count(*) FROM t7 JOIN t8 USING (a) } {1} do_execsql_test e_select-1.8.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_execsql_test e_select-1.9.1a { SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a) } {x ex 24 x abc 24} do_execsql_test e_select-1.9.1b { SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) } {x ex 24 x abc 24 y why 25 {} {} {}} do_execsql_test e_select-1.9.2a { SELECT * FROM t7 JOIN t8 USING (a) } {x ex 24 abc 24} do_execsql_test e_select-1.9.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. # foreach {tn s1 s2 res} { 1 { SELECT * FROM t7 JOIN t8 USING (a) } { SELECT * FROM t7 NATURAL JOIN t8 } {x ex 24 abc 24} 2 { SELECT * FROM t8 JOIN t7 USING (a) } { SELECT * FROM t8 NATURAL JOIN t7 } {x abc 24 ex 24} 3 { SELECT * FROM t7 LEFT JOIN t8 USING (a) } { SELECT * FROM t7 NATURAL LEFT JOIN t8 } {x ex 24 abc 24 y why 25 {} {}} 4 { SELECT * FROM t8 LEFT JOIN t7 USING (a) } { SELECT * FROM t8 NATURAL LEFT JOIN t7 } {x abc 24 ex 24 z ghi 26 {} {}} 5 { SELECT * FROM t3 JOIN t4 USING (a,c) } { SELECT * FROM t3 NATURAL JOIN t4 } {b 2} 6 { SELECT * FROM t3 LEFT JOIN t4 USING (a,c) } { SELECT * FROM t3 NATURAL LEFT JOIN t4 } {a 1 b 2} } { do_execsql_test e_select-1.10.${tn}a $s1 $res do_execsql_test e_select-1.10.${tn}b $s2 $res } # 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'); } {} foreach {tn s1 s2 res} { 1 { SELECT a, x FROM t1 CROSS JOIN t10 } { SELECT a, x FROM t1 NATURAL CROSS JOIN t10 } {a 1 a 0 b 1 b 0 c 1 c 0} } { do_execsql_test e_select-1.11.${tn}a $s1 $res do_execsql_test e_select-1.11.${tn}b $s2 $res } # 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}} } #------------------------------------------------------------------------- # te_* commands: # # # te_read_sql DB SELECT-STATEMENT # te_read_tbl DB TABLENAME # # These two commands are used to read a dataset from the database. A dataset # consists of N rows of M named columns of values each, where each value has a # type (null, integer, real, text or blob) and a value within the types domain. # The tcl format for a "dataset" is a list of two elements: # # * A list of the column names. # * A list of data rows. Each row is itself a list, where each element is # the contents of a column of the row. Each of these is a list of two # elements, the type name and the actual value. # # For example, the contents of table [t1] as a dataset is: # # CREATE TABLE t1(a, b); # INSERT INTO t1 VALUES('abc', NULL); # INSERT INTO t1 VALUES(43.1, 22); # # {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}} # # The [te_read_tbl] command returns a dataset read from a table. The # [te_read_sql] returns the dataset that results from executing a SELECT # command. # # # te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE # te_join ?SWITCHES? LHS-DATASET RHS-DATASET # # This command joins the two datasets and returns the resulting dataset. If # there are no switches specified, then the results is the cartesian product # of the two inputs. The [te_tbljoin] command reads the left and right-hand # datasets from the specified tables. The [te_join] command is passed the # datasets directly. # # Optional switches are as follows: # # -on SCRIPT # -using COLUMN-LIST # -left # # The -on option specifies a tcl script that is executed for each row in the # cartesian product of the two datasets. The script has 4 arguments appended # to it, in the following order: # # * The list of column-names from the left-hand dataset. # * A single row from the left-hand dataset (one "data row" list as # described above. # * The list of column-names from the right-hand dataset. # * A single row from the right-hand dataset. # # The script must return a boolean value - true if the combination of rows # should be included in the output dataset, or false otherwise. # # The -using option specifies a list of the columns from the right-hand # dataset that should be omitted from the output dataset. # # If the -left option is present, the join is done LEFT JOIN style. # Specifically, an extra row is inserted if after the -on script is run there # exist rows in the left-hand dataset that have no corresponding rows in # the output. See the implementation for more specific comments. # # # te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args> # # The only supported switch is "-nocase". If it is present, then text values # are compared in a case-independent fashion. Otherwise, they are compared # as if using the SQLite BINARY collation sequence. # # # te_and ONSCRIPT1 ONSCRIPT2... # # # # te_read_tbl DB TABLENAME # te_read_sql DB SELECT-STATEMENT # # These two procs are used to extract datasets from the database, either # by reading the contents of a named table (te_read_tbl), or by executing # a SELECT statement (t3_read_sql). # # See the comment above, describing "te_* commands", for details of the # return values. # proc te_read_tbl {db tbl} { te_read_sql $db "SELECT * FROM '$tbl'" } proc te_read_sql {db sql} { set S [sqlite3_prepare_v2 $db $sql -1 DUMMY] set cols [list] for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { lappend cols [sqlite3_column_name $S $i] } set rows [list] while {[sqlite3_step $S] == "SQLITE_ROW"} { set r [list] for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]] } lappend rows $r } sqlite3_finalize $S return [list $cols $rows] } #------- # Usage: te_join ... # # Where a join-spec is an optional list of arguments as follows: # # ?-left? # ?-using colname-list? # ?-on on-expr-proc? # proc te_join {data1 data2 args} { set testproc "" set usinglist [list] set isleft 0 for {set i 0} {$i < [llength $args]} {incr i} { set a [lindex $args $i] switch -- $a { -on { set testproc [lindex $args [incr i]] } -using { set usinglist [lindex $args [incr i]] } -left { set isleft 1 } default { error "Unknown argument: $a" } } } set c1 [lindex $data1 0] set c2 [lindex $data2 0] set omitlist [list] set nullrowlist [list] set cret $c1 set cidx 0 foreach col $c2 { set idx [lsearch $usinglist $col] if {$idx>=0} {lappend omitlist $cidx} if {$idx<0} { lappend nullrowlist {NULL {}} lappend cret $col } incr cidx } set omitlist [lsort -integer -decreasing $omitlist] set rret [list] foreach r1 [lindex $data1 1] { set one 0 foreach r2 [lindex $data2 1] { set ok 1 if {$testproc != ""} { set ok [eval $testproc [list $c1 $r1 $c2 $r2]] } if {$ok} { set one 1 foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]} lappend rret [concat $r1 $r2] } } if {$isleft && $one==0} { lappend rret [concat $r1 $nullrowlist] } } list $cret $rret } proc te_tbljoin {db t1 t2 args} { te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args } proc te_apply_affinity {affinity typevar valvar} { upvar $typevar type upvar $valvar val switch -- $affinity { integer { if {[string is double $val]} { set type REAL } if {[string is wideinteger $val]} { set type INTEGER } if {$type == "REAL" && int($val)==$val} { set type INTEGER set val [expr {int($val)}] } } text { set type TEXT } none { } default { error "invalid affinity: $affinity" } } } #---------- # te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2 # proc te_equals {args} { if {[llength $args]<6} {error "invalid arguments to te_equals"} foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break set nocase 0 set affinity none for {set i 0} {$i < ([llength $args]-6)} {incr i} { set a [lindex $args $i] switch -- $a { -nocase { set nocase 1 } -affinity { set affinity [string tolower [lindex $args [incr i]]] } default { error "invalid arguments to te_equals" } } } set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }] set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }] set t1 [lindex $row1 $idx1 0] set t2 [lindex $row2 $idx2 0] set v1 [lindex $row1 $idx1 1] set v2 [lindex $row2 $idx2 1] te_apply_affinity $affinity t1 v1 te_apply_affinity $affinity t2 v2 if {$t1 == "NULL" || $t2 == "NULL"} { return 0 } if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] } if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] } set res [expr {$t1 == $t2 && [string equal $v1 $v2]}] return $res } proc te_false {args} { return 0 } proc te_true {args} { return 1 } proc te_and {args} { foreach a [lrange $args 0 end-4] { set res [eval $a [lrange $args end-3 end]] if {$res == 0} {return 0} } return 1 } proc te_dataset_eq {testname got expected} { uplevel #0 [list do_test $testname [list set {} $got] $expected] } proc te_dataset_eq_unordered {testname got expected} { lset got 1 [lsort [lindex $got 1]] lset expected 1 [lsort [lindex $expected 1]] te_dataset_eq $testname $got $expected } proc te_dataset_ne {testname got unexpected} { uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0] } proc te_dataset_ne_unordered {testname got unexpected} { lset got 1 [lsort [lindex $got 1]] lset unexpected 1 [lsort [lindex $unexpected 1]] te_dataset_ne $testname $got $unexpected } #------------------------------------------------------------------------- # proc test_join {tn sqljoin tbljoinargs} { set sql [te_read_sql db "SELECT * FROM $sqljoin"] set te [te_tbljoin db {*}$tbljoinargs] te_dataset_eq_unordered $tn $sql $te } drop_all_tables do_execsql_test e_select-2.0 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b); CREATE TABLE t3(b COLLATE nocase); INSERT INTO t1 VALUES(2, 'B'); INSERT INTO t1 VALUES(1, 'A'); INSERT INTO t1 VALUES(4, 'D'); INSERT INTO t1 VALUES(NULL, NULL); INSERT INTO t1 VALUES(3, NULL); INSERT INTO t2 VALUES(1, 'A'); INSERT INTO t2 VALUES(2, NULL); INSERT INTO t2 VALUES(5, 'E'); INSERT INTO t2 VALUES(NULL, NULL); INSERT INTO t2 VALUES(3, 'C'); INSERT INTO t3 VALUES('a'); INSERT INTO t3 VALUES('c'); INSERT INTO t3 VALUES('b'); } {} foreach {tn indexes} { e_select-2.1.1 { } e_select-2.1.2 { CREATE INDEX i1 ON t1(a) } e_select-2.1.3 { CREATE INDEX i1 ON t2(a) } e_select-2.1.4 { CREATE INDEX i1 ON t3(b) } } { catchsql { DROP INDEX i1 } catchsql { DROP INDEX i2 } catchsql { DROP INDEX i3 } execsql $indexes # 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. # # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER # JOIN", "JOIN" and "," join operators. # # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the # same data as the "INNER JOIN", "JOIN" and "," operators # test_join $tn.1.1 "t1, t2" {t1 t2} test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2} test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2} test_join $tn.1.4 "t1 JOIN t2" {t1 t2} test_join $tn.1.5 "t2, t3" {t2 t3} test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3} test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3} test_join $tn.1.8 "t2 JOIN t3" {t2 t3} test_join $tn.1.9 "t2, t2 AS x" {t2 t2} test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2} test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2} test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2} # EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then # the ON expression is evaluated for each row of the cartesian product # and the result cast to a numeric value as if by a CAST expression. All # rows for which the expression evaluates to NULL or zero (integer value # 0 or real value 0.0) are excluded from the dataset. # test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}} test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}} test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true} test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false} test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false} test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true} test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}} test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" { t1 t2 -left -using a -on {te_equals a a} } test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.6 "t1 NATURAL JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.7 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.8 "t1 NATURAL CROSS JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.9 "t1 NATURAL INNER JOIN t2" { t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.10 "t1 NATURAL LEFT JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.11 "t1 NATURAL LEFT OUTER JOIN t2" { t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.12 "t2 NATURAL JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.13 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.14 "t2 NATURAL CROSS JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.15 "t2 NATURAL INNER JOIN t1" { t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.16 "t2 NATURAL LEFT JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.17 "t2 NATURAL LEFT OUTER JOIN t1" { t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} } test_join $tn.18 "t1 LEFT JOIN t2 USING (b)" { t1 t2 -left -using b -on {te_equals b b} } test_join $tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}} test_join $tn.20 "t3 JOIN t1 USING(b)" { t3 t1 -using b -on {te_equals -nocase b b} } test_join $tn.21 "t1 NATURAL JOIN t3" { t1 t3 -using b -on {te_equals b b} } test_join $tn.22 "t3 NATURAL JOIN t1" { t3 t1 -using b -on {te_equals -nocase b b} } test_join $tn.23 "t1 NATURAL LEFT JOIN t3" { t1 t3 -left -using b -on {te_equals b b} } test_join $tn.24 "t3 NATURAL LEFT JOIN t1" { t3 t1 -left -using b -on {te_equals -nocase b b} } test_join $tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" { t1 t3 -left -on {te_equals -nocase b b} } test_join $tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" { t1 t3 -left -on {te_equals b b} } test_join $tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} } # EVIDENCE-OF: R-28760-53843 When more than two tables are joined # together as part of a FROM clause, the join operations are processed # in order from left to right. In other words, the FROM clause (A # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C). # # Tests 28a and 28b show that the statement above is true for this case. # Test 28c shows that if the parenthesis force a different order of # evaluation the result is different. Test 28d verifies that the result # of the query with the parenthesis forcing a different order of evaluation # is as calculated by the [te_*] procs. # set t3_natural_left_join_t2 [ te_tbljoin db t3 t2 -left -using {b} -on {te_equals -nocase b b} ] set t1 [te_read_tbl db t1] te_dataset_eq_unordered $tn.28a [ te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1" ] [te_join $t3_natural_left_join_t2 $t1 \ -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ ] te_dataset_eq_unordered $tn.28b [ te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" ] [te_join $t3_natural_left_join_t2 $t1 \ -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ ] te_dataset_ne_unordered $tn.28c [ te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" ] [ te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" ] set t2_natural_join_t1 [te_tbljoin db t2 t1 -using {a b} \ -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ ] set t3 [te_read_tbl db t3] te_dataset_eq_unordered $tn.28d [ te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" ] [te_join $t3 $t2_natural_join_t1 \ -left -using {b} -on {te_equals -nocase b b} \ ] } do_execsql_test e_select-2.2.0 { CREATE TABLE t4(x TEXT COLLATE nocase); CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary); INSERT INTO t4 VALUES('2.0'); INSERT INTO t4 VALUES('TWO'); INSERT INTO t5 VALUES(2, 'two'); } {} # EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source # following the FROM clause in a simple SELECT statement is handled as # if it was a table containing the data returned by executing the # sub-select statement. # # EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset # inherits the collation sequence and affinity of the corresponding # expression in the sub-select statement. # foreach {tn subselect select spec} { 1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss%" {t1 %ss%} 2 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" {t1 %ss% -on {te_equals 0 0}} 3 "SELECT * FROM t2" "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" {%ss% t1 -on {te_equals 0 0}} 4 "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3" {%ss% t3} 5 "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3" {%ss% t3 -using b -on {te_equals 1 0}} 6 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%" {t3 %ss% -using b -on {te_equals -nocase 0 1}} 7 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%" {t3 %ss% -left -using b -on {te_equals -nocase 0 1}} 8 "SELECT count(*) AS y FROM t4" "SELECT * FROM t5, %ss% USING (y)" {t5 %ss% -using y -on {te_equals -affinity text 0 0}} 9 "SELECT count(*) AS y FROM t4" "SELECT * FROM %ss%, t5 USING (y)" {%ss% t5 -using y -on {te_equals -affinity text 0 0}} 10 "SELECT x AS y FROM t4" "SELECT * FROM %ss% JOIN t5 USING (y)" {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}} 11 "SELECT x AS y FROM t4" "SELECT * FROM t5 JOIN %ss% USING (y)" {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}} 12 "SELECT y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}} 13 "SELECT y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}} 14 "SELECT +y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}} 15 "SELECT +y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}} } { # Create a temporary table named %ss% containing the data returned by # the sub-select. Then have the [te_tbljoin] proc use this table to # compute the expected results of the $select query. Drop the temporary # table before continuing. # execsql "CREATE TEMP TABLE '%ss%' AS $subselect" set te [eval te_tbljoin db $spec] execsql "DROP TABLE '%ss%'" # Check that the actual data returned by the $select query is the same # as the expected data calculated using [te_tbljoin] above. # te_dataset_eq_unordered e_select-2.2.1.$tn [ te_read_sql db [string map [list %ss% "($subselect)"] $select] ] $te } #------------------------------------------------------------------------- # 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-22873-49686 If a WHERE clause is specified, the WHERE # expression is evaluated for each row in the input data and the result # cast to a numeric value. All rows for which the WHERE clause # expression evaluates to a NULL value or to zero (integer value 0 or # real value 0.0) 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. # foreach {tn select res} { 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" {63 born -26 -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 } } { do_execsql_test e_select-4.1.$tn $select [list {*}$res] } # 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 } # 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. # # By other definitions in lang_select.html, a non-aggregate query is # any simple SELECT that has no GROUP BY clause and no aggregate expressions # in the result expression list. # do_execsql_test 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} do_execsql_test e_select-4.4.2 { SELECT a IS NULL, b+1, * FROM z1 } [list {*}{ 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 }] do_execsql_test e_select-4.4.3 { SELECT 32*32, d||e FROM z2 } {1024 {} 1024 366} # 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. # foreach {tn select res} { 5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} 5.2 "SELECT count(*), max(1)" {1 1} 5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} 5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} 5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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); } {} foreach {tn select res} { 6.1 "SELECT one, two, count(*) FROM a1" {4 10 4} 6.2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} 6.3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 6.4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} 6.5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 6.6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 6.7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 7.1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} 7.2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} 7.3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { 1 1 1 } } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 9.1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { 4,5 f 1 o 7,6 s 3,2 t } 9.2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 1,4,3,2 10 5,7,6 18 } 9.3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 4 1,5 2,6 3,7 } 9.4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { 4,3,5,7,6 1,2 } } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL # values are considered equal. # foreach {tn select res} { 10.1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} 10.2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" { 4 1 } } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 11.1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} 11.2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} 11.3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} 11.4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} 11.5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} 11.6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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-40359-04817 If a HAVING clause is specified, it is # evaluated once for each group of rows and cast to an integer value. If # the result of evaluating the HAVING clause is NULL or zero (integer # value 0), 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.* # 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'); } {} foreach {tn select res} { 13.1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} 13.1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 13.1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 13.1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} 13.2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} 13.2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} 13.2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 14.1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} 14.2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} 14.3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} 14.4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} 14.5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" {3 4.33 1 2.0} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 15.1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} 15.2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} 15.3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 15.4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 15.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} } { do_execsql_test e_select-4.$tn $select [list {*}$res] } # 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. # foreach {tn select nRow} { 16.1 "SELECT i, j FROM c2 GROUP BY i%2" 2 16.2 "SELECT i, j FROM c2 GROUP BY i" 9 16.3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 } { set rows 0 db eval $select {incr rows} do_test e_select-4.$tn [list set rows] $nRow } #------------------------------------------------------------------------- # 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_execsql_test e_select-5.1.1 { SELECT ALL a FROM h1 } {1 1 1 4 4 4} do_execsql_test e_select-5.1.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-47911-02086 If neither ALL or DISTINCT are present, # then the behaviour 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. # foreach {tn select res} { 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" {four one three two} 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one} } { do_execsql_test e_select-5.$tn $select [list {*}$res] } # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate # rows, two NULL values are considered to be equal. # do_execsql_test 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. # foreach {tn select res} { 6.1 "SELECT DISTINCT b FROM h1" {I IV four i iv one} 6.2 "SELECT DISTINCT b COLLATE nocase FROM h1" {four i iv one} 6.3 "SELECT DISTINCT x FROM h2" {four one three two} 6.4 "SELECT DISTINCT x COLLATE binary FROM h2" { Four One Three Two four one three two } } { 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); 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); } {} foreach {tn select res} { 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} } { do_execsql_test e_select-7.4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-7.5.$tn $select [list {*}$res] } # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the # intersection of the results of the left and right SELECTs. # foreach {tn select res} { 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} } { do_execsql_test e_select-7.6.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-7.7.$tn $select [list {*}$res] } # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results # of INTERSECT and EXCEPT operators before the result set is returned. # foreach {tn select res} { 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} } { do_execsql_test e_select-7.8.$tn $select [list {*}$res] } # 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 foreach {tn select res} { 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} } { do_execsql_test e_select-7.9.$tn $select [list {*}$res] } 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'); } {} foreach {tn select res} { 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} } { do_execsql_test e_select-7.10.$tn $select [list {*}$res] } # 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); } {} foreach {tn select res} { 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} } { do_execsql_test e_select-7.11.$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.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. # foreach {tn select res} { 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 } } { do_execsql_test e_select-8.1.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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 } } { do_execsql_test e_select-8.$tn $select [list {*}$res] } # 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). # foreach {tn select res} { 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" { 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 } 9 "SELECT z, x FROM d1 ORDER BY 1" { -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 } } { do_execsql_test e_select-8.4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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" { 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 } 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 } } { do_execsql_test e_select-8.5.$tn $select [list {*}$res] } # EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is # any other expression, it is evaluated and the 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. # foreach {tn select res} { 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 } } { do_execsql_test e_select-8.6.$tn $select [list {*}$res] } # 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. # foreach {tn select violation} { 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 } { set err "$violation ORDER BY term does not match any column in the result set" do_catchsql_test e_select-8.7.1.$tn $select [list 1 $err] } foreach {tn select res} { 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.7.2.$tn $select [list {*}$res] } 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'); } {} foreach {tn select res} { 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} } { do_execsql_test e_select-8.13.$tn $select [list {*}$res] } # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in # the result columns of any constituent SELECT, it is an error. # foreach {tn select idx} { 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 } { set err "$idx ORDER BY term does not match any column in the result set" do_catchsql_test e_select-8.14.$tn $select [list 1 $err] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-8.15.$tn $select [list {*}$res] } #------------------------------------------------------------------------- # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.1.$tn $select [list {*}$res] } # 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. # foreach {tn select} { 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) } } { do_catchsql_test e_select-9.2.$tn $select {1 {datatype mismatch}} } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.4.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.5.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.6.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.8.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.9.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.10.$tn $select [list {*}$res] } # 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. # foreach {tn select res} { 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} } { do_execsql_test e_select-9.11.$tn $select [list {*}$res] } finish_test