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