AryaWu/sqlite
0
1# 2010 July 162#3# The author disclaims copyright to this source code. In place of4# 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.tcl18 19ifcapable !compound {20 finish_test21 return22}23 24do_execsql_test e_select-1.0 {25 CREATE TABLE t1(a, b);26 INSERT INTO t1 VALUES('a', 'one');27 INSERT INTO t1 VALUES('b', 'two');28 INSERT INTO t1 VALUES('c', 'three');29 30 CREATE TABLE t2(a, b);31 INSERT INTO t2 VALUES('a', 'I');32 INSERT INTO t2 VALUES('b', 'II');33 INSERT INTO t2 VALUES('c', 'III');34 35 CREATE TABLE t3(a, c);36 INSERT INTO t3 VALUES('a', 1);37 INSERT INTO t3 VALUES('b', 2);38 39 CREATE TABLE t4(a, c);40 INSERT INTO t4 VALUES('a', NULL);41 INSERT INTO t4 VALUES('b', 2);42} {}43set t1_cross_t2 [list \44 a one a I a one b II \45 a one c III b two a I \46 b two b II b two c III \47 c three a I c three b II \48 c three c III \49]50set t1_cross_t1 [list \51 a one a one a one b two \52 a one c three b two a one \53 b two b two b two c three \54 c three a one c three b two \55 c three c three \56]57 58 59# This proc is a specialized version of [do_execsql_test].60#61# The second argument to this proc must be a SELECT statement that 62# features a cross join of some time. Instead of the usual ",", 63# "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be 64# substituted.65#66# This test runs the SELECT three times - once with:67#68# * s/%JOIN%/,/69# * s/%JOIN%/JOIN/70# * s/%JOIN%/INNER JOIN/71# * s/%JOIN%/CROSS JOIN/72#73# and checks that each time the results of the SELECT are $res.74#75proc do_join_test {tn select res} {76 foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] {77 set S [string map [list %JOIN% $joinop] $select]78 uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]79 }80}81 82#-------------------------------------------------------------------------83# The following tests check that all paths on the syntax diagrams on84# the lang_select.html page may be taken.85#86# -- syntax diagram join-constraint87#88do_join_test e_select-0.1.1 {89 SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a)90} {3}91do_join_test e_select-0.1.2 {92 SELECT count(*) FROM t1 %JOIN% t2 USING (a)93} {3}94do_join_test e_select-0.1.3 {95 SELECT count(*) FROM t1 %JOIN% t296} {9}97do_catchsql_test e_select-0.1.4 {98 SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a)99} {1 {near "USING": syntax error}}100do_catchsql_test e_select-0.1.5 {101 SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a)102} {1 {near "ON": syntax error}}103 104# -- syntax diagram select-core105#106# 0: SELECT ...107# 1: SELECT DISTINCT ...108# 2: SELECT ALL ...109#110# 0: No FROM clause111# 1: Has FROM clause112#113# 0: No WHERE clause114# 1: Has WHERE clause115#116# 0: No GROUP BY clause117# 1: Has GROUP BY clause118# 2: Has GROUP BY and HAVING clauses119#120do_select_tests e_select-0.2 {121 0000.1 "SELECT 1, 2, 3 " {1 2 3}122 1000.1 "SELECT DISTINCT 1, 2, 3 " {1 2 3}123 2000.1 "SELECT ALL 1, 2, 3 " {1 2 3}124 125 0100.1 "SELECT a, b, a||b FROM t1 " {126 a one aone b two btwo c three cthree127 }128 1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " {129 a one aone b two btwo c three cthree130 }131 1200.1 "SELECT ALL a, b, a||b FROM t1 " {132 a one aone b two btwo c three cthree133 }134 135 0010.1 "SELECT 1, 2, 3 WHERE 1 " {1 2 3}136 0010.2 "SELECT 1, 2, 3 WHERE 0 " {}137 0010.3 "SELECT 1, 2, 3 WHERE NULL " {}138 139 1010.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}140 141 2010.1 "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}142 143 0110.1 "SELECT a, b, a||b FROM t1 WHERE a!='x' " {144 a one aone b two btwo c three cthree145 }146 0110.2 "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}147 148 1110.1 "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {149 a one aone b two btwo c three cthree150 }151 152 2110.0 "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}153 154 0001.1 "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}155 0002.1 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}156 0002.2 "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}157 158 1001.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}159 1002.1 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}160 1002.2 "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}161 162 2001.1 "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}163 2002.1 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}164 2002.2 "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}165 166 0101.1 "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}167 0102.1 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {168 1 a 1 c 1 b169 }170 0102.2 "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" {}171 172 1101.1 "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}173 1102.1 "SELECT DISTINCT count(*), max(a) FROM t1 174 GROUP BY b HAVING count(*)=1" {175 1 a 1 c 1 b176 }177 1102.2 "SELECT DISTINCT count(*), max(a) FROM t1 178 GROUP BY b HAVING count(*)=2" {}179 180 2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}181 2102.1 "SELECT ALL count(*), max(a) FROM t1 182 GROUP BY b HAVING count(*)=1" {183 1 a 1 c 1 b184 }185 2102.2 "SELECT ALL count(*), max(a) FROM t1 186 GROUP BY b HAVING count(*)=2" {}187 188 0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}189 0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}190 0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}191 192 1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}193 1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 194 {1 2 3}195 1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}196 197 2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}198 2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}199 2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}200 201 0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}202 0112.1 "SELECT count(*), max(a) FROM t1 203 WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}204 0112.2 "SELECT count(*), max(a) FROM t1 205 WHERE 0 GROUP BY b HAVING count(*)=2" {}206 1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 207 {1 a 1 b}208 1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'209 GROUP BY b HAVING count(*)=1" {210 1 c 1 b211 }212 1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0213 GROUP BY b HAVING count(*)=2" {}214 215 2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 216 {1 c 1 b}217 2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'218 GROUP BY b HAVING count(*)=1" {219 1 a 1 c220 }221 2112.2 "SELECT ALL count(*), max(a) FROM t1 222 WHERE 0 GROUP BY b HAVING count(*)=2" {}223}224 225 226# -- syntax diagram result-column227#228do_select_tests e_select-0.3 {229 1 "SELECT * FROM t1" {a one b two c three}230 2 "SELECT t1.* FROM t1" {a one b two c three}231 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}232 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}233 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}234}235 236# -- syntax diagram join-source237#238# -- syntax diagram join-op239#240do_select_tests e_select-0.4 {241 1 "SELECT t1.rowid FROM t1" {1 2 3}242 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}243 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}244 245 4 "SELECT t1.rowid FROM t1" {1 2 3}246 5 "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}247 6 "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 248 {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}249 250 7 "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}251 8 "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}252 9 "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3}253 10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}254 11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}255 256 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}257 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}258 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3}259 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}260 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}261}262 263# -- syntax diagram compound-operator264#265do_select_tests e_select-0.5 {266 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}267 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4}268 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}269 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2}270}271 272# -- syntax diagram ordering-term273#274do_select_tests e_select-0.6 {275 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob}276 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}277 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob}278 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea}279}280 281# -- syntax diagram select-stmt282#283do_select_tests e_select-0.7 {284 1 "SELECT * FROM t1" {a one b two c three}285 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two}286 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}287 288 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three}289 5 "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}290 6 "SELECT * FROM t1 LIMIT 10, 5" {}291 292 7 "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three}293 8 "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {}294 9 "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {}295 296 10 "SELECT * FROM t1 UNION SELECT b, a FROM t1" 297 {a one b two c three one a three c two b}298 11 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 299 {one a two b three c a one c three b two}300 12 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 301 {one a two b three c a one c three b two}302 13 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 303 {a one b two c three one a three c two b}304 14 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 305 {two b}306 15 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 307 {}308 16 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 309 {a one b two c three one a three c two b}310 17 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 311 {b two}312 18 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 313 {}314}315 316#-------------------------------------------------------------------------317# The following tests focus on FROM clause (join) processing.318#319 320# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple321# SELECT statement, then the input data is implicitly a single row zero322# columns wide323#324do_select_tests e_select-1.1 {325 1 "SELECT 'abc'" {abc}326 2 "SELECT 'abc' WHERE NULL" {}327 3 "SELECT NULL" {{}}328 4 "SELECT count(*)" {1}329 5 "SELECT count(*) WHERE 0" {0}330 6 "SELECT count(*) WHERE 1" {1}331}332 333# EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery334# in the FROM clause, then the input data used by the SELECT statement335# is the contents of the named table.336#337# The results of the SELECT queries suggest that they are operating on the338# contents of the table 'xx'.339#340do_execsql_test e_select-1.2.0 {341 CREATE TABLE xx(x, y);342 INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');343 INSERT INTO xx VALUES(NULL, -16.87);344 INSERT INTO xx VALUES(-17.89, 'linguistically');345} {}346do_select_tests e_select-1.2 {347 1 "SELECT quote(x), quote(y) FROM xx" {348 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 349 NULL -16.87 350 -17.89 'linguistically' 351 }352 353 2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}354 3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87}355}356 357# EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery358# in FROM clause then the contents of all tables and/or subqueries are359# joined into a single dataset for the simple SELECT statement to360# operate on.361#362# There are more detailed tests for subsequent requirements that add 363# more detail to this idea. We just add a single test that shows that364# data is coming from each of the three tables following the FROM clause365# here to show that the statement, vague as it is, is not incorrect.366#367do_select_tests e_select-1.3 {368 1 "SELECT * FROM t1, t2, t3" {369 a one a I a 1 a one a I b 2 a one b II a 1 370 a one b II b 2 a one c III a 1 a one c III b 2 371 b two a I a 1 b two a I b 2 b two b II a 1 372 b two b II b 2 b two c III a 1 b two c III b 2 373 c three a I a 1 c three a I b 2 c three b II a 1 374 c three b II b 2 c three c III a 1 c three c III b 2375 }376}377 378#379# The following block of tests - e_select-1.4.* - test that the description380# of cartesian joins in the SELECT documentation is consistent with SQLite.381# In doing so, we test the following three requirements as a side-effect:382#383# EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN",384# "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING385# clause, then the result of the join is simply the cartesian product of386# the left and right-hand datasets.387#388# The tests are built on this assertion. Really, they test that the output389# of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result390# of calculating the cartesian product of the left and right-hand datasets. 391#392# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER393# JOIN", "JOIN" and "," join operators.394#395# EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the396# same result as the "INNER JOIN", "JOIN" and "," operators397#398# All tests are run 4 times, with the only difference in each run being399# which of the 4 equivalent cartesian product join operators are used.400# Since the output data is the same in all cases, we consider that this401# qualifies as testing the two statements above.402#403do_execsql_test e_select-1.4.0 {404 CREATE TABLE x1(a, b);405 CREATE TABLE x2(c, d, e);406 CREATE TABLE x3(f, g, h, i);407 408 -- x1: 3 rows, 2 columns409 INSERT INTO x1 VALUES(24, 'converging');410 INSERT INTO x1 VALUES(NULL, X'CB71');411 INSERT INTO x1 VALUES('blonds', 'proprietary');412 413 -- x2: 2 rows, 3 columns414 INSERT INTO x2 VALUES(-60.06, NULL, NULL);415 INSERT INTO x2 VALUES(-58, NULL, 1.21);416 417 -- x3: 5 rows, 4 columns418 INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1);419 INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified');420 INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL);421 INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3);422 INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL);423} {}424 425# EVIDENCE-OF: R-59089-25828 The columns of the cartesian product426# dataset are, in order, all the columns of the left-hand dataset427# followed by all the columns of the right-hand dataset.428#429do_join_test e_select-1.4.1.1 {430 SELECT * FROM x1 %JOIN% x2 LIMIT 1431} [concat {24 converging} {-60.06 {} {}}]432 433do_join_test e_select-1.4.1.2 {434 SELECT * FROM x2 %JOIN% x1 LIMIT 1435} [concat {-60.06 {} {}} {24 converging}]436 437do_join_test e_select-1.4.1.3 {438 SELECT * FROM x3 %JOIN% x2 LIMIT 1439} [concat {-39.24 {} encompass -1} {-60.06 {} {}}]440 441do_join_test e_select-1.4.1.4 {442 SELECT * FROM x2 %JOIN% x3 LIMIT 1443} [concat {-60.06 {} {}} {-39.24 {} encompass -1}]444 445# EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product446# dataset formed by combining each unique combination of a row from the447# left-hand and right-hand datasets.448#449do_join_test e_select-1.4.2.1 {450 SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f451} [list -60.06 {} {} -39.24 {} encompass -1 \452 -60.06 {} {} alerting {} -93.79 {} \453 -60.06 {} {} coldest -96 dramatists 82.3 \454 -60.06 {} {} conducting -87.24 37.56 {} \455 -60.06 {} {} presenting 51 reformation dignified \456 -58 {} 1.21 -39.24 {} encompass -1 \457 -58 {} 1.21 alerting {} -93.79 {} \458 -58 {} 1.21 coldest -96 dramatists 82.3 \459 -58 {} 1.21 conducting -87.24 37.56 {} \460 -58 {} 1.21 presenting 51 reformation dignified \461]462# TODO: Come back and add a few more like the above.463 464# EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset465# consists of Nleft rows of Mleft columns, and the right-hand dataset of466# Nright rows of Mright columns, then the cartesian product is a dataset467# of Nleft×Nright rows, each containing Mleft+Mright columns.468#469# x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3)470do_join_test e_select-1.4.3.1 { 471 SELECT count(*) FROM x1 %JOIN% x2 472} [expr 3*2]473do_test e_select-1.4.3.2 { 474 expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6}475} [expr 2+3]476 477# x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4)478do_join_test e_select-1.4.3.3 { 479 SELECT count(*) FROM x2 %JOIN% x3 480} [expr 2*5]481do_test e_select-1.4.3.4 { 482 expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10}483} [expr 3+4]484 485# x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2)486do_join_test e_select-1.4.3.5 { 487 SELECT count(*) FROM x3 %JOIN% x1 488} [expr 5*3]489do_test e_select-1.4.3.6 { 490 expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15}491} [expr 4+2]492 493# x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4)494do_join_test e_select-1.4.3.7 { 495 SELECT count(*) FROM x3 %JOIN% x3 496} [expr 5*5]497do_test e_select-1.4.3.8 { 498 expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}499} [expr 4+4]500 501# Some extra cartesian product tests using tables t1 and t2.502#503do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2504do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1505 506do_select_tests e_select-1.4.5 [list \507 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \508 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \509 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \510 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \511]512 513# EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON514# expression is evaluated for each row of the cartesian product as a515# boolean expression. Only rows for which the expression evaluates to516# true are included from the dataset.517#518foreach {tn select res} [list \519 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \520 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \521 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \522 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \523 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \524 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \525 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \526 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \527 \528 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \529 {one I two II three III} \530 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \531 {one I one II one III} \532 11 { SELECT t1.b, t2.b 533 FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \534 {two I two II two III three I three II three III} \535] {536 do_join_test e_select-1.3.$tn $select $res537}538 539# EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the540# column names specified must exist in the datasets to both the left and541# right of the join-operator.542#543do_select_tests e_select-1.4 -error {544 cannot join using column %s - column not present in both tables545} {546 1 { SELECT * FROM t1, t3 USING (b) } "b"547 2 { SELECT * FROM t3, t1 USING (c) } "c"548 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a"549} 550 551# EVIDENCE-OF: R-22776-52830 For each pair of named columns, the552# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian553# product as a boolean expression. Only rows for which all such554# expressions evaluates to true are included from the result set.555#556do_select_tests e_select-1.5 {557 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2}558 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2}559} 560 561# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a562# USING clause, the normal rules for handling affinities, collation563# sequences and NULL values in comparisons apply.564#565# EVIDENCE-OF: R-38422-04402 The column from the dataset on the566# left-hand side of the join-operator is considered to be on the567# left-hand side of the comparison operator (=) for the purposes of568# collation sequence and affinity precedence.569#570do_execsql_test e_select-1.6.0 {571 CREATE TABLE t5(a COLLATE nocase, b COLLATE binary);572 INSERT INTO t5 VALUES('AA', 'cc');573 INSERT INTO t5 VALUES('BB', 'dd');574 INSERT INTO t5 VALUES(NULL, NULL);575 CREATE TABLE t6(a COLLATE binary, b COLLATE nocase);576 INSERT INTO t6 VALUES('aa', 'cc');577 INSERT INTO t6 VALUES('bb', 'DD');578 INSERT INTO t6 VALUES(NULL, NULL);579} {}580foreach {tn select res} {581 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD}582 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {}583 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } 584 {aa cc cc bb DD dd}585 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc}586 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {}587} {588 do_join_test e_select-1.6.$tn $select $res589}590 591# EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a592# USING clause, the column from the right-hand dataset is omitted from593# the joined dataset.594#595# EVIDENCE-OF: R-56132-15700 This is the only difference between a USING596# clause and its equivalent ON constraint.597#598foreach {tn select res} {599 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } 600 {a one I b two II c three III}601 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) }602 {a one a I b two b II c three c III}603 604 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } 605 {a 1 {} b 2 2}606 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } 607 {a 1 a {} b 2 b 2}608 609 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2}610 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2}611 612 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 613 %JOIN% t5 USING (a) } 614 {aa cc cc bb DD dd}615 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x616 %JOIN% t5 ON (x.a=t5.a) } 617 {aa cc AA cc bb DD BB dd}618} {619 do_join_test e_select-1.7.$tn $select $res620}621 622# EVIDENCE-OF: R-24610-05866 If the join-operator is a "LEFT JOIN" or623# "LEFT OUTER JOIN", then after the ON or USING filtering clauses have624# been applied, an extra row is added to the output for each row in the625# original left-hand input dataset that does not match any row in the626# right-hand dataset.627#628do_execsql_test e_select-1.8.0 {629 CREATE TABLE t7(a, b, c);630 CREATE TABLE t8(a, d, e);631 632 INSERT INTO t7 VALUES('x', 'ex', 24);633 INSERT INTO t7 VALUES('y', 'why', 25);634 635 INSERT INTO t8 VALUES('x', 'abc', 24);636 INSERT INTO t8 VALUES('z', 'ghi', 26);637} {}638 639do_select_tests e_select-1.8 {640 1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1}641 1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2}642 2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1}643 2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2}644}645 646 647# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the648# columns that would normally contain values copied from the right-hand649# input dataset.650#651do_select_tests e_select-1.9 {652 1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}653 1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 654 {x ex 24 x abc 24 y why 25 {} {} {}}655 2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}656 2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}657}658 659# EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the660# join-operator then an implicit USING clause is added to the661# join-constraints. The implicit USING clause contains each of the662# column names that appear in both the left and right-hand input663# datasets.664#665do_select_tests e_select-1-10 {666 1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}667 1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24}668 669 2a "SELECT * FROM t8 JOIN t7 USING (a)" {x abc 24 ex 24}670 2b "SELECT * FROM t8 NATURAL JOIN t7" {x abc 24 ex 24}671 672 3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}673 3b "SELECT * FROM t7 NATURAL LEFT JOIN t8" {x ex 24 abc 24 y why 25 {} {}}674 675 4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)" {x abc 24 ex 24 z ghi 26 {} {}}676 4b "SELECT * FROM t8 NATURAL LEFT JOIN t7" {x abc 24 ex 24 z ghi 26 {} {}}677 678 5a "SELECT * FROM t3 JOIN t4 USING (a,c)" {b 2}679 5b "SELECT * FROM t3 NATURAL JOIN t4" {b 2}680 681 6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2}682 6b "SELECT * FROM t3 NATURAL LEFT JOIN t4" {a 1 b 2}683} 684 685# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets686# feature no common column names, then the NATURAL keyword has no effect687# on the results of the join.688#689do_execsql_test e_select-1.11.0 {690 CREATE TABLE t10(x, y);691 INSERT INTO t10 VALUES(1, 'true');692 INSERT INTO t10 VALUES(0, 'false');693} {}694do_select_tests e_select-1-11 {695 1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}696 1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}697}698 699# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a700# join that specifies the NATURAL keyword.701#702foreach {tn sql} {703 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}704 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)}705 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)}706} {707 do_catchsql_test e_select-1.12.$tn "708 $sql709 " {1 {a NATURAL join may not have an ON or USING clause}}710}711 712#-------------------------------------------------------------------------713# The next block of tests - e_select-3.* - concentrate on verifying 714# statements made regarding WHERE clause processing.715#716drop_all_tables717do_execsql_test e_select-3.0 {718 CREATE TABLE x1(k, x, y, z);719 INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43);720 INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81);721 INSERT INTO x1 VALUES(3, -22, -27.57, NULL);722 INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky');723 INSERT INTO x1 VALUES(5, NULL, 96.28, NULL);724 INSERT INTO x1 VALUES(6, 0, 1, 2);725 726 CREATE TABLE x2(k, x, y2);727 INSERT INTO x2 VALUES(1, 50, X'B82838');728 INSERT INTO x2 VALUES(5, 84.79, 65.88);729 INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393');730 INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized');731} {}732 733# EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE734# expression is evaluated for each row in the input data as a boolean735# expression. Only rows for which the WHERE clause expression evaluates736# to true are included from the dataset before continuing.737#738do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3}739do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6}740do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6}741do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6}742do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5}743do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6}744 745do_execsql_test e_select-3.2.1a {746 SELECT k FROM x1 LEFT JOIN x2 USING(k)747} {1 2 3 4 5 6}748do_execsql_test e_select-3.2.1b {749 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k ORDER BY +k750} {1 3 5}751do_execsql_test e_select-3.2.2 {752 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL753} {2 4 6}754 755do_execsql_test e_select-3.2.3 {756 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k757} {3}758do_execsql_test e_select-3.2.4 {759 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3760} {}761 762#-------------------------------------------------------------------------763# Tests below this point are focused on verifying the testable statements764# related to caculating the result rows of a simple SELECT statement.765#766 767drop_all_tables768do_execsql_test e_select-4.0 {769 CREATE TABLE z1(a, b, c);770 CREATE TABLE z2(d, e);771 CREATE TABLE z3(a, b);772 773 INSERT INTO z1 VALUES(51.65, -59.58, 'belfries');774 INSERT INTO z1 VALUES(-5, NULL, 75);775 INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters');776 INSERT INTO z1 VALUES(NULL, 67, 'quartets');777 INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen');778 INSERT INTO z1 VALUES(63, 'born', -26);779 780 INSERT INTO z2 VALUES(NULL, 21);781 INSERT INTO z2 VALUES(36, 6);782 783 INSERT INTO z3 VALUES('subsistence', 'gauze');784 INSERT INTO z3 VALUES(49.17, -67);785} {}786 787# EVIDENCE-OF: R-36327-17224 If a result expression is the special788# expression "*" then all columns in the input data are substituted for789# that one expression.790#791# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table792# or subquery in the FROM clause followed by ".*" then all columns from793# the named table or subquery are substituted for the single expression.794#795do_select_tests e_select-4.1 {796 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries}797 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21}798 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries}799 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21}800 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}801 802 6 "SELECT count(*), * FROM z1" {6 51.65 -59.58 belfries}803 7 "SELECT max(a), * FROM z1" {63 63 born -26}804 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5}805 806 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 807 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21808 }809 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { 810 51.65 -59.58 belfries 51.65 -59.58 belfries811 }812}813 814# EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*"815# expression in any context other than a result expression list.816#817# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or818# "alias.*" expression in a simple SELECT query that does not have a819# FROM clause.820#821foreach {tn select err} {822 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error}823 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error}824 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error}825 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error}826 827 2.1 "SELECT *" {no tables specified}828 2.2 "SELECT * WHERE 1" {no tables specified}829 2.3 "SELECT * WHERE 0" {no tables specified}830 2.4 "SELECT count(*), *" {no tables specified}831} {832 do_catchsql_test e_select-4.2.$tn $select [list 1 $err]833}834 835# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned836# by a simple SELECT statement is equal to the number of expressions in837# the result expression list after substitution of * and alias.*838# expressions.839#840foreach {tn select nCol} {841 1 "SELECT * FROM z1" 3842 2 "SELECT * FROM z1 NATURAL JOIN z3" 3843 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3844 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2845 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5846 6 "SELECT 1, 2, z1.* FROM z1" 5847 7 "SELECT a, *, b, c FROM z1" 6848} {849 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]850 do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol851 sqlite3_finalize $::stmt852}853 854 855 856# In lang_select.html, a non-aggregate query is defined as any simple SELECT857# that has no GROUP BY clause and no aggregate expressions in the result858# expression list. Other queries are aggregate queries. Test cases859# e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of860# simple SELECT that is different for aggregate and non-aggregate queries861# verify (in a way) that these definitions are consistent:862#863# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate864# query if it contains either a GROUP BY clause or one or more aggregate865# functions in the result-set.866#867# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no868# aggregate functions or a GROUP BY clause, it is a non-aggregate query.869#870 871# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate872# query, then each expression in the result expression list is evaluated873# for each row in the dataset filtered by the WHERE clause.874#875do_select_tests e_select-4.4 {876 1 "SELECT a, b FROM z1"877 {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}878 879 2 "SELECT a IS NULL, b+1, * FROM z1" {880 0 -58.58 51.65 -59.58 belfries881 0 {} -5 {} 75 882 0 -22.18 -2.2 -23.18 suiters883 1 68 {} 67 quartets 884 0 -31.3 -1.04 -32.3 aspen885 0 1 63 born -26886 }887 888 3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366}889}890 891 892# Test cases e_select-4.5.* and e_select-4.6.* together show that:893#894# EVIDENCE-OF: R-51988-01124 The single row of result-set data created895# by evaluating the aggregate and non-aggregate expressions in the896# result-set forms the result of an aggregate query without a GROUP BY897# clause.898#899 900# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate901# query without a GROUP BY clause, then each aggregate expression in the902# result-set is evaluated once across the entire dataset.903#904do_select_tests e_select-4.5 {905 1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born}906 2 "SELECT count(*), max(1)" {1 1}907 908 3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06}909 4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06}910 5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}911}912 913# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the914# result-set is evaluated once for an arbitrarily selected row of the915# dataset.916#917# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used918# for each non-aggregate expression.919#920# Note: The results of many of the queries in this block of tests are921# technically undefined, as the documentation does not specify which row922# SQLite will arbitrarily select to use for the evaluation of the923# non-aggregate expressions.924#925drop_all_tables926do_execsql_test e_select-4.6.0 {927 CREATE TABLE a1(one PRIMARY KEY, two);928 INSERT INTO a1 VALUES(1, 1);929 INSERT INTO a1 VALUES(2, 3);930 INSERT INTO a1 VALUES(3, 6);931 INSERT INTO a1 VALUES(4, 10);932 933 CREATE TABLE a2(one PRIMARY KEY, three);934 INSERT INTO a2 VALUES(1, 1);935 INSERT INTO a2 VALUES(3, 2);936 INSERT INTO a2 VALUES(6, 3);937 INSERT INTO a2 VALUES(10, 4);938} {}939do_select_tests e_select-4.6 {940 1 "SELECT one, two, count(*) FROM a1" {1 1 4}941 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {1 1 2}942 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 943 4 "SELECT *, count(*) FROM a1 JOIN a2" {1 1 1 1 16}944 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3}945 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {1 1 1 3}946 7 "SELECT string_agg(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 1 1}947}948 949# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then950# each non-aggregate expression is evaluated against a row consisting951# entirely of NULL values.952#953do_select_tests e_select-4.7 {954 1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0}955 2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}}956 3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {957 1 1 1958 }959} 960 961# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY962# clause always returns exactly one row of data, even if there are zero963# rows of input data.964#965foreach {tn select} {966 8.1 "SELECT count(*) FROM a1"967 8.2 "SELECT count(*) FROM a1 WHERE 0"968 8.3 "SELECT count(*) FROM a1 WHERE 1"969 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1"970 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0"971} {972 # Set $nRow to the number of rows returned by $select:973 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]974 set nRow 0975 while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow }976 set rc [sqlite3_finalize $::stmt]977 978 # Test that $nRow==1 and that statement execution was successful 979 # (rc==SQLITE_OK).980 do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1}981}982 983drop_all_tables984do_execsql_test e_select-4.9.0 {985 CREATE TABLE b1(one PRIMARY KEY, two);986 INSERT INTO b1 VALUES(1, 'o');987 INSERT INTO b1 VALUES(4, 'f');988 INSERT INTO b1 VALUES(3, 't');989 INSERT INTO b1 VALUES(2, 't');990 INSERT INTO b1 VALUES(5, 'f');991 INSERT INTO b1 VALUES(7, 's');992 INSERT INTO b1 VALUES(6, 's');993 994 CREATE TABLE b2(x, y);995 INSERT INTO b2 VALUES(NULL, 0);996 INSERT INTO b2 VALUES(NULL, 1);997 INSERT INTO b2 VALUES('xyz', 2);998 INSERT INTO b2 VALUES('abc', 3);999 INSERT INTO b2 VALUES('xyz', 4);1000 1001 CREATE TABLE b3(a COLLATE nocase, b COLLATE binary);1002 INSERT INTO b3 VALUES('abc', 'abc');1003 INSERT INTO b3 VALUES('aBC', 'aBC');1004 INSERT INTO b3 VALUES('Def', 'Def');1005 INSERT INTO b3 VALUES('dEF', 'dEF');1006} {}1007 1008# EVIDENCE-OF: R-40855-36147 If the SELECT statement is an aggregate1009# query with a GROUP BY clause, then each of the expressions specified1010# as part of the GROUP BY clause is evaluated for each row of the1011# dataset according to the processing rules stated below for ORDER BY1012# expressions. Each row is then assigned to a "group" based on the1013# results; rows for which the results of evaluating the GROUP BY1014# expressions are the same get assigned to the same group.1015#1016# These tests also show that the following is not untrue:1017#1018# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do1019# not have to be expressions that appear in the result.1020#1021do_select_tests e_select-4.9 {1022 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" {1023 /#,# f 1 o #,# s #,# t/1024 }1025 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {1026 1,2,3,4 10 5,6,7 181027 }1028 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {1029 4 1,5 2,6 3,71030 }1031 4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {1032 4,3,5,7,6 1,21033 }1034}1035 1036# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL1037# values are considered equal.1038#1039do_select_tests e_select-4.10 {1040 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/}1041 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}1042} 1043 1044# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation1045# sequence with which to compare text values apply when evaluating1046# expressions in a GROUP BY clause.1047#1048do_select_tests e_select-4.11 {1049 1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1}1050 2 "SELECT count(*) FROM b3 GROUP BY a" {2 2}1051 3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1}1052 4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2}1053 5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1}1054 6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1}1055}1056 1057# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may1058# not be aggregate expressions.1059#1060foreach {tn select} {1061 12.1 "SELECT * FROM b3 GROUP BY count(*)"1062 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)"1063 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)"1064} {1065 set res {1 {aggregate functions are not allowed in the GROUP BY clause}}1066 do_catchsql_test e_select-4.$tn $select $res1067}1068 1069# EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is1070# evaluated once for each group of rows as a boolean expression. If the1071# result of evaluating the HAVING clause is false, the group is1072# discarded.1073#1074# This requirement is tested by all e_select-4.13.* tests.1075#1076# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate1077# expression, it is evaluated across all rows in the group.1078#1079# Tested by e_select-4.13.1.*1080#1081# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate1082# expression, it is evaluated with respect to an arbitrarily selected1083# row from the group.1084#1085# Tested by e_select-4.13.2.*1086#1087# Tests in this block also show that this is not untrue:1088#1089# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values,1090# even aggregate functions, that are not in the result.1091#1092do_execsql_test e_select-4.13.0 {1093 CREATE TABLE c1(up, down);1094 INSERT INTO c1 VALUES('x', 1);1095 INSERT INTO c1 VALUES('x', 2);1096 INSERT INTO c1 VALUES('x', 4);1097 INSERT INTO c1 VALUES('x', 8);1098 INSERT INTO c1 VALUES('y', 16);1099 INSERT INTO c1 VALUES('y', 32);1100 1101 CREATE TABLE c2(i, j);1102 INSERT INTO c2 VALUES(1, 0);1103 INSERT INTO c2 VALUES(2, 1);1104 INSERT INTO c2 VALUES(3, 3);1105 INSERT INTO c2 VALUES(4, 6);1106 INSERT INTO c2 VALUES(5, 10);1107 INSERT INTO c2 VALUES(6, 15);1108 INSERT INTO c2 VALUES(7, 21);1109 INSERT INTO c2 VALUES(8, 28);1110 INSERT INTO c2 VALUES(9, 36);1111 1112 CREATE TABLE c3(i PRIMARY KEY, k TEXT);1113 INSERT INTO c3 VALUES(1, 'hydrogen');1114 INSERT INTO c3 VALUES(2, 'helium');1115 INSERT INTO c3 VALUES(3, 'lithium');1116 INSERT INTO c3 VALUES(4, 'beryllium');1117 INSERT INTO c3 VALUES(5, 'boron');1118 INSERT INTO c3 VALUES(94, 'plutonium');1119} {}1120 1121do_select_tests e_select-4.13 {1122 1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}1123 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}1124 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}1125 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}1126 1127 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}1128 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y}1129 1130 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING j>6" {5 10}1131}1132 1133# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then1134# evaluated once for each group of rows.1135#1136# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate1137# expression, it is evaluated across all rows in the group.1138#1139do_select_tests e_select-4.15 {1140 1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48}1141 2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28}1142 3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21}1143 4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}1144 5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"1145 {3 4.33 1 2.0}1146} 1147 1148# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single1149# arbitrarily chosen row from within the group.1150#1151# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate1152# expression in the result-set, then all such expressions are evaluated1153# for the same row.1154#1155do_select_tests e_select-4.15 {1156 1 "SELECT i, j FROM c2 GROUP BY i%2" {2 1 1 0}1157 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {2 1 1 0}1158 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {}1159 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {}1160 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"1161 {2 4 beryllium 2 1 hydrogen 1 3 lithium}1162} 1163 1164# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows1165# contributes a single row to the set of result rows.1166#1167# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the1168# DISTINCT keyword, the number of rows returned by an aggregate query1169# with a GROUP BY clause is the same as the number of groups of rows1170# produced by applying the GROUP BY and HAVING clauses to the filtered1171# input dataset.1172#1173do_select_tests e_select.4.16 -count {1174 1 "SELECT i, j FROM c2 GROUP BY i%2" 21175 2 "SELECT i, j FROM c2 GROUP BY i" 91176 3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 41177} 1178 1179#-------------------------------------------------------------------------1180# The following tests attempt to verify statements made regarding the ALL1181# and DISTINCT keywords.1182#1183drop_all_tables1184do_execsql_test e_select-5.1.0 {1185 CREATE TABLE h1(a, b);1186 INSERT INTO h1 VALUES(1, 'one');1187 INSERT INTO h1 VALUES(1, 'I');1188 INSERT INTO h1 VALUES(1, 'i');1189 INSERT INTO h1 VALUES(4, 'four');1190 INSERT INTO h1 VALUES(4, 'IV');1191 INSERT INTO h1 VALUES(4, 'iv');1192 1193 CREATE TABLE h2(x COLLATE nocase);1194 INSERT INTO h2 VALUES('One');1195 INSERT INTO h2 VALUES('Two');1196 INSERT INTO h2 VALUES('Three');1197 INSERT INTO h2 VALUES('Four');1198 INSERT INTO h2 VALUES('one');1199 INSERT INTO h2 VALUES('two');1200 INSERT INTO h2 VALUES('three');