test/e_select.test

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