1# 2010 July 16 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# 12# This file implements tests to verify that the "testable statements" in 13# the lang_select.html document are correct. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19do_execsql_test e_select-1.0 { 20 CREATE TABLE t1(a, b); 21 INSERT INTO t1 VALUES('a', 'one'); 22 INSERT INTO t1 VALUES('b', 'two'); 23 INSERT INTO t1 VALUES('c', 'three'); 24 25 CREATE TABLE t2(a, b); 26 INSERT INTO t2 VALUES('a', 'I'); 27 INSERT INTO t2 VALUES('b', 'II'); 28 INSERT INTO t2 VALUES('c', 'III'); 29 30 CREATE TABLE t3(a, c); 31 INSERT INTO t3 VALUES('a', 1); 32 INSERT INTO t3 VALUES('b', 2); 33 34 CREATE TABLE t4(a, c); 35 INSERT INTO t4 VALUES('a', NULL); 36 INSERT INTO t4 VALUES('b', 2); 37} {} 38set t1_cross_t2 [list \ 39 a one a I a one b II \ 40 a one c III b two a I \ 41 b two b II b two c III \ 42 c three a I c three b II \ 43 c three c III \ 44] 45set t1_cross_t1 [list \ 46 a one a one a one b two \ 47 a one c three b two a one \ 48 b two b two b two c three \ 49 c three a one c three b two \ 50 c three c three \ 51] 52 53 54# This proc is a specialized version of [do_execsql_test]. 55# 56# The second argument to this proc must be a SELECT statement that 57# features a cross join of some time. Instead of the usual ",", 58# "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be 59# substituted. 60# 61# This test runs the SELECT three times - once with: 62# 63# * s/%JOIN%/,/ 64# * s/%JOIN%/INNER JOIN/ 65# * s/%JOIN%/CROSS JOIN/ 66# 67# and checks that each time the results of the SELECT are $res. 68# 69proc do_join_test {tn select res} { 70 foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { 71 set S [string map [list %JOIN% $joinop] $select] 72 uplevel do_execsql_test $tn.$tn2 [list $S] [list $res] 73 } 74} 75 76#------------------------------------------------------------------------- 77# The following tests check that all paths on the syntax diagrams on 78# the lang_select.html page may be taken. 79# 80# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint 81# 82do_join_test e_select-0.1.1 { 83 SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) 84} {3} 85do_join_test e_select-0.1.2 { 86 SELECT count(*) FROM t1 %JOIN% t2 USING (a) 87} {3} 88do_join_test e_select-0.1.3 { 89 SELECT count(*) FROM t1 %JOIN% t2 90} {9} 91do_catchsql_test e_select-0.1.4 { 92 SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) 93} {1 {cannot have both ON and USING clauses in the same join}} 94do_catchsql_test e_select-0.1.5 { 95 SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) 96} {1 {near "ON": syntax error}} 97 98#------------------------------------------------------------------------- 99# The following tests focus on FROM clause (join) processing. 100# 101# EVIDENCE-OF: R-26491-65072 If the join-op is a comma (","), then the 102# composite dataset is the cartesian product of the sets of records from 103# the left and right sides of the join-op. 104# 105do_execsql_test e_select-1.1.2 { SELECT * FROM t1, t2 } $t1_cross_t2 106do_execsql_test e_select-1.1.3 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 107 108 109# EVIDENCE-OF: R-22228-15000 If the join-op is a "CROSS JOIN" or "INNER 110# JOIN", then the composite dataset is created in the same way as for 111# the comma join-op. 112# 113foreach {tn select res} [list \ 114 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ 115 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ 116 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ 117 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ 118] { 119 do_execsql_test e_select-1.2.$tn $select $res 120} 121 122 123# EVIDENCE-OF: R-00387-12725 If there is an ON clause specified, then 124# the ON expression is evaluated for each row of the cartesian product 125# and the result cast to a numeric value as if by a CAST expression. All 126# rows for which the expression evaluates to NULL or zero (integer value 127# 0 or real value 0.0) are excluded from the composite dataset. 128# 129# Each of the SELECT statements below is executed three times - once with 130# the string %JOIN% replaced with a comma, once with "CROSS JOIN" and once 131# with "INNER JOIN". The test shows that the results of the query are the 132# same in each case. 133# 134foreach {tn select res} [list \ 135 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ 136 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ 137 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ 138 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ 139 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ 140 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ 141 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ 142 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ 143 \ 144 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ 145 {one I two II three III} \ 146 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ 147 {one I one II one III} \ 148 11 { SELECT t1.b, t2.b 149 FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ 150 {two I two II two III three I three II three III} \ 151] { 152 do_join_test e_select-1.3.$tn $select $res 153} 154 155# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as 156# part of the join-constraint, then each of the column names specified 157# must exist in the datasets to both the left and right of the join-op. 158# 159foreach {tn select col} { 160 1 { SELECT * FROM t1, t3 USING (b) } "b" 161 2 { SELECT * FROM t3, t1 USING (c) } "c" 162 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" 163} { 164 set err "cannot join using column $col - column not present in both tables" 165 do_catchsql_test e_select-1.4.$tn $select [list 1 $err] 166} 167 168# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the 169# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian 170# product and the result cast to a numeric value. All rows for which one 171# or more of the expressions evaluates to NULL or zero are excluded from 172# the result set. 173# 174foreach {tn select res} { 175 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} 176 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} 177} { 178 do_execsql_test e_select-1.5.$tn $select $res 179} 180 181# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a 182# USING clause, the normal rules for handling affinities, collation 183# sequences and NULL values in comparisons apply. 184# 185# EVIDENCE-OF: R-35466-18578 The column from the dataset on the 186# left-hand side of the join operator is considered to be on the 187# left-hand side of the comparison operator (=) for the purposes of 188# collation sequence and affinity precedence. 189# 190do_execsql_test e_select-1.6.0 { 191 CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); 192 INSERT INTO t5 VALUES('AA', 'cc'); 193 INSERT INTO t5 VALUES('BB', 'dd'); 194 INSERT INTO t5 VALUES(NULL, NULL); 195 CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); 196 INSERT INTO t6 VALUES('aa', 'cc'); 197 INSERT INTO t6 VALUES('bb', 'DD'); 198 INSERT INTO t6 VALUES(NULL, NULL); 199} {} 200foreach {tn select res} { 201 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} 202 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} 203 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } 204 {aa cc cc bb DD dd} 205 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} 206 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} 207} { 208 do_join_test e_select-1.6.$tn $select $res 209} 210 211# EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a 212# USING clause, the column from the right-hand dataset is omitted from 213# the joined dataset. 214# 215# EVIDENCE-OF: R-56132-15700 This is the only difference between a USING 216# clause and its equivalent ON constraint. 217# 218foreach {tn select res} { 219 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } 220 {a one I b two II c three III} 221 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } 222 {a one a I b two b II c three c III} 223 224 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } 225 {a 1 {} b 2 2} 226 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } 227 {a 1 a {} b 2 b 2} 228 229 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} 230 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} 231 232 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 233 %JOIN% t5 USING (a) } 234 {aa cc cc bb DD dd} 235 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 236 %JOIN% t5 ON (x.a=t5.a) } 237 {aa cc AA cc bb DD BB dd} 238} { 239 do_join_test e_select-1.7.$tn $select $res 240} 241 242# EVIDENCE-OF: R-04095-00676 If the join-op is a "LEFT JOIN" or "LEFT 243# OUTER JOIN", then the composite dataset is created as for an "INNER 244# JOIN". Except, after the ON or USING filtering clauses have been 245# applied, an extra row is added to the output for each row in the 246# original left-hand input dataset (if any) that corresponds to no rows 247# at all in the composite dataset. 248# 249do_execsql_test e_select-1.8.0 { 250 CREATE TABLE t7(a, b, c); 251 CREATE TABLE t8(a, d, e); 252 253 INSERT INTO t7 VALUES('x', 'ex', 24); 254 INSERT INTO t7 VALUES('y', 'why', 25); 255 256 INSERT INTO t8 VALUES('x', 'abc', 24); 257 INSERT INTO t8 VALUES('z', 'ghi', 26); 258} {} 259 260do_execsql_test e_select-1.8.1a { 261 SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a) 262} {1} 263do_execsql_test e_select-1.8.1b { 264 SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) 265} {2} 266 267do_execsql_test e_select-1.8.2a { 268 SELECT count(*) FROM t7 JOIN t8 USING (a) 269} {1} 270do_execsql_test e_select-1.8.2b { 271 SELECT count(*) FROM t7 LEFT JOIN t8 USING (a) 272} {2} 273 274# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the 275# columns that would normally contain values copied from the right-hand 276# input dataset. 277# 278do_execsql_test e_select-1.9.1a { 279 SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a) 280} {x ex 24 x abc 24} 281do_execsql_test e_select-1.9.1b { 282 SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) 283} {x ex 24 x abc 24 y why 25 {} {} {}} 284 285do_execsql_test e_select-1.9.2a { 286 SELECT * FROM t7 JOIN t8 USING (a) 287} {x ex 24 abc 24} 288do_execsql_test e_select-1.9.2b { 289 SELECT * FROM t7 LEFT JOIN t8 USING (a) 290} {x ex 24 abc 24 y why 25 {} {}} 291 292# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of 293# the join-ops, then an implicit USING clause is added to the 294# join-constraints. The implicit USING clause contains each of the 295# column names that appear in both the left and right-hand input 296# datasets. 297# 298foreach {tn s1 s2 res} { 299 1 { SELECT * FROM t7 JOIN t8 USING (a) } 300 { SELECT * FROM t7 NATURAL JOIN t8 } 301 {x ex 24 abc 24} 302 303 2 { SELECT * FROM t8 JOIN t7 USING (a) } 304 { SELECT * FROM t8 NATURAL JOIN t7 } 305 {x abc 24 ex 24} 306 307 3 { SELECT * FROM t7 LEFT JOIN t8 USING (a) } 308 { SELECT * FROM t7 NATURAL LEFT JOIN t8 } 309 {x ex 24 abc 24 y why 25 {} {}} 310 311 4 { SELECT * FROM t8 LEFT JOIN t7 USING (a) } 312 { SELECT * FROM t8 NATURAL LEFT JOIN t7 } 313 {x abc 24 ex 24 z ghi 26 {} {}} 314 315 5 { SELECT * FROM t3 JOIN t4 USING (a,c) } 316 { SELECT * FROM t3 NATURAL JOIN t4 } 317 {b 2} 318 319 6 { SELECT * FROM t3 LEFT JOIN t4 USING (a,c) } 320 { SELECT * FROM t3 NATURAL LEFT JOIN t4 } 321 {a 1 b 2} 322} { 323 do_execsql_test e_select-1.10.${tn}a $s1 $res 324 do_execsql_test e_select-1.10.${tn}b $s2 $res 325} 326 327# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets 328# feature no common column names, then the NATURAL keyword has no effect 329# on the results of the join. 330# 331do_execsql_test e_select-1.11.0 { 332 CREATE TABLE t10(x, y); 333 INSERT INTO t10 VALUES(1, 'true'); 334 INSERT INTO t10 VALUES(0, 'false'); 335} {} 336foreach {tn s1 s2 res} { 337 1 { SELECT a, x FROM t1 CROSS JOIN t10 } 338 { SELECT a, x FROM t1 NATURAL CROSS JOIN t10 } 339 {a 1 a 0 b 1 b 0 c 1 c 0} 340} { 341 do_execsql_test e_select-1.11.${tn}a $s1 $res 342 do_execsql_test e_select-1.11.${tn}b $s2 $res 343} 344 345# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a 346# join that specifies the NATURAL keyword. 347# 348foreach {tn sql} { 349 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} 350 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} 351 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} 352} { 353 do_catchsql_test e_select-1.12.$tn " 354 $sql 355 " {1 {a NATURAL join may not have an ON or USING clause}} 356} 357 358finish_test 359