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