OLD | NEW |
(Empty) | |
| 1 # 2010 July 16 |
| 2 # |
| 3 # The author disclaims copyright to this source code. In place of |
| 4 # a legal notice, here is a blessing: |
| 5 # |
| 6 # May you do good and not evil. |
| 7 # May you find forgiveness for yourself and forgive others. |
| 8 # May you share freely, never taking more than you give. |
| 9 # |
| 10 #*********************************************************************** |
| 11 # |
| 12 # This file implements tests to verify that the "testable statements" in |
| 13 # the lang_select.html document are correct. |
| 14 # |
| 15 |
| 16 set testdir [file dirname $argv0] |
| 17 source $testdir/tester.tcl |
| 18 |
| 19 ifcapable !compound { |
| 20 finish_test |
| 21 return |
| 22 } |
| 23 |
| 24 do_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 } {} |
| 43 set 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 ] |
| 50 set 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 # |
| 75 proc 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 on |
| 84 # the lang_select.html page may be taken. |
| 85 # |
| 86 # -- syntax diagram join-constraint |
| 87 # |
| 88 do_join_test e_select-0.1.1 { |
| 89 SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) |
| 90 } {3} |
| 91 do_join_test e_select-0.1.2 { |
| 92 SELECT count(*) FROM t1 %JOIN% t2 USING (a) |
| 93 } {3} |
| 94 do_join_test e_select-0.1.3 { |
| 95 SELECT count(*) FROM t1 %JOIN% t2 |
| 96 } {9} |
| 97 do_catchsql_test e_select-0.1.4 { |
| 98 SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) |
| 99 } {1 {cannot have both ON and USING clauses in the same join}} |
| 100 do_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-core |
| 105 # |
| 106 # 0: SELECT ... |
| 107 # 1: SELECT DISTINCT ... |
| 108 # 2: SELECT ALL ... |
| 109 # |
| 110 # 0: No FROM clause |
| 111 # 1: Has FROM clause |
| 112 # |
| 113 # 0: No WHERE clause |
| 114 # 1: Has WHERE clause |
| 115 # |
| 116 # 0: No GROUP BY clause |
| 117 # 1: Has GROUP BY clause |
| 118 # 2: Has GROUP BY and HAVING clauses |
| 119 # |
| 120 do_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 cthree |
| 127 } |
| 128 1100.1 "SELECT DISTINCT a, b, a||b FROM t1 " { |
| 129 a one aone b two btwo c three cthree |
| 130 } |
| 131 1200.1 "SELECT ALL a, b, a||b FROM t1 " { |
| 132 a one aone b two btwo c three cthree |
| 133 } |
| 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 cthree |
| 145 } |
| 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 cthree |
| 150 } |
| 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 b |
| 169 } |
| 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 b |
| 176 } |
| 177 1102.2 "SELECT DISTINCT count(*), max(a) FROM t1 |
| 178 GROUP BY b HAVING count(*)=2" { |
| 179 } |
| 180 |
| 181 2101.1 "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b} |
| 182 2102.1 "SELECT ALL count(*), max(a) FROM t1 |
| 183 GROUP BY b HAVING count(*)=1" { |
| 184 1 a 1 c 1 b |
| 185 } |
| 186 2102.2 "SELECT ALL count(*), max(a) FROM t1 |
| 187 GROUP BY b HAVING count(*)=2" { |
| 188 } |
| 189 |
| 190 0011.1 "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} |
| 191 0012.1 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} |
| 192 0012.2 "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {} |
| 193 |
| 194 1011.1 "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {} |
| 195 1012.1 "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" |
| 196 {1 2 3} |
| 197 1012.2 "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {} |
| 198 |
| 199 2011.1 "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3} |
| 200 2012.1 "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {} |
| 201 2012.2 "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {} |
| 202 |
| 203 0111.1 "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a} |
| 204 0112.1 "SELECT count(*), max(a) FROM t1 |
| 205 WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c} |
| 206 0112.2 "SELECT count(*), max(a) FROM t1 |
| 207 WHERE 0 GROUP BY b HAVING count(*)=2" { } |
| 208 1111.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" |
| 209 {1 a 1 b} |
| 210 1112.1 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a' |
| 211 GROUP BY b HAVING count(*)=1" { |
| 212 1 c 1 b |
| 213 } |
| 214 1112.2 "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0 |
| 215 GROUP BY b HAVING count(*)=2" { |
| 216 } |
| 217 |
| 218 2111.1 "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" |
| 219 {1 c 1 b} |
| 220 2112.1 "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b' |
| 221 GROUP BY b HAVING count(*)=1" { |
| 222 1 a 1 c |
| 223 } |
| 224 2112.2 "SELECT ALL count(*), max(a) FROM t1 |
| 225 WHERE 0 GROUP BY b HAVING count(*)=2" { } |
| 226 } |
| 227 |
| 228 |
| 229 # -- syntax diagram result-column |
| 230 # |
| 231 do_select_tests e_select-0.3 { |
| 232 1 "SELECT * FROM t1" {a one b two c three} |
| 233 2 "SELECT t1.* FROM t1" {a one b two c three} |
| 234 3 "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx} |
| 235 4 "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx} |
| 236 5 "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx} |
| 237 } |
| 238 |
| 239 # -- syntax diagram join-source |
| 240 # |
| 241 # -- syntax diagram join-op |
| 242 # |
| 243 do_select_tests e_select-0.4 { |
| 244 1 "SELECT t1.rowid FROM t1" {1 2 3} |
| 245 2 "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3} |
| 246 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} |
| 247 |
| 248 4 "SELECT t1.rowid FROM t1" {1 2 3} |
| 249 5 "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3} |
| 250 6 "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" |
| 251 {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3} |
| 252 |
| 253 7 "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2} |
| 254 8 "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3} |
| 255 9 "SELECT t1.rowid FROM t1 NATURAL LEFT JOIN t3" {1 2 3} |
| 256 10 "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2} |
| 257 11 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2} |
| 258 |
| 259 12 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3} |
| 260 13 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3} |
| 261 14 "SELECT t1.rowid FROM t1 LEFT JOIN t3" {1 1 2 2 3 3} |
| 262 15 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3} |
| 263 16 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3} |
| 264 } |
| 265 |
| 266 # -- syntax diagram compound-operator |
| 267 # |
| 268 do_select_tests e_select-0.5 { |
| 269 1 "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4} |
| 270 2 "SELECT rowid FROM t1 UNION SELECT rowid+2 FROM t4" {1 2 3 4} |
| 271 3 "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3} |
| 272 4 "SELECT rowid FROM t1 EXCEPT SELECT rowid+2 FROM t4" {1 2} |
| 273 } |
| 274 |
| 275 # -- syntax diagram ordering-term |
| 276 # |
| 277 do_select_tests e_select-0.6 { |
| 278 1 "SELECT b||a FROM t1 ORDER BY b||a" {onea threec twob} |
| 279 2 "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob} |
| 280 3 "SELECT b||a FROM t1 ORDER BY (b||a) ASC" {onea threec twob} |
| 281 4 "SELECT b||a FROM t1 ORDER BY (b||a) DESC" {twob threec onea} |
| 282 } |
| 283 |
| 284 # -- syntax diagram select-stmt |
| 285 # |
| 286 do_select_tests e_select-0.7 { |
| 287 1 "SELECT * FROM t1" {a one b two c three} |
| 288 2 "SELECT * FROM t1 ORDER BY b" {a one c three b two} |
| 289 3 "SELECT * FROM t1 ORDER BY b, a" {a one c three b two} |
| 290 |
| 291 4 "SELECT * FROM t1 LIMIT 10" {a one b two c three} |
| 292 5 "SELECT * FROM t1 LIMIT 10 OFFSET 5" {} |
| 293 6 "SELECT * FROM t1 LIMIT 10, 5" {} |
| 294 |
| 295 7 "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three} |
| 296 8 "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {} |
| 297 9 "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {} |
| 298 |
| 299 10 "SELECT * FROM t1 UNION SELECT b, a FROM t1" |
| 300 {a one b two c three one a three c two b} |
| 301 11 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" |
| 302 {one a two b three c a one c three b two} |
| 303 12 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" |
| 304 {one a two b three c a one c three b two} |
| 305 13 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" |
| 306 {a one b two c three one a three c two b} |
| 307 14 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" |
| 308 {two b} |
| 309 15 "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" |
| 310 {} |
| 311 16 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" |
| 312 {a one b two c three one a three c two b} |
| 313 17 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" |
| 314 {b two} |
| 315 18 "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" |
| 316 {} |
| 317 } |
| 318 |
| 319 #------------------------------------------------------------------------- |
| 320 # The following tests focus on FROM clause (join) processing. |
| 321 # |
| 322 |
| 323 # EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple |
| 324 # SELECT statement, then the input data is implicitly a single row zero |
| 325 # columns wide |
| 326 # |
| 327 do_select_tests e_select-1.1 { |
| 328 1 "SELECT 'abc'" {abc} |
| 329 2 "SELECT 'abc' WHERE NULL" {} |
| 330 3 "SELECT NULL" {{}} |
| 331 4 "SELECT count(*)" {1} |
| 332 5 "SELECT count(*) WHERE 0" {0} |
| 333 6 "SELECT count(*) WHERE 1" {1} |
| 334 } |
| 335 |
| 336 # EVIDENCE-OF: R-45424-07352 If there is only a single table or subquery |
| 337 # in the FROM clause, then the input data used by the SELECT statement |
| 338 # is the contents of the named table. |
| 339 # |
| 340 # The results of the SELECT queries suggest that they are operating on the |
| 341 # contents of the table 'xx'. |
| 342 # |
| 343 do_execsql_test e_select-1.2.0 { |
| 344 CREATE TABLE xx(x, y); |
| 345 INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2'); |
| 346 INSERT INTO xx VALUES(NULL, -16.87); |
| 347 INSERT INTO xx VALUES(-17.89, 'linguistically'); |
| 348 } {} |
| 349 do_select_tests e_select-1.2 { |
| 350 1 "SELECT quote(x), quote(y) FROM xx" { |
| 351 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' |
| 352 NULL -16.87 |
| 353 -17.89 'linguistically' |
| 354 } |
| 355 |
| 356 2 "SELECT count(*), count(x), count(y) FROM xx" {3 2 3} |
| 357 3 "SELECT sum(x), sum(y) FROM xx" {-17.89 -16.87} |
| 358 } |
| 359 |
| 360 # EVIDENCE-OF: R-28355-09804 If there is more than one table or subquery |
| 361 # in FROM clause then the contents of all tables and/or subqueries are |
| 362 # joined into a single dataset for the simple SELECT statement to |
| 363 # operate on. |
| 364 # |
| 365 # There are more detailed tests for subsequent requirements that add |
| 366 # more detail to this idea. We just add a single test that shows that |
| 367 # data is coming from each of the three tables following the FROM clause |
| 368 # here to show that the statement, vague as it is, is not incorrect. |
| 369 # |
| 370 do_select_tests e_select-1.3 { |
| 371 1 "SELECT * FROM t1, t2, t3" { |
| 372 a one a I a 1 a one a I b 2 a one b II a 1 |
| 373 a one b II b 2 a one c III a 1 a one c III b 2 |
| 374 b two a I a 1 b two a I b 2 b two b II a 1 |
| 375 b two b II b 2 b two c III a 1 b two c III b 2 |
| 376 c three a I a 1 c three a I b 2 c three b II a 1 |
| 377 c three b II b 2 c three c III a 1 c three c III b 2 |
| 378 } |
| 379 } |
| 380 |
| 381 # |
| 382 # The following block of tests - e_select-1.4.* - test that the description |
| 383 # of cartesian joins in the SELECT documentation is consistent with SQLite. |
| 384 # In doing so, we test the following three requirements as a side-effect: |
| 385 # |
| 386 # EVIDENCE-OF: R-49872-03192 If the join-operator is "CROSS JOIN", |
| 387 # "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING |
| 388 # clause, then the result of the join is simply the cartesian product of |
| 389 # the left and right-hand datasets. |
| 390 # |
| 391 # The tests are built on this assertion. Really, they test that the output |
| 392 # of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result |
| 393 # of calculating the cartesian product of the left and right-hand datasets. |
| 394 # |
| 395 # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER |
| 396 # JOIN", "JOIN" and "," join operators. |
| 397 # |
| 398 # EVIDENCE-OF: R-25071-21202 The "CROSS JOIN" join operator produces the |
| 399 # same result as the "INNER JOIN", "JOIN" and "," operators |
| 400 # |
| 401 # All tests are run 4 times, with the only difference in each run being |
| 402 # which of the 4 equivalent cartesian product join operators are used. |
| 403 # Since the output data is the same in all cases, we consider that this |
| 404 # qualifies as testing the two statements above. |
| 405 # |
| 406 do_execsql_test e_select-1.4.0 { |
| 407 CREATE TABLE x1(a, b); |
| 408 CREATE TABLE x2(c, d, e); |
| 409 CREATE TABLE x3(f, g, h, i); |
| 410 |
| 411 -- x1: 3 rows, 2 columns |
| 412 INSERT INTO x1 VALUES(24, 'converging'); |
| 413 INSERT INTO x1 VALUES(NULL, X'CB71'); |
| 414 INSERT INTO x1 VALUES('blonds', 'proprietary'); |
| 415 |
| 416 -- x2: 2 rows, 3 columns |
| 417 INSERT INTO x2 VALUES(-60.06, NULL, NULL); |
| 418 INSERT INTO x2 VALUES(-58, NULL, 1.21); |
| 419 |
| 420 -- x3: 5 rows, 4 columns |
| 421 INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1); |
| 422 INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified'); |
| 423 INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL); |
| 424 INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3); |
| 425 INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL); |
| 426 } {} |
| 427 |
| 428 # EVIDENCE-OF: R-59089-25828 The columns of the cartesian product |
| 429 # dataset are, in order, all the columns of the left-hand dataset |
| 430 # followed by all the columns of the right-hand dataset. |
| 431 # |
| 432 do_join_test e_select-1.4.1.1 { |
| 433 SELECT * FROM x1 %JOIN% x2 LIMIT 1 |
| 434 } [concat {24 converging} {-60.06 {} {}}] |
| 435 |
| 436 do_join_test e_select-1.4.1.2 { |
| 437 SELECT * FROM x2 %JOIN% x1 LIMIT 1 |
| 438 } [concat {-60.06 {} {}} {24 converging}] |
| 439 |
| 440 do_join_test e_select-1.4.1.3 { |
| 441 SELECT * FROM x3 %JOIN% x2 LIMIT 1 |
| 442 } [concat {-39.24 {} encompass -1} {-60.06 {} {}}] |
| 443 |
| 444 do_join_test e_select-1.4.1.4 { |
| 445 SELECT * FROM x2 %JOIN% x3 LIMIT 1 |
| 446 } [concat {-60.06 {} {}} {-39.24 {} encompass -1}] |
| 447 |
| 448 # EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product |
| 449 # dataset formed by combining each unique combination of a row from the |
| 450 # left-hand and right-hand datasets. |
| 451 # |
| 452 do_join_test e_select-1.4.2.1 { |
| 453 SELECT * FROM x2 %JOIN% x3 ORDER BY +c, +f |
| 454 } [list -60.06 {} {} -39.24 {} encompass -1 \ |
| 455 -60.06 {} {} alerting {} -93.79 {} \ |
| 456 -60.06 {} {} coldest -96 dramatists 82.3 \ |
| 457 -60.06 {} {} conducting -87.24 37.56 {} \ |
| 458 -60.06 {} {} presenting 51 reformation dignified \ |
| 459 -58 {} 1.21 -39.24 {} encompass -1 \ |
| 460 -58 {} 1.21 alerting {} -93.79 {} \ |
| 461 -58 {} 1.21 coldest -96 dramatists 82.3 \ |
| 462 -58 {} 1.21 conducting -87.24 37.56 {} \ |
| 463 -58 {} 1.21 presenting 51 reformation dignified \ |
| 464 ] |
| 465 # TODO: Come back and add a few more like the above. |
| 466 |
| 467 # EVIDENCE-OF: R-18439-38548 In other words, if the left-hand dataset |
| 468 # consists of Nleft rows of Mleft columns, and the right-hand dataset of |
| 469 # Nright rows of Mright columns, then the cartesian product is a dataset |
| 470 # of Nleft×Nright rows, each containing Mleft+Mright columns. |
| 471 # |
| 472 # x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3) |
| 473 do_join_test e_select-1.4.3.1 { |
| 474 SELECT count(*) FROM x1 %JOIN% x2 |
| 475 } [expr 3*2] |
| 476 do_test e_select-1.4.3.2 { |
| 477 expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6} |
| 478 } [expr 2+3] |
| 479 |
| 480 # x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4) |
| 481 do_join_test e_select-1.4.3.3 { |
| 482 SELECT count(*) FROM x2 %JOIN% x3 |
| 483 } [expr 2*5] |
| 484 do_test e_select-1.4.3.4 { |
| 485 expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10} |
| 486 } [expr 3+4] |
| 487 |
| 488 # x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2) |
| 489 do_join_test e_select-1.4.3.5 { |
| 490 SELECT count(*) FROM x3 %JOIN% x1 |
| 491 } [expr 5*3] |
| 492 do_test e_select-1.4.3.6 { |
| 493 expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15} |
| 494 } [expr 4+2] |
| 495 |
| 496 # x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4) |
| 497 do_join_test e_select-1.4.3.7 { |
| 498 SELECT count(*) FROM x3 %JOIN% x3 |
| 499 } [expr 5*5] |
| 500 do_test e_select-1.4.3.8 { |
| 501 expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25} |
| 502 } [expr 4+4] |
| 503 |
| 504 # Some extra cartesian product tests using tables t1 and t2. |
| 505 # |
| 506 do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2 |
| 507 do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 |
| 508 |
| 509 do_select_tests e_select-1.4.5 [list \ |
| 510 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ |
| 511 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ |
| 512 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ |
| 513 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ |
| 514 ] |
| 515 |
| 516 # EVIDENCE-OF: R-38465-03616 If there is an ON clause then the ON |
| 517 # expression is evaluated for each row of the cartesian product as a |
| 518 # boolean expression. Only rows for which the expression evaluates to |
| 519 # true are included from the dataset. |
| 520 # |
| 521 foreach {tn select res} [list \ |
| 522 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ |
| 523 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ |
| 524 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ |
| 525 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ |
| 526 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ |
| 527 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ |
| 528 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ |
| 529 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ |
| 530 \ |
| 531 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ |
| 532 {one I two II three III} \ |
| 533 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ |
| 534 {one I one II one III} \ |
| 535 11 { SELECT t1.b, t2.b |
| 536 FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ |
| 537 {two I two II two III three I three II three III} \ |
| 538 ] { |
| 539 do_join_test e_select-1.3.$tn $select $res |
| 540 } |
| 541 |
| 542 # EVIDENCE-OF: R-49933-05137 If there is a USING clause then each of the |
| 543 # column names specified must exist in the datasets to both the left and |
| 544 # right of the join-operator. |
| 545 # |
| 546 do_select_tests e_select-1.4 -error { |
| 547 cannot join using column %s - column not present in both tables |
| 548 } { |
| 549 1 { SELECT * FROM t1, t3 USING (b) } "b" |
| 550 2 { SELECT * FROM t3, t1 USING (c) } "c" |
| 551 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" |
| 552 } |
| 553 |
| 554 # EVIDENCE-OF: R-22776-52830 For each pair of named columns, the |
| 555 # expression "lhs.X = rhs.X" is evaluated for each row of the cartesian |
| 556 # product as a boolean expression. Only rows for which all such |
| 557 # expressions evaluates to true are included from the result set. |
| 558 # |
| 559 do_select_tests e_select-1.5 { |
| 560 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} |
| 561 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} |
| 562 } |
| 563 |
| 564 # EVIDENCE-OF: R-54046-48600 When comparing values as a result of a |
| 565 # USING clause, the normal rules for handling affinities, collation |
| 566 # sequences and NULL values in comparisons apply. |
| 567 # |
| 568 # EVIDENCE-OF: R-38422-04402 The column from the dataset on the |
| 569 # left-hand side of the join-operator is considered to be on the |
| 570 # left-hand side of the comparison operator (=) for the purposes of |
| 571 # collation sequence and affinity precedence. |
| 572 # |
| 573 do_execsql_test e_select-1.6.0 { |
| 574 CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); |
| 575 INSERT INTO t5 VALUES('AA', 'cc'); |
| 576 INSERT INTO t5 VALUES('BB', 'dd'); |
| 577 INSERT INTO t5 VALUES(NULL, NULL); |
| 578 CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); |
| 579 INSERT INTO t6 VALUES('aa', 'cc'); |
| 580 INSERT INTO t6 VALUES('bb', 'DD'); |
| 581 INSERT INTO t6 VALUES(NULL, NULL); |
| 582 } {} |
| 583 foreach {tn select res} { |
| 584 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} |
| 585 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} |
| 586 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } |
| 587 {aa cc cc bb DD dd} |
| 588 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} |
| 589 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} |
| 590 } { |
| 591 do_join_test e_select-1.6.$tn $select $res |
| 592 } |
| 593 |
| 594 # EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a |
| 595 # USING clause, the column from the right-hand dataset is omitted from |
| 596 # the joined dataset. |
| 597 # |
| 598 # EVIDENCE-OF: R-56132-15700 This is the only difference between a USING |
| 599 # clause and its equivalent ON constraint. |
| 600 # |
| 601 foreach {tn select res} { |
| 602 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } |
| 603 {a one I b two II c three III} |
| 604 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } |
| 605 {a one a I b two b II c three c III} |
| 606 |
| 607 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } |
| 608 {a 1 {} b 2 2} |
| 609 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } |
| 610 {a 1 a {} b 2 b 2} |
| 611 |
| 612 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} |
| 613 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} |
| 614 |
| 615 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x |
| 616 %JOIN% t5 USING (a) } |
| 617 {aa cc cc bb DD dd} |
| 618 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x |
| 619 %JOIN% t5 ON (x.a=t5.a) } |
| 620 {aa cc AA cc bb DD BB dd} |
| 621 } { |
| 622 do_join_test e_select-1.7.$tn $select $res |
| 623 } |
| 624 # EVIDENCE-OF: R-42531-52874 If the join-operator is a "LEFT JOIN" or |
| 625 # "LEFT OUTER JOIN", then after the ON or USING filtering clauses have |
| 626 # been applied, an extra row is added to the output for each row in the |
| 627 # original left-hand input dataset that corresponds to no rows at all in |
| 628 # the composite dataset (if any). |
| 629 # |
| 630 do_execsql_test e_select-1.8.0 { |
| 631 CREATE TABLE t7(a, b, c); |
| 632 CREATE TABLE t8(a, d, e); |
| 633 |
| 634 INSERT INTO t7 VALUES('x', 'ex', 24); |
| 635 INSERT INTO t7 VALUES('y', 'why', 25); |
| 636 |
| 637 INSERT INTO t8 VALUES('x', 'abc', 24); |
| 638 INSERT INTO t8 VALUES('z', 'ghi', 26); |
| 639 } {} |
| 640 |
| 641 do_select_tests e_select-1.8 { |
| 642 1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1} |
| 643 1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2} |
| 644 2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1} |
| 645 2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2} |
| 646 } |
| 647 |
| 648 |
| 649 # EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the |
| 650 # columns that would normally contain values copied from the right-hand |
| 651 # input dataset. |
| 652 # |
| 653 do_select_tests e_select-1.9 { |
| 654 1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24} |
| 655 1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" |
| 656 {x ex 24 x abc 24 y why 25 {} {} {}} |
| 657 2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} |
| 658 2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} |
| 659 } |
| 660 |
| 661 # EVIDENCE-OF: R-04932-55942 If the NATURAL keyword is in the |
| 662 # join-operator then an implicit USING clause is added to the |
| 663 # join-constraints. The implicit USING clause contains each of the |
| 664 # column names that appear in both the left and right-hand input |
| 665 # datasets. |
| 666 # |
| 667 do_select_tests e_select-1-10 { |
| 668 1a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24} |
| 669 1b "SELECT * FROM t7 NATURAL JOIN t8" {x ex 24 abc 24} |
| 670 |
| 671 2a "SELECT * FROM t8 JOIN t7 USING (a)" {x abc 24 ex 24} |
| 672 2b "SELECT * FROM t8 NATURAL JOIN t7" {x abc 24 ex 24} |
| 673 |
| 674 3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}} |
| 675 3b "SELECT * FROM t7 NATURAL LEFT JOIN t8" {x ex 24 abc 24 y why 25 {} {}} |
| 676 |
| 677 4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)" {x abc 24 ex 24 z ghi 26 {} {}} |
| 678 4b "SELECT * FROM t8 NATURAL LEFT JOIN t7" {x abc 24 ex 24 z ghi 26 {} {}} |
| 679 |
| 680 5a "SELECT * FROM t3 JOIN t4 USING (a,c)" {b 2} |
| 681 5b "SELECT * FROM t3 NATURAL JOIN t4" {b 2} |
| 682 |
| 683 6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2} |
| 684 6b "SELECT * FROM t3 NATURAL LEFT JOIN t4" {a 1 b 2} |
| 685 } |
| 686 |
| 687 # EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets |
| 688 # feature no common column names, then the NATURAL keyword has no effect |
| 689 # on the results of the join. |
| 690 # |
| 691 do_execsql_test e_select-1.11.0 { |
| 692 CREATE TABLE t10(x, y); |
| 693 INSERT INTO t10 VALUES(1, 'true'); |
| 694 INSERT INTO t10 VALUES(0, 'false'); |
| 695 } {} |
| 696 do_select_tests e_select-1-11 { |
| 697 1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} |
| 698 1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0} |
| 699 } |
| 700 |
| 701 # EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a |
| 702 # join that specifies the NATURAL keyword. |
| 703 # |
| 704 foreach {tn sql} { |
| 705 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} |
| 706 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} |
| 707 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} |
| 708 } { |
| 709 do_catchsql_test e_select-1.12.$tn " |
| 710 $sql |
| 711 " {1 {a NATURAL join may not have an ON or USING clause}} |
| 712 } |
| 713 |
| 714 #------------------------------------------------------------------------- |
| 715 # The next block of tests - e_select-3.* - concentrate on verifying |
| 716 # statements made regarding WHERE clause processing. |
| 717 # |
| 718 drop_all_tables |
| 719 do_execsql_test e_select-3.0 { |
| 720 CREATE TABLE x1(k, x, y, z); |
| 721 INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43); |
| 722 INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81); |
| 723 INSERT INTO x1 VALUES(3, -22, -27.57, NULL); |
| 724 INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky'); |
| 725 INSERT INTO x1 VALUES(5, NULL, 96.28, NULL); |
| 726 INSERT INTO x1 VALUES(6, 0, 1, 2); |
| 727 |
| 728 CREATE TABLE x2(k, x, y2); |
| 729 INSERT INTO x2 VALUES(1, 50, X'B82838'); |
| 730 INSERT INTO x2 VALUES(5, 84.79, 65.88); |
| 731 INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393'); |
| 732 INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized'); |
| 733 } {} |
| 734 |
| 735 # EVIDENCE-OF: R-60775-64916 If a WHERE clause is specified, the WHERE |
| 736 # expression is evaluated for each row in the input data as a boolean |
| 737 # expression. Only rows for which the WHERE clause expression evaluates |
| 738 # to true are included from the dataset before continuing. |
| 739 # |
| 740 do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3} |
| 741 do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6} |
| 742 do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6} |
| 743 do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6} |
| 744 do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5} |
| 745 do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6} |
| 746 |
| 747 do_execsql_test e_select-3.2.1a { |
| 748 SELECT k FROM x1 LEFT JOIN x2 USING(k) |
| 749 } {1 2 3 4 5 6} |
| 750 do_execsql_test e_select-3.2.1b { |
| 751 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k |
| 752 } {1 3 5} |
| 753 do_execsql_test e_select-3.2.2 { |
| 754 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL |
| 755 } {2 4 6} |
| 756 |
| 757 do_execsql_test e_select-3.2.3 { |
| 758 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k |
| 759 } {3} |
| 760 do_execsql_test e_select-3.2.4 { |
| 761 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 |
| 762 } {} |
| 763 |
| 764 #------------------------------------------------------------------------- |
| 765 # Tests below this point are focused on verifying the testable statements |
| 766 # related to caculating the result rows of a simple SELECT statement. |
| 767 # |
| 768 |
| 769 drop_all_tables |
| 770 do_execsql_test e_select-4.0 { |
| 771 CREATE TABLE z1(a, b, c); |
| 772 CREATE TABLE z2(d, e); |
| 773 CREATE TABLE z3(a, b); |
| 774 |
| 775 INSERT INTO z1 VALUES(51.65, -59.58, 'belfries'); |
| 776 INSERT INTO z1 VALUES(-5, NULL, 75); |
| 777 INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters'); |
| 778 INSERT INTO z1 VALUES(NULL, 67, 'quartets'); |
| 779 INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen'); |
| 780 INSERT INTO z1 VALUES(63, 'born', -26); |
| 781 |
| 782 INSERT INTO z2 VALUES(NULL, 21); |
| 783 INSERT INTO z2 VALUES(36, 6); |
| 784 |
| 785 INSERT INTO z3 VALUES('subsistence', 'gauze'); |
| 786 INSERT INTO z3 VALUES(49.17, -67); |
| 787 } {} |
| 788 |
| 789 # EVIDENCE-OF: R-36327-17224 If a result expression is the special |
| 790 # expression "*" then all columns in the input data are substituted for |
| 791 # that one expression. |
| 792 # |
| 793 # EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table |
| 794 # or subquery in the FROM clause followed by ".*" then all columns from |
| 795 # the named table or subquery are substituted for the single expression. |
| 796 # |
| 797 do_select_tests e_select-4.1 { |
| 798 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} |
| 799 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} |
| 800 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} |
| 801 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} |
| 802 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} |
| 803 |
| 804 6 "SELECT count(*), * FROM z1" {6 63 born -26} |
| 805 7 "SELECT max(a), * FROM z1" {63 63 born -26} |
| 806 8 "SELECT *, min(a) FROM z1" {-5 {} 75 -5} |
| 807 |
| 808 9 "SELECT *,* FROM z1,z2 LIMIT 1" { |
| 809 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 |
| 810 } |
| 811 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { |
| 812 51.65 -59.58 belfries 51.65 -59.58 belfries |
| 813 } |
| 814 } |
| 815 |
| 816 # EVIDENCE-OF: R-38023-18396 It is an error to use a "*" or "alias.*" |
| 817 # expression in any context other than a result expression list. |
| 818 # |
| 819 # EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or |
| 820 # "alias.*" expression in a simple SELECT query that does not have a |
| 821 # FROM clause. |
| 822 # |
| 823 foreach {tn select err} { |
| 824 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error} |
| 825 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error} |
| 826 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error} |
| 827 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error} |
| 828 |
| 829 2.1 "SELECT *" {no tables specified} |
| 830 2.2 "SELECT * WHERE 1" {no tables specified} |
| 831 2.3 "SELECT * WHERE 0" {no tables specified} |
| 832 2.4 "SELECT count(*), *" {no tables specified} |
| 833 } { |
| 834 do_catchsql_test e_select-4.2.$tn $select [list 1 $err] |
| 835 } |
| 836 |
| 837 # EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned |
| 838 # by a simple SELECT statement is equal to the number of expressions in |
| 839 # the result expression list after substitution of * and alias.* |
| 840 # expressions. |
| 841 # |
| 842 foreach {tn select nCol} { |
| 843 1 "SELECT * FROM z1" 3 |
| 844 2 "SELECT * FROM z1 NATURAL JOIN z3" 3 |
| 845 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3 |
| 846 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2 |
| 847 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5 |
| 848 6 "SELECT 1, 2, z1.* FROM z1" 5 |
| 849 7 "SELECT a, *, b, c FROM z1" 6 |
| 850 } { |
| 851 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] |
| 852 do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol |
| 853 sqlite3_finalize $::stmt |
| 854 } |
| 855 |
| 856 |
| 857 |
| 858 # In lang_select.html, a non-aggregate query is defined as any simple SELECT |
| 859 # that has no GROUP BY clause and no aggregate expressions in the result |
| 860 # expression list. Other queries are aggregate queries. Test cases |
| 861 # e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of |
| 862 # simple SELECT that is different for aggregate and non-aggregate queries |
| 863 # verify (in a way) that these definitions are consistent: |
| 864 # |
| 865 # EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate |
| 866 # query if it contains either a GROUP BY clause or one or more aggregate |
| 867 # functions in the result-set. |
| 868 # |
| 869 # EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no |
| 870 # aggregate functions or a GROUP BY clause, it is a non-aggregate query. |
| 871 # |
| 872 |
| 873 # EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate |
| 874 # query, then each expression in the result expression list is evaluated |
| 875 # for each row in the dataset filtered by the WHERE clause. |
| 876 # |
| 877 do_select_tests e_select-4.4 { |
| 878 1 "SELECT a, b FROM z1" |
| 879 {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born} |
| 880 |
| 881 2 "SELECT a IS NULL, b+1, * FROM z1" { |
| 882 0 -58.58 51.65 -59.58 belfries |
| 883 0 {} -5 {} 75 |
| 884 0 -22.18 -2.2 -23.18 suiters |
| 885 1 68 {} 67 quartets |
| 886 0 -31.3 -1.04 -32.3 aspen |
| 887 0 1 63 born -26 |
| 888 } |
| 889 |
| 890 3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366} |
| 891 } |
| 892 |
| 893 |
| 894 # Test cases e_select-4.5.* and e_select-4.6.* together show that: |
| 895 # |
| 896 # EVIDENCE-OF: R-51988-01124 The single row of result-set data created |
| 897 # by evaluating the aggregate and non-aggregate expressions in the |
| 898 # result-set forms the result of an aggregate query without a GROUP BY |
| 899 # clause. |
| 900 # |
| 901 |
| 902 # EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate |
| 903 # query without a GROUP BY clause, then each aggregate expression in the |
| 904 # result-set is evaluated once across the entire dataset. |
| 905 # |
| 906 do_select_tests e_select-4.5 { |
| 907 1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} |
| 908 2 "SELECT count(*), max(1)" {1 1} |
| 909 |
| 910 3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} |
| 911 4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} |
| 912 5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} |
| 913 } |
| 914 |
| 915 # EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the |
| 916 # result-set is evaluated once for an arbitrarily selected row of the |
| 917 # dataset. |
| 918 # |
| 919 # EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used |
| 920 # for each non-aggregate expression. |
| 921 # |
| 922 # Note: The results of many of the queries in this block of tests are |
| 923 # technically undefined, as the documentation does not specify which row |
| 924 # SQLite will arbitrarily select to use for the evaluation of the |
| 925 # non-aggregate expressions. |
| 926 # |
| 927 drop_all_tables |
| 928 do_execsql_test e_select-4.6.0 { |
| 929 CREATE TABLE a1(one PRIMARY KEY, two); |
| 930 INSERT INTO a1 VALUES(1, 1); |
| 931 INSERT INTO a1 VALUES(2, 3); |
| 932 INSERT INTO a1 VALUES(3, 6); |
| 933 INSERT INTO a1 VALUES(4, 10); |
| 934 |
| 935 CREATE TABLE a2(one PRIMARY KEY, three); |
| 936 INSERT INTO a2 VALUES(1, 1); |
| 937 INSERT INTO a2 VALUES(3, 2); |
| 938 INSERT INTO a2 VALUES(6, 3); |
| 939 INSERT INTO a2 VALUES(10, 4); |
| 940 } {} |
| 941 do_select_tests e_select-4.6 { |
| 942 1 "SELECT one, two, count(*) FROM a1" {4 10 4} |
| 943 2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} |
| 944 3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} |
| 945 4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} |
| 946 5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} |
| 947 6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} |
| 948 7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} |
| 949 } |
| 950 |
| 951 # EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then |
| 952 # each non-aggregate expression is evaluated against a row consisting |
| 953 # entirely of NULL values. |
| 954 # |
| 955 do_select_tests e_select-4.7 { |
| 956 1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} |
| 957 2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} |
| 958 3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { |
| 959 1 1 1 |
| 960 } |
| 961 } |
| 962 |
| 963 # EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY |
| 964 # clause always returns exactly one row of data, even if there are zero |
| 965 # rows of input data. |
| 966 # |
| 967 foreach {tn select} { |
| 968 8.1 "SELECT count(*) FROM a1" |
| 969 8.2 "SELECT count(*) FROM a1 WHERE 0" |
| 970 8.3 "SELECT count(*) FROM a1 WHERE 1" |
| 971 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1" |
| 972 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0" |
| 973 } { |
| 974 # Set $nRow to the number of rows returned by $select: |
| 975 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] |
| 976 set nRow 0 |
| 977 while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow } |
| 978 set rc [sqlite3_finalize $::stmt] |
| 979 |
| 980 # Test that $nRow==1 and that statement execution was successful |
| 981 # (rc==SQLITE_OK). |
| 982 do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1} |
| 983 } |
| 984 |
| 985 drop_all_tables |
| 986 do_execsql_test e_select-4.9.0 { |
| 987 CREATE TABLE b1(one PRIMARY KEY, two); |
| 988 INSERT INTO b1 VALUES(1, 'o'); |
| 989 INSERT INTO b1 VALUES(4, 'f'); |
| 990 INSERT INTO b1 VALUES(3, 't'); |
| 991 INSERT INTO b1 VALUES(2, 't'); |
| 992 INSERT INTO b1 VALUES(5, 'f'); |
| 993 INSERT INTO b1 VALUES(7, 's'); |
| 994 INSERT INTO b1 VALUES(6, 's'); |
| 995 |
| 996 CREATE TABLE b2(x, y); |
| 997 INSERT INTO b2 VALUES(NULL, 0); |
| 998 INSERT INTO b2 VALUES(NULL, 1); |
| 999 INSERT INTO b2 VALUES('xyz', 2); |
| 1000 INSERT INTO b2 VALUES('abc', 3); |
| 1001 INSERT INTO b2 VALUES('xyz', 4); |
| 1002 |
| 1003 CREATE TABLE b3(a COLLATE nocase, b COLLATE binary); |
| 1004 INSERT INTO b3 VALUES('abc', 'abc'); |
| 1005 INSERT INTO b3 VALUES('aBC', 'aBC'); |
| 1006 INSERT INTO b3 VALUES('Def', 'Def'); |
| 1007 INSERT INTO b3 VALUES('dEF', 'dEF'); |
| 1008 } {} |
| 1009 |
| 1010 # EVIDENCE-OF: R-07284-35990 If the SELECT statement is an aggregate |
| 1011 # query with a GROUP BY clause, then each of the expressions specified |
| 1012 # as part of the GROUP BY clause is evaluated for each row of the |
| 1013 # dataset. Each row is then assigned to a "group" based on the results; |
| 1014 # rows for which the results of evaluating the GROUP BY expressions are |
| 1015 # the same get assigned to the same group. |
| 1016 # |
| 1017 # These tests also show that the following is not untrue: |
| 1018 # |
| 1019 # EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do |
| 1020 # not have to be expressions that appear in the result. |
| 1021 # |
| 1022 do_select_tests e_select-4.9 { |
| 1023 1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { |
| 1024 /#,# f 1 o #,# s #,# t/ |
| 1025 } |
| 1026 2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { |
| 1027 1,2,3,4 10 5,6,7 18 |
| 1028 } |
| 1029 3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { |
| 1030 4 1,5 2,6 3,7 |
| 1031 } |
| 1032 4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { |
| 1033 4,3,5,7,6 1,2 |
| 1034 } |
| 1035 } |
| 1036 |
| 1037 # EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL |
| 1038 # values are considered equal. |
| 1039 # |
| 1040 do_select_tests e_select-4.10 { |
| 1041 1 "SELECT group_concat(y) FROM b2 GROUP BY x" {/#,# 3 #,#/} |
| 1042 2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1} |
| 1043 } |
| 1044 |
| 1045 # EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation |
| 1046 # sequence with which to compare text values apply when evaluating |
| 1047 # expressions in a GROUP BY clause. |
| 1048 # |
| 1049 do_select_tests e_select-4.11 { |
| 1050 1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} |
| 1051 2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} |
| 1052 3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} |
| 1053 4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} |
| 1054 5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} |
| 1055 6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} |
| 1056 } |
| 1057 |
| 1058 # EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may |
| 1059 # not be aggregate expressions. |
| 1060 # |
| 1061 foreach {tn select} { |
| 1062 12.1 "SELECT * FROM b3 GROUP BY count(*)" |
| 1063 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)" |
| 1064 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)" |
| 1065 } { |
| 1066 set res {1 {aggregate functions are not allowed in the GROUP BY clause}} |
| 1067 do_catchsql_test e_select-4.$tn $select $res |
| 1068 } |
| 1069 |
| 1070 # EVIDENCE-OF: R-31537-00101 If a HAVING clause is specified, it is |
| 1071 # evaluated once for each group of rows as a boolean expression. If the |
| 1072 # result of evaluating the HAVING clause is false, the group is |
| 1073 # discarded. |
| 1074 # |
| 1075 # This requirement is tested by all e_select-4.13.* tests. |
| 1076 # |
| 1077 # EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate |
| 1078 # expression, it is evaluated across all rows in the group. |
| 1079 # |
| 1080 # Tested by e_select-4.13.1.* |
| 1081 # |
| 1082 # EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate |
| 1083 # expression, it is evaluated with respect to an arbitrarily selected |
| 1084 # row from the group. |
| 1085 # |
| 1086 # Tested by e_select-4.13.2.* |
| 1087 # |
| 1088 # Tests in this block also show that this is not untrue: |
| 1089 # |
| 1090 # EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values, |
| 1091 # even aggregate functions, that are not in the result. |
| 1092 # |
| 1093 do_execsql_test e_select-4.13.0 { |
| 1094 CREATE TABLE c1(up, down); |
| 1095 INSERT INTO c1 VALUES('x', 1); |
| 1096 INSERT INTO c1 VALUES('x', 2); |
| 1097 INSERT INTO c1 VALUES('x', 4); |
| 1098 INSERT INTO c1 VALUES('x', 8); |
| 1099 INSERT INTO c1 VALUES('y', 16); |
| 1100 INSERT INTO c1 VALUES('y', 32); |
| 1101 |
| 1102 CREATE TABLE c2(i, j); |
| 1103 INSERT INTO c2 VALUES(1, 0); |
| 1104 INSERT INTO c2 VALUES(2, 1); |
| 1105 INSERT INTO c2 VALUES(3, 3); |
| 1106 INSERT INTO c2 VALUES(4, 6); |
| 1107 INSERT INTO c2 VALUES(5, 10); |
| 1108 INSERT INTO c2 VALUES(6, 15); |
| 1109 INSERT INTO c2 VALUES(7, 21); |
| 1110 INSERT INTO c2 VALUES(8, 28); |
| 1111 INSERT INTO c2 VALUES(9, 36); |
| 1112 |
| 1113 CREATE TABLE c3(i PRIMARY KEY, k TEXT); |
| 1114 INSERT INTO c3 VALUES(1, 'hydrogen'); |
| 1115 INSERT INTO c3 VALUES(2, 'helium'); |
| 1116 INSERT INTO c3 VALUES(3, 'lithium'); |
| 1117 INSERT INTO c3 VALUES(4, 'beryllium'); |
| 1118 INSERT INTO c3 VALUES(5, 'boron'); |
| 1119 INSERT INTO c3 VALUES(94, 'plutonium'); |
| 1120 } {} |
| 1121 |
| 1122 do_select_tests e_select-4.13 { |
| 1123 1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} |
| 1124 1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} |
| 1125 1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} |
| 1126 1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} |
| 1127 |
| 1128 2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} |
| 1129 2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} |
| 1130 |
| 1131 2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} |
| 1132 } |
| 1133 |
| 1134 # EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then |
| 1135 # evaluated once for each group of rows. |
| 1136 # |
| 1137 # EVIDENCE-OF: R-53735-47017 If the expression is an aggregate |
| 1138 # expression, it is evaluated across all rows in the group. |
| 1139 # |
| 1140 do_select_tests e_select-4.15 { |
| 1141 1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} |
| 1142 2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} |
| 1143 3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} |
| 1144 4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} |
| 1145 5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" |
| 1146 {3 4.33 1 2.0} |
| 1147 } |
| 1148 |
| 1149 # EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single |
| 1150 # arbitrarily chosen row from within the group. |
| 1151 # |
| 1152 # EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate |
| 1153 # expression in the result-set, then all such expressions are evaluated |
| 1154 # for the same row. |
| 1155 # |
| 1156 do_select_tests e_select-4.15 { |
| 1157 1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} |
| 1158 2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} |
| 1159 3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} |
| 1160 4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} |
| 1161 5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" |
| 1162 {2 5 boron 2 2 helium 1 3 lithium} |
| 1163 } |
| 1164 |
| 1165 # EVIDENCE-OF: R-19334-12811 Each group of input dataset rows |
| 1166 # contributes a single row to the set of result rows. |
| 1167 # |
| 1168 # EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the |
| 1169 # DISTINCT keyword, the number of rows returned by an aggregate query |
| 1170 # with a GROUP BY clause is the same as the number of groups of rows |
| 1171 # produced by applying the GROUP BY and HAVING clauses to the filtered |
| 1172 # input dataset. |
| 1173 # |
| 1174 do_select_tests e_select.4.16 -count { |
| 1175 1 "SELECT i, j FROM c2 GROUP BY i%2" 2 |
| 1176 2 "SELECT i, j FROM c2 GROUP BY i" 9 |
| 1177 3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 |
| 1178 } |
| 1179 |
| 1180 #------------------------------------------------------------------------- |
| 1181 # The following tests attempt to verify statements made regarding the ALL |
| 1182 # and DISTINCT keywords. |
| 1183 # |
| 1184 drop_all_tables |
| 1185 do_execsql_test e_select-5.1.0 { |
| 1186 CREATE TABLE h1(a, b); |
| 1187 INSERT INTO h1 VALUES(1, 'one'); |
| 1188 INSERT INTO h1 VALUES(1, 'I'); |
| 1189 INSERT INTO h1 VALUES(1, 'i'); |
| 1190 INSERT INTO h1 VALUES(4, 'four'); |
| 1191 INSERT INTO h1 VALUES(4, 'IV'); |
| 1192 INSERT INTO h1 VALUES(4, 'iv'); |
| 1193 |
| 1194 CREATE TABLE h2(x COLLATE nocase); |
| 1195 INSERT INTO h2 VALUES('One'); |
| 1196 INSERT INTO h2 VALUES('Two'); |
| 1197 INSERT INTO h2 VALUES('Three'); |
| 1198 INSERT INTO h2 VALUES('Four'); |
| 1199 INSERT INTO h2 VALUES('one'); |
| 1200 INSERT INTO h2 VALUES('two'); |
| 1201 INSERT INTO h2 VALUES('three'); |
| 1202 INSERT INTO h2 VALUES('four'); |
| 1203 |
| 1204 CREATE TABLE h3(c, d); |
| 1205 INSERT INTO h3 VALUES(1, NULL); |
| 1206 INSERT INTO h3 VALUES(2, NULL); |
| 1207 INSERT INTO h3 VALUES(3, NULL); |
| 1208 INSERT INTO h3 VALUES(4, '2'); |
| 1209 INSERT INTO h3 VALUES(5, NULL); |
| 1210 INSERT INTO h3 VALUES(6, '2,3'); |
| 1211 INSERT INTO h3 VALUES(7, NULL); |
| 1212 INSERT INTO h3 VALUES(8, '2,4'); |
| 1213 INSERT INTO h3 VALUES(9, '3'); |
| 1214 } {} |
| 1215 |
| 1216 # EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may |
| 1217 # follow the SELECT keyword in a simple SELECT statement. |
| 1218 # |
| 1219 do_select_tests e_select-5.1 { |
| 1220 1 "SELECT ALL a FROM h1" {1 1 1 4 4 4} |
| 1221 2 "SELECT DISTINCT a FROM h1" {1 4} |
| 1222 } |
| 1223 |
| 1224 # EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then |
| 1225 # the entire set of result rows are returned by the SELECT. |
| 1226 # |
| 1227 # EVIDENCE-OF: R-01256-01950 If neither ALL or DISTINCT are present, |
| 1228 # then the behavior is as if ALL were specified. |
| 1229 # |
| 1230 # EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, |
| 1231 # then duplicate rows are removed from the set of result rows before it |
| 1232 # is returned. |
| 1233 # |
| 1234 # The three testable statements above are tested by e_select-5.2.*, |
| 1235 # 5.3.* and 5.4.* respectively. |
| 1236 # |
| 1237 do_select_tests e_select-5 { |
| 1238 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} |
| 1239 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} |
| 1240 |
| 1241 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} |
| 1242 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} |
| 1243 |
| 1244 4.1 "SELECT DISTINCT x FROM h2" {One Two Three Four} |
| 1245 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {One Four} |
| 1246 } |
| 1247 |
| 1248 # EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate |
| 1249 # rows, two NULL values are considered to be equal. |
| 1250 # |
| 1251 do_select_tests e_select-5.5 { |
| 1252 1 "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3} |
| 1253 } |
| 1254 |
| 1255 # EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation |
| 1256 # sequence to compare text values with apply. |
| 1257 # |
| 1258 do_select_tests e_select-5.6 { |
| 1259 1 "SELECT DISTINCT b FROM h1" {one I i four IV iv} |
| 1260 2 "SELECT DISTINCT b COLLATE nocase FROM h1" {one I four IV} |
| 1261 3 "SELECT DISTINCT x FROM h2" {One Two Three Four} |
| 1262 4 "SELECT DISTINCT x COLLATE binary FROM h2" { |
| 1263 One Two Three Four one two three four |
| 1264 } |
| 1265 } |
| 1266 |
| 1267 #------------------------------------------------------------------------- |
| 1268 # The following tests - e_select-7.* - test that statements made to do |
| 1269 # with compound SELECT statements are correct. |
| 1270 # |
| 1271 |
| 1272 # EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent |
| 1273 # SELECTs must return the same number of result columns. |
| 1274 # |
| 1275 # All the other tests in this section use compound SELECTs created |
| 1276 # using component SELECTs that do return the same number of columns. |
| 1277 # So the tests here just show that it is an error to attempt otherwise. |
| 1278 # |
| 1279 drop_all_tables |
| 1280 do_execsql_test e_select-7.1.0 { |
| 1281 CREATE TABLE j1(a, b, c); |
| 1282 CREATE TABLE j2(e, f); |
| 1283 CREATE TABLE j3(g); |
| 1284 } {} |
| 1285 do_select_tests e_select-7.1 -error { |
| 1286 SELECTs to the left and right of %s do not have the same number of result colu
mns |
| 1287 } { |
| 1288 1 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} |
| 1289 2 "SELECT * FROM j1 UNION ALL SELECT * FROM j3" {{UNION ALL}} |
| 1290 3 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {{UNION ALL}} |
| 1291 4 "SELECT a, b FROM j1 UNION ALL SELECT * FROM j3,j2" {{UNION ALL}} |
| 1292 5 "SELECT * FROM j3,j2 UNION ALL SELECT a, b FROM j1" {{UNION ALL}} |
| 1293 |
| 1294 6 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} |
| 1295 7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION} |
| 1296 8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} |
| 1297 9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION} |
| 1298 10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION} |
| 1299 |
| 1300 11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} |
| 1301 12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT} |
| 1302 13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} |
| 1303 14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT} |
| 1304 15 "SELECT * FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT} |
| 1305 |
| 1306 16 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} |
| 1307 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j3" {EXCEPT} |
| 1308 18 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} |
| 1309 19 "SELECT a, b FROM j1 EXCEPT SELECT * FROM j3,j2" {EXCEPT} |
| 1310 20 "SELECT * FROM j3,j2 EXCEPT SELECT a, b FROM j1" {EXCEPT} |
| 1311 } |
| 1312 |
| 1313 # EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must |
| 1314 # be simple SELECT statements, they may not contain ORDER BY or LIMIT |
| 1315 # clauses. |
| 1316 # |
| 1317 foreach {tn select op1 op2} { |
| 1318 1 "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" |
| 1319 {ORDER BY} {UNION ALL} |
| 1320 2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2" |
| 1321 {ORDER BY} {UNION ALL} |
| 1322 3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2" |
| 1323 {ORDER BY} {UNION ALL} |
| 1324 4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" |
| 1325 LIMIT {UNION ALL} |
| 1326 5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" |
| 1327 LIMIT {UNION ALL} |
| 1328 6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" |
| 1329 LIMIT {UNION ALL} |
| 1330 |
| 1331 7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" |
| 1332 {ORDER BY} {UNION} |
| 1333 8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2" |
| 1334 {ORDER BY} {UNION} |
| 1335 9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2" |
| 1336 {ORDER BY} {UNION} |
| 1337 10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" |
| 1338 LIMIT {UNION} |
| 1339 11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" |
| 1340 LIMIT {UNION} |
| 1341 12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" |
| 1342 LIMIT {UNION} |
| 1343 |
| 1344 13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" |
| 1345 {ORDER BY} {EXCEPT} |
| 1346 14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2" |
| 1347 {ORDER BY} {EXCEPT} |
| 1348 15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2" |
| 1349 {ORDER BY} {EXCEPT} |
| 1350 16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" |
| 1351 LIMIT {EXCEPT} |
| 1352 17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" |
| 1353 LIMIT {EXCEPT} |
| 1354 18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" |
| 1355 LIMIT {EXCEPT} |
| 1356 |
| 1357 19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" |
| 1358 {ORDER BY} {INTERSECT} |
| 1359 20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2" |
| 1360 {ORDER BY} {INTERSECT} |
| 1361 21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2" |
| 1362 {ORDER BY} {INTERSECT} |
| 1363 22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" |
| 1364 LIMIT {INTERSECT} |
| 1365 23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" |
| 1366 LIMIT {INTERSECT} |
| 1367 24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" |
| 1368 LIMIT {INTERSECT} |
| 1369 } { |
| 1370 set err "$op1 clause should come after $op2 not before" |
| 1371 do_catchsql_test e_select-7.2.$tn $select [list 1 $err] |
| 1372 } |
| 1373 |
| 1374 # EVIDENCE-OF: R-45440-25633 ORDER BY and LIMIT clauses may only occur |
| 1375 # at the end of the entire compound SELECT, and then only if the final |
| 1376 # element of the compound is not a VALUES clause. |
| 1377 # |
| 1378 foreach {tn select} { |
| 1379 1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a" |
| 1380 2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1" |
| 1381 3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3" |
| 1382 4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" |
| 1383 5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
| 1384 6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
| 1385 |
| 1386 7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a" |
| 1387 8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1" |
| 1388 8b "VALUES('8b') UNION SELECT max(e) FROM j2 ORDER BY 1" |
| 1389 9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3" |
| 1390 10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" |
| 1391 11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
| 1392 12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
| 1393 |
| 1394 13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a" |
| 1395 14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1" |
| 1396 15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3" |
| 1397 16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" |
| 1398 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
| 1399 18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
| 1400 |
| 1401 19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a" |
| 1402 20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1" |
| 1403 21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3" |
| 1404 22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" |
| 1405 23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" |
| 1406 24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" |
| 1407 } { |
| 1408 do_test e_select-7.3.$tn { catch {execsql $select} msg } 0 |
| 1409 } |
| 1410 foreach {tn select} { |
| 1411 50 "SELECT * FROM j1 ORDER BY 1 UNION ALL SELECT * FROM j2,j3" |
| 1412 51 "SELECT * FROM j1 LIMIT 1 UNION ALL SELECT * FROM j2,j3" |
| 1413 52 "SELECT count(*) FROM j1 UNION ALL VALUES(11) ORDER BY 1" |
| 1414 53 "SELECT count(*) FROM j1 UNION ALL VALUES(11) LIMIT 1" |
| 1415 } { |
| 1416 do_test e_select-7.3.$tn { catch {execsql $select} msg } 1 |
| 1417 } |
| 1418 |
| 1419 # EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL |
| 1420 # operator returns all the rows from the SELECT to the left of the UNION |
| 1421 # ALL operator, and all the rows from the SELECT to the right of it. |
| 1422 # |
| 1423 drop_all_tables |
| 1424 do_execsql_test e_select-7.4.0 { |
| 1425 CREATE TABLE q1(a TEXT, b INTEGER, c); |
| 1426 CREATE TABLE q2(d NUMBER, e BLOB); |
| 1427 CREATE TABLE q3(f REAL, g); |
| 1428 |
| 1429 INSERT INTO q1 VALUES(16, -87.66, NULL); |
| 1430 INSERT INTO q1 VALUES('legible', 94, -42.47); |
| 1431 INSERT INTO q1 VALUES('beauty', 36, NULL); |
| 1432 |
| 1433 INSERT INTO q2 VALUES('legible', 1); |
| 1434 INSERT INTO q2 VALUES('beauty', 2); |
| 1435 INSERT INTO q2 VALUES(-65.91, 4); |
| 1436 INSERT INTO q2 VALUES('emanating', -16.56); |
| 1437 |
| 1438 INSERT INTO q3 VALUES('beauty', 2); |
| 1439 INSERT INTO q3 VALUES('beauty', 2); |
| 1440 } {} |
| 1441 do_select_tests e_select-7.4 { |
| 1442 1 {SELECT a FROM q1 UNION ALL SELECT d FROM q2} |
| 1443 {16 legible beauty legible beauty -65.91 emanating} |
| 1444 |
| 1445 2 {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1} |
| 1446 {16 -87.66 {} x legible 1} |
| 1447 |
| 1448 3 {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} |
| 1449 {3 -16.56} |
| 1450 |
| 1451 4 {SELECT * FROM q2 UNION ALL SELECT * FROM q3} |
| 1452 {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2} |
| 1453 } |
| 1454 |
| 1455 # EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as |
| 1456 # UNION ALL, except that duplicate rows are removed from the final |
| 1457 # result set. |
| 1458 # |
| 1459 do_select_tests e_select-7.5 { |
| 1460 1 {SELECT a FROM q1 UNION SELECT d FROM q2} |
| 1461 {-65.91 16 beauty emanating legible} |
| 1462 |
| 1463 2 {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1} |
| 1464 {16 -87.66 {} x legible 1} |
| 1465 |
| 1466 3 {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} |
| 1467 {-16.56 3} |
| 1468 |
| 1469 4 {SELECT * FROM q2 UNION SELECT * FROM q3} |
| 1470 {-65.91 4 beauty 2 emanating -16.56 legible 1} |
| 1471 } |
| 1472 |
| 1473 # EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the |
| 1474 # intersection of the results of the left and right SELECTs. |
| 1475 # |
| 1476 do_select_tests e_select-7.6 { |
| 1477 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} |
| 1478 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} |
| 1479 } |
| 1480 |
| 1481 # EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of |
| 1482 # rows returned by the left SELECT that are not also returned by the |
| 1483 # right-hand SELECT. |
| 1484 # |
| 1485 do_select_tests e_select-7.7 { |
| 1486 1 {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16} |
| 1487 |
| 1488 2 {SELECT * FROM q2 EXCEPT SELECT * FROM q3} |
| 1489 {-65.91 4 emanating -16.56 legible 1} |
| 1490 } |
| 1491 |
| 1492 # EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results |
| 1493 # of INTERSECT and EXCEPT operators before the result set is returned. |
| 1494 # |
| 1495 do_select_tests e_select-7.8 { |
| 1496 0 {SELECT * FROM q3} {beauty 2 beauty 2} |
| 1497 |
| 1498 1 {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2} |
| 1499 2 {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1} {beauty 2} |
| 1500 } |
| 1501 |
| 1502 # EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate |
| 1503 # rows for the results of compound SELECT operators, NULL values are |
| 1504 # considered equal to other NULL values and distinct from all non-NULL |
| 1505 # values. |
| 1506 # |
| 1507 db nullvalue null |
| 1508 do_select_tests e_select-7.9 { |
| 1509 1 {SELECT NULL UNION ALL SELECT NULL} {null null} |
| 1510 2 {SELECT NULL UNION SELECT NULL} {null} |
| 1511 3 {SELECT NULL INTERSECT SELECT NULL} {null} |
| 1512 4 {SELECT NULL EXCEPT SELECT NULL} {} |
| 1513 |
| 1514 5 {SELECT NULL UNION ALL SELECT 'ab'} {null ab} |
| 1515 6 {SELECT NULL UNION SELECT 'ab'} {null ab} |
| 1516 7 {SELECT NULL INTERSECT SELECT 'ab'} {} |
| 1517 8 {SELECT NULL EXCEPT SELECT 'ab'} {null} |
| 1518 |
| 1519 9 {SELECT NULL UNION ALL SELECT 0} {null 0} |
| 1520 10 {SELECT NULL UNION SELECT 0} {null 0} |
| 1521 11 {SELECT NULL INTERSECT SELECT 0} {} |
| 1522 12 {SELECT NULL EXCEPT SELECT 0} {null} |
| 1523 |
| 1524 13 {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2} |
| 1525 14 {SELECT c FROM q1 UNION SELECT g FROM q3} {null -42.47 2} |
| 1526 15 {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {} |
| 1527 16 {SELECT c FROM q1 EXCEPT SELECT g FROM q3} {null -42.47} |
| 1528 } |
| 1529 db nullvalue {} |
| 1530 |
| 1531 # EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two |
| 1532 # text values is determined as if the columns of the left and right-hand |
| 1533 # SELECT statements were the left and right-hand operands of the equals |
| 1534 # (=) operator, except that greater precedence is not assigned to a |
| 1535 # collation sequence specified with the postfix COLLATE operator. |
| 1536 # |
| 1537 drop_all_tables |
| 1538 do_execsql_test e_select-7.10.0 { |
| 1539 CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c); |
| 1540 INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); |
| 1541 } {} |
| 1542 do_select_tests e_select-7.10 { |
| 1543 1 {SELECT 'abc' UNION SELECT 'ABC'} {ABC abc} |
| 1544 2 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC} |
| 1545 3 {SELECT 'abc' UNION SELECT 'ABC' COLLATE nocase} {ABC} |
| 1546 4 {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc} |
| 1547 5 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC} |
| 1548 |
| 1549 6 {SELECT a FROM y1 UNION SELECT b FROM y1} {abc} |
| 1550 7 {SELECT b FROM y1 UNION SELECT a FROM y1} {Abc abc} |
| 1551 8 {SELECT a FROM y1 UNION SELECT c FROM y1} {aBC} |
| 1552 |
| 1553 9 {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC} |
| 1554 } |
| 1555 |
| 1556 # EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to |
| 1557 # any values when comparing rows as part of a compound SELECT. |
| 1558 # |
| 1559 drop_all_tables |
| 1560 do_execsql_test e_select-7.10.0 { |
| 1561 CREATE TABLE w1(a TEXT, b NUMBER); |
| 1562 CREATE TABLE w2(a, b TEXT); |
| 1563 |
| 1564 INSERT INTO w1 VALUES('1', 4.1); |
| 1565 INSERT INTO w2 VALUES(1, 4.1); |
| 1566 } {} |
| 1567 |
| 1568 do_select_tests e_select-7.11 { |
| 1569 1 { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1} |
| 1570 2 { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1} |
| 1571 3 { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1} |
| 1572 4 { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1} |
| 1573 |
| 1574 5 { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {} |
| 1575 6 { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {} |
| 1576 7 { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {} |
| 1577 8 { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {} |
| 1578 |
| 1579 9 { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1} |
| 1580 10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1} |
| 1581 11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1} |
| 1582 12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1} |
| 1583 } |
| 1584 |
| 1585 |
| 1586 # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are |
| 1587 # connected into a compound SELECT, they group from left to right. In |
| 1588 # other words, if "A", "B" and "C" are all simple SELECT statements, (A |
| 1589 # op B op C) is processed as ((A op B) op C). |
| 1590 # |
| 1591 # e_select-7.12.1: Precedence of UNION vs. INTERSECT |
| 1592 # e_select-7.12.2: Precedence of UNION vs. UNION ALL |
| 1593 # e_select-7.12.3: Precedence of UNION vs. EXCEPT |
| 1594 # e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL |
| 1595 # e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT |
| 1596 # e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT |
| 1597 # e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as |
| 1598 # "(a EXCEPT b) EXCEPT c". |
| 1599 # |
| 1600 # The INTERSECT and EXCEPT operations are mutually commutative. So |
| 1601 # the e_select-7.12.5 test cases do not prove very much. |
| 1602 # |
| 1603 drop_all_tables |
| 1604 do_execsql_test e_select-7.12.0 { |
| 1605 CREATE TABLE t1(x); |
| 1606 INSERT INTO t1 VALUES(1); |
| 1607 INSERT INTO t1 VALUES(2); |
| 1608 INSERT INTO t1 VALUES(3); |
| 1609 } {} |
| 1610 foreach {tn select res} { |
| 1611 1a "(1,2) INTERSECT (1) UNION (3)" {1 3} |
| 1612 1b "(3) UNION (1,2) INTERSECT (1)" {1} |
| 1613 |
| 1614 2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1} |
| 1615 2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3} |
| 1616 |
| 1617 3a "(1,2) UNION (3) EXCEPT (1)" {2 3} |
| 1618 3b "(1,2) EXCEPT (3) UNION (1)" {1 2} |
| 1619 |
| 1620 4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3} |
| 1621 4b "(3) UNION (1,2) INTERSECT (1)" {1} |
| 1622 |
| 1623 5a "(1,2) INTERSECT (2) EXCEPT (2)" {} |
| 1624 5b "(2,3) EXCEPT (2) INTERSECT (2)" {} |
| 1625 |
| 1626 6a "(2) UNION ALL (2) EXCEPT (2)" {} |
| 1627 6b "(2) EXCEPT (2) UNION ALL (2)" {2} |
| 1628 |
| 1629 7 "(2,3) EXCEPT (2) EXCEPT (3)" {} |
| 1630 } { |
| 1631 set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select] |
| 1632 do_execsql_test e_select-7.12.$tn $select [list {*}$res] |
| 1633 } |
| 1634 |
| 1635 |
| 1636 #------------------------------------------------------------------------- |
| 1637 # ORDER BY clauses |
| 1638 # |
| 1639 |
| 1640 drop_all_tables |
| 1641 do_execsql_test e_select-8.1.0 { |
| 1642 CREATE TABLE d1(x, y, z); |
| 1643 |
| 1644 INSERT INTO d1 VALUES(1, 2, 3); |
| 1645 INSERT INTO d1 VALUES(2, 5, -1); |
| 1646 INSERT INTO d1 VALUES(1, 2, 8); |
| 1647 INSERT INTO d1 VALUES(1, 2, 7); |
| 1648 INSERT INTO d1 VALUES(2, 4, 93); |
| 1649 INSERT INTO d1 VALUES(1, 2, -20); |
| 1650 INSERT INTO d1 VALUES(1, 4, 93); |
| 1651 INSERT INTO d1 VALUES(1, 5, -1); |
| 1652 |
| 1653 CREATE TABLE d2(a, b); |
| 1654 INSERT INTO d2 VALUES('gently', 'failings'); |
| 1655 INSERT INTO d2 VALUES('commercials', 'bathrobe'); |
| 1656 INSERT INTO d2 VALUES('iterate', 'sexton'); |
| 1657 INSERT INTO d2 VALUES('babied', 'charitableness'); |
| 1658 INSERT INTO d2 VALUES('solemnness', 'annexed'); |
| 1659 INSERT INTO d2 VALUES('rejoicing', 'liabilities'); |
| 1660 INSERT INTO d2 VALUES('pragmatist', 'guarded'); |
| 1661 INSERT INTO d2 VALUES('barked', 'interrupted'); |
| 1662 INSERT INTO d2 VALUES('reemphasizes', 'reply'); |
| 1663 INSERT INTO d2 VALUES('lad', 'relenting'); |
| 1664 } {} |
| 1665 |
| 1666 # EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results |
| 1667 # of evaluating the left-most expression in the ORDER BY list, then ties |
| 1668 # are broken by evaluating the second left-most expression and so on. |
| 1669 # |
| 1670 do_select_tests e_select-8.1 { |
| 1671 1 "SELECT * FROM d1 ORDER BY x, y, z" { |
| 1672 1 2 -20 1 2 3 1 2 7 1 2 8 |
| 1673 1 4 93 1 5 -1 2 4 93 2 5 -1 |
| 1674 } |
| 1675 } |
| 1676 |
| 1677 # EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally |
| 1678 # followed by one of the keywords ASC (smaller values are returned |
| 1679 # first) or DESC (larger values are returned first). |
| 1680 # |
| 1681 # Test cases e_select-8.2.* test the above. |
| 1682 # |
| 1683 # EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows |
| 1684 # are sorted in ascending (smaller values first) order by default. |
| 1685 # |
| 1686 # Test cases e_select-8.3.* test the above. All 8.3 test cases are |
| 1687 # copies of 8.2 test cases with the explicit "ASC" removed. |
| 1688 # |
| 1689 do_select_tests e_select-8 { |
| 1690 2.1 "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" { |
| 1691 1 2 -20 1 2 3 1 2 7 1 2 8 |
| 1692 1 4 93 1 5 -1 2 4 93 2 5 -1 |
| 1693 } |
| 1694 2.2 "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" { |
| 1695 2 5 -1 2 4 93 1 5 -1 1 4 93 |
| 1696 1 2 8 1 2 7 1 2 3 1 2 -20 |
| 1697 } |
| 1698 2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" { |
| 1699 2 4 93 2 5 -1 1 2 8 1 2 7 |
| 1700 1 2 3 1 2 -20 1 4 93 1 5 -1 |
| 1701 } |
| 1702 2.4 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" { |
| 1703 2 4 93 2 5 -1 1 2 -20 1 2 3 |
| 1704 1 2 7 1 2 8 1 4 93 1 5 -1 |
| 1705 } |
| 1706 |
| 1707 3.1 "SELECT * FROM d1 ORDER BY x, y, z" { |
| 1708 1 2 -20 1 2 3 1 2 7 1 2 8 |
| 1709 1 4 93 1 5 -1 2 4 93 2 5 -1 |
| 1710 } |
| 1711 3.3 "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" { |
| 1712 2 4 93 2 5 -1 1 2 8 1 2 7 |
| 1713 1 2 3 1 2 -20 1 4 93 1 5 -1 |
| 1714 } |
| 1715 3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" { |
| 1716 2 4 93 2 5 -1 1 2 -20 1 2 3 |
| 1717 1 2 7 1 2 8 1 4 93 1 5 -1 |
| 1718 } |
| 1719 } |
| 1720 |
| 1721 # EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant |
| 1722 # integer K then the expression is considered an alias for the K-th |
| 1723 # column of the result set (columns are numbered from left to right |
| 1724 # starting with 1). |
| 1725 # |
| 1726 do_select_tests e_select-8.4 { |
| 1727 1 "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" { |
| 1728 1 2 -20 1 2 3 1 2 7 1 2 8 |
| 1729 1 4 93 1 5 -1 2 4 93 2 5 -1 |
| 1730 } |
| 1731 2 "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" { |
| 1732 2 5 -1 2 4 93 1 5 -1 1 4 93 |
| 1733 1 2 8 1 2 7 1 2 3 1 2 -20 |
| 1734 } |
| 1735 3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" { |
| 1736 2 4 93 2 5 -1 1 2 8 1 2 7 |
| 1737 1 2 3 1 2 -20 1 4 93 1 5 -1 |
| 1738 } |
| 1739 4 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" { |
| 1740 2 4 93 2 5 -1 1 2 -20 1 2 3 |
| 1741 1 2 7 1 2 8 1 4 93 1 5 -1 |
| 1742 } |
| 1743 5 "SELECT * FROM d1 ORDER BY 1, 2, 3" { |
| 1744 1 2 -20 1 2 3 1 2 7 1 2 8 |
| 1745 1 4 93 1 5 -1 2 4 93 2 5 -1 |
| 1746 } |
| 1747 6 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" { |
| 1748 2 4 93 2 5 -1 1 2 8 1 2 7 |
| 1749 1 2 3 1 2 -20 1 4 93 1 5 -1 |
| 1750 } |
| 1751 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { |
| 1752 2 4 93 2 5 -1 1 2 -20 1 2 3 |
| 1753 1 2 7 1 2 8 1 4 93 1 5 -1 |
| 1754 } |
| 1755 8 "SELECT z, x FROM d1 ORDER BY 2" { |
| 1756 /# 1 # 1 # 1 # 1 |
| 1757 # 1 # 1 # 2 # 2/ |
| 1758 } |
| 1759 9 "SELECT z, x FROM d1 ORDER BY 1" { |
| 1760 /-20 1 -1 # -1 # 3 1 |
| 1761 7 1 8 1 93 # 93 #/ |
| 1762 } |
| 1763 } |
| 1764 |
| 1765 # EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier |
| 1766 # that corresponds to the alias of one of the output columns, then the |
| 1767 # expression is considered an alias for that column. |
| 1768 # |
| 1769 do_select_tests e_select-8.5 { |
| 1770 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { |
| 1771 -19 0 0 4 8 9 94 94 |
| 1772 } |
| 1773 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { |
| 1774 94 94 9 8 4 0 0 -19 |
| 1775 } |
| 1776 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { |
| 1777 /# 1 # 1 # 1 # 1 # 1 # 1 # 2 # 2/ |
| 1778 } |
| 1779 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { |
| 1780 /-20 1 -1 # -1 # 3 1 7 1 8 1 93 # 93 #/ |
| 1781 } |
| 1782 } |
| 1783 |
| 1784 # EVIDENCE-OF: R-65068-27207 Otherwise, if the ORDER BY expression is |
| 1785 # any other expression, it is evaluated and the returned value used to |
| 1786 # order the output rows. |
| 1787 # |
| 1788 # EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, |
| 1789 # then an ORDER BY may contain any arbitrary expressions. |
| 1790 # |
| 1791 do_select_tests e_select-8.6 { |
| 1792 1 "SELECT * FROM d1 ORDER BY x+y+z" { |
| 1793 1 2 -20 1 5 -1 1 2 3 2 5 -1 |
| 1794 1 2 7 1 2 8 1 4 93 2 4 93 |
| 1795 } |
| 1796 2 "SELECT * FROM d1 ORDER BY x*z" { |
| 1797 1 2 -20 2 5 -1 1 5 -1 1 2 3 |
| 1798 1 2 7 1 2 8 1 4 93 2 4 93 |
| 1799 } |
| 1800 3 "SELECT * FROM d1 ORDER BY y*z" { |
| 1801 1 2 -20 2 5 -1 1 5 -1 1 2 3 |
| 1802 1 2 7 1 2 8 2 4 93 1 4 93 |
| 1803 } |
| 1804 } |
| 1805 |
| 1806 # EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound |
| 1807 # SELECT, then ORDER BY expressions that are not aliases to output |
| 1808 # columns must be exactly the same as an expression used as an output |
| 1809 # column. |
| 1810 # |
| 1811 do_select_tests e_select-8.7.1 -error { |
| 1812 %s ORDER BY term does not match any column in the result set |
| 1813 } { |
| 1814 1 "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" 1st |
| 1815 2 "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd |
| 1816 } |
| 1817 |
| 1818 do_select_tests e_select-8.7.2 { |
| 1819 1 "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" { |
| 1820 -20 -2 -1 3 7 8 93 186 babied barked commercials gently |
| 1821 iterate lad pragmatist reemphasizes rejoicing solemnness |
| 1822 } |
| 1823 2 "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" { |
| 1824 1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 |
| 1825 babied charitableness barked interrupted commercials bathrobe gently |
| 1826 failings iterate sexton lad relenting pragmatist guarded reemphasizes reply |
| 1827 rejoicing liabilities solemnness annexed |
| 1828 } |
| 1829 } |
| 1830 |
| 1831 do_execsql_test e_select-8.8.0 { |
| 1832 CREATE TABLE d3(a); |
| 1833 INSERT INTO d3 VALUES('text'); |
| 1834 INSERT INTO d3 VALUES(14.1); |
| 1835 INSERT INTO d3 VALUES(13); |
| 1836 INSERT INTO d3 VALUES(X'78787878'); |
| 1837 INSERT INTO d3 VALUES(15); |
| 1838 INSERT INTO d3 VALUES(12.9); |
| 1839 INSERT INTO d3 VALUES(null); |
| 1840 |
| 1841 CREATE TABLE d4(x COLLATE nocase); |
| 1842 INSERT INTO d4 VALUES('abc'); |
| 1843 INSERT INTO d4 VALUES('ghi'); |
| 1844 INSERT INTO d4 VALUES('DEF'); |
| 1845 INSERT INTO d4 VALUES('JKL'); |
| 1846 } {} |
| 1847 |
| 1848 # EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values |
| 1849 # are compared in the same way as for comparison expressions. |
| 1850 # |
| 1851 # The following tests verify that values of different types are sorted |
| 1852 # correctly, and that mixed real and integer values are compared properly. |
| 1853 # |
| 1854 do_execsql_test e_select-8.8.1 { |
| 1855 SELECT a FROM d3 ORDER BY a |
| 1856 } {{} 12.9 13 14.1 15 text xxxx} |
| 1857 do_execsql_test e_select-8.8.2 { |
| 1858 SELECT a FROM d3 ORDER BY a DESC |
| 1859 } {xxxx text 15 14.1 13 12.9 {}} |
| 1860 |
| 1861 |
| 1862 # EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a |
| 1863 # collation sequence using the postfix COLLATE operator, then the |
| 1864 # specified collation sequence is used. |
| 1865 # |
| 1866 do_execsql_test e_select-8.9.1 { |
| 1867 SELECT x FROM d4 ORDER BY 1 COLLATE binary |
| 1868 } {DEF JKL abc ghi} |
| 1869 do_execsql_test e_select-8.9.2 { |
| 1870 SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase |
| 1871 } {abc DEF ghi JKL} |
| 1872 |
| 1873 # EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is |
| 1874 # an alias to an expression that has been assigned a collation sequence |
| 1875 # using the postfix COLLATE operator, then the collation sequence |
| 1876 # assigned to the aliased expression is used. |
| 1877 # |
| 1878 # In the test 8.10.2, the only result-column expression has no alias. So the |
| 1879 # ORDER BY expression is not a reference to it and therefore does not inherit |
| 1880 # the collation sequence. In test 8.10.3, "x" is the alias (as well as the |
| 1881 # column name), so the ORDER BY expression is interpreted as an alias and the |
| 1882 # collation sequence attached to the result column is used for sorting. |
| 1883 # |
| 1884 do_execsql_test e_select-8.10.1 { |
| 1885 SELECT x COLLATE binary FROM d4 ORDER BY 1 |
| 1886 } {DEF JKL abc ghi} |
| 1887 do_execsql_test e_select-8.10.2 { |
| 1888 SELECT x COLLATE binary FROM d4 ORDER BY x |
| 1889 } {abc DEF ghi JKL} |
| 1890 do_execsql_test e_select-8.10.3 { |
| 1891 SELECT x COLLATE binary AS x FROM d4 ORDER BY x |
| 1892 } {DEF JKL abc ghi} |
| 1893 |
| 1894 # EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a |
| 1895 # column or an alias of an expression that is a column, then the default |
| 1896 # collation sequence for the column is used. |
| 1897 # |
| 1898 do_execsql_test e_select-8.11.1 { |
| 1899 SELECT x AS y FROM d4 ORDER BY y |
| 1900 } {abc DEF ghi JKL} |
| 1901 do_execsql_test e_select-8.11.2 { |
| 1902 SELECT x||'' FROM d4 ORDER BY x |
| 1903 } {abc DEF ghi JKL} |
| 1904 |
| 1905 # EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is |
| 1906 # used. |
| 1907 # |
| 1908 do_execsql_test e_select-8.12.1 { |
| 1909 SELECT x FROM d4 ORDER BY x||'' |
| 1910 } {DEF JKL abc ghi} |
| 1911 |
| 1912 # EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer |
| 1913 # alias, then SQLite searches the left-most SELECT in the compound for a |
| 1914 # result column that matches either the second or third rules above. If |
| 1915 # a match is found, the search stops and the expression is handled as an |
| 1916 # alias for the result column that it has been matched against. |
| 1917 # Otherwise, the next SELECT to the right is tried, and so on. |
| 1918 # |
| 1919 do_execsql_test e_select-8.13.0 { |
| 1920 CREATE TABLE d5(a, b); |
| 1921 CREATE TABLE d6(c, d); |
| 1922 CREATE TABLE d7(e, f); |
| 1923 |
| 1924 INSERT INTO d5 VALUES(1, 'f'); |
| 1925 INSERT INTO d6 VALUES(2, 'e'); |
| 1926 INSERT INTO d7 VALUES(3, 'd'); |
| 1927 INSERT INTO d5 VALUES(4, 'c'); |
| 1928 INSERT INTO d6 VALUES(5, 'b'); |
| 1929 INSERT INTO d7 VALUES(6, 'a'); |
| 1930 |
| 1931 CREATE TABLE d8(x COLLATE nocase); |
| 1932 CREATE TABLE d9(y COLLATE nocase); |
| 1933 |
| 1934 INSERT INTO d8 VALUES('a'); |
| 1935 INSERT INTO d9 VALUES('B'); |
| 1936 INSERT INTO d8 VALUES('c'); |
| 1937 INSERT INTO d9 VALUES('D'); |
| 1938 } {} |
| 1939 do_select_tests e_select-8.13 { |
| 1940 1 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
| 1941 ORDER BY a |
| 1942 } {1 2 3 4 5 6} |
| 1943 2 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
| 1944 ORDER BY c |
| 1945 } {1 2 3 4 5 6} |
| 1946 3 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
| 1947 ORDER BY e |
| 1948 } {1 2 3 4 5 6} |
| 1949 4 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 |
| 1950 ORDER BY 1 |
| 1951 } {1 2 3 4 5 6} |
| 1952 |
| 1953 5 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } |
| 1954 {f 1 c 4 4 c 1 f} |
| 1955 6 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } |
| 1956 {f 1 c 4 4 c 1 f} |
| 1957 |
| 1958 7 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } |
| 1959 {1 f 4 c c 4 f 1} |
| 1960 8 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } |
| 1961 {1 f 4 c c 4 f 1} |
| 1962 |
| 1963 9 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } |
| 1964 {f 2 c 5 4 c 1 f} |
| 1965 10 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } |
| 1966 {f 2 c 5 4 c 1 f} |
| 1967 |
| 1968 11 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } |
| 1969 {2 f 5 c c 5 f 2} |
| 1970 12 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } |
| 1971 {2 f 5 c c 5 f 2} |
| 1972 } |
| 1973 |
| 1974 # EVIDENCE-OF: R-39265-04070 If no matching expression can be found in |
| 1975 # the result columns of any constituent SELECT, it is an error. |
| 1976 # |
| 1977 do_select_tests e_select-8.14 -error { |
| 1978 %s ORDER BY term does not match any column in the result set |
| 1979 } { |
| 1980 1 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 } 1st |
| 1981 2 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 } 2nd |
| 1982 3 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' } 1st |
| 1983 4 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah } 1st |
| 1984 5 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d } 3rd |
| 1985 6 { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b } 4th |
| 1986 } |
| 1987 |
| 1988 # EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is |
| 1989 # processed separately and may be matched against result columns from |
| 1990 # different SELECT statements in the compound. |
| 1991 # |
| 1992 do_select_tests e_select-8.15 { |
| 1993 1 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d } |
| 1994 {1 e 1 f 4 b 4 c} |
| 1995 2 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b } |
| 1996 {1 e 1 f 4 b 4 c} |
| 1997 3 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 } |
| 1998 {1 e 1 f 4 b 4 c} |
| 1999 } |
| 2000 |
| 2001 |
| 2002 #------------------------------------------------------------------------- |
| 2003 # Tests related to statements made about the LIMIT/OFFSET clause. |
| 2004 # |
| 2005 do_execsql_test e_select-9.0 { |
| 2006 CREATE TABLE f1(a, b); |
| 2007 INSERT INTO f1 VALUES(26, 'z'); |
| 2008 INSERT INTO f1 VALUES(25, 'y'); |
| 2009 INSERT INTO f1 VALUES(24, 'x'); |
| 2010 INSERT INTO f1 VALUES(23, 'w'); |
| 2011 INSERT INTO f1 VALUES(22, 'v'); |
| 2012 INSERT INTO f1 VALUES(21, 'u'); |
| 2013 INSERT INTO f1 VALUES(20, 't'); |
| 2014 INSERT INTO f1 VALUES(19, 's'); |
| 2015 INSERT INTO f1 VALUES(18, 'r'); |
| 2016 INSERT INTO f1 VALUES(17, 'q'); |
| 2017 INSERT INTO f1 VALUES(16, 'p'); |
| 2018 INSERT INTO f1 VALUES(15, 'o'); |
| 2019 INSERT INTO f1 VALUES(14, 'n'); |
| 2020 INSERT INTO f1 VALUES(13, 'm'); |
| 2021 INSERT INTO f1 VALUES(12, 'l'); |
| 2022 INSERT INTO f1 VALUES(11, 'k'); |
| 2023 INSERT INTO f1 VALUES(10, 'j'); |
| 2024 INSERT INTO f1 VALUES(9, 'i'); |
| 2025 INSERT INTO f1 VALUES(8, 'h'); |
| 2026 INSERT INTO f1 VALUES(7, 'g'); |
| 2027 INSERT INTO f1 VALUES(6, 'f'); |
| 2028 INSERT INTO f1 VALUES(5, 'e'); |
| 2029 INSERT INTO f1 VALUES(4, 'd'); |
| 2030 INSERT INTO f1 VALUES(3, 'c'); |
| 2031 INSERT INTO f1 VALUES(2, 'b'); |
| 2032 INSERT INTO f1 VALUES(1, 'a'); |
| 2033 } {} |
| 2034 |
| 2035 # EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the |
| 2036 # LIMIT clause, so long as it evaluates to an integer or a value that |
| 2037 # can be losslessly converted to an integer. |
| 2038 # |
| 2039 do_select_tests e_select-9.1 { |
| 2040 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e} |
| 2041 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e} |
| 2042 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } |
| 2043 {a b c d e} |
| 2044 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e} |
| 2045 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e} |
| 2046 } |
| 2047 |
| 2048 # EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value |
| 2049 # or any other value that cannot be losslessly converted to an integer, |
| 2050 # an error is returned. |
| 2051 # |
| 2052 |
| 2053 do_select_tests e_select-9.2 -error "datatype mismatch" { |
| 2054 1 { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {} |
| 2055 2 { SELECT b FROM f1 ORDER BY a LIMIT NULL } {} |
| 2056 3 { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {} |
| 2057 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {} |
| 2058 5 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {} |
| 2059 } |
| 2060 |
| 2061 # EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a |
| 2062 # negative value, then there is no upper bound on the number of rows |
| 2063 # returned. |
| 2064 # |
| 2065 do_select_tests e_select-9.4 { |
| 2066 1 { SELECT b FROM f1 ORDER BY a LIMIT -1 } |
| 2067 {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} |
| 2068 2 { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } |
| 2069 {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} |
| 2070 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 } |
| 2071 {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} |
| 2072 } |
| 2073 |
| 2074 # EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N |
| 2075 # rows of its result set only, where N is the value that the LIMIT |
| 2076 # expression evaluates to. |
| 2077 # |
| 2078 do_select_tests e_select-9.5 { |
| 2079 1 { SELECT b FROM f1 ORDER BY a LIMIT 0 } {} |
| 2080 2 { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w} |
| 2081 3 { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s} |
| 2082 4 { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o} |
| 2083 } |
| 2084 |
| 2085 # EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return |
| 2086 # less than N rows without a LIMIT clause, then the entire result set is |
| 2087 # returned. |
| 2088 # |
| 2089 do_select_tests e_select-9.6 { |
| 2090 1 { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z} |
| 2091 2 { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5} |
| 2092 } |
| 2093 |
| 2094 |
| 2095 # EVIDENCE-OF: R-24188-24349 The expression attached to the optional |
| 2096 # OFFSET clause that may follow a LIMIT clause must also evaluate to an |
| 2097 # integer, or a value that can be losslessly converted to an integer. |
| 2098 # |
| 2099 foreach {tn select} { |
| 2100 1 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } |
| 2101 2 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } |
| 2102 3 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } |
| 2103 4 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } |
| 2104 5 { SELECT b FROM f1 ORDER BY a |
| 2105 LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) |
| 2106 } |
| 2107 } { |
| 2108 do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}} |
| 2109 } |
| 2110 |
| 2111 # EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then |
| 2112 # the first M rows are omitted from the result set returned by the |
| 2113 # SELECT statement and the next N rows are returned, where M and N are |
| 2114 # the values that the OFFSET and LIMIT clauses evaluate to, |
| 2115 # respectively. |
| 2116 # |
| 2117 do_select_tests e_select-9.8 { |
| 2118 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o} |
| 2119 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o} |
| 2120 3 { SELECT b FROM f1 ORDER BY a |
| 2121 LIMIT (SELECT a FROM f1 WHERE b='j') |
| 2122 OFFSET (SELECT a FROM f1 WHERE b='b') |
| 2123 } {c d e f g h i j k l} |
| 2124 4 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h} |
| 2125 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e} |
| 2126 6 { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {} |
| 2127 7 { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r} |
| 2128 } |
| 2129 |
| 2130 # EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than |
| 2131 # M+N rows if it did not have a LIMIT clause, then the first M rows are |
| 2132 # skipped and the remaining rows (if any) are returned. |
| 2133 # |
| 2134 do_select_tests e_select-9.9 { |
| 2135 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z} |
| 2136 2 { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1} |
| 2137 } |
| 2138 |
| 2139 |
| 2140 # EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a |
| 2141 # negative value, the results are the same as if it had evaluated to |
| 2142 # zero. |
| 2143 # |
| 2144 do_select_tests e_select-9.10 { |
| 2145 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e} |
| 2146 2 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e} |
| 2147 3 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 } {a b c d e} |
| 2148 } |
| 2149 |
| 2150 # EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the |
| 2151 # LIMIT clause may specify two scalar expressions separated by a comma. |
| 2152 # |
| 2153 # EVIDENCE-OF: R-33788-46243 In this case, the first expression is used |
| 2154 # as the OFFSET expression and the second as the LIMIT expression. |
| 2155 # |
| 2156 do_select_tests e_select-9.11 { |
| 2157 1 { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o} |
| 2158 2 { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o} |
| 2159 3 { SELECT b FROM f1 ORDER BY a |
| 2160 LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') |
| 2161 } {c d e f g h i j k l} |
| 2162 4 { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h} |
| 2163 5 { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e} |
| 2164 6 { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {} |
| 2165 7 { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r} |
| 2166 |
| 2167 8 { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z} |
| 2168 9 { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1} |
| 2169 |
| 2170 10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e} |
| 2171 11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e} |
| 2172 12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e} |
| 2173 } |
| 2174 |
| 2175 finish_test |
OLD | NEW |