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 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19do_execsql_test e_select-1.0 { 20 CREATE TABLE t1(a, b); 21 INSERT INTO t1 VALUES('a', 'one'); 22 INSERT INTO t1 VALUES('b', 'two'); 23 INSERT INTO t1 VALUES('c', 'three'); 24 25 CREATE TABLE t2(a, b); 26 INSERT INTO t2 VALUES('a', 'I'); 27 INSERT INTO t2 VALUES('b', 'II'); 28 INSERT INTO t2 VALUES('c', 'III'); 29 30 CREATE TABLE t3(a, c); 31 INSERT INTO t3 VALUES('a', 1); 32 INSERT INTO t3 VALUES('b', 2); 33 34 CREATE TABLE t4(a, c); 35 INSERT INTO t4 VALUES('a', NULL); 36 INSERT INTO t4 VALUES('b', 2); 37} {} 38set t1_cross_t2 [list \ 39 a one a I a one b II \ 40 a one c III b two a I \ 41 b two b II b two c III \ 42 c three a I c three b II \ 43 c three c III \ 44] 45set t1_cross_t1 [list \ 46 a one a one a one b two \ 47 a one c three b two a one \ 48 b two b two b two c three \ 49 c three a one c three b two \ 50 c three c three \ 51] 52 53 54# This proc is a specialized version of [do_execsql_test]. 55# 56# The second argument to this proc must be a SELECT statement that 57# features a cross join of some time. Instead of the usual ",", 58# "CROSS JOIN" or "INNER JOIN" join-op, the string %JOIN% must be 59# substituted. 60# 61# This test runs the SELECT three times - once with: 62# 63# * s/%JOIN%/,/ 64# * s/%JOIN%/JOIN/ 65# * s/%JOIN%/INNER JOIN/ 66# * s/%JOIN%/CROSS JOIN/ 67# 68# and checks that each time the results of the SELECT are $res. 69# 70proc do_join_test {tn select res} { 71 foreach {tn2 joinop} [list 1 , 2 "CROSS JOIN" 3 "INNER JOIN"] { 72 set S [string map [list %JOIN% $joinop] $select] 73 uplevel do_execsql_test $tn.$tn2 [list $S] [list $res] 74 } 75} 76 77#------------------------------------------------------------------------- 78# The following tests check that all paths on the syntax diagrams on 79# the lang_select.html page may be taken. 80# 81# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint 82# 83do_join_test e_select-0.1.1 { 84 SELECT count(*) FROM t1 %JOIN% t2 ON (t1.a=t2.a) 85} {3} 86do_join_test e_select-0.1.2 { 87 SELECT count(*) FROM t1 %JOIN% t2 USING (a) 88} {3} 89do_join_test e_select-0.1.3 { 90 SELECT count(*) FROM t1 %JOIN% t2 91} {9} 92do_catchsql_test e_select-0.1.4 { 93 SELECT count(*) FROM t1, t2 ON (t1.a=t2.a) USING (a) 94} {1 {cannot have both ON and USING clauses in the same join}} 95do_catchsql_test e_select-0.1.5 { 96 SELECT count(*) FROM t1, t2 USING (a) ON (t1.a=t2.a) 97} {1 {near "ON": syntax error}} 98 99#------------------------------------------------------------------------- 100# The following tests focus on FROM clause (join) processing. 101# 102 103# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple 104# SELECT statement, then the input data is implicitly a single row zero 105# columns wide 106# 107do_execsql_test e_select-1.1.1 { SELECT 'abc' } {abc} 108do_execsql_test e_select-1.1.2 { SELECT 'abc' WHERE NULL } {} 109do_execsql_test e_select-1.1.3 { SELECT NULL } {{}} 110do_execsql_test e_select-1.1.4 { SELECT count(*) } {1} 111do_execsql_test e_select-1.1.5 { SELECT count(*) WHERE 0 } {0} 112do_execsql_test e_select-1.1.6 { SELECT count(*) WHERE 1 } {1} 113 114# EVIDENCE-OF: R-48114-33255 If there is only a single table in the 115# join-source following the FROM clause, then the input data used by the 116# SELECT statement is the contents of the named table. 117# 118# The results of the SELECT queries suggest that they are operating on the 119# contents of the table 'xx'. 120# 121do_execsql_test e_select-1.2.1 { 122 CREATE TABLE xx(x, y); 123 INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2'); 124 INSERT INTO xx VALUES(NULL, -16.87); 125 INSERT INTO xx VALUES(-17.89, 'linguistically'); 126} {} 127do_execsql_test e_select-1.2.2 { 128 SELECT quote(x), quote(y) FROM xx 129} [list \ 130 'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' \ 131 NULL -16.87 \ 132 -17.89 'linguistically' \ 133] 134do_execsql_test e_select-1.2.3 { 135 SELECT count(*), count(x), count(y) FROM xx 136} {3 2 3} 137do_execsql_test e_select-1.2.4 { 138 SELECT sum(x), sum(y) FROM xx 139} {-17.89 -16.87} 140 141# EVIDENCE-OF: R-23593-12456 If there is more than one table specified 142# as part of the join-source following the FROM keyword, then the 143# contents of each named table are joined into a single dataset for the 144# simple SELECT statement to operate on. 145# 146# There are more detailed tests for subsequent requirements that add 147# more detail to this idea. We just add a single test that shows that 148# data is coming from each of the three tables following the FROM clause 149# here to show that the statement, vague as it is, is not incorrect. 150# 151do_execsql_test e_select-1.3.1 { 152 SELECT * FROM t1, t2, t3 153} [list a one a I a 1 a one a I b 2 a one b II a 1 a one b II b 2 a one c III a 1 a one c III b 2 b two a I a 1 b two a I b 2 b two b II a 1 b two b II b 2 b two c III a 1 b two c III b 2 c three a I a 1 c three a I b 2 c three b II a 1 c three b II b 2 c three c III a 1 c three c III b 2] 154 155# 156# The following block of tests - e_select-1.4.* - test that the description 157# of cartesian joins in the SELECT documentation is consistent with SQLite. 158# In doing so, we test the following three requirements as a side-effect: 159# 160# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER 161# JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, 162# then the result of the join is simply the cartesian product of the 163# left and right-hand datasets. 164# 165# The tests are built on this assertion. Really, they test that the output 166# of a CROSS JOIN, JOIN, INNER JOIN or "," join matches the expected result 167# of calculating the cartesian product of the left and right-hand datasets. 168# 169# EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER 170# JOIN", "JOIN" and "," join operators. 171# 172# EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the 173# same data as the "INNER JOIN", "JOIN" and "," operators 174# 175# All tests are run 4 times, with the only difference in each run being 176# which of the 4 equivalent cartesian product join operators are used. 177# Since the output data is the same in all cases, we consider that this 178# qualifies as testing the two statements above. 179# 180do_execsql_test e_select-1.4.0 { 181 CREATE TABLE x1(a, b); 182 CREATE TABLE x2(c, d, e); 183 CREATE TABLE x3(f, g, h, i); 184 185 -- x1: 3 rows, 2 columns 186 INSERT INTO x1 VALUES(24, 'converging'); 187 INSERT INTO x1 VALUES(NULL, X'CB71'); 188 INSERT INTO x1 VALUES('blonds', 'proprietary'); 189 190 -- x2: 2 rows, 3 columns 191 INSERT INTO x2 VALUES(-60.06, NULL, NULL); 192 INSERT INTO x2 VALUES(-58, NULL, 1.21); 193 194 -- x3: 5 rows, 4 columns 195 INSERT INTO x3 VALUES(-39.24, NULL, 'encompass', -1); 196 INSERT INTO x3 VALUES('presenting', 51, 'reformation', 'dignified'); 197 INSERT INTO x3 VALUES('conducting', -87.24, 37.56, NULL); 198 INSERT INTO x3 VALUES('coldest', -96, 'dramatists', 82.3); 199 INSERT INTO x3 VALUES('alerting', NULL, -93.79, NULL); 200} {} 201 202# EVIDENCE-OF: R-59089-25828 The columns of the cartesian product 203# dataset are, in order, all the columns of the left-hand dataset 204# followed by all the columns of the right-hand dataset. 205# 206do_join_test e_select-1.4.1.1 { 207 SELECT * FROM x1 %JOIN% x2 LIMIT 1 208} [concat {24 converging} {-60.06 {} {}}] 209 210do_join_test e_select-1.4.1.2 { 211 SELECT * FROM x2 %JOIN% x1 LIMIT 1 212} [concat {-60.06 {} {}} {24 converging}] 213 214do_join_test e_select-1.4.1.3 { 215 SELECT * FROM x3 %JOIN% x2 LIMIT 1 216} [concat {-39.24 {} encompass -1} {-60.06 {} {}}] 217 218do_join_test e_select-1.4.1.4 { 219 SELECT * FROM x2 %JOIN% x3 LIMIT 1 220} [concat {-60.06 {} {}} {-39.24 {} encompass -1}] 221 222# EVIDENCE-OF: R-44414-54710 There is a row in the cartesian product 223# dataset formed by combining each unique combination of a row from the 224# left-hand and right-hand datasets. 225# 226do_join_test e_select-1.4.2.1 { 227 SELECT * FROM x2 %JOIN% x3 228} [list -60.06 {} {} -39.24 {} encompass -1 \ 229 -60.06 {} {} presenting 51 reformation dignified \ 230 -60.06 {} {} conducting -87.24 37.56 {} \ 231 -60.06 {} {} coldest -96 dramatists 82.3 \ 232 -60.06 {} {} alerting {} -93.79 {} \ 233 -58 {} 1.21 -39.24 {} encompass -1 \ 234 -58 {} 1.21 presenting 51 reformation dignified \ 235 -58 {} 1.21 conducting -87.24 37.56 {} \ 236 -58 {} 1.21 coldest -96 dramatists 82.3 \ 237 -58 {} 1.21 alerting {} -93.79 {} \ 238] 239# TODO: Come back and add a few more like the above. 240 241# EVIDENCE-OF: R-20659-43267 In other words, if the left-hand dataset 242# consists of Nlhs rows of Mlhs columns, and the right-hand dataset of 243# Nrhs rows of Mrhs columns, then the cartesian product is a dataset of 244# Nlhs.Nrhs rows, each containing Mlhs+Mrhs columns. 245# 246# x1, x2 (Nlhs=3, Nrhs=2) (Mlhs=2, Mrhs=3) 247do_join_test e_select-1.4.3.1 { 248 SELECT count(*) FROM x1 %JOIN% x2 249} [expr 3*2] 250do_test e_select-1.4.3.2 { 251 expr {[llength [execsql {SELECT * FROM x1, x2}]] / 6} 252} [expr 2+3] 253 254# x2, x3 (Nlhs=2, Nrhs=5) (Mlhs=3, Mrhs=4) 255do_join_test e_select-1.4.3.3 { 256 SELECT count(*) FROM x2 %JOIN% x3 257} [expr 2*5] 258do_test e_select-1.4.3.4 { 259 expr {[llength [execsql {SELECT * FROM x2 JOIN x3}]] / 10} 260} [expr 3+4] 261 262# x3, x1 (Nlhs=5, Nrhs=3) (Mlhs=4, Mrhs=2) 263do_join_test e_select-1.4.3.5 { 264 SELECT count(*) FROM x3 %JOIN% x1 265} [expr 5*3] 266do_test e_select-1.4.3.6 { 267 expr {[llength [execsql {SELECT * FROM x3 CROSS JOIN x1}]] / 15} 268} [expr 4+2] 269 270# x3, x3 (Nlhs=5, Nrhs=5) (Mlhs=4, Mrhs=4) 271do_join_test e_select-1.4.3.7 { 272 SELECT count(*) FROM x3 %JOIN% x3 273} [expr 5*5] 274do_test e_select-1.4.3.8 { 275 expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25} 276} [expr 4+4] 277 278# Some extra cartesian product tests using tables t1 and t2. 279# 280do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2 281do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1 282foreach {tn select res} [list \ 283 1 { SELECT * FROM t1 CROSS JOIN t2 } $t1_cross_t2 \ 284 2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1 \ 285 3 { SELECT * FROM t1 INNER JOIN t2 } $t1_cross_t2 \ 286 4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1 \ 287] { 288 do_execsql_test e_select-1.4.5.$tn $select $res 289} 290 291 292# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then 293# the ON expression is evaluated for each row of the cartesian product 294# and the result cast to a numeric value as if by a CAST expression. All 295# rows for which the expression evaluates to NULL or zero (integer value 296# 0 or real value 0.0) are excluded from the dataset. 297# 298foreach {tn select res} [list \ 299 1 { SELECT * FROM t1 %JOIN% t2 ON (1) } $t1_cross_t2 \ 300 2 { SELECT * FROM t1 %JOIN% t2 ON (0) } [list] \ 301 3 { SELECT * FROM t1 %JOIN% t2 ON (NULL) } [list] \ 302 4 { SELECT * FROM t1 %JOIN% t2 ON ('abc') } [list] \ 303 5 { SELECT * FROM t1 %JOIN% t2 ON ('1ab') } $t1_cross_t2 \ 304 6 { SELECT * FROM t1 %JOIN% t2 ON (0.9) } $t1_cross_t2 \ 305 7 { SELECT * FROM t1 %JOIN% t2 ON ('0.9') } $t1_cross_t2 \ 306 8 { SELECT * FROM t1 %JOIN% t2 ON (0.0) } [list] \ 307 \ 308 9 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = t2.a) } \ 309 {one I two II three III} \ 310 10 { SELECT t1.b, t2.b FROM t1 %JOIN% t2 ON (t1.a = 'a') } \ 311 {one I one II one III} \ 312 11 { SELECT t1.b, t2.b 313 FROM t1 %JOIN% t2 ON (CASE WHEN t1.a = 'a' THEN NULL ELSE 1 END) } \ 314 {two I two II two III three I three II three III} \ 315] { 316 do_join_test e_select-1.3.$tn $select $res 317} 318 319# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as 320# part of the join-constraint, then each of the column names specified 321# must exist in the datasets to both the left and right of the join-op. 322# 323foreach {tn select col} { 324 1 { SELECT * FROM t1, t3 USING (b) } "b" 325 2 { SELECT * FROM t3, t1 USING (c) } "c" 326 3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) } "a" 327} { 328 set err "cannot join using column $col - column not present in both tables" 329 do_catchsql_test e_select-1.4.$tn $select [list 1 $err] 330} 331 332# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the 333# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian 334# product and the result cast to a numeric value. All rows for which one 335# or more of the expressions evaluates to NULL or zero are excluded from 336# the result set. 337# 338foreach {tn select res} { 339 1 { SELECT * FROM t1, t3 USING (a) } {a one 1 b two 2} 340 2 { SELECT * FROM t3, t4 USING (a,c) } {b 2} 341} { 342 do_execsql_test e_select-1.5.$tn $select $res 343} 344 345# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a 346# USING clause, the normal rules for handling affinities, collation 347# sequences and NULL values in comparisons apply. 348# 349# EVIDENCE-OF: R-35466-18578 The column from the dataset on the 350# left-hand side of the join operator is considered to be on the 351# left-hand side of the comparison operator (=) for the purposes of 352# collation sequence and affinity precedence. 353# 354do_execsql_test e_select-1.6.0 { 355 CREATE TABLE t5(a COLLATE nocase, b COLLATE binary); 356 INSERT INTO t5 VALUES('AA', 'cc'); 357 INSERT INTO t5 VALUES('BB', 'dd'); 358 INSERT INTO t5 VALUES(NULL, NULL); 359 CREATE TABLE t6(a COLLATE binary, b COLLATE nocase); 360 INSERT INTO t6 VALUES('aa', 'cc'); 361 INSERT INTO t6 VALUES('bb', 'DD'); 362 INSERT INTO t6 VALUES(NULL, NULL); 363} {} 364foreach {tn select res} { 365 1 { SELECT * FROM t5 %JOIN% t6 USING (a) } {AA cc cc BB dd DD} 366 2 { SELECT * FROM t6 %JOIN% t5 USING (a) } {} 367 3 { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) %JOIN% t5 USING (a) } 368 {aa cc cc bb DD dd} 369 4 { SELECT * FROM t5 %JOIN% t6 USING (a,b) } {AA cc} 370 5 { SELECT * FROM t6 %JOIN% t5 USING (a,b) } {} 371} { 372 do_join_test e_select-1.6.$tn $select $res 373} 374 375# EVIDENCE-OF: R-57047-10461 For each pair of columns identified by a 376# USING clause, the column from the right-hand dataset is omitted from 377# the joined dataset. 378# 379# EVIDENCE-OF: R-56132-15700 This is the only difference between a USING 380# clause and its equivalent ON constraint. 381# 382foreach {tn select res} { 383 1a { SELECT * FROM t1 %JOIN% t2 USING (a) } 384 {a one I b two II c three III} 385 1b { SELECT * FROM t1 %JOIN% t2 ON (t1.a=t2.a) } 386 {a one a I b two b II c three c III} 387 388 2a { SELECT * FROM t3 %JOIN% t4 USING (a) } 389 {a 1 {} b 2 2} 390 2b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a) } 391 {a 1 a {} b 2 b 2} 392 393 3a { SELECT * FROM t3 %JOIN% t4 USING (a,c) } {b 2} 394 3b { SELECT * FROM t3 %JOIN% t4 ON (t3.a=t4.a AND t3.c=t4.c) } {b 2 b 2} 395 396 4a { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 397 %JOIN% t5 USING (a) } 398 {aa cc cc bb DD dd} 399 4b { SELECT * FROM (SELECT a COLLATE nocase, b FROM t6) AS x 400 %JOIN% t5 ON (x.a=t5.a) } 401 {aa cc AA cc bb DD BB dd} 402} { 403 do_join_test e_select-1.7.$tn $select $res 404} 405 406# EVIDENCE-OF: R-41434-12448 If the join-op is a "LEFT JOIN" or "LEFT 407# OUTER JOIN", then after the ON or USING filtering clauses have been 408# applied, an extra row is added to the output for each row in the 409# original left-hand input dataset that corresponds to no rows at all in 410# the composite dataset (if any). 411# 412do_execsql_test e_select-1.8.0 { 413 CREATE TABLE t7(a, b, c); 414 CREATE TABLE t8(a, d, e); 415 416 INSERT INTO t7 VALUES('x', 'ex', 24); 417 INSERT INTO t7 VALUES('y', 'why', 25); 418 419 INSERT INTO t8 VALUES('x', 'abc', 24); 420 INSERT INTO t8 VALUES('z', 'ghi', 26); 421} {} 422 423do_execsql_test e_select-1.8.1a { 424 SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a) 425} {1} 426do_execsql_test e_select-1.8.1b { 427 SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) 428} {2} 429 430do_execsql_test e_select-1.8.2a { 431 SELECT count(*) FROM t7 JOIN t8 USING (a) 432} {1} 433do_execsql_test e_select-1.8.2b { 434 SELECT count(*) FROM t7 LEFT JOIN t8 USING (a) 435} {2} 436 437# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the 438# columns that would normally contain values copied from the right-hand 439# input dataset. 440# 441do_execsql_test e_select-1.9.1a { 442 SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a) 443} {x ex 24 x abc 24} 444do_execsql_test e_select-1.9.1b { 445 SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a) 446} {x ex 24 x abc 24 y why 25 {} {} {}} 447 448do_execsql_test e_select-1.9.2a { 449 SELECT * FROM t7 JOIN t8 USING (a) 450} {x ex 24 abc 24} 451do_execsql_test e_select-1.9.2b { 452 SELECT * FROM t7 LEFT JOIN t8 USING (a) 453} {x ex 24 abc 24 y why 25 {} {}} 454 455# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of 456# the join-ops, then an implicit USING clause is added to the 457# join-constraints. The implicit USING clause contains each of the 458# column names that appear in both the left and right-hand input 459# datasets. 460# 461foreach {tn s1 s2 res} { 462 1 { SELECT * FROM t7 JOIN t8 USING (a) } 463 { SELECT * FROM t7 NATURAL JOIN t8 } 464 {x ex 24 abc 24} 465 466 2 { SELECT * FROM t8 JOIN t7 USING (a) } 467 { SELECT * FROM t8 NATURAL JOIN t7 } 468 {x abc 24 ex 24} 469 470 3 { SELECT * FROM t7 LEFT JOIN t8 USING (a) } 471 { SELECT * FROM t7 NATURAL LEFT JOIN t8 } 472 {x ex 24 abc 24 y why 25 {} {}} 473 474 4 { SELECT * FROM t8 LEFT JOIN t7 USING (a) } 475 { SELECT * FROM t8 NATURAL LEFT JOIN t7 } 476 {x abc 24 ex 24 z ghi 26 {} {}} 477 478 5 { SELECT * FROM t3 JOIN t4 USING (a,c) } 479 { SELECT * FROM t3 NATURAL JOIN t4 } 480 {b 2} 481 482 6 { SELECT * FROM t3 LEFT JOIN t4 USING (a,c) } 483 { SELECT * FROM t3 NATURAL LEFT JOIN t4 } 484 {a 1 b 2} 485} { 486 do_execsql_test e_select-1.10.${tn}a $s1 $res 487 do_execsql_test e_select-1.10.${tn}b $s2 $res 488} 489 490# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets 491# feature no common column names, then the NATURAL keyword has no effect 492# on the results of the join. 493# 494do_execsql_test e_select-1.11.0 { 495 CREATE TABLE t10(x, y); 496 INSERT INTO t10 VALUES(1, 'true'); 497 INSERT INTO t10 VALUES(0, 'false'); 498} {} 499foreach {tn s1 s2 res} { 500 1 { SELECT a, x FROM t1 CROSS JOIN t10 } 501 { SELECT a, x FROM t1 NATURAL CROSS JOIN t10 } 502 {a 1 a 0 b 1 b 0 c 1 c 0} 503} { 504 do_execsql_test e_select-1.11.${tn}a $s1 $res 505 do_execsql_test e_select-1.11.${tn}b $s2 $res 506} 507 508# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a 509# join that specifies the NATURAL keyword. 510# 511foreach {tn sql} { 512 1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)} 513 2 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (t1.a=t2.a)} 514 3 {SELECT * FROM t1 NATURAL LEFT JOIN t2 ON (45)} 515} { 516 do_catchsql_test e_select-1.12.$tn " 517 $sql 518 " {1 {a NATURAL join may not have an ON or USING clause}} 519} 520 521#------------------------------------------------------------------------- 522# te_* commands: 523# 524# 525# te_read_sql DB SELECT-STATEMENT 526# te_read_tbl DB TABLENAME 527# 528# These two commands are used to read a dataset from the database. A dataset 529# consists of N rows of M named columns of values each, where each value has a 530# type (null, integer, real, text or blob) and a value within the types domain. 531# The tcl format for a "dataset" is a list of two elements: 532# 533# * A list of the column names. 534# * A list of data rows. Each row is itself a list, where each element is 535# the contents of a column of the row. Each of these is a list of two 536# elements, the type name and the actual value. 537# 538# For example, the contents of table [t1] as a dataset is: 539# 540# CREATE TABLE t1(a, b); 541# INSERT INTO t1 VALUES('abc', NULL); 542# INSERT INTO t1 VALUES(43.1, 22); 543# 544# {a b} {{{TEXT abc} {NULL {}}} {{REAL 43.1} {INTEGER 22}}} 545# 546# The [te_read_tbl] command returns a dataset read from a table. The 547# [te_read_sql] returns the dataset that results from executing a SELECT 548# command. 549# 550# 551# te_tbljoin ?SWITCHES? LHS-TABLE RHS-TABLE 552# te_join ?SWITCHES? LHS-DATASET RHS-DATASET 553# 554# This command joins the two datasets and returns the resulting dataset. If 555# there are no switches specified, then the results is the cartesian product 556# of the two inputs. The [te_tbljoin] command reads the left and right-hand 557# datasets from the specified tables. The [te_join] command is passed the 558# datasets directly. 559# 560# Optional switches are as follows: 561# 562# -on SCRIPT 563# -using COLUMN-LIST 564# -left 565# 566# The -on option specifies a tcl script that is executed for each row in the 567# cartesian product of the two datasets. The script has 4 arguments appended 568# to it, in the following order: 569# 570# * The list of column-names from the left-hand dataset. 571# * A single row from the left-hand dataset (one "data row" list as 572# described above. 573# * The list of column-names from the right-hand dataset. 574# * A single row from the right-hand dataset. 575# 576# The script must return a boolean value - true if the combination of rows 577# should be included in the output dataset, or false otherwise. 578# 579# The -using option specifies a list of the columns from the right-hand 580# dataset that should be omitted from the output dataset. 581# 582# If the -left option is present, the join is done LEFT JOIN style. 583# Specifically, an extra row is inserted if after the -on script is run there 584# exist rows in the left-hand dataset that have no corresponding rows in 585# the output. See the implementation for more specific comments. 586# 587# 588# te_equals ?SWITCHES? COLNAME1 COLNAME2 <-on script args> 589# 590# The only supported switch is "-nocase". If it is present, then text values 591# are compared in a case-independent fashion. Otherwise, they are compared 592# as if using the SQLite BINARY collation sequence. 593# 594# 595# te_and ONSCRIPT1 ONSCRIPT2... 596# 597# 598 599 600# 601# te_read_tbl DB TABLENAME 602# te_read_sql DB SELECT-STATEMENT 603# 604# These two procs are used to extract datasets from the database, either 605# by reading the contents of a named table (te_read_tbl), or by executing 606# a SELECT statement (t3_read_sql). 607# 608# See the comment above, describing "te_* commands", for details of the 609# return values. 610# 611proc te_read_tbl {db tbl} { 612 te_read_sql $db "SELECT * FROM '$tbl'" 613} 614proc te_read_sql {db sql} { 615 set S [sqlite3_prepare_v2 $db $sql -1 DUMMY] 616 617 set cols [list] 618 for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { 619 lappend cols [sqlite3_column_name $S $i] 620 } 621 622 set rows [list] 623 while {[sqlite3_step $S] == "SQLITE_ROW"} { 624 set r [list] 625 for {set i 0} {$i < [sqlite3_column_count $S]} {incr i} { 626 lappend r [list [sqlite3_column_type $S $i] [sqlite3_column_text $S $i]] 627 } 628 lappend rows $r 629 } 630 sqlite3_finalize $S 631 632 return [list $cols $rows] 633} 634 635#------- 636# Usage: te_join <table-data1> <table-data2> <join spec>... 637# 638# Where a join-spec is an optional list of arguments as follows: 639# 640# ?-left? 641# ?-using colname-list? 642# ?-on on-expr-proc? 643# 644proc te_join {data1 data2 args} { 645 646 set testproc "" 647 set usinglist [list] 648 set isleft 0 649 for {set i 0} {$i < [llength $args]} {incr i} { 650 set a [lindex $args $i] 651 switch -- $a { 652 -on { set testproc [lindex $args [incr i]] } 653 -using { set usinglist [lindex $args [incr i]] } 654 -left { set isleft 1 } 655 default { 656 error "Unknown argument: $a" 657 } 658 } 659 } 660 661 set c1 [lindex $data1 0] 662 set c2 [lindex $data2 0] 663 set omitlist [list] 664 set nullrowlist [list] 665 set cret $c1 666 667 set cidx 0 668 foreach col $c2 { 669 set idx [lsearch $usinglist $col] 670 if {$idx>=0} {lappend omitlist $cidx} 671 if {$idx<0} { 672 lappend nullrowlist {NULL {}} 673 lappend cret $col 674 } 675 incr cidx 676 } 677 set omitlist [lsort -integer -decreasing $omitlist] 678 679 680 set rret [list] 681 foreach r1 [lindex $data1 1] { 682 set one 0 683 foreach r2 [lindex $data2 1] { 684 set ok 1 685 if {$testproc != ""} { 686 set ok [eval $testproc [list $c1 $r1 $c2 $r2]] 687 } 688 if {$ok} { 689 set one 1 690 foreach idx $omitlist {set r2 [lreplace $r2 $idx $idx]} 691 lappend rret [concat $r1 $r2] 692 } 693 } 694 695 if {$isleft && $one==0} { 696 lappend rret [concat $r1 $nullrowlist] 697 } 698 } 699 700 list $cret $rret 701} 702 703proc te_tbljoin {db t1 t2 args} { 704 te_join [te_read_tbl $db $t1] [te_read_tbl $db $t2] {*}$args 705} 706 707proc te_apply_affinity {affinity typevar valvar} { 708 upvar $typevar type 709 upvar $valvar val 710 711 switch -- $affinity { 712 integer { 713 if {[string is double $val]} { set type REAL } 714 if {[string is wideinteger $val]} { set type INTEGER } 715 if {$type == "REAL" && int($val)==$val} { 716 set type INTEGER 717 set val [expr {int($val)}] 718 } 719 } 720 text { 721 set type TEXT 722 } 723 none { } 724 725 default { error "invalid affinity: $affinity" } 726 } 727} 728 729#---------- 730# te_equals ?SWITCHES? c1 c2 cols1 row1 cols2 row2 731# 732proc te_equals {args} { 733 734 if {[llength $args]<6} {error "invalid arguments to te_equals"} 735 foreach {c1 c2 cols1 row1 cols2 row2} [lrange $args end-5 end] break 736 737 set nocase 0 738 set affinity none 739 740 for {set i 0} {$i < ([llength $args]-6)} {incr i} { 741 set a [lindex $args $i] 742 switch -- $a { 743 -nocase { 744 set nocase 1 745 } 746 -affinity { 747 set affinity [string tolower [lindex $args [incr i]]] 748 } 749 default { 750 error "invalid arguments to te_equals" 751 } 752 } 753 } 754 755 set idx2 [if {[string is integer $c2]} { set c2 } else { lsearch $cols2 $c2 }] 756 set idx1 [if {[string is integer $c1]} { set c1 } else { lsearch $cols1 $c1 }] 757 758 set t1 [lindex $row1 $idx1 0] 759 set t2 [lindex $row2 $idx2 0] 760 set v1 [lindex $row1 $idx1 1] 761 set v2 [lindex $row2 $idx2 1] 762 763 te_apply_affinity $affinity t1 v1 764 te_apply_affinity $affinity t2 v2 765 766 if {$t1 == "NULL" || $t2 == "NULL"} { return 0 } 767 if {$nocase && $t1 == "TEXT"} { set v1 [string tolower $v1] } 768 if {$nocase && $t2 == "TEXT"} { set v2 [string tolower $v2] } 769 770 771 set res [expr {$t1 == $t2 && [string equal $v1 $v2]}] 772 return $res 773} 774 775proc te_false {args} { return 0 } 776proc te_true {args} { return 1 } 777 778proc te_and {args} { 779 foreach a [lrange $args 0 end-4] { 780 set res [eval $a [lrange $args end-3 end]] 781 if {$res == 0} {return 0} 782 } 783 return 1 784} 785 786 787proc te_dataset_eq {testname got expected} { 788 uplevel #0 [list do_test $testname [list set {} $got] $expected] 789} 790proc te_dataset_eq_unordered {testname got expected} { 791 lset got 1 [lsort [lindex $got 1]] 792 lset expected 1 [lsort [lindex $expected 1]] 793 te_dataset_eq $testname $got $expected 794} 795 796proc te_dataset_ne {testname got unexpected} { 797 uplevel #0 [list do_test $testname [list string equal $got $unexpected] 0] 798} 799proc te_dataset_ne_unordered {testname got unexpected} { 800 lset got 1 [lsort [lindex $got 1]] 801 lset unexpected 1 [lsort [lindex $unexpected 1]] 802 te_dataset_ne $testname $got $unexpected 803} 804 805 806#------------------------------------------------------------------------- 807# 808proc test_join {tn sqljoin tbljoinargs} { 809 set sql [te_read_sql db "SELECT * FROM $sqljoin"] 810 set te [te_tbljoin db {*}$tbljoinargs] 811 te_dataset_eq_unordered $tn $sql $te 812} 813 814drop_all_tables 815do_execsql_test e_select-2.0 { 816 CREATE TABLE t1(a, b); 817 CREATE TABLE t2(a, b); 818 CREATE TABLE t3(b COLLATE nocase); 819 820 INSERT INTO t1 VALUES(2, 'B'); 821 INSERT INTO t1 VALUES(1, 'A'); 822 INSERT INTO t1 VALUES(4, 'D'); 823 INSERT INTO t1 VALUES(NULL, NULL); 824 INSERT INTO t1 VALUES(3, NULL); 825 826 INSERT INTO t2 VALUES(1, 'A'); 827 INSERT INTO t2 VALUES(2, NULL); 828 INSERT INTO t2 VALUES(5, 'E'); 829 INSERT INTO t2 VALUES(NULL, NULL); 830 INSERT INTO t2 VALUES(3, 'C'); 831 832 INSERT INTO t3 VALUES('a'); 833 INSERT INTO t3 VALUES('c'); 834 INSERT INTO t3 VALUES('b'); 835} {} 836 837foreach {tn indexes} { 838 e_select-2.1.1 { } 839 e_select-2.1.2 { CREATE INDEX i1 ON t1(a) } 840 e_select-2.1.3 { CREATE INDEX i1 ON t2(a) } 841 e_select-2.1.4 { CREATE INDEX i1 ON t3(b) } 842} { 843 844 catchsql { DROP INDEX i1 } 845 catchsql { DROP INDEX i2 } 846 catchsql { DROP INDEX i3 } 847 execsql $indexes 848 849 # EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER 850 # JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, 851 # then the result of the join is simply the cartesian product of the 852 # left and right-hand datasets. 853 # 854 # EVIDENCE-OF: R-46256-57243 There is no difference between the "INNER 855 # JOIN", "JOIN" and "," join operators. 856 # 857 # EVIDENCE-OF: R-07544-24155 The "CROSS JOIN" join operator produces the 858 # same data as the "INNER JOIN", "JOIN" and "," operators 859 # 860 test_join $tn.1.1 "t1, t2" {t1 t2} 861 test_join $tn.1.2 "t1 INNER JOIN t2" {t1 t2} 862 test_join $tn.1.3 "t1 CROSS JOIN t2" {t1 t2} 863 test_join $tn.1.4 "t1 JOIN t2" {t1 t2} 864 test_join $tn.1.5 "t2, t3" {t2 t3} 865 test_join $tn.1.6 "t2 INNER JOIN t3" {t2 t3} 866 test_join $tn.1.7 "t2 CROSS JOIN t3" {t2 t3} 867 test_join $tn.1.8 "t2 JOIN t3" {t2 t3} 868 test_join $tn.1.9 "t2, t2 AS x" {t2 t2} 869 test_join $tn.1.10 "t2 INNER JOIN t2 AS x" {t2 t2} 870 test_join $tn.1.11 "t2 CROSS JOIN t2 AS x" {t2 t2} 871 test_join $tn.1.12 "t2 JOIN t2 AS x" {t2 t2} 872 873 # EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then 874 # the ON expression is evaluated for each row of the cartesian product 875 # and the result cast to a numeric value as if by a CAST expression. All 876 # rows for which the expression evaluates to NULL or zero (integer value 877 # 0 or real value 0.0) are excluded from the dataset. 878 # 879 test_join $tn.2.1 "t1, t2 ON (t1.a=t2.a)" {t1 t2 -on {te_equals a a}} 880 test_join $tn.2.2 "t2, t1 ON (t1.a=t2.a)" {t2 t1 -on {te_equals a a}} 881 test_join $tn.2.3 "t2, t1 ON (1)" {t2 t1 -on te_true} 882 test_join $tn.2.4 "t2, t1 ON (NULL)" {t2 t1 -on te_false} 883 test_join $tn.2.5 "t2, t1 ON (1.1-1.1)" {t2 t1 -on te_false} 884 test_join $tn.2.6 "t1, t2 ON (1.1-1.0)" {t1 t2 -on te_true} 885 886 887 test_join $tn.3 "t1 LEFT JOIN t2 ON (t1.a=t2.a)" {t1 t2 -left -on {te_equals a a}} 888 test_join $tn.4 "t1 LEFT JOIN t2 USING (a)" { 889 t1 t2 -left -using a -on {te_equals a a} 890 } 891 test_join $tn.5 "t1 CROSS JOIN t2 USING(b, a)" { 892 t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 893 } 894 test_join $tn.6 "t1 NATURAL JOIN t2" { 895 t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 896 } 897 test_join $tn.7 "t1 NATURAL INNER JOIN t2" { 898 t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 899 } 900 test_join $tn.8 "t1 NATURAL CROSS JOIN t2" { 901 t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 902 } 903 test_join $tn.9 "t1 NATURAL INNER JOIN t2" { 904 t1 t2 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 905 } 906 test_join $tn.10 "t1 NATURAL LEFT JOIN t2" { 907 t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 908 } 909 test_join $tn.11 "t1 NATURAL LEFT OUTER JOIN t2" { 910 t1 t2 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 911 } 912 test_join $tn.12 "t2 NATURAL JOIN t1" { 913 t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 914 } 915 test_join $tn.13 "t2 NATURAL INNER JOIN t1" { 916 t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 917 } 918 test_join $tn.14 "t2 NATURAL CROSS JOIN t1" { 919 t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 920 } 921 test_join $tn.15 "t2 NATURAL INNER JOIN t1" { 922 t2 t1 -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 923 } 924 test_join $tn.16 "t2 NATURAL LEFT JOIN t1" { 925 t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 926 } 927 test_join $tn.17 "t2 NATURAL LEFT OUTER JOIN t1" { 928 t2 t1 -left -using {a b} -on {te_and {te_equals a a} {te_equals b b}} 929 } 930 test_join $tn.18 "t1 LEFT JOIN t2 USING (b)" { 931 t1 t2 -left -using b -on {te_equals b b} 932 } 933 test_join $tn.19 "t1 JOIN t3 USING(b)" {t1 t3 -using b -on {te_equals b b}} 934 test_join $tn.20 "t3 JOIN t1 USING(b)" { 935 t3 t1 -using b -on {te_equals -nocase b b} 936 } 937 test_join $tn.21 "t1 NATURAL JOIN t3" { 938 t1 t3 -using b -on {te_equals b b} 939 } 940 test_join $tn.22 "t3 NATURAL JOIN t1" { 941 t3 t1 -using b -on {te_equals -nocase b b} 942 } 943 test_join $tn.23 "t1 NATURAL LEFT JOIN t3" { 944 t1 t3 -left -using b -on {te_equals b b} 945 } 946 test_join $tn.24 "t3 NATURAL LEFT JOIN t1" { 947 t3 t1 -left -using b -on {te_equals -nocase b b} 948 } 949 test_join $tn.25 "t1 LEFT JOIN t3 ON (t3.b=t1.b)" { 950 t1 t3 -left -on {te_equals -nocase b b} 951 } 952 test_join $tn.26 "t1 LEFT JOIN t3 ON (t1.b=t3.b)" { 953 t1 t3 -left -on {te_equals b b} 954 } 955 test_join $tn.27 "t1 JOIN t3 ON (t1.b=t3.b)" { t1 t3 -on {te_equals b b} } 956 957 # EVIDENCE-OF: R-28760-53843 When more than two tables are joined 958 # together as part of a FROM clause, the join operations are processed 959 # in order from left to right. In other words, the FROM clause (A 960 # join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) join-op-2 C). 961 # 962 # Tests 28a and 28b show that the statement above is true for this case. 963 # Test 28c shows that if the parenthesis force a different order of 964 # evaluation the result is different. Test 28d verifies that the result 965 # of the query with the parenthesis forcing a different order of evaluation 966 # is as calculated by the [te_*] procs. 967 # 968 set t3_natural_left_join_t2 [ 969 te_tbljoin db t3 t2 -left -using {b} -on {te_equals -nocase b b} 970 ] 971 set t1 [te_read_tbl db t1] 972 te_dataset_eq_unordered $tn.28a [ 973 te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN t2 NATURAL JOIN t1" 974 ] [te_join $t3_natural_left_join_t2 $t1 \ 975 -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ 976 ] 977 978 te_dataset_eq_unordered $tn.28b [ 979 te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" 980 ] [te_join $t3_natural_left_join_t2 $t1 \ 981 -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ 982 ] 983 984 te_dataset_ne_unordered $tn.28c [ 985 te_read_sql db "SELECT * FROM (t3 NATURAL LEFT JOIN t2) NATURAL JOIN t1" 986 ] [ 987 te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" 988 ] 989 990 set t2_natural_join_t1 [te_tbljoin db t2 t1 -using {a b} \ 991 -using {a b} -on {te_and {te_equals a a} {te_equals -nocase b b}} \ 992 ] 993 set t3 [te_read_tbl db t3] 994 te_dataset_eq_unordered $tn.28d [ 995 te_read_sql db "SELECT * FROM t3 NATURAL LEFT JOIN (t2 NATURAL JOIN t1)" 996 ] [te_join $t3 $t2_natural_join_t1 \ 997 -left -using {b} -on {te_equals -nocase b b} \ 998 ] 999} 1000 1001do_execsql_test e_select-2.2.0 { 1002 CREATE TABLE t4(x TEXT COLLATE nocase); 1003 CREATE TABLE t5(y INTEGER, z TEXT COLLATE binary); 1004 1005 INSERT INTO t4 VALUES('2.0'); 1006 INSERT INTO t4 VALUES('TWO'); 1007 INSERT INTO t5 VALUES(2, 'two'); 1008} {} 1009 1010# EVIDENCE-OF: R-55824-40976 A sub-select specified in the join-source 1011# following the FROM clause in a simple SELECT statement is handled as 1012# if it was a table containing the data returned by executing the 1013# sub-select statement. 1014# 1015# EVIDENCE-OF: R-42612-06757 Each column of the sub-select dataset 1016# inherits the collation sequence and affinity of the corresponding 1017# expression in the sub-select statement. 1018# 1019foreach {tn subselect select spec} { 1020 1 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss%" 1021 {t1 %ss%} 1022 1023 2 "SELECT * FROM t2" "SELECT * FROM t1 JOIN %ss% AS x ON (t1.a=x.a)" 1024 {t1 %ss% -on {te_equals 0 0}} 1025 1026 3 "SELECT * FROM t2" "SELECT * FROM %ss% AS x JOIN t1 ON (t1.a=x.a)" 1027 {%ss% t1 -on {te_equals 0 0}} 1028 1029 4 "SELECT * FROM t1, t2" "SELECT * FROM %ss% AS x JOIN t3" 1030 {%ss% t3} 1031 1032 5 "SELECT * FROM t1, t2" "SELECT * FROM %ss% NATURAL JOIN t3" 1033 {%ss% t3 -using b -on {te_equals 1 0}} 1034 1035 6 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL JOIN %ss%" 1036 {t3 %ss% -using b -on {te_equals -nocase 0 1}} 1037 1038 7 "SELECT * FROM t1, t2" "SELECT * FROM t3 NATURAL LEFT JOIN %ss%" 1039 {t3 %ss% -left -using b -on {te_equals -nocase 0 1}} 1040 1041 8 "SELECT count(*) AS y FROM t4" "SELECT * FROM t5, %ss% USING (y)" 1042 {t5 %ss% -using y -on {te_equals -affinity text 0 0}} 1043 1044 9 "SELECT count(*) AS y FROM t4" "SELECT * FROM %ss%, t5 USING (y)" 1045 {%ss% t5 -using y -on {te_equals -affinity text 0 0}} 1046 1047 10 "SELECT x AS y FROM t4" "SELECT * FROM %ss% JOIN t5 USING (y)" 1048 {%ss% t5 -using y -on {te_equals -nocase -affinity integer 0 0}} 1049 1050 11 "SELECT x AS y FROM t4" "SELECT * FROM t5 JOIN %ss% USING (y)" 1051 {t5 %ss% -using y -on {te_equals -nocase -affinity integer 0 0}} 1052 1053 12 "SELECT y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" 1054 {%ss% t4 -using x -on {te_equals -nocase -affinity integer 0 0}} 1055 1056 13 "SELECT y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" 1057 {t4 %ss% -using x -on {te_equals -nocase -affinity integer 0 0}} 1058 1059 14 "SELECT +y AS x FROM t5" "SELECT * FROM %ss% JOIN t4 USING (x)" 1060 {%ss% t4 -using x -on {te_equals -nocase -affinity text 0 0}} 1061 1062 15 "SELECT +y AS x FROM t5" "SELECT * FROM t4 JOIN %ss% USING (x)" 1063 {t4 %ss% -using x -on {te_equals -nocase -affinity text 0 0}} 1064} { 1065 1066 # Create a temporary table named %ss% containing the data returned by 1067 # the sub-select. Then have the [te_tbljoin] proc use this table to 1068 # compute the expected results of the $select query. Drop the temporary 1069 # table before continuing. 1070 # 1071 execsql "CREATE TEMP TABLE '%ss%' AS $subselect" 1072 set te [eval te_tbljoin db $spec] 1073 execsql "DROP TABLE '%ss%'" 1074 1075 # Check that the actual data returned by the $select query is the same 1076 # as the expected data calculated using [te_tbljoin] above. 1077 # 1078 te_dataset_eq_unordered e_select-2.2.1.$tn [ 1079 te_read_sql db [string map [list %ss% "($subselect)"] $select] 1080 ] $te 1081} 1082 1083#------------------------------------------------------------------------- 1084# The next block of tests - e_select-3.* - concentrate on verifying 1085# statements made regarding WHERE clause processing. 1086# 1087drop_all_tables 1088do_execsql_test e_select-3.0 { 1089 CREATE TABLE x1(k, x, y, z); 1090 INSERT INTO x1 VALUES(1, 'relinquished', 'aphasia', 78.43); 1091 INSERT INTO x1 VALUES(2, X'A8E8D66F', X'07CF', -81); 1092 INSERT INTO x1 VALUES(3, -22, -27.57, NULL); 1093 INSERT INTO x1 VALUES(4, NULL, 'bygone', 'picky'); 1094 INSERT INTO x1 VALUES(5, NULL, 96.28, NULL); 1095 INSERT INTO x1 VALUES(6, 0, 1, 2); 1096 1097 CREATE TABLE x2(k, x, y2); 1098 INSERT INTO x2 VALUES(1, 50, X'B82838'); 1099 INSERT INTO x2 VALUES(5, 84.79, 65.88); 1100 INSERT INTO x2 VALUES(3, -22, X'0E1BE452A393'); 1101 INSERT INTO x2 VALUES(7, 'mistrusted', 'standardized'); 1102} {} 1103 1104# EVIDENCE-OF: R-22873-49686 If a WHERE clause is specified, the WHERE 1105# expression is evaluated for each row in the input data and the result 1106# cast to a numeric value. All rows for which the WHERE clause 1107# expression evaluates to a NULL value or to zero (integer value 0 or 1108# real value 0.0) are excluded from the dataset before continuing. 1109# 1110do_execsql_test e_select-3.1.1 { SELECT k FROM x1 WHERE x } {3} 1111do_execsql_test e_select-3.1.2 { SELECT k FROM x1 WHERE y } {3 5 6} 1112do_execsql_test e_select-3.1.3 { SELECT k FROM x1 WHERE z } {1 2 6} 1113do_execsql_test e_select-3.1.4 { SELECT k FROM x1 WHERE '1'||z } {1 2 4 6} 1114do_execsql_test e_select-3.1.5 { SELECT k FROM x1 WHERE x IS NULL } {4 5} 1115do_execsql_test e_select-3.1.6 { SELECT k FROM x1 WHERE z - 78.43 } {2 4 6} 1116 1117do_execsql_test e_select-3.2.1a { 1118 SELECT k FROM x1 LEFT JOIN x2 USING(k) 1119} {1 2 3 4 5 6} 1120do_execsql_test e_select-3.2.1b { 1121 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k 1122} {1 3 5} 1123do_execsql_test e_select-3.2.2 { 1124 SELECT k FROM x1 LEFT JOIN x2 USING(k) WHERE x2.k IS NULL 1125} {2 4 6} 1126 1127do_execsql_test e_select-3.2.3 { 1128 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k 1129} {3} 1130do_execsql_test e_select-3.2.4 { 1131 SELECT k FROM x1 NATURAL JOIN x2 WHERE x2.k-3 1132} {} 1133 1134#------------------------------------------------------------------------- 1135# Tests below this point are focused on verifying the testable statements 1136# related to caculating the result rows of a simple SELECT statement. 1137# 1138 1139drop_all_tables 1140do_execsql_test e_select-4.0 { 1141 CREATE TABLE z1(a, b, c); 1142 CREATE TABLE z2(d, e); 1143 CREATE TABLE z3(a, b); 1144 1145 INSERT INTO z1 VALUES(51.65, -59.58, 'belfries'); 1146 INSERT INTO z1 VALUES(-5, NULL, 75); 1147 INSERT INTO z1 VALUES(-2.2, -23.18, 'suiters'); 1148 INSERT INTO z1 VALUES(NULL, 67, 'quartets'); 1149 INSERT INTO z1 VALUES(-1.04, -32.3, 'aspen'); 1150 INSERT INTO z1 VALUES(63, 'born', -26); 1151 1152 INSERT INTO z2 VALUES(NULL, 21); 1153 INSERT INTO z2 VALUES(36, 6); 1154 1155 INSERT INTO z3 VALUES('subsistence', 'gauze'); 1156 INSERT INTO z3 VALUES(49.17, -67); 1157} {} 1158 1159# EVIDENCE-OF: R-36327-17224 If a result expression is the special 1160# expression "*" then all columns in the input data are substituted for 1161# that one expression. 1162# 1163# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table 1164# or subquery in the FROM clause followed by ".*" then all columns from 1165# the named table or subquery are substituted for the single expression. 1166# 1167foreach {tn select res} { 1168 1 "SELECT * FROM z1 LIMIT 1" {51.65 -59.58 belfries} 1169 2 "SELECT * FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries {} 21} 1170 3 "SELECT z1.* FROM z1,z2 LIMIT 1" {51.65 -59.58 belfries} 1171 4 "SELECT z2.* FROM z1,z2 LIMIT 1" {{} 21} 1172 5 "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries} 1173 1174 6 "SELECT count(*), * FROM z1" {6 63 born -26} 1175 7 "SELECT max(a), * FROM z1" {63 63 born -26} 1176 8 "SELECT *, min(a) FROM z1" {63 born -26 -5} 1177 1178 9 "SELECT *,* FROM z1,z2 LIMIT 1" { 1179 51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21 1180 } 1181 10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" { 1182 51.65 -59.58 belfries 51.65 -59.58 belfries 1183 } 1184} { 1185 do_execsql_test e_select-4.1.$tn $select [list {*}$res] 1186} 1187 1188# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*" 1189# expression in any context other than than a result expression list. 1190# 1191# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or 1192# "alias.*" expression in a simple SELECT query that does not have a 1193# FROM clause. 1194# 1195foreach {tn select err} { 1196 1.1 "SELECT a, b, c FROM z1 WHERE *" {near "*": syntax error} 1197 1.2 "SELECT a, b, c FROM z1 GROUP BY *" {near "*": syntax error} 1198 1.3 "SELECT 1 + * FROM z1" {near "*": syntax error} 1199 1.4 "SELECT * + 1 FROM z1" {near "+": syntax error} 1200 1201 2.1 "SELECT *" {no tables specified} 1202 2.2 "SELECT * WHERE 1" {no tables specified} 1203 2.3 "SELECT * WHERE 0" {no tables specified} 1204 2.4 "SELECT count(*), *" {no tables specified} 1205} { 1206 do_catchsql_test e_select-4.2.$tn $select [list 1 $err] 1207} 1208 1209# EVIDENCE-OF: R-08669-22397 The number of columns in the rows returned 1210# by a simple SELECT statement is equal to the number of expressions in 1211# the result expression list after substitution of * and alias.* 1212# expressions. 1213# 1214foreach {tn select nCol} { 1215 1 "SELECT * FROM z1" 3 1216 2 "SELECT * FROM z1 NATURAL JOIN z3" 3 1217 3 "SELECT z1.* FROM z1 NATURAL JOIN z3" 3 1218 4 "SELECT z3.* FROM z1 NATURAL JOIN z3" 2 1219 5 "SELECT z1.*, z3.* FROM z1 NATURAL JOIN z3" 5 1220 6 "SELECT 1, 2, z1.* FROM z1" 5 1221 7 "SELECT a, *, b, c FROM z1" 6 1222} { 1223 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] 1224 do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol 1225 sqlite3_finalize $::stmt 1226} 1227 1228# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate 1229# query, then each expression in the result expression list is evaluated 1230# for each row in the dataset filtered by the WHERE clause. 1231# 1232# By other definitions in lang_select.html, a non-aggregate query is 1233# any simple SELECT that has no GROUP BY clause and no aggregate expressions 1234# in the result expression list. 1235# 1236do_execsql_test e_select-4.4.1 { 1237 SELECT a, b FROM z1 1238} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born} 1239 1240do_execsql_test e_select-4.4.2 { 1241 SELECT a IS NULL, b+1, * FROM z1 1242} [list {*}{ 1243 0 -58.58 51.65 -59.58 belfries 1244 0 {} -5 {} 75 1245 0 -22.18 -2.2 -23.18 suiters 1246 1 68 {} 67 quartets 1247 0 -31.3 -1.04 -32.3 aspen 1248 0 1 63 born -26 1249}] 1250 1251do_execsql_test e_select-4.4.3 { 1252 SELECT 32*32, d||e FROM z2 1253} {1024 {} 1024 366} 1254 1255# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate 1256# query without a GROUP BY clause, then each aggregate expression in the 1257# result-set is evaluated once across the entire dataset. 1258# 1259foreach {tn select res} { 1260 5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1" {5 63 5 born} 1261 5.2 "SELECT count(*), max(1)" {1 1} 1262 1263 5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3" {-43.06} 1264 5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3" {-38.06} 1265 5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5} 1266} { 1267 do_execsql_test e_select-4.$tn $select [list {*}$res] 1268} 1269 1270# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the 1271# result-set is evaluated once for an arbitrarily selected row of the 1272# dataset. 1273# 1274# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used 1275# for each non-aggregate expression. 1276# 1277# Note: The results of many of the queries in this block of tests are 1278# technically undefined, as the documentation does not specify which row 1279# SQLite will arbitrarily select to use for the evaluation of the 1280# non-aggregate expressions. 1281# 1282drop_all_tables 1283do_execsql_test e_select-4.6.0 { 1284 CREATE TABLE a1(one PRIMARY KEY, two); 1285 INSERT INTO a1 VALUES(1, 1); 1286 INSERT INTO a1 VALUES(2, 3); 1287 INSERT INTO a1 VALUES(3, 6); 1288 INSERT INTO a1 VALUES(4, 10); 1289 1290 CREATE TABLE a2(one PRIMARY KEY, three); 1291 INSERT INTO a2 VALUES(1, 1); 1292 INSERT INTO a2 VALUES(3, 2); 1293 INSERT INTO a2 VALUES(6, 3); 1294 INSERT INTO a2 VALUES(10, 4); 1295} {} 1296foreach {tn select res} { 1297 6.1 "SELECT one, two, count(*) FROM a1" {4 10 4} 1298 6.2 "SELECT one, two, count(*) FROM a1 WHERE one<3" {2 3 2} 1299 6.3 "SELECT one, two, count(*) FROM a1 WHERE one>3" {4 10 1} 1300 6.4 "SELECT *, count(*) FROM a1 JOIN a2" {4 10 10 4 16} 1301 6.5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 1302 6.6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2" {3 6 2 3} 1303 6.7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6} 1304} { 1305 do_execsql_test e_select-4.$tn $select [list {*}$res] 1306} 1307 1308# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then 1309# each non-aggregate expression is evaluated against a row consisting 1310# entirely of NULL values. 1311# 1312foreach {tn select res} { 1313 7.1 "SELECT one, two, count(*) FROM a1 WHERE 0" {{} {} 0} 1314 7.2 "SELECT sum(two), * FROM a1, a2 WHERE three>5" {{} {} {} {} {}} 1315 7.3 "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" { 1316 1 1 1 1317 } 1318} { 1319 do_execsql_test e_select-4.$tn $select [list {*}$res] 1320} 1321 1322# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY 1323# clause always returns exactly one row of data, even if there are zero 1324# rows of input data. 1325# 1326foreach {tn select} { 1327 8.1 "SELECT count(*) FROM a1" 1328 8.2 "SELECT count(*) FROM a1 WHERE 0" 1329 8.3 "SELECT count(*) FROM a1 WHERE 1" 1330 8.4 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 1" 1331 8.5 "SELECT max(a1.one)+min(two), a1.one, two, * FROM a1, a2 WHERE 0" 1332} { 1333 # Set $nRow to the number of rows returned by $select: 1334 set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY] 1335 set nRow 0 1336 while {"SQLITE_ROW" == [sqlite3_step $::stmt]} { incr nRow } 1337 set rc [sqlite3_finalize $::stmt] 1338 1339 # Test that $nRow==1 and that statement execution was successful 1340 # (rc==SQLITE_OK). 1341 do_test e_select-4.$tn [list list $rc $nRow] {SQLITE_OK 1} 1342} 1343 1344drop_all_tables 1345do_execsql_test e_select-4.9.0 { 1346 CREATE TABLE b1(one PRIMARY KEY, two); 1347 INSERT INTO b1 VALUES(1, 'o'); 1348 INSERT INTO b1 VALUES(4, 'f'); 1349 INSERT INTO b1 VALUES(3, 't'); 1350 INSERT INTO b1 VALUES(2, 't'); 1351 INSERT INTO b1 VALUES(5, 'f'); 1352 INSERT INTO b1 VALUES(7, 's'); 1353 INSERT INTO b1 VALUES(6, 's'); 1354 1355 CREATE TABLE b2(x, y); 1356 INSERT INTO b2 VALUES(NULL, 0); 1357 INSERT INTO b2 VALUES(NULL, 1); 1358 INSERT INTO b2 VALUES('xyz', 2); 1359 INSERT INTO b2 VALUES('abc', 3); 1360 INSERT INTO b2 VALUES('xyz', 4); 1361 1362 CREATE TABLE b3(a COLLATE nocase, b COLLATE binary); 1363 INSERT INTO b3 VALUES('abc', 'abc'); 1364 INSERT INTO b3 VALUES('aBC', 'aBC'); 1365 INSERT INTO b3 VALUES('Def', 'Def'); 1366 INSERT INTO b3 VALUES('dEF', 'dEF'); 1367} {} 1368 1369# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate 1370# query with a GROUP BY clause, then each of the expressions specified 1371# as part of the GROUP BY clause is evaluated for each row of the 1372# dataset. Each row is then assigned to a "group" based on the results; 1373# rows for which the results of evaluating the GROUP BY expressions are 1374# the same are assigned to the same group. 1375# 1376foreach {tn select res} { 1377 9.1 "SELECT group_concat(one), two FROM b1 GROUP BY two" { 1378 4,5 f 1 o 7,6 s 3,2 t 1379 } 1380 9.2 "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" { 1381 1,4,3,2 10 5,7,6 18 1382 } 1383 9.3 "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" { 1384 4 1,5 2,6 3,7 1385 } 1386 9.4 "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" { 1387 4,3,5,7,6 1,2 1388 } 1389} { 1390 do_execsql_test e_select-4.$tn $select [list {*}$res] 1391} 1392 1393# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL 1394# values are considered equal. 1395# 1396foreach {tn select res} { 1397 10.1 "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1 3 2,4} 1398 10.2 "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" { 1399 4 1 1400 } 1401} { 1402 do_execsql_test e_select-4.$tn $select [list {*}$res] 1403} 1404 1405# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation 1406# sequence with which to compare text values apply when evaluating 1407# expressions in a GROUP BY clause. 1408# 1409foreach {tn select res} { 1410 11.1 "SELECT count(*) FROM b3 GROUP BY b" {1 1 1 1} 1411 11.2 "SELECT count(*) FROM b3 GROUP BY a" {2 2} 1412 11.3 "SELECT count(*) FROM b3 GROUP BY +b" {1 1 1 1} 1413 11.4 "SELECT count(*) FROM b3 GROUP BY +a" {2 2} 1414 11.5 "SELECT count(*) FROM b3 GROUP BY b||''" {1 1 1 1} 1415 11.6 "SELECT count(*) FROM b3 GROUP BY a||''" {1 1 1 1} 1416} { 1417 do_execsql_test e_select-4.$tn $select [list {*}$res] 1418} 1419 1420# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may 1421# not be aggregate expressions. 1422# 1423foreach {tn select} { 1424 12.1 "SELECT * FROM b3 GROUP BY count(*)" 1425 12.2 "SELECT max(a) FROM b3 GROUP BY max(b)" 1426 12.3 "SELECT group_concat(a) FROM b3 GROUP BY a, max(b)" 1427} { 1428 set res {1 {aggregate functions are not allowed in the GROUP BY clause}} 1429 do_catchsql_test e_select-4.$tn $select $res 1430} 1431 1432# EVIDENCE-OF: R-40359-04817 If a HAVING clause is specified, it is 1433# evaluated once for each group of rows and cast to an integer value. If 1434# the result of evaluating the HAVING clause is NULL or zero (integer 1435# value 0), the group is discarded. 1436# 1437# This requirement is tested by all e_select-4.13.* tests. 1438# 1439# EVIDENCE-OF: R-04132-09474 If the HAVING clause is an aggregate 1440# expression, it is evaluated across all rows in the group. 1441# 1442# Tested by e_select-4.13.1.* 1443# 1444# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate 1445# expression, it is evaluated with respect to an arbitrarily selected 1446# row from the group. 1447# 1448# Tested by e_select-4.13.2.* 1449# 1450do_execsql_test e_select-4.13.0 { 1451 CREATE TABLE c1(up, down); 1452 INSERT INTO c1 VALUES('x', 1); 1453 INSERT INTO c1 VALUES('x', 2); 1454 INSERT INTO c1 VALUES('x', 4); 1455 INSERT INTO c1 VALUES('x', 8); 1456 INSERT INTO c1 VALUES('y', 16); 1457 INSERT INTO c1 VALUES('y', 32); 1458 1459 CREATE TABLE c2(i, j); 1460 INSERT INTO c2 VALUES(1, 0); 1461 INSERT INTO c2 VALUES(2, 1); 1462 INSERT INTO c2 VALUES(3, 3); 1463 INSERT INTO c2 VALUES(4, 6); 1464 INSERT INTO c2 VALUES(5, 10); 1465 INSERT INTO c2 VALUES(6, 15); 1466 INSERT INTO c2 VALUES(7, 21); 1467 INSERT INTO c2 VALUES(8, 28); 1468 INSERT INTO c2 VALUES(9, 36); 1469 1470 CREATE TABLE c3(i PRIMARY KEY, k TEXT); 1471 INSERT INTO c3 VALUES(1, 'hydrogen'); 1472 INSERT INTO c3 VALUES(2, 'helium'); 1473 INSERT INTO c3 VALUES(3, 'lithium'); 1474 INSERT INTO c3 VALUES(4, 'beryllium'); 1475 INSERT INTO c3 VALUES(5, 'boron'); 1476 INSERT INTO c3 VALUES(94, 'plutonium'); 1477} {} 1478 1479foreach {tn select res} { 1480 13.1.1 "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x} 1481 13.1.2 "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y} 1482 13.1.3 "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x} 1483 13.1.4 "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4} 1484 1485 13.2.1 "SELECT up FROM c1 GROUP BY up HAVING down>10" {y} 1486 13.2.2 "SELECT up FROM c1 GROUP BY up HAVING up='y'" {y} 1487 1488 13.2.3 "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6" {9 36} 1489} { 1490 do_execsql_test e_select-4.$tn $select [list {*}$res] 1491} 1492 1493# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then 1494# evaluated once for each group of rows. 1495# 1496# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate 1497# expression, it is evaluated across all rows in the group. 1498# 1499foreach {tn select res} { 1500 14.1 "SELECT sum(down) FROM c1 GROUP BY up" {15 48} 1501 14.2 "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)" {54 36 27 21 39 28} 1502 14.3 "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)" {80 36 40 21} 1503 14.4 "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22} 1504 14.5 "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2" 1505 {3 4.33 1 2.0} 1506} { 1507 do_execsql_test e_select-4.$tn $select [list {*}$res] 1508} 1509 1510# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single 1511# arbitrarily chosen row from within the group. 1512# 1513# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate 1514# expression in the result-set, then all such expressions are evaluated 1515# for the same row. 1516# 1517foreach {tn select res} { 1518 15.1 "SELECT i, j FROM c2 GROUP BY i%2" {8 28 9 36} 1519 15.2 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28} 1520 15.3 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 1521 15.4 "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36} 1522 15.5 "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)" 1523 {2 5 boron 2 2 helium 1 3 lithium} 1524} { 1525 do_execsql_test e_select-4.$tn $select [list {*}$res] 1526} 1527 1528# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows 1529# contributes a single row to the set of result rows. 1530# 1531# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the 1532# DISTINCT keyword, the number of rows returned by an aggregate query 1533# with a GROUP BY clause is the same as the number of groups of rows 1534# produced by applying the GROUP BY and HAVING clauses to the filtered 1535# input dataset. 1536# 1537foreach {tn select nRow} { 1538 16.1 "SELECT i, j FROM c2 GROUP BY i%2" 2 1539 16.2 "SELECT i, j FROM c2 GROUP BY i" 9 1540 16.3 "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4 1541} { 1542 set rows 0 1543 db eval $select {incr rows} 1544 do_test e_select-4.$tn [list set rows] $nRow 1545} 1546 1547#------------------------------------------------------------------------- 1548# The following tests attempt to verify statements made regarding the ALL 1549# and DISTINCT keywords. 1550# 1551drop_all_tables 1552do_execsql_test e_select-5.1.0 { 1553 CREATE TABLE h1(a, b); 1554 INSERT INTO h1 VALUES(1, 'one'); 1555 INSERT INTO h1 VALUES(1, 'I'); 1556 INSERT INTO h1 VALUES(1, 'i'); 1557 INSERT INTO h1 VALUES(4, 'four'); 1558 INSERT INTO h1 VALUES(4, 'IV'); 1559 INSERT INTO h1 VALUES(4, 'iv'); 1560 1561 CREATE TABLE h2(x COLLATE nocase); 1562 INSERT INTO h2 VALUES('One'); 1563 INSERT INTO h2 VALUES('Two'); 1564 INSERT INTO h2 VALUES('Three'); 1565 INSERT INTO h2 VALUES('Four'); 1566 INSERT INTO h2 VALUES('one'); 1567 INSERT INTO h2 VALUES('two'); 1568 INSERT INTO h2 VALUES('three'); 1569 INSERT INTO h2 VALUES('four'); 1570 1571 CREATE TABLE h3(c, d); 1572 INSERT INTO h3 VALUES(1, NULL); 1573 INSERT INTO h3 VALUES(2, NULL); 1574 INSERT INTO h3 VALUES(3, NULL); 1575 INSERT INTO h3 VALUES(4, '2'); 1576 INSERT INTO h3 VALUES(5, NULL); 1577 INSERT INTO h3 VALUES(6, '2,3'); 1578 INSERT INTO h3 VALUES(7, NULL); 1579 INSERT INTO h3 VALUES(8, '2,4'); 1580 INSERT INTO h3 VALUES(9, '3'); 1581} {} 1582 1583# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may 1584# follow the SELECT keyword in a simple SELECT statement. 1585# 1586do_execsql_test e_select-5.1.1 { SELECT ALL a FROM h1 } {1 1 1 4 4 4} 1587do_execsql_test e_select-5.1.2 { SELECT DISTINCT a FROM h1 } {1 4} 1588 1589# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then 1590# the entire set of result rows are returned by the SELECT. 1591# 1592# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present, 1593# then the behaviour is as if ALL were specified. 1594# 1595# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT, 1596# then duplicate rows are removed from the set of result rows before it 1597# is returned. 1598# 1599# The three testable statements above are tested by e_select-5.2.*, 1600# 5.3.* and 5.4.* respectively. 1601# 1602foreach {tn select res} { 1603 3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four} 1604 3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four} 1605 1606 3.1 "SELECT x FROM h2" {One Two Three Four one two three four} 1607 3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four} 1608 1609 4.1 "SELECT DISTINCT x FROM h2" {four one three two} 1610 4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one} 1611} { 1612 do_execsql_test e_select-5.$tn $select [list {*}$res] 1613} 1614 1615# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate 1616# rows, two NULL values are considered to be equal. 1617# 1618do_execsql_test e_select-5.5.1 { SELECT DISTINCT d FROM h3 } {{} 2 2,3 2,4 3} 1619 1620# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation 1621# sequence to compare text values with apply. 1622# 1623foreach {tn select res} { 1624 6.1 "SELECT DISTINCT b FROM h1" {I IV four i iv one} 1625 6.2 "SELECT DISTINCT b COLLATE nocase FROM h1" {four i iv one} 1626 6.3 "SELECT DISTINCT x FROM h2" {four one three two} 1627 6.4 "SELECT DISTINCT x COLLATE binary FROM h2" { 1628 Four One Three Two four one three two 1629 } 1630} { 1631 do_execsql_test e_select-5.$tn $select [list {*}$res] 1632} 1633 1634#------------------------------------------------------------------------- 1635# The following tests - e_select-7.* - test that statements made to do 1636# with compound SELECT statements are correct. 1637# 1638 1639# EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent 1640# SELECTs must return the same number of result columns. 1641# 1642# All the other tests in this section use compound SELECTs created 1643# using component SELECTs that do return the same number of columns. 1644# So the tests here just show that it is an error to attempt otherwise. 1645# 1646drop_all_tables 1647do_execsql_test e_select-7.1.0 { 1648 CREATE TABLE j1(a, b, c); 1649 CREATE TABLE j2(e, f); 1650 CREATE TABLE j3(g); 1651} {} 1652foreach {tn select op} { 1653 1 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {UNION ALL} 1654 2 "SELECT * FROM j1 UNION ALL SELECT * FROM j3" {UNION ALL} 1655 3 "SELECT a, b FROM j1 UNION ALL SELECT g FROM j3" {UNION ALL} 1656 4 "SELECT a, b FROM j1 UNION ALL SELECT * FROM j3,j2" {UNION ALL} 1657 5 "SELECT * FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL} 1658 1659 6 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} 1660 7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION} 1661 8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION} 1662 9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION} 1663 10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION} 1664 1665 11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} 1666 12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT} 1667 13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT} 1668 14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT} 1669 15 "SELECT * FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT} 1670 1671 16 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} 1672 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j3" {EXCEPT} 1673 18 "SELECT a, b FROM j1 EXCEPT SELECT g FROM j3" {EXCEPT} 1674 19 "SELECT a, b FROM j1 EXCEPT SELECT * FROM j3,j2" {EXCEPT} 1675 20 "SELECT * FROM j3,j2 EXCEPT SELECT a, b FROM j1" {EXCEPT} 1676} { 1677 set err "SELECTs to the left and right of " 1678 append err $op 1679 append err " do not have the same number of result columns" 1680 do_catchsql_test e_select-7.1.$tn $select [list 1 $err] 1681} 1682 1683# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must 1684# be simple SELECT statements, they may not contain ORDER BY or LIMIT 1685# clauses. 1686# 1687foreach {tn select op1 op2} { 1688 1 "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 1689 {ORDER BY} {UNION ALL} 1690 2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2" 1691 {ORDER BY} {UNION ALL} 1692 3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2" 1693 {ORDER BY} {UNION ALL} 1694 4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3" 1695 LIMIT {UNION ALL} 1696 5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3" 1697 LIMIT {UNION ALL} 1698 6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3" 1699 LIMIT {UNION ALL} 1700 1701 7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3" 1702 {ORDER BY} {UNION} 1703 8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2" 1704 {ORDER BY} {UNION} 1705 9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2" 1706 {ORDER BY} {UNION} 1707 10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3" 1708 LIMIT {UNION} 1709 11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3" 1710 LIMIT {UNION} 1711 12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3" 1712 LIMIT {UNION} 1713 1714 13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3" 1715 {ORDER BY} {EXCEPT} 1716 14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2" 1717 {ORDER BY} {EXCEPT} 1718 15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2" 1719 {ORDER BY} {EXCEPT} 1720 16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3" 1721 LIMIT {EXCEPT} 1722 17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3" 1723 LIMIT {EXCEPT} 1724 18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3" 1725 LIMIT {EXCEPT} 1726 1727 19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3" 1728 {ORDER BY} {INTERSECT} 1729 20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2" 1730 {ORDER BY} {INTERSECT} 1731 21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2" 1732 {ORDER BY} {INTERSECT} 1733 22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3" 1734 LIMIT {INTERSECT} 1735 23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3" 1736 LIMIT {INTERSECT} 1737 24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3" 1738 LIMIT {INTERSECT} 1739} { 1740 set err "$op1 clause should come after $op2 not before" 1741 do_catchsql_test e_select-7.2.$tn $select [list 1 $err] 1742} 1743 1744# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur 1745# at the end of the entire compound SELECT. 1746# 1747foreach {tn select} { 1748 1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a" 1749 2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1" 1750 3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3" 1751 4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10" 1752 5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 1753 6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)" 1754 1755 7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a" 1756 8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1" 1757 9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3" 1758 10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10" 1759 11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 1760 12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)" 1761 1762 13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a" 1763 14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1" 1764 15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3" 1765 16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10" 1766 17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 1767 18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 1768 1769 19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a" 1770 20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1" 1771 21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3" 1772 22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10" 1773 23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5" 1774 24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)" 1775} { 1776 do_test e_select-7.3.$tn { catch {execsql $select} msg } 0 1777} 1778 1779# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL 1780# operator returns all the rows from the SELECT to the left of the UNION 1781# ALL operator, and all the rows from the SELECT to the right of it. 1782# 1783drop_all_tables 1784do_execsql_test e_select-7.4.0 { 1785 CREATE TABLE q1(a TEXT, b INTEGER, c); 1786 CREATE TABLE q2(d NUMBER, e BLOB); 1787 CREATE TABLE q3(f REAL, g); 1788 1789 INSERT INTO q1 VALUES(16, -87.66, NULL); 1790 INSERT INTO q1 VALUES('legible', 94, -42.47); 1791 INSERT INTO q1 VALUES('beauty', 36, NULL); 1792 1793 INSERT INTO q2 VALUES('legible', 1); 1794 INSERT INTO q2 VALUES('beauty', 2); 1795 INSERT INTO q2 VALUES(-65.91, 4); 1796 INSERT INTO q2 VALUES('emanating', -16.56); 1797 1798 INSERT INTO q3 VALUES('beauty', 2); 1799 INSERT INTO q3 VALUES('beauty', 2); 1800} {} 1801foreach {tn select res} { 1802 1 {SELECT a FROM q1 UNION ALL SELECT d FROM q2} 1803 {16 legible beauty legible beauty -65.91 emanating} 1804 1805 2 {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1} 1806 {16 -87.66 {} x legible 1} 1807 1808 3 {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 1809 {3 -16.56} 1810 1811 4 {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 1812 {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2} 1813} { 1814 do_execsql_test e_select-7.4.$tn $select [list {*}$res] 1815} 1816 1817# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as 1818# UNION ALL, except that duplicate rows are removed from the final 1819# result set. 1820# 1821foreach {tn select res} { 1822 1 {SELECT a FROM q1 UNION SELECT d FROM q2} 1823 {-65.91 16 beauty emanating legible} 1824 1825 2 {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1} 1826 {16 -87.66 {} x legible 1} 1827 1828 3 {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 1829 {-16.56 3} 1830 1831 4 {SELECT * FROM q2 UNION SELECT * FROM q3} 1832 {-65.91 4 beauty 2 emanating -16.56 legible 1} 1833} { 1834 do_execsql_test e_select-7.5.$tn $select [list {*}$res] 1835} 1836 1837# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the 1838# intersection of the results of the left and right SELECTs. 1839# 1840foreach {tn select res} { 1841 1 {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible} 1842 2 {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2} 1843} { 1844 do_execsql_test e_select-7.6.$tn $select [list {*}$res] 1845} 1846 1847# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of 1848# rows returned by the left SELECT that are not also returned by the 1849# right-hand SELECT. 1850# 1851foreach {tn select res} { 1852 1 {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16} 1853 1854 2 {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 1855 {-65.91 4 emanating -16.56 legible 1} 1856} { 1857 do_execsql_test e_select-7.7.$tn $select [list {*}$res] 1858} 1859 1860# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results 1861# of INTERSECT and EXCEPT operators before the result set is returned. 1862# 1863foreach {tn select res} { 1864 0 {SELECT * FROM q3} {beauty 2 beauty 2} 1865 1866 1 {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2} 1867 2 {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1} {beauty 2} 1868} { 1869 do_execsql_test e_select-7.8.$tn $select [list {*}$res] 1870} 1871 1872# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate 1873# rows for the results of compound SELECT operators, NULL values are 1874# considered equal to other NULL values and distinct from all non-NULL 1875# values. 1876# 1877db nullvalue null 1878foreach {tn select res} { 1879 1 {SELECT NULL UNION ALL SELECT NULL} {null null} 1880 2 {SELECT NULL UNION SELECT NULL} {null} 1881 3 {SELECT NULL INTERSECT SELECT NULL} {null} 1882 4 {SELECT NULL EXCEPT SELECT NULL} {} 1883 1884 5 {SELECT NULL UNION ALL SELECT 'ab'} {null ab} 1885 6 {SELECT NULL UNION SELECT 'ab'} {null ab} 1886 7 {SELECT NULL INTERSECT SELECT 'ab'} {} 1887 8 {SELECT NULL EXCEPT SELECT 'ab'} {null} 1888 1889 9 {SELECT NULL UNION ALL SELECT 0} {null 0} 1890 10 {SELECT NULL UNION SELECT 0} {null 0} 1891 11 {SELECT NULL INTERSECT SELECT 0} {} 1892 12 {SELECT NULL EXCEPT SELECT 0} {null} 1893 1894 13 {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2} 1895 14 {SELECT c FROM q1 UNION SELECT g FROM q3} {null -42.47 2} 1896 15 {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {} 1897 16 {SELECT c FROM q1 EXCEPT SELECT g FROM q3} {null -42.47} 1898} { 1899 do_execsql_test e_select-7.9.$tn $select [list {*}$res] 1900} 1901db nullvalue {} 1902 1903# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two 1904# text values is determined as if the columns of the left and right-hand 1905# SELECT statements were the left and right-hand operands of the equals 1906# (=) operator, except that greater precedence is not assigned to a 1907# collation sequence specified with the postfix COLLATE operator. 1908# 1909drop_all_tables 1910do_execsql_test e_select-7.10.0 { 1911 CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c); 1912 INSERT INTO y1 VALUES('Abc', 'abc', 'aBC'); 1913} {} 1914foreach {tn select res} { 1915 1 {SELECT 'abc' UNION SELECT 'ABC'} {ABC abc} 1916 2 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC} 1917 3 {SELECT 'abc' UNION SELECT 'ABC' COLLATE nocase} {ABC} 1918 4 {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc} 1919 5 {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC} 1920 1921 6 {SELECT a FROM y1 UNION SELECT b FROM y1} {abc} 1922 7 {SELECT b FROM y1 UNION SELECT a FROM y1} {Abc abc} 1923 8 {SELECT a FROM y1 UNION SELECT c FROM y1} {aBC} 1924 1925 9 {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC} 1926 1927} { 1928 do_execsql_test e_select-7.10.$tn $select [list {*}$res] 1929} 1930 1931# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to 1932# any values when comparing rows as part of a compound SELECT. 1933# 1934drop_all_tables 1935do_execsql_test e_select-7.10.0 { 1936 CREATE TABLE w1(a TEXT, b NUMBER); 1937 CREATE TABLE w2(a, b TEXT); 1938 1939 INSERT INTO w1 VALUES('1', 4.1); 1940 INSERT INTO w2 VALUES(1, 4.1); 1941} {} 1942 1943foreach {tn select res} { 1944 1 { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1} 1945 2 { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1} 1946 3 { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1} 1947 4 { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1} 1948 1949 5 { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {} 1950 6 { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {} 1951 7 { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {} 1952 8 { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {} 1953 1954 9 { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1} 1955 10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1} 1956 11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1} 1957 12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1} 1958} { 1959 do_execsql_test e_select-7.11.$tn $select [list {*}$res] 1960} 1961 1962 1963# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are 1964# connected into a compound SELECT, they group from left to right. In 1965# other words, if "A", "B" and "C" are all simple SELECT statements, (A 1966# op B op C) is processed as ((A op B) op C). 1967# 1968# e_select-7.12.1: Precedence of UNION vs. INTERSECT 1969# e_select-7.12.2: Precedence of UNION vs. UNION ALL 1970# e_select-7.12.3: Precedence of UNION vs. EXCEPT 1971# e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL 1972# e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT 1973# e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT 1974# e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as 1975# "(a EXCEPT b) EXCEPT c". 1976# 1977# The INTERSECT and EXCEPT operations are mutually commutative. So 1978# the e_select-7.12.5 test cases do not prove very much. 1979# 1980drop_all_tables 1981do_execsql_test e_select-7.12.0 { 1982 CREATE TABLE t1(x); 1983 INSERT INTO t1 VALUES(1); 1984 INSERT INTO t1 VALUES(2); 1985 INSERT INTO t1 VALUES(3); 1986} {} 1987foreach {tn select res} { 1988 1a "(1,2) INTERSECT (1) UNION (3)" {1 3} 1989 1b "(3) UNION (1,2) INTERSECT (1)" {1} 1990 1991 2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1} 1992 2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3} 1993 1994 3a "(1,2) UNION (3) EXCEPT (1)" {2 3} 1995 3b "(1,2) EXCEPT (3) UNION (1)" {1 2} 1996 1997 4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3} 1998 4b "(3) UNION (1,2) INTERSECT (1)" {1} 1999 2000 5a "(1,2) INTERSECT (2) EXCEPT (2)" {} 2001 5b "(2,3) EXCEPT (2) INTERSECT (2)" {} 2002 2003 6a "(2) UNION ALL (2) EXCEPT (2)" {} 2004 6b "(2) EXCEPT (2) UNION ALL (2)" {2} 2005 2006 7 "(2,3) EXCEPT (2) EXCEPT (3)" {} 2007} { 2008 set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select] 2009 do_execsql_test e_select-7.12.$tn $select [list {*}$res] 2010} 2011 2012 2013#------------------------------------------------------------------------- 2014# ORDER BY clauses 2015# 2016 2017drop_all_tables 2018do_execsql_test e_select-8.1.0 { 2019 CREATE TABLE d1(x, y, z); 2020 2021 INSERT INTO d1 VALUES(1, 2, 3); 2022 INSERT INTO d1 VALUES(2, 5, -1); 2023 INSERT INTO d1 VALUES(1, 2, 8); 2024 INSERT INTO d1 VALUES(1, 2, 7); 2025 INSERT INTO d1 VALUES(2, 4, 93); 2026 INSERT INTO d1 VALUES(1, 2, -20); 2027 INSERT INTO d1 VALUES(1, 4, 93); 2028 INSERT INTO d1 VALUES(1, 5, -1); 2029 2030 CREATE TABLE d2(a, b); 2031 INSERT INTO d2 VALUES('gently', 'failings'); 2032 INSERT INTO d2 VALUES('commercials', 'bathrobe'); 2033 INSERT INTO d2 VALUES('iterate', 'sexton'); 2034 INSERT INTO d2 VALUES('babied', 'charitableness'); 2035 INSERT INTO d2 VALUES('solemnness', 'annexed'); 2036 INSERT INTO d2 VALUES('rejoicing', 'liabilities'); 2037 INSERT INTO d2 VALUES('pragmatist', 'guarded'); 2038 INSERT INTO d2 VALUES('barked', 'interrupted'); 2039 INSERT INTO d2 VALUES('reemphasizes', 'reply'); 2040 INSERT INTO d2 VALUES('lad', 'relenting'); 2041} {} 2042 2043# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results 2044# of evaluating the left-most expression in the ORDER BY list, then ties 2045# are broken by evaluating the second left-most expression and so on. 2046# 2047foreach {tn select res} { 2048 1 "SELECT * FROM d1 ORDER BY x, y, z" { 2049 1 2 -20 1 2 3 1 2 7 1 2 8 2050 1 4 93 1 5 -1 2 4 93 2 5 -1 2051 } 2052} { 2053 do_execsql_test e_select-8.1.$tn $select [list {*}$res] 2054} 2055 2056# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally 2057# followed by one of the keywords ASC (smaller values are returned 2058# first) or DESC (larger values are returned first). 2059# 2060# Test cases e_select-8.2.* test the above. 2061# 2062# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows 2063# are sorted in ascending (smaller values first) order by default. 2064# 2065# Test cases e_select-8.3.* test the above. All 8.3 test cases are 2066# copies of 8.2 test cases with the explicit "ASC" removed. 2067# 2068foreach {tn select res} { 2069 2.1 "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" { 2070 1 2 -20 1 2 3 1 2 7 1 2 8 2071 1 4 93 1 5 -1 2 4 93 2 5 -1 2072 } 2073 2.2 "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" { 2074 2 5 -1 2 4 93 1 5 -1 1 4 93 2075 1 2 8 1 2 7 1 2 3 1 2 -20 2076 } 2077 2.3 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z DESC" { 2078 2 4 93 2 5 -1 1 2 8 1 2 7 2079 1 2 3 1 2 -20 1 4 93 1 5 -1 2080 } 2081 2.4 "SELECT * FROM d1 ORDER BY x DESC, y ASC, z ASC" { 2082 2 4 93 2 5 -1 1 2 -20 1 2 3 2083 1 2 7 1 2 8 1 4 93 1 5 -1 2084 } 2085 2086 3.1 "SELECT * FROM d1 ORDER BY x, y, z" { 2087 1 2 -20 1 2 3 1 2 7 1 2 8 2088 1 4 93 1 5 -1 2 4 93 2 5 -1 2089 } 2090 3.3 "SELECT * FROM d1 ORDER BY x DESC, y, z DESC" { 2091 2 4 93 2 5 -1 1 2 8 1 2 7 2092 1 2 3 1 2 -20 1 4 93 1 5 -1 2093 } 2094 3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" { 2095 2 4 93 2 5 -1 1 2 -20 1 2 3 2096 1 2 7 1 2 8 1 4 93 1 5 -1 2097 } 2098} { 2099 do_execsql_test e_select-8.$tn $select [list {*}$res] 2100} 2101 2102# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant 2103# integer K then the expression is considered an alias for the K-th 2104# column of the result set (columns are numbered from left to right 2105# starting with 1). 2106# 2107foreach {tn select res} { 2108 1 "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" { 2109 1 2 -20 1 2 3 1 2 7 1 2 8 2110 1 4 93 1 5 -1 2 4 93 2 5 -1 2111 } 2112 2 "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" { 2113 2 5 -1 2 4 93 1 5 -1 1 4 93 2114 1 2 8 1 2 7 1 2 3 1 2 -20 2115 } 2116 3 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 DESC" { 2117 2 4 93 2 5 -1 1 2 8 1 2 7 2118 1 2 3 1 2 -20 1 4 93 1 5 -1 2119 } 2120 4 "SELECT * FROM d1 ORDER BY 1 DESC, 2 ASC, 3 ASC" { 2121 2 4 93 2 5 -1 1 2 -20 1 2 3 2122 1 2 7 1 2 8 1 4 93 1 5 -1 2123 } 2124 5 "SELECT * FROM d1 ORDER BY 1, 2, 3" { 2125 1 2 -20 1 2 3 1 2 7 1 2 8 2126 1 4 93 1 5 -1 2 4 93 2 5 -1 2127 } 2128 6 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3 DESC" { 2129 2 4 93 2 5 -1 1 2 8 1 2 7 2130 1 2 3 1 2 -20 1 4 93 1 5 -1 2131 } 2132 7 "SELECT * FROM d1 ORDER BY 1 DESC, 2, 3" { 2133 2 4 93 2 5 -1 1 2 -20 1 2 3 2134 1 2 7 1 2 8 1 4 93 1 5 -1 2135 } 2136 8 "SELECT z, x FROM d1 ORDER BY 2" { 2137 3 1 8 1 7 1 -20 1 2138 93 1 -1 1 -1 2 93 2 2139 } 2140 9 "SELECT z, x FROM d1 ORDER BY 1" { 2141 -20 1 -1 2 -1 1 3 1 2142 7 1 8 1 93 2 93 1 2143 } 2144} { 2145 do_execsql_test e_select-8.4.$tn $select [list {*}$res] 2146} 2147 2148# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier 2149# that corresponds to the alias of one of the output columns, then the 2150# expression is considered an alias for that column. 2151# 2152foreach {tn select res} { 2153 1 "SELECT z+1 AS abc FROM d1 ORDER BY abc" { 2154 -19 0 0 4 8 9 94 94 2155 } 2156 2 "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" { 2157 94 94 9 8 4 0 0 -19 2158 } 2159 3 "SELECT z AS x, x AS z FROM d1 ORDER BY z" { 2160 3 1 8 1 7 1 -20 1 93 1 -1 1 -1 2 93 2 2161 } 2162 4 "SELECT z AS x, x AS z FROM d1 ORDER BY x" { 2163 -20 1 -1 2 -1 1 3 1 7 1 8 1 93 2 93 1 2164 } 2165} { 2166 do_execsql_test e_select-8.5.$tn $select [list {*}$res] 2167} 2168 2169# EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is 2170# any other expression, it is evaluated and the the returned value used 2171# to order the output rows. 2172# 2173# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT, 2174# then an ORDER BY may contain any arbitrary expressions. 2175# 2176foreach {tn select res} { 2177 1 "SELECT * FROM d1 ORDER BY x+y+z" { 2178 1 2 -20 1 5 -1 1 2 3 2 5 -1 2179 1 2 7 1 2 8 1 4 93 2 4 93 2180 } 2181 2 "SELECT * FROM d1 ORDER BY x*z" { 2182 1 2 -20 2 5 -1 1 5 -1 1 2 3 2183 1 2 7 1 2 8 1 4 93 2 4 93 2184 } 2185 3 "SELECT * FROM d1 ORDER BY y*z" { 2186 1 2 -20 2 5 -1 1 5 -1 1 2 3 2187 1 2 7 1 2 8 2 4 93 1 4 93 2188 } 2189} { 2190 do_execsql_test e_select-8.6.$tn $select [list {*}$res] 2191} 2192 2193# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound 2194# SELECT, then ORDER BY expressions that are not aliases to output 2195# columns must be exactly the same as an expression used as an output 2196# column. 2197# 2198foreach {tn select violation} { 2199 1 "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" 1st 2200 2 "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd 2201} { 2202 set err "$violation ORDER BY term does not match any column in the result set" 2203 do_catchsql_test e_select-8.7.1.$tn $select [list 1 $err] 2204} 2205foreach {tn select res} { 2206 1 "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" { 2207 -20 -2 -1 3 7 8 93 186 babied barked commercials gently 2208 iterate lad pragmatist reemphasizes rejoicing solemnness 2209 } 2210 2 "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" { 2211 1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 2212 babied charitableness barked interrupted commercials bathrobe gently 2213 failings iterate sexton lad relenting pragmatist guarded reemphasizes reply 2214 rejoicing liabilities solemnness annexed 2215 } 2216} { 2217 do_execsql_test e_select-8.7.2.$tn $select [list {*}$res] 2218} 2219 2220do_execsql_test e_select-8.8.0 { 2221 CREATE TABLE d3(a); 2222 INSERT INTO d3 VALUES('text'); 2223 INSERT INTO d3 VALUES(14.1); 2224 INSERT INTO d3 VALUES(13); 2225 INSERT INTO d3 VALUES(X'78787878'); 2226 INSERT INTO d3 VALUES(15); 2227 INSERT INTO d3 VALUES(12.9); 2228 INSERT INTO d3 VALUES(null); 2229 2230 CREATE TABLE d4(x COLLATE nocase); 2231 INSERT INTO d4 VALUES('abc'); 2232 INSERT INTO d4 VALUES('ghi'); 2233 INSERT INTO d4 VALUES('DEF'); 2234 INSERT INTO d4 VALUES('JKL'); 2235} {} 2236 2237# EVIDENCE-OF: R-10883-17697 For the purposes of sorting rows, values 2238# are compared in the same way as for comparison expressions. 2239# 2240# The following tests verify that values of different types are sorted 2241# correctly, and that mixed real and integer values are compared properly. 2242# 2243do_execsql_test e_select-8.8.1 { 2244 SELECT a FROM d3 ORDER BY a 2245} {{} 12.9 13 14.1 15 text xxxx} 2246do_execsql_test e_select-8.8.2 { 2247 SELECT a FROM d3 ORDER BY a DESC 2248} {xxxx text 15 14.1 13 12.9 {}} 2249 2250 2251# EVIDENCE-OF: R-64199-22471 If the ORDER BY expression is assigned a 2252# collation sequence using the postfix COLLATE operator, then the 2253# specified collation sequence is used. 2254# 2255do_execsql_test e_select-8.9.1 { 2256 SELECT x FROM d4 ORDER BY 1 COLLATE binary 2257} {DEF JKL abc ghi} 2258do_execsql_test e_select-8.9.2 { 2259 SELECT x COLLATE binary FROM d4 ORDER BY 1 COLLATE nocase 2260} {abc DEF ghi JKL} 2261 2262# EVIDENCE-OF: R-09398-26102 Otherwise, if the ORDER BY expression is 2263# an alias to an expression that has been assigned a collation sequence 2264# using the postfix COLLATE operator, then the collation sequence 2265# assigned to the aliased expression is used. 2266# 2267# In the test 8.10.2, the only result-column expression has no alias. So the 2268# ORDER BY expression is not a reference to it and therefore does not inherit 2269# the collation sequence. In test 8.10.3, "x" is the alias (as well as the 2270# column name), so the ORDER BY expression is interpreted as an alias and the 2271# collation sequence attached to the result column is used for sorting. 2272# 2273do_execsql_test e_select-8.10.1 { 2274 SELECT x COLLATE binary FROM d4 ORDER BY 1 2275} {DEF JKL abc ghi} 2276do_execsql_test e_select-8.10.2 { 2277 SELECT x COLLATE binary FROM d4 ORDER BY x 2278} {abc DEF ghi JKL} 2279do_execsql_test e_select-8.10.3 { 2280 SELECT x COLLATE binary AS x FROM d4 ORDER BY x 2281} {DEF JKL abc ghi} 2282 2283# EVIDENCE-OF: R-27301-09658 Otherwise, if the ORDER BY expression is a 2284# column or an alias of an expression that is a column, then the default 2285# collation sequence for the column is used. 2286# 2287do_execsql_test e_select-8.11.1 { 2288 SELECT x AS y FROM d4 ORDER BY y 2289} {abc DEF ghi JKL} 2290do_execsql_test e_select-8.11.2 { 2291 SELECT x||'' FROM d4 ORDER BY x 2292} {abc DEF ghi JKL} 2293 2294# EVIDENCE-OF: R-49925-55905 Otherwise, the BINARY collation sequence is 2295# used. 2296# 2297do_execsql_test e_select-8.12.1 { 2298 SELECT x FROM d4 ORDER BY x||'' 2299} {DEF JKL abc ghi} 2300 2301# EVIDENCE-OF: R-44130-32593 If an ORDER BY expression is not an integer 2302# alias, then SQLite searches the left-most SELECT in the compound for a 2303# result column that matches either the second or third rules above. If 2304# a match is found, the search stops and the expression is handled as an 2305# alias for the result column that it has been matched against. 2306# Otherwise, the next SELECT to the right is tried, and so on. 2307# 2308do_execsql_test e_select-8.13.0 { 2309 CREATE TABLE d5(a, b); 2310 CREATE TABLE d6(c, d); 2311 CREATE TABLE d7(e, f); 2312 2313 INSERT INTO d5 VALUES(1, 'f'); 2314 INSERT INTO d6 VALUES(2, 'e'); 2315 INSERT INTO d7 VALUES(3, 'd'); 2316 INSERT INTO d5 VALUES(4, 'c'); 2317 INSERT INTO d6 VALUES(5, 'b'); 2318 INSERT INTO d7 VALUES(6, 'a'); 2319 2320 CREATE TABLE d8(x COLLATE nocase); 2321 CREATE TABLE d9(y COLLATE nocase); 2322 2323 INSERT INTO d8 VALUES('a'); 2324 INSERT INTO d9 VALUES('B'); 2325 INSERT INTO d8 VALUES('c'); 2326 INSERT INTO d9 VALUES('D'); 2327} {} 2328foreach {tn select res} { 2329 1 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 2330 ORDER BY a 2331 } {1 2 3 4 5 6} 2332 2 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 2333 ORDER BY c 2334 } {1 2 3 4 5 6} 2335 3 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 2336 ORDER BY e 2337 } {1 2 3 4 5 6} 2338 4 { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7 2339 ORDER BY 1 2340 } {1 2 3 4 5 6} 2341 2342 5 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY b } 2343 {f 1 c 4 4 c 1 f} 2344 6 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 2 } 2345 {f 1 c 4 4 c 1 f} 2346 2347 7 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY a } 2348 {1 f 4 c c 4 f 1} 2349 8 { SELECT a, b FROM d5 UNION ALL SELECT b, a FROM d5 ORDER BY 1 } 2350 {1 f 4 c c 4 f 1} 2351 2352 9 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 2353 {f 2 c 5 4 c 1 f} 2354 10 { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 2355 {f 2 c 5 4 c 1 f} 2356 2357 11 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 2358 {2 f 5 c c 5 f 2} 2359 12 { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 2360 {2 f 5 c c 5 f 2} 2361 2362} { 2363 do_execsql_test e_select-8.13.$tn $select [list {*}$res] 2364} 2365 2366# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in 2367# the result columns of any constituent SELECT, it is an error. 2368# 2369foreach {tn select idx} { 2370 1 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 } 1st 2371 2 { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 } 2nd 2372 3 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' } 1st 2373 4 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah } 1st 2374 5 { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d } 3rd 2375 6 { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b } 4th 2376} { 2377 set err "$idx ORDER BY term does not match any column in the result set" 2378 do_catchsql_test e_select-8.14.$tn $select [list 1 $err] 2379} 2380 2381# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is 2382# processed separately and may be matched against result columns from 2383# different SELECT statements in the compound. 2384# 2385foreach {tn select res} { 2386 1 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d } 2387 {1 e 1 f 4 b 4 c} 2388 2 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b } 2389 {1 e 1 f 4 b 4 c} 2390 3 { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 } 2391 {1 e 1 f 4 b 4 c} 2392} { 2393 do_execsql_test e_select-8.15.$tn $select [list {*}$res] 2394} 2395 2396 2397#------------------------------------------------------------------------- 2398# Tests related to statements made about the LIMIT/OFFSET clause. 2399# 2400do_execsql_test e_select-9.0 { 2401 CREATE TABLE f1(a, b); 2402 INSERT INTO f1 VALUES(26, 'z'); 2403 INSERT INTO f1 VALUES(25, 'y'); 2404 INSERT INTO f1 VALUES(24, 'x'); 2405 INSERT INTO f1 VALUES(23, 'w'); 2406 INSERT INTO f1 VALUES(22, 'v'); 2407 INSERT INTO f1 VALUES(21, 'u'); 2408 INSERT INTO f1 VALUES(20, 't'); 2409 INSERT INTO f1 VALUES(19, 's'); 2410 INSERT INTO f1 VALUES(18, 'r'); 2411 INSERT INTO f1 VALUES(17, 'q'); 2412 INSERT INTO f1 VALUES(16, 'p'); 2413 INSERT INTO f1 VALUES(15, 'o'); 2414 INSERT INTO f1 VALUES(14, 'n'); 2415 INSERT INTO f1 VALUES(13, 'm'); 2416 INSERT INTO f1 VALUES(12, 'l'); 2417 INSERT INTO f1 VALUES(11, 'k'); 2418 INSERT INTO f1 VALUES(10, 'j'); 2419 INSERT INTO f1 VALUES(9, 'i'); 2420 INSERT INTO f1 VALUES(8, 'h'); 2421 INSERT INTO f1 VALUES(7, 'g'); 2422 INSERT INTO f1 VALUES(6, 'f'); 2423 INSERT INTO f1 VALUES(5, 'e'); 2424 INSERT INTO f1 VALUES(4, 'd'); 2425 INSERT INTO f1 VALUES(3, 'c'); 2426 INSERT INTO f1 VALUES(2, 'b'); 2427 INSERT INTO f1 VALUES(1, 'a'); 2428} {} 2429 2430# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the 2431# LIMIT clause, so long as it evaluates to an integer or a value that 2432# can be losslessly converted to an integer. 2433# 2434foreach {tn select res} { 2435 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e} 2436 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e} 2437 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 2438 {a b c d e} 2439 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e} 2440 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e} 2441} { 2442 do_execsql_test e_select-9.1.$tn $select [list {*}$res] 2443} 2444 2445# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value 2446# or any other value that cannot be losslessly converted to an integer, 2447# an error is returned. 2448# 2449foreach {tn select} { 2450 1 { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } 2451 2 { SELECT b FROM f1 ORDER BY a LIMIT NULL } 2452 3 { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } 2453 4 { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } 2454 5 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } 2455} { 2456 do_catchsql_test e_select-9.2.$tn $select {1 {datatype mismatch}} 2457} 2458 2459# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a 2460# negative value, then there is no upper bound on the number of rows 2461# returned. 2462# 2463foreach {tn select res} { 2464 1 { SELECT b FROM f1 ORDER BY a LIMIT -1 } 2465 {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} 2466 2 { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 2467 {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} 2468 3 { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 } 2469 {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} 2470} { 2471 do_execsql_test e_select-9.4.$tn $select [list {*}$res] 2472} 2473 2474# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N 2475# rows of its result set only, where N is the value that the LIMIT 2476# expression evaluates to. 2477# 2478foreach {tn select res} { 2479 1 { SELECT b FROM f1 ORDER BY a LIMIT 0 } {} 2480 2 { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w} 2481 3 { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s} 2482 4 { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o} 2483} { 2484 do_execsql_test e_select-9.5.$tn $select [list {*}$res] 2485} 2486 2487# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return 2488# less than N rows without a LIMIT clause, then the entire result set is 2489# returned. 2490# 2491foreach {tn select res} { 2492 1 { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z} 2493 2 { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5} 2494} { 2495 do_execsql_test e_select-9.6.$tn $select [list {*}$res] 2496} 2497 2498 2499# EVIDENCE-OF: R-24188-24349 The expression attached to the optional 2500# OFFSET clause that may follow a LIMIT clause must also evaluate to an 2501# integer, or a value that can be losslessly converted to an integer. 2502# 2503foreach {tn select} { 2504 1 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 'hello' } 2505 2 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET NULL } 2506 3 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET X'ABCD' } 2507 4 { SELECT b FROM f1 ORDER BY a LIMIT 2 OFFSET 5.1 } 2508 5 { SELECT b FROM f1 ORDER BY a 2509 LIMIT 2 OFFSET (SELECT group_concat(b) FROM f1) 2510 } 2511} { 2512 do_catchsql_test e_select-9.7.$tn $select {1 {datatype mismatch}} 2513} 2514 2515# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then 2516# the first M rows are omitted from the result set returned by the 2517# SELECT statement and the next N rows are returned, where M and N are 2518# the values that the OFFSET and LIMIT clauses evaluate to, 2519# respectively. 2520# 2521foreach {tn select res} { 2522 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o} 2523 2 { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o} 2524 3 { SELECT b FROM f1 ORDER BY a 2525 LIMIT (SELECT a FROM f1 WHERE b='j') 2526 OFFSET (SELECT a FROM f1 WHERE b='b') 2527 } {c d e f g h i j k l} 2528 4 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h} 2529 5 { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e} 2530 6 { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {} 2531 7 { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r} 2532} { 2533 do_execsql_test e_select-9.8.$tn $select [list {*}$res] 2534} 2535 2536# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than 2537# M+N rows if it did not have a LIMIT clause, then the first M rows are 2538# skipped and the remaining rows (if any) are returned. 2539# 2540foreach {tn select res} { 2541 1 { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z} 2542 2 { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1} 2543} { 2544 do_execsql_test e_select-9.9.$tn $select [list {*}$res] 2545} 2546 2547 2548# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a 2549# negative value, the results are the same as if it had evaluated to 2550# zero. 2551# 2552foreach {tn select res} { 2553 1 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e} 2554 2 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e} 2555 3 { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0 } {a b c d e} 2556} { 2557 do_execsql_test e_select-9.10.$tn $select [list {*}$res] 2558} 2559 2560# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the 2561# LIMIT clause may specify two scalar expressions separated by a comma. 2562# 2563# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used 2564# as the OFFSET expression and the second as the LIMIT expression. 2565# 2566foreach {tn select res} { 2567 1 { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o} 2568 2 { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o} 2569 3 { SELECT b FROM f1 ORDER BY a 2570 LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 2571 } {c d e f g h i j k l} 2572 4 { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h} 2573 5 { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e} 2574 6 { SELECT b FROM f1 ORDER BY a LIMIT 10, 0 } {} 2575 7 { SELECT b FROM f1 ORDER BY a LIMIT '1'||'5', 3 } {p q r} 2576 2577 8 { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z} 2578 9 { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1} 2579 2580 10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e} 2581 11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e} 2582 12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e} 2583} { 2584 do_execsql_test e_select-9.11.$tn $select [list {*}$res] 2585} 2586 2587 2588finish_test 2589