000001 # 2010 July 16 000002 # 000003 # The author disclaims copyright to this source code. In place of 000004 # a legal notice, here is a blessing: 000005 # 000006 # May you do good and not evil. 000007 # May you find forgiveness for yourself and forgive others. 000008 # May you share freely, never taking more than you give. 000009 # 000010 #*********************************************************************** 000011 # 000012 # This file implements tests to verify that the "testable statements" in 000013 # the lang_select.html document are correct. 000014 # 000015 000016 set testdir [file dirname $argv0] 000017 source $testdir/tester.tcl 000018 000019 ifcapable !compound { 000020 finish_test 000021 return 000022 } 000023 000024 do_execsql_test e_select-1.0 { 000025 CREATE TABLE t1(a, b); 000026 INSERT INTO t1 VALUES('a', 'one'); 000027 INSERT INTO t1 VALUES('b', 'two'); 000028 INSERT INTO t1 VALUES('c', 'three'); 000029 000030 CREATE TABLE t2(a, b); 000031 INSERT INTO t2 VALUES('a', 'I'); 000032 INSERT INTO t2 VALUES('b', 'II'); 000033 INSERT INTO t2 VALUES('c', 'III'); 000034 000035 CREATE TABLE t3(a, c); 000036 INSERT INTO t3 VALUES('a', 1); 000037 INSERT INTO t3 VALUES('b', 2); 000038 000039 CREATE TABLE t4(a, c); 000040 INSERT INTO t4 VALUES('a', NULL); 000041 INSERT INTO t4 VALUES('b', 2); 000042 } {} 000043 set t1_cross_t2 [list \ 000044 a one a I a one b II \ 000045 a one c III b two a I \ 000046 b two b II b two c III \ 000047 c three a I c three b II \ 000048 c three c III \ 000049 ] 000050 set t1_cross_t1 [list \ 000051 a one a one a one b two \ 000052 a one c three b two a one \ 000053 b two b two b two c three \ 000054 c three a one c three b two \ 000055 c three c three \ 000056 ] 000057 000058 000059 # This proc is a specialized version of [do_execsql_test]. 000060 # 000061 # The second argument to this proc must be a SELECT statement that 000062 # features a cross join of some time. Instead of the usual ",", 000063 # "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be 000064 # substituted. 000065 # 000066 # This test runs the SELECT three times - once with: 000067 # 000068 # * s/%JOIN%/,/ 000069 # * s/%JOIN%/JOIN/ 000070 # * s/%JOIN%/INNER JOIN/ 000071 # * s/%JOIN%/CROSS JOIN/ 000072 # 000073 # and checks that each time the results of the SELECT are $res. 000074 # 000075 proc do_join_test {tn select res} { 000076 foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { 000077 set S [string map [list %JOIN% $joinop] $select] 000078 uplevel do_execsql_test $tn.$tn2 [list $S] [list $res] 000079 } 000080 } 000081 000082 #------------------------------------------------------------------------- 000083 # The following tests check that all paths on the syntax diagrams on 000084 # the lang_select.html page may be taken. 000085 # 000086 # -- syntax diagram join-constraint 000087 # 000088 do_join_test e_select-0.1.1 { 000089 SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) 000090 } {3} 000091 do_join_test e_select-0.1.2 { 000092 SELECT count(*) FROM t1 %JOIN% t2 USING (a) 000093 } {3} 000094 do_join_test e_select-0.1.3 { 000095 SELECT count(*) FROM t1 %JOIN% t2 000096 } {9} 000097 do_catchsql_test e_select-0.1.4 { 000098 SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) 000099 } {1 {cannot have both ON and USING clauses in the same join}} 000100 do_catchsql_test e_select-0.1.5 { 000101 SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) 000102 } {1 {near "ON": syntax error}} 000103 000104 # -- syntax diagram select-core 000105 # 000106 # 0: SELECT ... 000107 # 1: SELECT DISTINCT ... 000108 # 2: SELECT ALL ... 000109 # 000110 # 0: No FROM clause 000111 # 1: Has FROM clause 000112 # 000113 # 0: No WHERE clause 000114 # 1: Has WHERE clause 000115 # 000116 # 0: No GROUP BY clause 000117 # 1: Has GROUP BY clause 000118 # 2: Has GROUP BY and HAVING clauses 000119 # 000120 do_select_tests e_select-0.2 { 000121 0000.1 "SELECT 1, 2, 3 " {1 2 3} 000122 1000.1 "SELECT DISTINCT 1, 2, 3 " {1 2 3} 000123 2000.1 "SELECT ALL 1, 2, 3 " {1 2 3} 000124 000125 0100.1 "SELECT a, b, a||b FROM t1 " { 000126 a one aone b two btwo c three cthree 000127 } 000128 1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " { 000129 a one aone b two btwo c three cthree 000130 } 000131 1200.1 "SELECT ALL a, b, a||b FROM t1 " { 000132 a one aone b two btwo c three cthree 000133 } 000134 000135 0010.1 "SELECT 1, 2, 3 WHERE 1 " {1 2 3} 000136 0010.2 "SELECT 1, 2, 3 WHERE 0 " {} 000137 0010.3 "SELECT 1, 2, 3 WHERE NULL " {} 000138 000139 1010.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3} 000140 000141 2010.1 "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3} 000142 000143 0110.1 "SELECT a, b, a||b FROM t1 WHERE a!='x' " { 000144 a one aone b two btwo c three cthree 000145 } 000146 0110.2 "SELECT a, b, a||b FROM t1 WHERE a=='x'" {} 000147 000148 1110.1 "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " { 000149 a one aone b two btwo c three cthree 000150 } 000151 000152 2110.0 "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {} 000153 000154 0001.1 "SELECT 1, 2, 3 GROUP BY 2" {1 2 3} 000155 0002.1 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} 000156 0002.2 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} 000157 000158 1001.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3} 000159 1002.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} 000160 1002.2 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} 000161 000162 2001.1 "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3} 000163 2002.1 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3} 000164 2002.2 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {} 000165 000166 0101.1 "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} 000167 0102.1 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" { 000168 1 a 1 c 1 b 000169 } 000170 0102.2 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" {} 000171 000172 1101.1 "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} 000173 1102.1 "SELECT DISTINCT count(*), max(a) FROM t1 000174 GROUP BY b HAVING count(*)=1" { 000175 1 a 1 c 1 b 000176 } 000177 1102.2 "SELECT DISTINCT count(*), max(a) FROM t1 000178 GROUP BY b HAVING count(*)=2" {} 000179 000180 2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} 000181 2102.1 "SELECT ALL count(*), max(a) FROM t1 000182 GROUP BY b HAVING count(*)=1" { 000183 1 a 1 c 1 b 000184 } 000185 2102.2 "SELECT ALL count(*), max(a) FROM t1 000186 GROUP BY b HAVING count(*)=2" {} 000187 000188 0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} 000189 0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} 000190 0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {} 000191 000192 1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {} 000193 1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 000194 {1 2 3} 000195 1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {} 000196 000197 2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} 000198 2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} 000199 2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {} 000200 000201 0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a} 000202 0112.1 "SELECT count(*), max(a) FROM t1 000203 WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c} 000204 0112.2 "SELECT count(*), max(a) FROM t1 000205 WHERE 0 GROUP BY b HAVING count(*)=2" {} 000206 1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 000207 {1 a 1 b} 000208 1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a' 000209 GROUP BY b HAVING count(*)=1" { 000210 1 c 1 b 000211 } 000212 1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0 000213 GROUP BY b HAVING count(*)=2" {} 000214 000215 2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 000216 {1 c 1 b} 000217 2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b' 000218 GROUP BY b HAVING count(*)=1" { 000219 1 a 1 c 000220 } 000221 2112.2 "SELECT ALL count(*), max(a) FROM t1 000222 WHERE 0 GROUP BY b HAVING count(*)=2" {} 000223 } 000224 000225 000226 # -- syntax diagram result-column 000227 # 000228 do_select_tests e_select-0.3 { 000229 1 "SELECT * FROM t1" {a one b two c three} 000230 2 "SELECT t1.* FROM t1" {a one b two c three} 000231 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx} 000232 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx} 000233 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx} 000234 } 000235 000236 # -- syntax diagram join-source 000237 # 000238 # -- syntax diagram join-op 000239 # 000240 do_select_tests e_select-0.4 { 000241 1 "SELECT t1.rowid FROM t1" {1 2 3} 000242 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3} 000243 3 "SELECT t1.rowid FROM t1,t2,t3" {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} 000244 000245 4 "SELECT t1.rowid FROM t1" {1 2 3} 000246 5 "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3} 000247 6 "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 000248 {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} 000249 000250 7 "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2} 000251 8 "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3} 000252 9 "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3} 000253 10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2} 000254 11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2} 000255 000256 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} 000257 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} 000258 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} 000259 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} 000260 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} 000261 } 000262 000263 # -- syntax diagram compound-operator 000264 # 000265 do_select_tests e_select-0.5 { 000266 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4} 000267 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4} 000268 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3} 000269 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2} 000270 } 000271 000272 # -- syntax diagram ordering-term 000273 # 000274 do_select_tests e_select-0.6 { 000275 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob} 000276 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob} 000277 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob} 000278 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea} 000279 } 000280 000281 # -- syntax diagram select-stmt 000282 # 000283 do_select_tests e_select-0.7 { 000284 1 "SELECT * FROM t1" {a one b two c three} 000285 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two} 000286 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two} 000287 000288 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three} 000289 5 "SELECT * FROM t1 LIMIT 10 OFFSET 5" {} 000290 6 "SELECT * FROM t1 LIMIT 10, 5" {} 000291 000292 7 "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three} 000293 8 "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {} 000294 9 "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {} 000295 000296 10 "SELECT * FROM t1 UNION SELECT b, a FROM t1" 000297 {a one b two c three one a three c two b} 000298 11 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 000299 {one a two b three c a one c three b two} 000300 12 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 000301 {one a two b three c a one c three b two} 000302 13 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 000303 {a one b two c three one a three c two b} 000304 14 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 000305 {two b} 000306 15 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 000307 {} 000308 16 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 000309 {a one b two c three one a three c two b} 000310 17 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 000311 {b two} 000312 18 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 000313 {} 000314 } 000315 000316 #------------------------------------------------------------------------- 000317 # The following tests focus on FROM clause (join) processing. 000318 # 000319 000320 # EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple 000321 # SELECT statement, then the input data is implicitly a single row zero 000322 # columns wide 000323 # 000324 do_select_tests e_select-1.1 { 000325 1 "SELECT 'abc'" {abc} 000326 2 "SELECT 'abc' WHERE NULL" {} 000327 3 "SELECT NULL" {{}} 000328 4 "SELECT count(*)" {1} 000329 5 "SELECT count(*) WHERE 0" {0} 000330 6 "SELECT count(*) WHERE 1" {1} 000331 } 000332 000333 # EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery 000334 # in the FROM clause, then the input data used by the SELECT statement 000335 # is the contents of the named table. 000336 # 000337 # The results of the SELECT queries suggest that they are operating on the 000338 # contents of the table 'xx'. 000339 # 000340 do_execsql_test e_select-1.2.0 { 000341 CREATE TABLE xx(x, y); 000342 INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2'); 000343 INSERT INTO xx VALUES(NULL, -16.87); 000344 INSERT INTO xx VALUES(-17.89, 'linguistically'); 000345 } {} 000346 do_select_tests e_select-1.2 { 000347 1 "SELECT quote(x), quote(y) FROM xx" { 000348 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 000349 NULL -16.87 000350 -17.89 'linguistically' 000351 } 000352 000353 2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3} 000354 3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87} 000355 } 000356 000357 # EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery 000358 # in FROM clause then the contents of all tables and/or subqueries are 000359 # joined into a single dataset for the simple SELECT statement to 000360 # operate on. 000361 # 000362 # There are more detailed tests for subsequent requirements that add 000363 # more detail to this idea. We just add a single test that shows that 000364 # data is coming from each of the three tables following the FROM clause 000365 # here to show that the statement, vague as it is, is not incorrect. 000366 # 000367 do_select_tests e_select-1.3 { 000368 1 "SELECT * FROM t1, t2, t3" { 000369 a one a I a 1 a one a I b 2 a one b II a 1 000370 a one b II b 2 a one c III a 1 a one c III b 2 000371 b two a I a 1 b two a I b 2 b two b II a 1 000372 b two b II b 2 b two c III a 1 b two c III b 2 000373 c three a I a 1 c three a I b 2 c three b II a 1 000374 c three b II b 2 c three c III a 1 c three c III b 2 000375 } 000376 } 000377 000378 # 000379 # The following block of tests - e_select-1.4.* - test that the description 000380 # of cartesian joins in the SELECT documentation is consistent with SQLite. 000381 # In doing so, we test the following three requirements as a side-effect: 000382 # 000383 # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN", 000384 # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING 000385 # clause, then the result of the join is simply the cartesian product of 000386 # the left and right-hand datasets. 000387 # 000388 # The tests are built on this assertion. Really, they test that the output 000389 # of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result 000390 # of calculating the cartesian product of the left and right-hand datasets. 000391 # 000392 # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER 000393 # JOIN", "JOIN" and "," join operators. 000394 # 000395 # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the 000396 # same result as the "INNER JOIN", "JOIN" and "," operators 000397 # 000398 # All tests are run 4 times, with the only difference in each run being 000399 # which of the 4 equivalent cartesian product join operators are used. 000400 # Since the output data is the same in all cases, we consider that this 000401 # qualifies as testing the two statements above. 000402 # 000403 do_execsql_test e_select-1.4.0 { 000404 CREATE TABLE x1(a, b); 000405 CREATE TABLE x2(c, d, e); 000406 CREATE TABLE x3(f, g, h, i); 000407 000408 -- x1: 3 rows, 2 columns 000409 INSERT INTO x1 VALUES(24, 'converging'); 000410 INSERT INTO x1 VALUES(NULL, X'CB71'); 000411 INSERT INTO x1 VALUES('blonds', 'proprietary'); 000412 000413 -- x2: 2 rows, 3 columns 000414 INSERT INTO x2 VALUES(-60.06, NULL, NULL); 000415 INSERT INTO x2 VALUES(-58, NULL, 1.21); 000416 000417 -- x3: 5 rows, 4 columns 000418 INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1); 000419 INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified'); 000420 INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL); 000421 INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3); 000422 INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL); 000423 } {} 000424 000425 # EVIDENCE-OF: R-59089-25828 The columns of the cartesian product 000426 # dataset are, in order, all the columns of the left-hand dataset 000427 # followed by all the columns of the right-hand dataset. 000428 # 000429 do_join_test e_select-1.4.1.1 { 000430 SELECT * FROM x1 %JOIN% x2 LIMIT 1 000431 } [concat {24 converging} {-60.06 {} {}}] 000432 000433 do_join_test e_select-1.4.1.2 { 000434 SELECT * FROM x2 %JOIN% x1 LIMIT 1 000435 } [concat {-60.06 {} {}} {24 converging}] 000436 000437 do_join_test e_select-1.4.1.3 { 000438 SELECT * FROM x3 %JOIN% x2 LIMIT 1 000439 } [concat {-39.24 {} encompass -1} {-60.06 {} {}}] 000440 000441 do_join_test e_select-1.4.1.4 { 000442 SELECT * FROM x2 %JOIN% x3 LIMIT 1 000443 } [concat {-60.06 {} {}} {-39.24 {} encompass -1}] 000444 000445 # EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product 000446 # dataset formed by combining each unique combination of a row from the 000447 # left-hand and right-hand datasets. 000448 # 000449 do_join_test e_select-1.4.2.1 { 000450 SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f 000451 } [list -60.06 {} {} -39.24 {} encompass -1 \ 000452 -60.06 {} {} alerting {} -93.79 {} \ 000453 -60.06 {} {} coldest -96 dramatists 82.3 \ 000454 -60.06 {} {} conducting -87.24 37.56 {} \ 000455 -60.06 {} {} presenting 51 reformation dignified \ 000456 -58 {} 1.21 -39.24 {} encompass -1 \ 000457 -58 {} 1.21 alerting {} -93.79 {} \ 000458 -58 {} 1.21 coldest -96 dramatists 82.3 \ 000459 -58 {} 1.21 conducting -87.24 37.56 {} \ 000460 -58 {} 1.21 presenting 51 reformation dignified \ 000461 ] 000462 # TODO: Come back and add a few more like the above. 000463 000464 # EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset 000465 # consists of Nleft rows of Mleft columns, and the right-hand dataset of 000466 # Nright rows of Mright columns, then the cartesian product is a dataset 000467 # of Nleft×Nright rows, each containing Mleft+Mright columns. 000468 # 000469 # x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3) 000470 do_join_test e_select-1.4.3.1 { 000471 SELECT count(*) FROM x1 %JOIN% x2 000472 } [expr 3*2] 000473 do_test e_select-1.4.3.2 { 000474 expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6} 000475 } [expr 2+3] 000476 000477 # x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4) 000478 do_join_test e_select-1.4.3.3 { 000479 SELECT count(*) FROM x2 %JOIN% x3 000480 } [expr 2*5] 000481 do_test e_select-1.4.3.4 { 000482 expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10} 000483 } [expr 3+4] 000484 000485 # x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2) 000486 do_join_test e_select-1.4.3.5 { 000487 SELECT count(*) FROM x3 %JOIN% x1 000488 } [expr 5*3] 000489 do_test e_select-1.4.3.6 { 000490 expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15} 000491 } [expr 4+2] 000492 000493 # x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4) 000494 do_join_test e_select-1.4.3.7 { 000495 SELECT count(*) FROM x3 %JOIN% x3 000496 } [expr 5*5] 000497 do_test e_select-1.4.3.8 { 000498 expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25} 000499 } [expr 4+4] 000500 000501 # Some extra cartesian product tests using tables t1 and t2. 000502 # 000503 do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2 000504 do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 000505 000506 do_select_tests e_select-1.4.5 [list \ 000507 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ 000508 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ 000509 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ 000510 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ 000511 ] 000512 000513 # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON 000514 # expression is evaluated for each row of the cartesian product as a 000515 # boolean expression. Only rows for which the expression evaluates to 000516 # true are included from the dataset. 000517 # 000518 foreach {tn select res} [list \ 000519 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ 000520 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ 000521 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ 000522 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ 000523 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ 000524 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ 000525 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ 000526 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ 000527 \ 000528 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ 000529 {one I two II three III} \ 000530 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ 000531 {one I one II one III} \ 000532 11 { SELECT t1.b, t2.b 000533 FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ 000534 {two I two II two III three I three II three III} \ 000535 ] { 000536 do_join_test e_select-1.3.$tn $select $res 000537 } 000538 000539 # EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the 000540 # column names specified must exist in the datasets to both the left and 000541 # right of the join-operator. 000542 # 000543 do_select_tests e_select-1.4 -error { 000544 cannot join using column %s - column not present in both tables 000545 } { 000546 1 { SELECT * FROM t1, t3 USING (b) } "b" 000547 2 { SELECT * FROM t3, t1 USING (c) } "c" 000548 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" 000549 } 000550 000551 # EVIDENCE-OF: R-22776-52830 For each pair of named columns, the 000552 # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian 000553 # product as a boolean expression. Only rows for which all such 000554 # expressions evaluates to true are included from the result set. 000555 # 000556 do_select_tests e_select-1.5 { 000557 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} 000558 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} 000559 } 000560 000561 # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a 000562 # USING clause, the normal rules for handling affinities, collation 000563 # sequences and NULL values in comparisons apply. 000564 # 000565 # EVIDENCE-OF: R-38422-04402 The column from the dataset on the 000566 # left-hand side of the join-operator is considered to be on the 000567 # left-hand side of the comparison operator (=) for the purposes of 000568 # collation sequence and affinity precedence. 000569 # 000570 do_execsql_test e_select-1.6.0 { 000571 CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); 000572 INSERT INTO t5 VALUES('AA', 'cc'); 000573 INSERT INTO t5 VALUES('BB', 'dd'); 000574 INSERT INTO t5 VALUES(NULL, NULL); 000575 CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); 000576 INSERT INTO t6 VALUES('aa', 'cc'); 000577 INSERT INTO t6 VALUES('bb', 'DD'); 000578 INSERT INTO t6 VALUES(NULL, NULL); 000579 } {} 000580 foreach {tn select res} { 000581 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} 000582 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} 000583 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } 000584 {aa cc cc bb DD dd} 000585 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} 000586 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} 000587 } { 000588 do_join_test e_select-1.6.$tn $select $res 000589 } 000590 000591 # EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a 000592 # USING clause, the column from the right-hand dataset is omitted from 000593 # the joined dataset. 000594 # 000595 # EVIDENCE-OF: R-56132-15700 This is the only difference between a USING 000596 # clause and its equivalent ON constraint. 000597 # 000598 foreach {tn select res} { 000599 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } 000600 {a one I b two II c three III} 000601 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } 000602 {a one a I b two b II c three c III} 000603 000604 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } 000605 {a 1 {} b 2 2} 000606 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } 000607 {a 1 a {} b 2 b 2} 000608 000609 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} 000610 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} 000611 000612 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 000613 %JOIN% t5 USING (a) } 000614 {aa cc cc bb DD dd} 000615 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 000616 %JOIN% t5 ON (x.a=t5.a) } 000617 {aa cc AA cc bb DD BB dd} 000618 } { 000619 do_join_test e_select-1.7.$tn $select $res 000620 } 000621 # EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or 000622 # "LEFT OUTER JOIN", then after the ON or USING filtering clauses have 000623 # been applied, an extra row is added to the output for each row in the 000624 # original left-hand input dataset that corresponds to no rows at all in 000625 # the composite dataset (if any). 000626 # 000627 do_execsql_test e_select-1.8.0 { 000628 CREATE TABLE t7(a, b, c); 000629 CREATE TABLE t8(a, d, e); 000630 000631 INSERT INTO t7 VALUES('x', 'ex', 24); 000632 INSERT INTO t7 VALUES('y', 'why', 25); 000633 000634 INSERT INTO t8 VALUES('x', 'abc', 24); 000635 INSERT INTO t8 VALUES('z', 'ghi', 26); 000636 } {} 000637 000638 do_select_tests e_select-1.8 { 000639 1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1} 000640 1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2} 000641 2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1} 000642 2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2} 000643 } 000644 000645 000646 # EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the 000647 # columns that would normally contain values copied from the right-hand 000648 # input dataset. 000649 # 000650 do_select_tests e_select-1.9 { 000651 1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24} 000652 1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 000653 {x ex 24 x abc 24 y why 25 {} {} {}} 000654 2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} 000655 2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} 000656 } 000657 000658 # EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the 000659 # join-operator then an implicit USING clause is added to the 000660 # join-constraints. The implicit USING clause contains each of the 000661 # column names that appear in both the left and right-hand input 000662 # datasets. 000663 # 000664 do_select_tests e_select-1-10 { 000665 1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} 000666 1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24} 000667 000668 2a "SELECT * FROM t8 JOIN t7 USING (a)" {x abc 24 ex 24} 000669 2b "SELECT * FROM t8 NATURAL JOIN t7" {x abc 24 ex 24} 000670 000671 3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} 000672 3b "SELECT * FROM t7 NATURAL LEFT JOIN t8" {x ex 24 abc 24 y why 25 {} {}} 000673 000674 4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)" {x abc 24 ex 24 z ghi 26 {} {}} 000675 4b "SELECT * FROM t8 NATURAL LEFT JOIN t7" {x abc 24 ex 24 z ghi 26 {} {}} 000676 000677 5a "SELECT * FROM t3 JOIN t4 USING (a,c)" {b 2} 000678 5b "SELECT * FROM t3 NATURAL JOIN t4" {b 2} 000679 000680 6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2} 000681 6b "SELECT * FROM t3 NATURAL LEFT JOIN t4" {a 1 b 2} 000682 } 000683 000684 # EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets 000685 # feature no common column names, then the NATURAL keyword has no effect 000686 # on the results of the join. 000687 # 000688 do_execsql_test e_select-1.11.0 { 000689 CREATE TABLE t10(x, y); 000690 INSERT INTO t10 VALUES(1, 'true'); 000691 INSERT INTO t10 VALUES(0, 'false'); 000692 } {} 000693 do_select_tests e_select-1-11 { 000694 1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} 000695 1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} 000696 } 000697 000698 # EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a 000699 # join that specifies the NATURAL keyword. 000700 # 000701 foreach {tn sql} { 000702 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} 000703 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} 000704 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} 000705 } { 000706 do_catchsql_test e_select-1.12.$tn " 000707 $sql 000708 " {1 {a NATURAL join may not have an ON or USING clause}} 000709 } 000710 000711 #------------------------------------------------------------------------- 000712 # The next block of tests - e_select-3.* - concentrate on verifying 000713 # statements made regarding WHERE clause processing. 000714 # 000715 drop_all_tables 000716 do_execsql_test e_select-3.0 { 000717 CREATE TABLE x1(k, x, y, z); 000718 INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43); 000719 INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81); 000720 INSERT INTO x1 VALUES(3, -22, -27.57, NULL); 000721 INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky'); 000722 INSERT INTO x1 VALUES(5, NULL, 96.28, NULL); 000723 INSERT INTO x1 VALUES(6, 0, 1, 2); 000724 000725 CREATE TABLE x2(k, x, y2); 000726 INSERT INTO x2 VALUES(1, 50, X'B82838'); 000727 INSERT INTO x2 VALUES(5, 84.79, 65.88); 000728 INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393'); 000729 INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized'); 000730 } {} 000731 000732 # EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE 000733 # expression is evaluated for each row in the input data as a boolean 000734 # expression. Only rows for which the WHERE clause expression evaluates 000735 # to true are included from the dataset before continuing. 000736 # 000737 do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3} 000738 do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6} 000739 do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6} 000740 do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6} 000741 do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5} 000742 do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6} 000743 000744 do_execsql_test e_select-3.2.1a { 000745 SELECT k FROM x1 LEFT JOIN x2 USING(k) 000746 } {1 2 3 4 5 6} 000747 do_execsql_test e_select-3.2.1b { 000748 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k ORDER BY +k 000749 } {1 3 5} 000750 do_execsql_test e_select-3.2.2 { 000751 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL 000752 } {2 4 6} 000753 000754 do_execsql_test e_select-3.2.3 { 000755 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k 000756 } {3} 000757 do_execsql_test e_select-3.2.4 { 000758 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 000759 } {} 000760 000761 #------------------------------------------------------------------------- 000762 # Tests below this point are focused on verifying the testable statements 000763 # related to caculating the result rows of a simple SELECT statement. 000764 # 000765 000766 drop_all_tables 000767 do_execsql_test e_select-4.0 { 000768 CREATE TABLE z1(a, b, c); 000769 CREATE TABLE z2(d, e); 000770 CREATE TABLE z3(a, b); 000771 000772 INSERT INTO z1 VALUES(51.65, -59.58, 'belfries'); 000773 INSERT INTO z1 VALUES(-5, NULL, 75); 000774 INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters'); 000775 INSERT INTO z1 VALUES(NULL, 67, 'quartets'); 000776 INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen'); 000777 INSERT INTO z1 VALUES(63, 'born', -26); 000778 000779 INSERT INTO z2 VALUES(NULL, 21); 000780 INSERT INTO z2 VALUES(36, 6); 000781 000782 INSERT INTO z3 VALUES('subsistence', 'gauze'); 000783 INSERT INTO z3 VALUES(49.17, -67); 000784 } {} 000785 000786 # EVIDENCE-OF: R-36327-17224 If a result expression is the special 000787 # expression "*" then all columns in the input data are substituted for 000788 # that one expression. 000789 # 000790 # EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table 000791 # or subquery in the FROM clause followed by ".*" then all columns from 000792 # the named table or subquery are substituted for the single expression. 000793 # 000794 do_select_tests e_select-4.1 { 000795 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} 000796 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 000797 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 000798 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 000799 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 000800 000801 6 "SELECT count(*), * FROM z1" {6 51.65 -59.58 belfries} 000802 7 "SELECT max(a), * FROM z1" {63 63 born -26} 000803 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} 000804 000805 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 000806 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 000807 } 000808 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { 000809 51.65 -59.58 belfries 51.65 -59.58 belfries 000810 } 000811 } 000812 000813 # EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*" 000814 # expression in any context other than a result expression list. 000815 # 000816 # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or 000817 # "alias.*" expression in a simple SELECT query that does not have a 000818 # FROM clause. 000819 # 000820 foreach {tn select err} { 000821 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error} 000822 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error} 000823 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error} 000824 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error} 000825 000826 2.1 "SELECT *" {no tables specified} 000827 2.2 "SELECT * WHERE 1" {no tables specified} 000828 2.3 "SELECT * WHERE 0" {no tables specified} 000829 2.4 "SELECT count(*), *" {no tables specified} 000830 } { 000831 do_catchsql_test e_select-4.2.$tn $select [list 1 $err] 000832 } 000833 000834 # EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned 000835 # by a simple SELECT statement is equal to the number of expressions in 000836 # the result expression list after substitution of * and alias.* 000837 # expressions. 000838 # 000839 foreach {tn select nCol} { 000840 1 "SELECT * FROM z1" 3 000841 2 "SELECT * FROM z1 NATURAL JOIN z3" 3 000842 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3 000843 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2 000844 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5 000845 6 "SELECT 1, 2, z1.* FROM z1" 5 000846 7 "SELECT a, *, b, c FROM z1" 6 000847 } { 000848 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] 000849 do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol 000850 sqlite3_finalize $::stmt 000851 } 000852 000853 000854 000855 # In lang_select.html, a non-aggregate query is defined as any simple SELECT 000856 # that has no GROUP BY clause and no aggregate expressions in the result 000857 # expression list. Other queries are aggregate queries. Test cases 000858 # e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of 000859 # simple SELECT that is different for aggregate and non-aggregate queries 000860 # verify (in a way) that these definitions are consistent: 000861 # 000862 # EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate 000863 # query if it contains either a GROUP BY clause or one or more aggregate 000864 # functions in the result-set. 000865 # 000866 # EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no 000867 # aggregate functions or a GROUP BY clause, it is a non-aggregate query. 000868 # 000869 000870 # EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate 000871 # query, then each expression in the result expression list is evaluated 000872 # for each row in the dataset filtered by the WHERE clause. 000873 # 000874 do_select_tests e_select-4.4 { 000875 1 "SELECT a, b FROM z1" 000876 {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born} 000877 000878 2 "SELECT a IS NULL, b+1, * FROM z1" { 000879 0 -58.58 51.65 -59.58 belfries 000880 0 {} -5 {} 75 000881 0 -22.18 -2.2 -23.18 suiters 000882 1 68 {} 67 quartets 000883 0 -31.3 -1.04 -32.3 aspen 000884 0 1 63 born -26 000885 } 000886 000887 3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366} 000888 } 000889 000890 000891 # Test cases e_select-4.5.* and e_select-4.6.* together show that: 000892 # 000893 # EVIDENCE-OF: R-51988-01124 The single row of result-set data created 000894 # by evaluating the aggregate and non-aggregate expressions in the 000895 # result-set forms the result of an aggregate query without a GROUP BY 000896 # clause. 000897 # 000898 000899 # EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate 000900 # query without a GROUP BY clause, then each aggregate expression in the 000901 # result-set is evaluated once across the entire dataset. 000902 # 000903 do_select_tests e_select-4.5 { 000904 1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} 000905 2 "SELECT count(*), max(1)" {1 1} 000906 000907 3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} 000908 4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} 000909 5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} 000910 } 000911 000912 # EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the 000913 # result-set is evaluated once for an arbitrarily selected row of the 000914 # dataset. 000915 # 000916 # EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used 000917 # for each non-aggregate expression. 000918 # 000919 # Note: The results of many of the queries in this block of tests are 000920 # technically undefined, as the documentation does not specify which row 000921 # SQLite will arbitrarily select to use for the evaluation of the 000922 # non-aggregate expressions. 000923 # 000924 drop_all_tables 000925 do_execsql_test e_select-4.6.0 { 000926 CREATE TABLE a1(one PRIMARY KEY, two); 000927 INSERT INTO a1 VALUES(1, 1); 000928 INSERT INTO a1 VALUES(2, 3); 000929 INSERT INTO a1 VALUES(3, 6); 000930 INSERT INTO a1 VALUES(4, 10); 000931 000932 CREATE TABLE a2(one PRIMARY KEY, three); 000933 INSERT INTO a2 VALUES(1, 1); 000934 INSERT INTO a2 VALUES(3, 2); 000935 INSERT INTO a2 VALUES(6, 3); 000936 INSERT INTO a2 VALUES(10, 4); 000937 } {} 000938 do_select_tests e_select-4.6 { 000939 1 "SELECT one, two, count(*) FROM a1" {1 1 4} 000940 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {1 1 2} 000941 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 000942 4 "SELECT *, count(*) FROM a1 JOIN a2" {1 1 1 1 16} 000943 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} 000944 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3} 000945 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1} 000946 } 000947 000948 # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then 000949 # each non-aggregate expression is evaluated against a row consisting 000950 # entirely of NULL values. 000951 # 000952 do_select_tests e_select-4.7 { 000953 1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} 000954 2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} 000955 3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { 000956 1 1 1 000957 } 000958 } 000959 000960 # EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY 000961 # clause always returns exactly one row of data, even if there are zero 000962 # rows of input data. 000963 # 000964 foreach {tn select} { 000965 8.1 "SELECT count(*) FROM a1" 000966 8.2 "SELECT count(*) FROM a1 WHERE 0" 000967 8.3 "SELECT count(*) FROM a1 WHERE 1" 000968 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1" 000969 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0" 000970 } { 000971 # Set $nRow to the number of rows returned by $select: 000972 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] 000973 set nRow 0 000974 while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow } 000975 set rc [sqlite3_finalize $::stmt] 000976 000977 # Test that $nRow==1 and that statement execution was successful 000978 # (rc==SQLITE_OK). 000979 do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1} 000980 } 000981 000982 drop_all_tables 000983 do_execsql_test e_select-4.9.0 { 000984 CREATE TABLE b1(one PRIMARY KEY, two); 000985 INSERT INTO b1 VALUES(1, 'o'); 000986 INSERT INTO b1 VALUES(4, 'f'); 000987 INSERT INTO b1 VALUES(3, 't'); 000988 INSERT INTO b1 VALUES(2, 't'); 000989 INSERT INTO b1 VALUES(5, 'f'); 000990 INSERT INTO b1 VALUES(7, 's'); 000991 INSERT INTO b1 VALUES(6, 's'); 000992 000993 CREATE TABLE b2(x, y); 000994 INSERT INTO b2 VALUES(NULL, 0); 000995 INSERT INTO b2 VALUES(NULL, 1); 000996 INSERT INTO b2 VALUES('xyz', 2); 000997 INSERT INTO b2 VALUES('abc', 3); 000998 INSERT INTO b2 VALUES('xyz', 4); 000999 001000 CREATE TABLE b3(a COLLATE nocase, b COLLATE binary); 001001 INSERT INTO b3 VALUES('abc', 'abc'); 001002 INSERT INTO b3 VALUES('aBC', 'aBC'); 001003 INSERT INTO b3 VALUES('Def', 'Def'); 001004 INSERT INTO b3 VALUES('dEF', 'dEF'); 001005 } {} 001006 001007 # EVIDENCE-OF: R-07284-35990 If the SELECT statement is an aggregate 001008 # query with a GROUP BY clause, then each of the expressions specified 001009 # as part of the GROUP BY clause is evaluated for each row of the 001010 # dataset. Each row is then assigned to a "group" based on the results; 001011 # rows for which the results of evaluating the GROUP BY expressions are 001012 # the same get assigned to the same group. 001013 # 001014 # These tests also show that the following is not untrue: 001015 # 001016 # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do 001017 # not have to be expressions that appear in the result. 001018 # 001019 do_select_tests e_select-4.9 { 001020 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { 001021 /#,# f 1 o #,# s #,# t/ 001022 } 001023 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 001024 1,2,3,4 10 5,6,7 18 001025 } 001026 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 001027 4 1,5 2,6 3,7 001028 } 001029 4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { 001030 4,3,5,7,6 1,2 001031 } 001032 } 001033 001034 # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL 001035 # values are considered equal. 001036 # 001037 do_select_tests e_select-4.10 { 001038 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} 001039 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} 001040 } 001041 001042 # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation 001043 # sequence with which to compare text values apply when evaluating 001044 # expressions in a GROUP BY clause. 001045 # 001046 do_select_tests e_select-4.11 { 001047 1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} 001048 2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} 001049 3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} 001050 4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} 001051 5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} 001052 6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} 001053 } 001054 001055 # EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may 001056 # not be aggregate expressions. 001057 # 001058 foreach {tn select} { 001059 12.1 "SELECT * FROM b3 GROUP BY count(*)" 001060 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)" 001061 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)" 001062 } { 001063 set res {1 {aggregate functions are not allowed in the GROUP BY clause}} 001064 do_catchsql_test e_select-4.$tn $select $res 001065 } 001066 001067 # EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is 001068 # evaluated once for each group of rows as a boolean expression. If the 001069 # result of evaluating the HAVING clause is false, the group is 001070 # discarded. 001071 # 001072 # This requirement is tested by all e_select-4.13.* tests. 001073 # 001074 # EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate 001075 # expression, it is evaluated across all rows in the group. 001076 # 001077 # Tested by e_select-4.13.1.* 001078 # 001079 # EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate 001080 # expression, it is evaluated with respect to an arbitrarily selected 001081 # row from the group. 001082 # 001083 # Tested by e_select-4.13.2.* 001084 # 001085 # Tests in this block also show that this is not untrue: 001086 # 001087 # EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values, 001088 # even aggregate functions, that are not in the result. 001089 # 001090 do_execsql_test e_select-4.13.0 { 001091 CREATE TABLE c1(up, down); 001092 INSERT INTO c1 VALUES('x', 1); 001093 INSERT INTO c1 VALUES('x', 2); 001094 INSERT INTO c1 VALUES('x', 4); 001095 INSERT INTO c1 VALUES('x', 8); 001096 INSERT INTO c1 VALUES('y', 16); 001097 INSERT INTO c1 VALUES('y', 32); 001098 001099 CREATE TABLE c2(i, j); 001100 INSERT INTO c2 VALUES(1, 0); 001101 INSERT INTO c2 VALUES(2, 1); 001102 INSERT INTO c2 VALUES(3, 3); 001103 INSERT INTO c2 VALUES(4, 6); 001104 INSERT INTO c2 VALUES(5, 10); 001105 INSERT INTO c2 VALUES(6, 15); 001106 INSERT INTO c2 VALUES(7, 21); 001107 INSERT INTO c2 VALUES(8, 28); 001108 INSERT INTO c2 VALUES(9, 36); 001109 001110 CREATE TABLE c3(i PRIMARY KEY, k TEXT); 001111 INSERT INTO c3 VALUES(1, 'hydrogen'); 001112 INSERT INTO c3 VALUES(2, 'helium'); 001113 INSERT INTO c3 VALUES(3, 'lithium'); 001114 INSERT INTO c3 VALUES(4, 'beryllium'); 001115 INSERT INTO c3 VALUES(5, 'boron'); 001116 INSERT INTO c3 VALUES(94, 'plutonium'); 001117 } {} 001118 001119 do_select_tests e_select-4.13 { 001120 1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} 001121 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 001122 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 001123 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} 001124 001125 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} 001126 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} 001127 001128 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING j>6" {5 10} 001129 } 001130 001131 # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then 001132 # evaluated once for each group of rows. 001133 # 001134 # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate 001135 # expression, it is evaluated across all rows in the group. 001136 # 001137 do_select_tests e_select-4.15 { 001138 1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} 001139 2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} 001140 3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} 001141 4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} 001142 5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" 001143 {3 4.33 1 2.0} 001144 } 001145 001146 # EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single 001147 # arbitrarily chosen row from within the group. 001148 # 001149 # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate 001150 # expression in the result-set, then all such expressions are evaluated 001151 # for the same row. 001152 # 001153 do_select_tests e_select-4.15 { 001154 1 "SELECT i, j FROM c2 GROUP BY i%2" {2 1 1 0} 001155 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {2 1 1 0} 001156 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} 001157 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {} 001158 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" 001159 {2 4 beryllium 2 1 hydrogen 1 3 lithium} 001160 } 001161 001162 # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows 001163 # contributes a single row to the set of result rows. 001164 # 001165 # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the 001166 # DISTINCT keyword, the number of rows returned by an aggregate query 001167 # with a GROUP BY clause is the same as the number of groups of rows 001168 # produced by applying the GROUP BY and HAVING clauses to the filtered 001169 # input dataset. 001170 # 001171 do_select_tests e_select.4.16 -count { 001172 1 "SELECT i, j FROM c2 GROUP BY i%2" 2 001173 2 "SELECT i, j FROM c2 GROUP BY i" 9 001174 3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 001175 } 001176 001177 #------------------------------------------------------------------------- 001178 # The following tests attempt to verify statements made regarding the ALL 001179 # and DISTINCT keywords. 001180 # 001181 drop_all_tables 001182 do_execsql_test e_select-5.1.0 { 001183 CREATE TABLE h1(a, b); 001184 INSERT INTO h1 VALUES(1, 'one'); 001185 INSERT INTO h1 VALUES(1, 'I'); 001186 INSERT INTO h1 VALUES(1, 'i'); 001187 INSERT INTO h1 VALUES(4, 'four'); 001188 INSERT INTO h1 VALUES(4, 'IV'); 001189 INSERT INTO h1 VALUES(4, 'iv'); 001190 001191 CREATE TABLE h2(x COLLATE nocase); 001192 INSERT INTO h2 VALUES('One'); 001193 INSERT INTO h2 VALUES('Two'); 001194 INSERT INTO h2 VALUES('Three'); 001195 INSERT INTO h2 VALUES('Four'); 001196 INSERT INTO h2 VALUES('one'); 001197 INSERT INTO h2 VALUES('two'); 001198 INSERT INTO h2 VALUES('three'); 001199 INSERT INTO h2 VALUES('four'); 001200 001201 CREATE TABLE h3(c, d); 001202 INSERT INTO h3 VALUES(1, NULL); 001203 INSERT INTO h3 VALUES(2, NULL); 001204 INSERT INTO h3 VALUES(3, NULL); 001205 INSERT INTO h3 VALUES(4, '2'); 001206 INSERT INTO h3 VALUES(5, NULL); 001207 INSERT INTO h3 VALUES(6, '2,3'); 001208 INSERT INTO h3 VALUES(7, NULL); 001209 INSERT INTO h3 VALUES(8, '2,4'); 001210 INSERT INTO h3 VALUES(9, '3'); 001211 } {} 001212 001213 # EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may 001214 # follow the SELECT keyword in a simple SELECT statement. 001215 # 001216 do_select_tests e_select-5.1 { 001217 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} 001218 2 "SELECT DISTINCT a FROM h1" {1 4} 001219 } 001220 001221 # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then 001222 # the entire set of result rows are returned by the SELECT. 001223 # 001224 # EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present, 001225 # then the behavior is as if ALL were specified. 001226 # 001227 # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, 001228 # then duplicate rows are removed from the set of result rows before it 001229 # is returned. 001230 # 001231 # The three testable statements above are tested by e_select-5.2.*, 001232 # 5.3.* and 5.4.* respectively. 001233 # 001234 do_select_tests e_select-5 { 001235 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} 001236 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} 001237 001238 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} 001239 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} 001240 001241 4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four} 001242 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four} 001243 } 001244 001245 # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate 001246 # rows, two NULL values are considered to be equal. 001247 # 001248 do_select_tests e_select-5.5 { 001249 1 "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3} 001250 } 001251 001252 # EVIDENCE-OF: R-47709-27231 The usual rules apply for selecting a 001253 # collation sequence to compare text values. 001254 # 001255 do_select_tests e_select-5.6 { 001256 1 "SELECT DISTINCT b FROM h1" {one I i four IV iv} 001257 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {one I four IV} 001258 3 "SELECT DISTINCT x FROM h2" {One Two Three Four} 001259 4 "SELECT DISTINCT x COLLATE binary FROM h2" { 001260 One Two Three Four one two three four 001261 } 001262 } 001263 001264 #------------------------------------------------------------------------- 001265 # The following tests - e_select-7.* - test that statements made to do 001266 # with compound SELECT statements are correct. 001267 # 001268 001269 # EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent 001270 # SELECTs must return the same number of result columns. 001271 # 001272 # All the other tests in this section use compound SELECTs created 001273 # using component SELECTs that do return the same number of columns. 001274 # So the tests here just show that it is an error to attempt otherwise. 001275 # 001276 drop_all_tables 001277 do_execsql_test e_select-7.1.0 { 001278 CREATE TABLE j1(a, b, c); 001279 CREATE TABLE j2(e, f); 001280 CREATE TABLE j3(g); 001281 } {} 001282 do_select_tests e_select-7.1 -error { 001283 SELECTs to the left and right of %s do not have the same number of result columns 001284 } { 001285 1 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} 001286 2 "SELECT * FROM j1 UNION ALL SELECT * FROM j3" {{UNION ALL}} 001287 3 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} 001288 4 "SELECT a, b FROM j1 UNION ALL SELECT * FROM j3,j2" {{UNION ALL}} 001289 5 "SELECT * FROM j3,j2 UNION ALL SELECT a, b FROM j1" {{UNION ALL}} 001290 001291 6 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} 001292 7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION} 001293 8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} 001294 9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION} 001295 10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION} 001296 001297 11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} 001298 12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT} 001299 13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} 001300 14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT} 001301 15 "SELECT * FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT} 001302 001303 16 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} 001304 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j3" {EXCEPT} 001305 18 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} 001306 19 "SELECT a, b FROM j1 EXCEPT SELECT * FROM j3,j2" {EXCEPT} 001307 20 "SELECT * FROM j3,j2 EXCEPT SELECT a, b FROM j1" {EXCEPT} 001308 } 001309 001310 # EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must 001311 # be simple SELECT statements, they may not contain ORDER BY or LIMIT 001312 # clauses. 001313 # 001314 foreach {tn select op1 op2} { 001315 1 "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 001316 {ORDER BY} {UNION ALL} 001317 2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2" 001318 {ORDER BY} {UNION ALL} 001319 3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2" 001320 {ORDER BY} {UNION ALL} 001321 4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" 001322 LIMIT {UNION ALL} 001323 5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" 001324 LIMIT {UNION ALL} 001325 6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" 001326 LIMIT {UNION ALL} 001327 001328 7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" 001329 {ORDER BY} {UNION} 001330 8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2" 001331 {ORDER BY} {UNION} 001332 9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2" 001333 {ORDER BY} {UNION} 001334 10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" 001335 LIMIT {UNION} 001336 11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" 001337 LIMIT {UNION} 001338 12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" 001339 LIMIT {UNION} 001340 001341 13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" 001342 {ORDER BY} {EXCEPT} 001343 14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2" 001344 {ORDER BY} {EXCEPT} 001345 15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2" 001346 {ORDER BY} {EXCEPT} 001347 16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" 001348 LIMIT {EXCEPT} 001349 17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" 001350 LIMIT {EXCEPT} 001351 18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" 001352 LIMIT {EXCEPT} 001353 001354 19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" 001355 {ORDER BY} {INTERSECT} 001356 20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2" 001357 {ORDER BY} {INTERSECT} 001358 21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2" 001359 {ORDER BY} {INTERSECT} 001360 22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" 001361 LIMIT {INTERSECT} 001362 23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" 001363 LIMIT {INTERSECT} 001364 24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 001365 LIMIT {INTERSECT} 001366 } { 001367 set err "$op1 clause should come after $op2 not before" 001368 do_catchsql_test e_select-7.2.$tn $select [list 1 $err] 001369 } 001370 001371 # EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur 001372 # at the end of the entire compound SELECT, and then only if the final 001373 # element of the compound is not a VALUES clause. 001374 # 001375 foreach {tn select} { 001376 1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a" 001377 2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1" 001378 3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3" 001379 4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 001380 5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 001381 6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 001382 001383 7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a" 001384 8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1" 001385 8b "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1" 001386 9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3" 001387 10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 001388 11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 001389 12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 001390 001391 13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a" 001392 14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1" 001393 15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3" 001394 16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" 001395 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 001396 18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 001397 001398 19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a" 001399 20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1" 001400 21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3" 001401 22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 001402 23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 001403 24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 001404 } { 001405 do_test e_select-7.3.$tn { catch {execsql $select} msg } 0 001406 } 001407 foreach {tn select} { 001408 50 "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3" 001409 51 "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3" 001410 52 "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1" 001411 53 "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1" 001412 } { 001413 do_test e_select-7.3.$tn { catch {execsql $select} msg } 1 001414 } 001415 001416 # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL 001417 # operator returns all the rows from the SELECT to the left of the UNION 001418 # ALL operator, and all the rows from the SELECT to the right of it. 001419 # 001420 drop_all_tables 001421 do_execsql_test e_select-7.4.0 { 001422 CREATE TABLE q1(a TEXT, b INTEGER, c); 001423 CREATE TABLE q2(d NUMBER, e BLOB); 001424 CREATE TABLE q3(f REAL, g); 001425 001426 INSERT INTO q1 VALUES(16, -87.66, NULL); 001427 INSERT INTO q1 VALUES('legible', 94, -42.47); 001428 INSERT INTO q1 VALUES('beauty', 36, NULL); 001429 001430 INSERT INTO q2 VALUES('legible', 1); 001431 INSERT INTO q2 VALUES('beauty', 2); 001432 INSERT INTO q2 VALUES(-65.91, 4); 001433 INSERT INTO q2 VALUES('emanating', -16.56); 001434 001435 INSERT INTO q3 VALUES('beauty', 2); 001436 INSERT INTO q3 VALUES('beauty', 2); 001437 } {} 001438 do_select_tests e_select-7.4 { 001439 1 {SELECT a FROM q1 UNION ALL SELECT d FROM q2} 001440 {16 legible beauty legible beauty -65.91 emanating} 001441 001442 2 {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1} 001443 {16 -87.66 {} x legible 1} 001444 001445 3 {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 001446 {3 -16.56} 001447 001448 4 {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 001449 {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2} 001450 } 001451 001452 # EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as 001453 # UNION ALL, except that duplicate rows are removed from the final 001454 # result set. 001455 # 001456 do_select_tests e_select-7.5 { 001457 1 {SELECT a FROM q1 UNION SELECT d FROM q2} 001458 {-65.91 16 beauty emanating legible} 001459 001460 2 {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1} 001461 {16 -87.66 {} x legible 1} 001462 001463 3 {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 001464 {-16.56 3} 001465 001466 4 {SELECT * FROM q2 UNION SELECT * FROM q3} 001467 {-65.91 4 beauty 2 emanating -16.56 legible 1} 001468 } 001469 001470 # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the 001471 # intersection of the results of the left and right SELECTs. 001472 # 001473 do_select_tests e_select-7.6 { 001474 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} 001475 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} 001476 } 001477 001478 # EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of 001479 # rows returned by the left SELECT that are not also returned by the 001480 # right-hand SELECT. 001481 # 001482 do_select_tests e_select-7.7 { 001483 1 {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16} 001484 001485 2 {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 001486 {-65.91 4 emanating -16.56 legible 1} 001487 } 001488 001489 # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results 001490 # of INTERSECT and EXCEPT operators before the result set is returned. 001491 # 001492 do_select_tests e_select-7.8 { 001493 0 {SELECT * FROM q3} {beauty 2 beauty 2} 001494 001495 1 {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2} 001496 2 {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1} {beauty 2} 001497 } 001498 001499 # EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate 001500 # rows for the results of compound SELECT operators, NULL values are 001501 # considered equal to other NULL values and distinct from all non-NULL 001502 # values. 001503 # 001504 db nullvalue null 001505 do_select_tests e_select-7.9 { 001506 1 {SELECT NULL UNION ALL SELECT NULL} {null null} 001507 2 {SELECT NULL UNION SELECT NULL} {null} 001508 3 {SELECT NULL INTERSECT SELECT NULL} {null} 001509 4 {SELECT NULL EXCEPT SELECT NULL} {} 001510 001511 5 {SELECT NULL UNION ALL SELECT 'ab'} {null ab} 001512 6 {SELECT NULL UNION SELECT 'ab'} {null ab} 001513 7 {SELECT NULL INTERSECT SELECT 'ab'} {} 001514 8 {SELECT NULL EXCEPT SELECT 'ab'} {null} 001515 001516 9 {SELECT NULL UNION ALL SELECT 0} {null 0} 001517 10 {SELECT NULL UNION SELECT 0} {null 0} 001518 11 {SELECT NULL INTERSECT SELECT 0} {} 001519 12 {SELECT NULL EXCEPT SELECT 0} {null} 001520 001521 13 {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2} 001522 14 {SELECT c FROM q1 UNION SELECT g FROM q3} {null -42.47 2} 001523 15 {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {} 001524 16 {SELECT c FROM q1 EXCEPT SELECT g FROM q3} {null -42.47} 001525 } 001526 db nullvalue {} 001527 001528 # EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two 001529 # text values is determined as if the columns of the left and right-hand 001530 # SELECT statements were the left and right-hand operands of the equals 001531 # (=) operator, except that greater precedence is not assigned to a 001532 # collation sequence specified with the postfix COLLATE operator. 001533 # 001534 drop_all_tables 001535 do_execsql_test e_select-7.10.0 { 001536 CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c); 001537 INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); 001538 } {} 001539 do_select_tests e_select-7.10 { 001540 1 {SELECT 'abc' UNION SELECT 'ABC'} {ABC abc} 001541 2 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC} 001542 3 {SELECT 'abc' UNION SELECT 'ABC' COLLATE nocase} {ABC} 001543 4 {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc} 001544 5 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC} 001545 001546 6 {SELECT a FROM y1 UNION SELECT b FROM y1} {abc} 001547 7 {SELECT b FROM y1 UNION SELECT a FROM y1} {Abc abc} 001548 8 {SELECT a FROM y1 UNION SELECT c FROM y1} {aBC} 001549 001550 9 {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC} 001551 } 001552 001553 # EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to 001554 # any values when comparing rows as part of a compound SELECT. 001555 # 001556 drop_all_tables 001557 do_execsql_test e_select-7.10.0 { 001558 CREATE TABLE w1(a TEXT, b NUMBER); 001559 CREATE TABLE w2(a, b TEXT); 001560 001561 INSERT INTO w1 VALUES('1', 4.1); 001562 INSERT INTO w2 VALUES(1, 4.1); 001563 } {} 001564 001565 do_select_tests e_select-7.11 { 001566 1 { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1} 001567 2 { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1} 001568 3 { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1} 001569 4 { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1} 001570 001571 5 { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {} 001572 6 { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {} 001573 7 { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {} 001574 8 { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {} 001575 001576 9 { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1} 001577 10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1} 001578 11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1} 001579 12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1} 001580 } 001581 001582 001583 # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are 001584 # connected into a compound SELECT, they group from left to right. In 001585 # other words, if "A", "B" and "C" are all simple SELECT statements, (A 001586 # op B op C) is processed as ((A op B) op C). 001587 # 001588 # e_select-7.12.1: Precedence of UNION vs. INTERSECT 001589 # e_select-7.12.2: Precedence of UNION vs. UNION ALL 001590 # e_select-7.12.3: Precedence of UNION vs. EXCEPT 001591 # e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL 001592 # e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT 001593 # e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT 001594 # e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as 001595 # "(a EXCEPT b) EXCEPT c". 001596 # 001597 # The INTERSECT and EXCEPT operations are mutually commutative. So 001598 # the e_select-7.12.5 test cases do not prove very much. 001599 # 001600 drop_all_tables 001601 do_execsql_test e_select-7.12.0 { 001602 CREATE TABLE t1(x); 001603 INSERT INTO t1 VALUES(1); 001604 INSERT INTO t1 VALUES(2); 001605 INSERT INTO t1 VALUES(3); 001606 } {} 001607 foreach {tn select res} { 001608 1a "(1,2) INTERSECT (1) UNION (3)" {1 3} 001609 1b "(3) UNION (1,2) INTERSECT (1)" {1} 001610 001611 2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1} 001612 2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3} 001613 001614 3a "(1,2) UNION (3) EXCEPT (1)" {2 3} 001615 3b "(1,2) EXCEPT (3) UNION (1)" {1 2} 001616 001617 4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3} 001618 4b "(3) UNION (1,2) INTERSECT (1)" {1} 001619 001620 5a "(1,2) INTERSECT (2) EXCEPT (2)" {} 001621 5b "(2,3) EXCEPT (2) INTERSECT (2)" {} 001622 001623 6a "(2) UNION ALL (2) EXCEPT (2)" {} 001624 6b "(2) EXCEPT (2) UNION ALL (2)" {2} 001625 001626 7 "(2,3) EXCEPT (2) EXCEPT (3)" {} 001627 } { 001628 set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select] 001629 do_execsql_test e_select-7.12.$tn $select [list {*}$res] 001630 } 001631 001632 001633 #------------------------------------------------------------------------- 001634 # ORDER BY clauses 001635 # 001636 001637 drop_all_tables 001638 do_execsql_test e_select-8.1.0 { 001639 CREATE TABLE d1(x, y, z); 001640 001641 INSERT INTO d1 VALUES(1, 2, 3); 001642 INSERT INTO d1 VALUES(2, 5, -1); 001643 INSERT INTO d1 VALUES(1, 2, 8); 001644 INSERT INTO d1 VALUES(1, 2, 7); 001645 INSERT INTO d1 VALUES(2, 4, 93); 001646 INSERT INTO d1 VALUES(1, 2, -20); 001647 INSERT INTO d1 VALUES(1, 4, 93); 001648 INSERT INTO d1 VALUES(1, 5, -1); 001649 001650 CREATE TABLE d2(a, b); 001651 INSERT INTO d2 VALUES('gently', 'failings'); 001652 INSERT INTO d2 VALUES('commercials', 'bathrobe'); 001653 INSERT INTO d2 VALUES('iterate', 'sexton'); 001654 INSERT INTO d2 VALUES('babied', 'charitableness'); 001655 INSERT INTO d2 VALUES('solemnness', 'annexed'); 001656 INSERT INTO d2 VALUES('rejoicing', 'liabilities'); 001657 INSERT INTO d2 VALUES('pragmatist', 'guarded'); 001658 INSERT INTO d2 VALUES('barked', 'interrupted'); 001659 INSERT INTO d2 VALUES('reemphasizes', 'reply'); 001660 INSERT INTO d2 VALUES('lad', 'relenting'); 001661 } {} 001662 001663 # EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results 001664 # of evaluating the left-most expression in the ORDER BY list, then ties 001665 # are broken by evaluating the second left-most expression and so on. 001666 # 001667 do_select_tests e_select-8.1 { 001668 1 "SELECT * FROM d1 ORDER BY x, y, z" { 001669 1 2 -20 1 2 3 1 2 7 1 2 8 001670 1 4 93 1 5 -1 2 4 93 2 5 -1 001671 } 001672 } 001673 001674 # EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally 001675 # followed by one of the keywords ASC (smaller values are returned 001676 # first) or DESC (larger values are returned first). 001677 # 001678 # Test cases e_select-8.2.* test the above. 001679 # 001680 # EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows 001681 # are sorted in ascending (smaller values first) order by default. 001682 # 001683 # Test cases e_select-8.3.* test the above. All 8.3 test cases are 001684 # copies of 8.2 test cases with the explicit "ASC" removed. 001685 # 001686 do_select_tests e_select-8 { 001687 2.1 "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" { 001688 1 2 -20 1 2 3 1 2 7 1 2 8 001689 1 4 93 1 5 -1 2 4 93 2 5 -1 001690 } 001691 2.2 "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" { 001692 2 5 -1 2 4 93 1 5 -1 1 4 93 001693 1 2 8 1 2 7 1 2 3 1 2 -20 001694 } 001695 2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" { 001696 2 4 93 2 5 -1 1 2 8 1 2 7 001697 1 2 3 1 2 -20 1 4 93 1 5 -1 001698 } 001699 2.4 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" { 001700 2 4 93 2 5 -1 1 2 -20 1 2 3 001701 1 2 7 1 2 8 1 4 93 1 5 -1 001702 } 001703 001704 3.1 "SELECT * FROM d1 ORDER BY x, y, z" { 001705 1 2 -20 1 2 3 1 2 7 1 2 8 001706 1 4 93 1 5 -1 2 4 93 2 5 -1 001707 } 001708 3.3 "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" { 001709 2 4 93 2 5 -1 1 2 8 1 2 7 001710 1 2 3 1 2 -20 1 4 93 1 5 -1 001711 } 001712 3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" { 001713 2 4 93 2 5 -1 1 2 -20 1 2 3 001714 1 2 7 1 2 8 1 4 93 1 5 -1 001715 } 001716 } 001717 001718 # EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant 001719 # integer K then the expression is considered an alias for the K-th 001720 # column of the result set (columns are numbered from left to right 001721 # starting with 1). 001722 # 001723 do_select_tests e_select-8.4 { 001724 1 "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" { 001725 1 2 -20 1 2 3 1 2 7 1 2 8 001726 1 4 93 1 5 -1 2 4 93 2 5 -1 001727 } 001728 2 "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" { 001729 2 5 -1 2 4 93 1 5 -1 1 4 93 001730 1 2 8 1 2 7 1 2 3 1 2 -20 001731 } 001732 3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" { 001733 2 4 93 2 5 -1 1 2 8 1 2 7 001734 1 2 3 1 2 -20 1 4 93 1 5 -1 001735 } 001736 4 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" { 001737 2 4 93 2 5 -1 1 2 -20 1 2 3 001738 1 2 7 1 2 8 1 4 93 1 5 -1 001739 } 001740 5 "SELECT * FROM d1 ORDER BY 1, 2, 3" { 001741 1 2 -20 1 2 3 1 2 7 1 2 8 001742 1 4 93 1 5 -1 2 4 93 2 5 -1 001743 } 001744 6 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" { 001745 2 4 93 2 5 -1 1 2 8 1 2 7 001746 1 2 3 1 2 -20 1 4 93 1 5 -1 001747 } 001748 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { 001749 2 4 93 2 5 -1 1 2 -20 1 2 3 001750 1 2 7 1 2 8 1 4 93 1 5 -1 001751 } 001752 8 "SELECT z, x FROM d1 ORDER BY 2" { 001753 /# 1 # 1 # 1 # 1 001754 # 1 # 1 # 2 # 2/ 001755 } 001756 9 "SELECT z, x FROM d1 ORDER BY 1" { 001757 /-20 1 -1 # -1 # 3 1 001758 7 1 8 1 93 # 93 #/ 001759 } 001760 } 001761 001762 # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier 001763 # that corresponds to the alias of one of the output columns, then the 001764 # expression is considered an alias for that column. 001765 # 001766 do_select_tests e_select-8.5 { 001767 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { 001768 -19 0 0 4 8 9 94 94 001769 } 001770 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { 001771 94 94 9 8 4 0 0 -19 001772 } 001773 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { 001774 /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ 001775 } 001776 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { 001777 /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ 001778 } 001779 } 001780 001781 # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is 001782 # any other expression, it is evaluated and the returned value used to 001783 # order the output rows. 001784 # 001785 # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, 001786 # then an ORDER BY may contain any arbitrary expressions. 001787 # 001788 do_select_tests e_select-8.6 { 001789 1 "SELECT * FROM d1 ORDER BY x+y+z" { 001790 1 2 -20 1 5 -1 1 2 3 2 5 -1 001791 1 2 7 1 2 8 1 4 93 2 4 93 001792 } 001793 2 "SELECT * FROM d1 ORDER BY x*z" { 001794 1 2 -20 2 5 -1 1 5 -1 1 2 3 001795 1 2 7 1 2 8 1 4 93 2 4 93 001796 } 001797 3 "SELECT * FROM d1 ORDER BY y*z" { 001798 1 2 -20 2 5 -1 1 5 -1 1 2 3 001799 1 2 7 1 2 8 2 4 93 1 4 93 001800 } 001801 } 001802 001803 # EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound 001804 # SELECT, then ORDER BY expressions that are not aliases to output 001805 # columns must be exactly the same as an expression used as an output 001806 # column. 001807 # 001808 do_select_tests e_select-8.7.1 -error { 001809 %s ORDER BY term does not match any column in the result set 001810 } { 001811 1 "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" 1st 001812 2 "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd 001813 } 001814 001815 do_select_tests e_select-8.7.2 { 001816 1 "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" { 001817 -20 -2 -1 3 7 8 93 186 babied barked commercials gently 001818 iterate lad pragmatist reemphasizes rejoicing solemnness 001819 } 001820 2 "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" { 001821 1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 001822 babied charitableness barked interrupted commercials bathrobe gently 001823 failings iterate sexton lad relenting pragmatist guarded reemphasizes reply 001824 rejoicing liabilities solemnness annexed 001825 } 001826 } 001827 001828 do_execsql_test e_select-8.8.0 { 001829 CREATE TABLE d3(a); 001830 INSERT INTO d3 VALUES('text'); 001831 INSERT INTO d3 VALUES(14.1); 001832 INSERT INTO d3 VALUES(13); 001833 INSERT INTO d3 VALUES(X'78787878'); 001834 INSERT INTO d3 VALUES(15); 001835 INSERT INTO d3 VALUES(12.9); 001836 INSERT INTO d3 VALUES(null); 001837 001838 CREATE TABLE d4(x COLLATE nocase); 001839 INSERT INTO d4 VALUES('abc'); 001840 INSERT INTO d4 VALUES('ghi'); 001841 INSERT INTO d4 VALUES('DEF'); 001842 INSERT INTO d4 VALUES('JKL'); 001843 } {} 001844 001845 # EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values 001846 # are compared in the same way as for comparison expressions. 001847 # 001848 # The following tests verify that values of different types are sorted 001849 # correctly, and that mixed real and integer values are compared properly. 001850 # 001851 do_execsql_test e_select-8.8.1 { 001852 SELECT a FROM d3 ORDER BY a 001853 } {{} 12.9 13 14.1 15 text xxxx} 001854 do_execsql_test e_select-8.8.2 { 001855 SELECT a FROM d3 ORDER BY a DESC 001856 } {xxxx text 15 14.1 13 12.9 {}} 001857 001858 001859 # EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a 001860 # collation sequence using the postfix COLLATE operator, then the 001861 # specified collation sequence is used. 001862 # 001863 do_execsql_test e_select-8.9.1 { 001864 SELECT x FROM d4 ORDER BY 1 COLLATE binary 001865 } {DEF JKL abc ghi} 001866 do_execsql_test e_select-8.9.2 { 001867 SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase 001868 } {abc DEF ghi JKL} 001869 001870 # EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is 001871 # an alias to an expression that has been assigned a collation sequence 001872 # using the postfix COLLATE operator, then the collation sequence 001873 # assigned to the aliased expression is used. 001874 # 001875 # In the test 8.10.2, the only result-column expression has no alias. So the 001876 # ORDER BY expression is not a reference to it and therefore does not inherit 001877 # the collation sequence. In test 8.10.3, "x" is the alias (as well as the 001878 # column name), so the ORDER BY expression is interpreted as an alias and the 001879 # collation sequence attached to the result column is used for sorting. 001880 # 001881 do_execsql_test e_select-8.10.1 { 001882 SELECT x COLLATE binary FROM d4 ORDER BY 1 001883 } {DEF JKL abc ghi} 001884 do_execsql_test e_select-8.10.2 { 001885 SELECT x COLLATE binary FROM d4 ORDER BY x 001886 } {abc DEF ghi JKL} 001887 do_execsql_test e_select-8.10.3 { 001888 SELECT x COLLATE binary AS x FROM d4 ORDER BY x 001889 } {DEF JKL abc ghi} 001890 001891 # EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a 001892 # column or an alias of an expression that is a column, then the default 001893 # collation sequence for the column is used. 001894 # 001895 do_execsql_test e_select-8.11.1 { 001896 SELECT x AS y FROM d4 ORDER BY y 001897 } {abc DEF ghi JKL} 001898 do_execsql_test e_select-8.11.2 { 001899 SELECT x||'' FROM d4 ORDER BY x 001900 } {abc DEF ghi JKL} 001901 001902 # EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is 001903 # used. 001904 # 001905 do_execsql_test e_select-8.12.1 { 001906 SELECT x FROM d4 ORDER BY x||'' 001907 } {DEF JKL abc ghi} 001908 001909 # EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer 001910 # alias, then SQLite searches the left-most SELECT in the compound for a 001911 # result column that matches either the second or third rules above. If 001912 # a match is found, the search stops and the expression is handled as an 001913 # alias for the result column that it has been matched against. 001914 # Otherwise, the next SELECT to the right is tried, and so on. 001915 # 001916 do_execsql_test e_select-8.13.0 { 001917 CREATE TABLE d5(a, b); 001918 CREATE TABLE d6(c, d); 001919 CREATE TABLE d7(e, f); 001920 001921 INSERT INTO d5 VALUES(1, 'f'); 001922 INSERT INTO d6 VALUES(2, 'e'); 001923 INSERT INTO d7 VALUES(3, 'd'); 001924 INSERT INTO d5 VALUES(4, 'c'); 001925 INSERT INTO d6 VALUES(5, 'b'); 001926 INSERT INTO d7 VALUES(6, 'a'); 001927 001928 CREATE TABLE d8(x COLLATE nocase); 001929 CREATE TABLE d9(y COLLATE nocase); 001930 001931 INSERT INTO d8 VALUES('a'); 001932 INSERT INTO d9 VALUES('B'); 001933 INSERT INTO d8 VALUES('c'); 001934 INSERT INTO d9 VALUES('D'); 001935 } {} 001936 do_select_tests e_select-8.13 { 001937 1 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 001938 ORDER BY a 001939 } {1 2 3 4 5 6} 001940 2 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 001941 ORDER BY c 001942 } {1 2 3 4 5 6} 001943 3 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 001944 ORDER BY e 001945 } {1 2 3 4 5 6} 001946 4 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 001947 ORDER BY 1 001948 } {1 2 3 4 5 6} 001949 001950 5 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } 001951 {f 1 c 4 4 c 1 f} 001952 6 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } 001953 {f 1 c 4 4 c 1 f} 001954 001955 7 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } 001956 {1 f 4 c c 4 f 1} 001957 8 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } 001958 {1 f 4 c c 4 f 1} 001959 001960 9 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 001961 {f 2 c 5 4 c 1 f} 001962 10 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 001963 {f 2 c 5 4 c 1 f} 001964 001965 11 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 001966 {2 f 5 c c 5 f 2} 001967 12 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 001968 {2 f 5 c c 5 f 2} 001969 } 001970 001971 # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in 001972 # the result columns of any constituent SELECT, it is an error. 001973 # 001974 do_select_tests e_select-8.14 -error { 001975 %s ORDER BY term does not match any column in the result set 001976 } { 001977 1 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 } 1st 001978 2 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 } 2nd 001979 3 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' } 1st 001980 4 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah } 1st 001981 5 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d } 3rd 001982 6 { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b } 4th 001983 } 001984 001985 # EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is 001986 # processed separately and may be matched against result columns from 001987 # different SELECT statements in the compound. 001988 # 001989 do_select_tests e_select-8.15 { 001990 1 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d } 001991 {1 e 1 f 4 b 4 c} 001992 2 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b } 001993 {1 e 1 f 4 b 4 c} 001994 3 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 } 001995 {1 e 1 f 4 b 4 c} 001996 } 001997 001998 001999 #------------------------------------------------------------------------- 002000 # Tests related to statements made about the LIMIT/OFFSET clause. 002001 # 002002 do_execsql_test e_select-9.0 { 002003 CREATE TABLE f1(a, b); 002004 INSERT INTO f1 VALUES(26, 'z'); 002005 INSERT INTO f1 VALUES(25, 'y'); 002006 INSERT INTO f1 VALUES(24, 'x'); 002007 INSERT INTO f1 VALUES(23, 'w'); 002008 INSERT INTO f1 VALUES(22, 'v'); 002009 INSERT INTO f1 VALUES(21, 'u'); 002010 INSERT INTO f1 VALUES(20, 't'); 002011 INSERT INTO f1 VALUES(19, 's'); 002012 INSERT INTO f1 VALUES(18, 'r'); 002013 INSERT INTO f1 VALUES(17, 'q'); 002014 INSERT INTO f1 VALUES(16, 'p'); 002015 INSERT INTO f1 VALUES(15, 'o'); 002016 INSERT INTO f1 VALUES(14, 'n'); 002017 INSERT INTO f1 VALUES(13, 'm'); 002018 INSERT INTO f1 VALUES(12, 'l'); 002019 INSERT INTO f1 VALUES(11, 'k'); 002020 INSERT INTO f1 VALUES(10, 'j'); 002021 INSERT INTO f1 VALUES(9, 'i'); 002022 INSERT INTO f1 VALUES(8, 'h'); 002023 INSERT INTO f1 VALUES(7, 'g'); 002024 INSERT INTO f1 VALUES(6, 'f'); 002025 INSERT INTO f1 VALUES(5, 'e'); 002026 INSERT INTO f1 VALUES(4, 'd'); 002027 INSERT INTO f1 VALUES(3, 'c'); 002028 INSERT INTO f1 VALUES(2, 'b'); 002029 INSERT INTO f1 VALUES(1, 'a'); 002030 } {} 002031 002032 # EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the 002033 # LIMIT clause, so long as it evaluates to an integer or a value that 002034 # can be losslessly converted to an integer. 002035 # 002036 do_select_tests e_select-9.1 { 002037 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e} 002038 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e} 002039 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 002040 {a b c d e} 002041 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e} 002042 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e} 002043 } 002044 002045 # EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value 002046 # or any other value that cannot be losslessly converted to an integer, 002047 # an error is returned. 002048 # 002049 002050 do_select_tests e_select-9.2 -error "datatype mismatch" { 002051 1 { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {} 002052 2 { SELECT b FROM f1 ORDER BY a LIMIT NULL } {} 002053 3 { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {} 002054 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {} 002055 5 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {} 002056 } 002057 002058 # EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a 002059 # negative value, then there is no upper bound on the number of rows 002060 # returned. 002061 # 002062 do_select_tests e_select-9.4 { 002063 1 { SELECT b FROM f1 ORDER BY a LIMIT -1 } 002064 {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} 002065 2 { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 002066 {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} 002067 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 } 002068 {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} 002069 } 002070 002071 # EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N 002072 # rows of its result set only, where N is the value that the LIMIT 002073 # expression evaluates to. 002074 # 002075 do_select_tests e_select-9.5 { 002076 1 { SELECT b FROM f1 ORDER BY a LIMIT 0 } {} 002077 2 { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w} 002078 3 { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s} 002079 4 { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o} 002080 } 002081 002082 # EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return 002083 # less than N rows without a LIMIT clause, then the entire result set is 002084 # returned. 002085 # 002086 do_select_tests e_select-9.6 { 002087 1 { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z} 002088 2 { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5} 002089 } 002090 002091 002092 # EVIDENCE-OF: R-24188-24349 The expression attached to the optional 002093 # OFFSET clause that may follow a LIMIT clause must also evaluate to an 002094 # integer, or a value that can be losslessly converted to an integer. 002095 # 002096 foreach {tn select} { 002097 1 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } 002098 2 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } 002099 3 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } 002100 4 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } 002101 5 { SELECT b FROM f1 ORDER BY a 002102 LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) 002103 } 002104 } { 002105 do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}} 002106 } 002107 002108 # EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then 002109 # the first M rows are omitted from the result set returned by the 002110 # SELECT statement and the next N rows are returned, where M and N are 002111 # the values that the OFFSET and LIMIT clauses evaluate to, 002112 # respectively. 002113 # 002114 do_select_tests e_select-9.8 { 002115 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o} 002116 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o} 002117 3 { SELECT b FROM f1 ORDER BY a 002118 LIMIT (SELECT a FROM f1 WHERE b='j') 002119 OFFSET (SELECT a FROM f1 WHERE b='b') 002120 } {c d e f g h i j k l} 002121 4 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h} 002122 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e} 002123 6 { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {} 002124 7 { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r} 002125 } 002126 002127 # EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than 002128 # M+N rows if it did not have a LIMIT clause, then the first M rows are 002129 # skipped and the remaining rows (if any) are returned. 002130 # 002131 do_select_tests e_select-9.9 { 002132 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z} 002133 2 { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1} 002134 } 002135 002136 002137 # EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a 002138 # negative value, the results are the same as if it had evaluated to 002139 # zero. 002140 # 002141 do_select_tests e_select-9.10 { 002142 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e} 002143 2 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e} 002144 3 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 } {a b c d e} 002145 } 002146 002147 # EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the 002148 # LIMIT clause may specify two scalar expressions separated by a comma. 002149 # 002150 # EVIDENCE-OF: R-33788-46243 In this case, the first expression is used 002151 # as the OFFSET expression and the second as the LIMIT expression. 002152 # 002153 do_select_tests e_select-9.11 { 002154 1 { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o} 002155 2 { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o} 002156 3 { SELECT b FROM f1 ORDER BY a 002157 LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 002158 } {c d e f g h i j k l} 002159 4 { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h} 002160 5 { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e} 002161 6 { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {} 002162 7 { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r} 002163 002164 8 { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z} 002165 9 { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1} 002166 002167 10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e} 002168 11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e} 002169 12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e} 002170 } 002171 002172 finish_test