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