Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add test file e_select.test. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
8b9d8c226e5d95584c1e61a796dc23b7 |
User & Date: | dan 2010-09-04 18:52:30.000 |
Context
2010-09-06
| ||
18:44 | Remove proprietary information from the releasetest.tcl script and add it to the public repository. (check-in: e1d4a87e4e user: drh tags: trunk) | |
2010-09-04
| ||
18:52 | Add test file e_select.test. (check-in: 8b9d8c226e user: dan tags: trunk) | |
2010-09-03
| ||
18:50 | Documentation enhancements for sqlite3_db_status(). Evidence marks on the SQL function call intrface. (check-in: f06c7b1973 user: drh tags: trunk) | |
Changes
Added test/e_select.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 | # 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 \ ] #------------------------------------------------------------------------- # The following tests focus on FROM clause (join) processing. # # EVIDENCE-OF: R-26491-65072 If the join-op is a comma (","), then the # composite dataset is the cartesian product of the sets of records from # the left and right sides of the join-op. # do_execsql_test e_select-1.1.2 { SELECT * FROM t1, t2 } $t1_cross_t2 do_execsql_test e_select-1.1.3 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 # EVIDENCE-OF: R-22228-15000 If the join-op is a "CROSS JOIN" or "INNER # JOIN", then the composite dataset is created in the same way as for # the comma join-op. # 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.2.$tn $select $res } # EVIDENCE-OF: R-00387-12725 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 composite dataset. # # Each of the SELECT statements below is executed three times - once with # the string %JOIN% replaced with a comma, once with "CROSS JOIN" and once # with "INNER JOIN". The test shows that the results of the query are the # same in each case. # 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} \ ] { foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { set S [string map [list %JOIN% $joinop] $select] do_execsql_test e_select-1.3.$tn.$tn2 $S $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) } {} } { foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { set S [string map [list %JOIN% $joinop] $select] do_execsql_test e_select-1.6.$tn.$tn2 $S $res } } finish_test |