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