/ Check-in [0ee9e755]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add tests to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0ee9e755719c45e6047f9f004030716029b886ca
User & Date: dan 2010-09-18 15:03:35
Context
2010-09-18
15:15
Fix a couple of stale evidence marks in e_select.test. check-in: 14227724 user: dan tags: trunk
15:03
Add tests to e_select.test. check-in: 0ee9e755 user: dan tags: trunk
2010-09-17
22:39
Clarifications to the sqlite3_auto_extension() documentation. check-in: ca96e0df user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

69
70
71
72
73
74
75

















































76
77
78
79
80
81
82
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225

226






















































































227
228
229
230
231
232
233
234
235


236
237
238


239
240

241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260

261
262
263
264
265
266
267

268
269
270
271
272
273
274
275
276
277
278
279
280
281







282
283
284
285
286
287
288
...
403
404
405
406
407
408
409
410

411
412
413
414
415
416
417
418
419
420
421
422
423
424
...
444
445
446
447
448
449
450
451


452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
...
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563


564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582

583
584
585
586
587
588
589

590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
....
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
....
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
....
1349
1350
1351
1352
1353
1354
1355

















1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380

1381
1382










1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
....
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
....
1498
1499
1500
1501
1502
1503
1504
1505





1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
....
1572
1573
1574
1575
1576
1577
1578





1579
1580
1581
1582
1583
1584
1585
....
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
....
1708
1709
1710
1711
1712
1713
1714
1715

1716

1717
1718
1719
1720
1721
1722
1723
....
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746

1747

1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
....
1774
1775
1776
1777
1778
1779
1780
1781


1782
1783
1784
1785
1786
1787
1788
....
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
....
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
....
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
....
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
....
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
....
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
....
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
....
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
....
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
....
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327


2328
2329
2330
2331
2332
2333

2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
....
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
....
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498


2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
....
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578


2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
....
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
....
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
#
proc do_join_test {tn select res} {
  foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
    set S [string map [list %JOIN% $joinop] $select]
    uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
  }
}


















































#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
#
................................................................................
#   0: No WHERE clause
#   1: Has WHERE clause
#
#   0: No GROUP BY clause
#   1: Has GROUP BY clause
#   2: Has GROUP BY and HAVING clauses
#
foreach {tn select res} {
    0000.1  "SELECT 1, 2, 3 " {1 2 3}
    1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
    2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
    
    0100.1  "SELECT a, b, a||b FROM t1 " {
      a one aone b two btwo c three cthree
    }
    1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
      a one aone b two btwo c three cthree
    }
    1200.1  "SELECT ALL a, b, a||b FROM t1 " {
      a one aone b two btwo c three cthree
    }

    0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
    0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
    0010.3  "SELECT 1, 2, 3 WHERE NULL " {}

    1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}

    2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}

    0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
      a one aone b two btwo c three cthree
    }
    0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}

    1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
      a one aone b two btwo c three cthree
    }

    2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}

    0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
    0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
    0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

    1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
    1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
    1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

    2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
    2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
    2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

    0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
    0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
      1 a 1 c 1 b
    }
    0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }

    1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
    1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
             GROUP BY b HAVING count(*)=1" {
      1 a 1 c 1 b
    }
    1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
             GROUP BY b HAVING count(*)=2" { 
    }

    2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
    2102.1  "SELECT ALL count(*), max(a) FROM t1 
             GROUP BY b HAVING count(*)=1" {
      1 a 1 c 1 b
    }
    2102.2  "SELECT ALL count(*), max(a) FROM t1 
             GROUP BY b HAVING count(*)=2" { 
    }

    0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
    0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
    0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}

    1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
    1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
            {1 2 3}
    1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}

    2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
    2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
    2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}

    0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
    0112.1  "SELECT count(*), max(a) FROM t1 
             WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
    0112.2  "SELECT count(*), max(a) FROM t1 
             WHERE 0 GROUP BY b HAVING count(*)=2" { }
    1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
            {1 a 1 b}
    1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
             GROUP BY b HAVING count(*)=1" {
      1 c 1 b
    }
    1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
             GROUP BY b HAVING count(*)=2" { 
    }

    2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
            {1 c 1 b}
    2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
             GROUP BY b HAVING count(*)=1" {
      1 a 1 c
    }
    2112.2  "SELECT ALL count(*), max(a) FROM t1 
             WHERE 0 GROUP BY b HAVING count(*)=2" { }
} {
  do_execsql_test e_select-0.2.$tn $select [list {*}$res]
}


























































































#-------------------------------------------------------------------------
# The following tests focus on FROM clause (join) processing.
#

# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
# SELECT statement, then the input data is implicitly a single row zero
# columns wide
#
do_execsql_test e_select-1.1.1 { SELECT 'abc' }            {abc}


do_execsql_test e_select-1.1.2 { SELECT 'abc' WHERE NULL } {}
do_execsql_test e_select-1.1.3 { SELECT NULL }             {{}}
do_execsql_test e_select-1.1.4 { SELECT count(*) }         {1}


do_execsql_test e_select-1.1.5 { SELECT count(*) WHERE 0 } {0}
do_execsql_test e_select-1.1.6 { SELECT count(*) WHERE 1 } {1}


# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
# join-source following the FROM clause, then the input data used by the
# SELECT statement is the contents of the named table.
#
#   The results of the SELECT queries suggest that they are operating on the
#   contents of the table 'xx'.
#
do_execsql_test e_select-1.2.1 {
  CREATE TABLE xx(x, y);
  INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
  INSERT INTO xx VALUES(NULL, -16.87);
  INSERT INTO xx VALUES(-17.89, 'linguistically');
} {}
do_execsql_test e_select-1.2.2 { 
  SELECT quote(x), quote(y) FROM xx
} [list \
  'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' \
  NULL             -16.87                          \
  -17.89           'linguistically'                \

]
do_execsql_test e_select-1.2.3 { 
  SELECT count(*), count(x), count(y) FROM xx
} {3 2 3}
do_execsql_test e_select-1.2.4 { 
  SELECT sum(x), sum(y) FROM xx
} {-17.89 -16.87}


# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
# as part of the join-source following the FROM keyword, then the
# contents of each named table are joined into a single dataset for the
# simple SELECT statement to operate on.
#
#   There are more detailed tests for subsequent requirements that add 
#   more detail to this idea. We just add a single test that shows that
#   data is coming from each of the three tables following the FROM clause
#   here to show that the statement, vague as it is, is not incorrect.
#
do_execsql_test e_select-1.3.1 {
  SELECT * FROM t1, t2, t3
} [list a one a I a 1 a one a I b 2 a one b II a 1 a one b II b 2 a one c III a 1 a one c III b 2 b two a I a 1 b two a I b 2 b two b II a 1 b two b II b 2 b two c III a 1 b two c III b 2 c three a I a 1 c three a I b 2 c three b II a 1 c three b II b 2 c three c III a 1 c three c III b 2]








#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
................................................................................
  expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}
} [expr 4+4]

# Some extra cartesian product tests using tables t1 and t2.
#
do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1
foreach {tn select res} [list \

    1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
    2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
    3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
    4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
] {
  do_execsql_test e_select-1.4.5.$tn $select $res
}


# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# and the result cast to a numeric value as if by a CAST expression. All
# rows for which the expression evaluates to NULL or zero (integer value
# 0 or real value 0.0) are excluded from the dataset.
................................................................................
  do_join_test e_select-1.3.$tn $select $res
}

# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
# part of the join-constraint, then each of the column names specified
# must exist in the datasets to both the left and right of the join-op.
#
foreach {tn select col} {


  1 { SELECT * FROM t1, t3 USING (b) }   "b"
  2 { SELECT * FROM t3, t1 USING (c) }   "c"
  3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"
} {
  set err "cannot join using column $col - column not present in both tables"
  do_catchsql_test e_select-1.4.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product and the result cast to a numeric value. All rows for which one
# or more of the expressions evaluates to NULL or zero are excluded from
# the result set.
#
foreach {tn select res} {
  1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
  2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}
} {
  do_execsql_test e_select-1.5.$tn $select $res
}

# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
# left-hand side of the join operator is considered to be on the
................................................................................
  INSERT INTO t7 VALUES('x', 'ex',  24);
  INSERT INTO t7 VALUES('y', 'why', 25);

  INSERT INTO t8 VALUES('x', 'abc', 24);
  INSERT INTO t8 VALUES('z', 'ghi', 26);
} {}

do_execsql_test e_select-1.8.1a {
  SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)
} {1}
do_execsql_test e_select-1.8.1b {
  SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)
} {2}

do_execsql_test e_select-1.8.2a {
  SELECT count(*) FROM t7 JOIN t8 USING (a)
} {1}
do_execsql_test e_select-1.8.2b {
  SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)
} {2}



# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the
# columns that would normally contain values copied from the right-hand
# input dataset.
#
do_execsql_test e_select-1.9.1a {
  SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)
} {x ex 24 x abc 24}
do_execsql_test e_select-1.9.1b {
  SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)
} {x ex 24 x abc 24 y why 25 {} {} {}}

do_execsql_test e_select-1.9.2a {
  SELECT * FROM t7 JOIN t8 USING (a)
} {x ex 24 abc 24}
do_execsql_test e_select-1.9.2b {
  SELECT * FROM t7 LEFT JOIN t8 USING (a)
} {x ex 24 abc 24 y why 25 {} {}}


# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
# the join-ops, then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#
foreach {tn s1 s2 res} {

  1 { SELECT * FROM t7 JOIN t8 USING (a) }
    { SELECT * FROM t7 NATURAL JOIN t8 }
    {x ex 24 abc 24}

  2 { SELECT * FROM t8 JOIN t7 USING (a) }
    { SELECT * FROM t8 NATURAL JOIN t7 }
    {x abc 24 ex 24}

  3 { SELECT * FROM t7 LEFT JOIN t8 USING (a) }
    { SELECT * FROM t7 NATURAL LEFT JOIN t8 }
    {x ex 24 abc 24 y why 25 {} {}}

  4 { SELECT * FROM t8 LEFT JOIN t7 USING (a) }
    { SELECT * FROM t8 NATURAL LEFT JOIN t7 }
    {x abc 24 ex 24 z ghi 26 {} {}}

  5 { SELECT * FROM t3 JOIN t4 USING (a,c) }
    { SELECT * FROM t3 NATURAL JOIN t4 }
    {b 2}

  6 { SELECT * FROM t3 LEFT JOIN t4 USING (a,c) }
    { SELECT * FROM t3 NATURAL LEFT JOIN t4 }
    {a 1 b 2}
} {
  do_execsql_test e_select-1.10.${tn}a $s1 $res
  do_execsql_test e_select-1.10.${tn}b $s2 $res
}

# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets
# feature no common column names, then the NATURAL keyword has no effect
# on the results of the join.
#
do_execsql_test e_select-1.11.0 {
  CREATE TABLE t10(x, y);
  INSERT INTO t10 VALUES(1, 'true');
  INSERT INTO t10 VALUES(0, 'false');
} {}
foreach {tn s1 s2 res} {
  1 { SELECT a, x FROM t1 CROSS JOIN t10 }
    { SELECT a, x FROM t1 NATURAL CROSS JOIN t10 }
    {a 1 a 0 b 1 b 0 c 1 c 0}
} {
  do_execsql_test e_select-1.11.${tn}a $s1 $res
  do_execsql_test e_select-1.11.${tn}b $s2 $res
}

# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a
# join that specifies the NATURAL keyword.
#
foreach {tn sql} {
  1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}
................................................................................
# expression "*" then all columns in the input data are substituted for
# that one expression.
#
# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
# or subquery in the FROM clause followed by ".*" then all columns from
# the named table or subquery are substituted for the single expression.
#
foreach {tn select res} {
  1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
  2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
  3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
  4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
  5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}

  6  "SELECT count(*), * FROM z1"           {6 63 born -26}
................................................................................

  9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }
} {
  do_execsql_test e_select-4.1.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
# expression in any context other than than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
................................................................................
  7   "SELECT a, *, b, c FROM z1"                   6
} {
  set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
  sqlite3_finalize $::stmt
}


















# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#
#   By other definitions in lang_select.html, a non-aggregate query is
#   any simple SELECT that has no GROUP BY clause and no aggregate expressions
#   in the result expression list. These tests also verify (in a way) that
#   that definition is consistent:
#
do_execsql_test e_select-4.4.1 {
  SELECT a, b FROM z1
} {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}

do_execsql_test e_select-4.4.2 {
  SELECT a IS NULL, b+1, * FROM z1
} [list {*}{
        0 -58.58   51.65 -59.58 belfries
        0 {}       -5 {} 75            
        0 -22.18   -2.2 -23.18 suiters
        1 68       {} 67 quartets    
        0 -31.3    -1.04 -32.3 aspen
        0 1        63 born -26
}]

do_execsql_test e_select-4.4.3 {

  SELECT 32*32, d||e FROM z2
} {1024 {} 1024 366}











# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
# query without a GROUP BY clause, then each aggregate expression in the
# result-set is evaluated once across the entire dataset.
#
foreach {tn select res} {
  5.1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
  5.2 "SELECT count(*), max(1)"                                {1 1}

  5.3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  5.4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  5.5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
# result-set is evaluated once for an arbitrarily selected row of the
# dataset.
#
# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used
................................................................................

  CREATE TABLE a2(one PRIMARY KEY, three);
  INSERT INTO a2 VALUES(1, 1);
  INSERT INTO a2 VALUES(3, 2);
  INSERT INTO a2 VALUES(6, 3);
  INSERT INTO a2 VALUES(10, 4);
} {}
foreach {tn select res} {
  6.1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
  6.2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
  6.3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
  6.4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
  6.5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6.6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6.7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
# each non-aggregate expression is evaluated against a row consisting
# entirely of NULL values.
#
foreach {tn select res} {
  7.1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
  7.2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
  7.3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
    1 1 1
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
# clause always returns exactly one row of data, even if there are zero
# rows of input data.
#
foreach {tn select} {
  8.1  "SELECT count(*) FROM a1"
................................................................................
# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same are assigned to the same group.
#
foreach {tn select res} {





  9.1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  9.2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 18
  }
  9.3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  9.4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
foreach {tn select res} {
  10.1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
  10.2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {
    4 1
  }
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}  

# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
foreach {tn select res} {
  11.1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
  11.2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
  11.3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
  11.4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
  11.5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
  11.6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
# not be aggregate expressions.
#
foreach {tn select} {
  12.1  "SELECT * FROM b3 GROUP BY count(*)"
................................................................................
#
# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
# expression, it is evaluated with respect to an arbitrarily selected
# row from the group.
#
#   Tested by e_select-4.13.2.*
#





do_execsql_test e_select-4.13.0 {
  CREATE TABLE c1(up, down);
  INSERT INTO c1 VALUES('x', 1);
  INSERT INTO c1 VALUES('x', 2);
  INSERT INTO c1 VALUES('x', 4);
  INSERT INTO c1 VALUES('x', 8);
  INSERT INTO c1 VALUES('y', 16);
................................................................................
  INSERT INTO c3 VALUES(2,  'helium');
  INSERT INTO c3 VALUES(3,  'lithium');
  INSERT INTO c3 VALUES(4,  'beryllium');
  INSERT INTO c3 VALUES(5,  'boron');
  INSERT INTO c3 VALUES(94, 'plutonium');
} {}

foreach {tn select res} {
  13.1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  13.1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  13.1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  13.1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}

  13.2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  13.2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}

  13.2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
# evaluated once for each group of rows.
#
# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
# expression, it is evaluated across all rows in the group.
#
foreach {tn select res} {
  14.1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
  14.2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
  14.3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
  14.4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
  14.5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
        {3 4.33 1 2.0}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
# arbitrarily chosen row from within the group.
#
# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
# expression in the result-set, then all such expressions are evaluated
# for the same row.
#
foreach {tn select res} {
  15.1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
  15.2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
  15.3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  15.4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  15.5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
        {2 5 boron   2 2 helium   1 3 lithium}
} {
  do_execsql_test e_select-4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
# contributes a single row to the set of result rows.
#
# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
# DISTINCT keyword, the number of rows returned by an aggregate query
# with a GROUP BY clause is the same as the number of groups of rows
# produced by applying the GROUP BY and HAVING clauses to the filtered
# input dataset.
#
foreach {tn select nRow} {
  16.1  "SELECT i, j FROM c2 GROUP BY i%2"          2
  16.2  "SELECT i, j FROM c2 GROUP BY i"            9
  16.3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4
} {
  set rows 0
  db eval $select {incr rows}
  do_test e_select-4.$tn [list set rows] $nRow
}

#-------------------------------------------------------------------------
# The following tests attempt to verify statements made regarding the ALL
# and DISTINCT keywords.
#
drop_all_tables
do_execsql_test e_select-5.1.0 {
................................................................................
  INSERT INTO h3 VALUES(8, '2,4');
  INSERT INTO h3 VALUES(9, '3');
} {}

# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
# follow the SELECT keyword in a simple SELECT statement.
#
do_execsql_test e_select-5.1.1 { SELECT ALL a FROM h1      } {1 1 1 4 4 4}

do_execsql_test e_select-5.1.2 { SELECT DISTINCT a FROM h1 } {1 4}


# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
# the entire set of result rows are returned by the SELECT.
#
# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
# then the behaviour is as if ALL were specified.
#
................................................................................
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
# then duplicate rows are removed from the set of result rows before it
# is returned.
#
#   The three testable statements above are tested by e_select-5.2.*,
#   5.3.* and 5.4.* respectively.
#
foreach {tn select res} {
  3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}

  3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}

  4.1 "SELECT DISTINCT x FROM h2" {four one three two}
  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}
} {
  do_execsql_test e_select-5.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
# rows, two NULL values are considered to be equal.
#

do_execsql_test e_select-5.5.1 { SELECT DISTINCT d FROM h3 } {{} 2 2,3 2,4 3}


# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
# sequence to compare text values with apply.
#
foreach {tn select res} {
  6.1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
  6.2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
  6.3  "SELECT DISTINCT x FROM h2"                  {four one three two}
  6.4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
    Four One Three Two four one three two
  }
} {
  do_execsql_test e_select-5.$tn $select [list {*}$res]
}

#-------------------------------------------------------------------------
# The following tests - e_select-7.* - test that statements made to do
# with compound SELECT statements are correct.
#

................................................................................
#
drop_all_tables
do_execsql_test e_select-7.1.0 {
  CREATE TABLE j1(a, b, c);
  CREATE TABLE j2(e, f);
  CREATE TABLE j3(g);
} {}
foreach {tn select op} {


  1   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  2   "SELECT *    FROM j1    UNION ALL SELECT * FROM j3"    {UNION ALL}
  3   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  4   "SELECT a, b FROM j1    UNION ALL SELECT * FROM j3,j2" {UNION ALL}
  5   "SELECT *    FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL}

  6   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}
................................................................................
  15  "SELECT *    FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT}

  16  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  17  "SELECT *    FROM j1    EXCEPT SELECT * FROM j3"       {EXCEPT}
  18  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  19  "SELECT a, b FROM j1    EXCEPT SELECT * FROM j3,j2"    {EXCEPT}
  20  "SELECT *    FROM j3,j2 EXCEPT SELECT a, b FROM j1"    {EXCEPT}
} {
  set    err "SELECTs to the left and right of "
  append err $op
  append err " do not have the same number of result columns"
  do_catchsql_test e_select-7.1.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must
# be simple SELECT statements, they may not contain ORDER BY or LIMIT
# clauses.
# 
foreach {tn select op1 op2} {
  1   "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 
................................................................................
  INSERT INTO q2 VALUES('beauty', 2);
  INSERT INTO q2 VALUES(-65.91, 4);
  INSERT INTO q2 VALUES('emanating', -16.56);

  INSERT INTO q3 VALUES('beauty', 2);
  INSERT INTO q3 VALUES('beauty', 2);
} {}
foreach {tn select res} {
  1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
      {16 legible beauty legible beauty -65.91 emanating}

  2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
      {3 -16.56}

  4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
      {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}
} {
  do_execsql_test e_select-7.4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
# UNION ALL, except that duplicate rows are removed from the final
# result set.
#
foreach {tn select res} {
  1   {SELECT a FROM q1 UNION SELECT d FROM q2}
      {-65.91 16 beauty emanating legible}

  2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
      {-16.56 3}

  4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
      {-65.91 4 beauty 2 emanating -16.56 legible 1}
} {
  do_execsql_test e_select-7.5.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
# intersection of the results of the left and right SELECTs.
#
foreach {tn select res} {
  1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
  2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}
} {
  do_execsql_test e_select-7.6.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
# rows returned by the left SELECT that are not also returned by the
# right-hand SELECT.
#
foreach {tn select res} {
  1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}

  2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
      {-65.91 4 emanating -16.56 legible 1}
} {
  do_execsql_test e_select-7.7.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
# of INTERSECT and EXCEPT operators before the result set is returned.
#
foreach {tn select res} {
  0   {SELECT * FROM q3} {beauty 2 beauty 2}

  1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
  2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}
} {
  do_execsql_test e_select-7.8.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
# rows for the results of compound SELECT operators, NULL values are
# considered equal to other NULL values and distinct from all non-NULL
# values.
#
db nullvalue null
foreach {tn select res} {
  1   {SELECT NULL UNION ALL SELECT NULL} {null null}
  2   {SELECT NULL UNION     SELECT NULL} {null}
  3   {SELECT NULL INTERSECT SELECT NULL} {null}
  4   {SELECT NULL EXCEPT    SELECT NULL} {}

  5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
  6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
................................................................................
  11  {SELECT NULL INTERSECT SELECT 0} {}
  12  {SELECT NULL EXCEPT    SELECT 0} {null}

  13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
  14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
  15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
  16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}
} {
  do_execsql_test e_select-7.9.$tn $select [list {*}$res]
}
db nullvalue {} 

# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
# text values is determined as if the columns of the left and right-hand
# SELECT statements were the left and right-hand operands of the equals
# (=) operator, except that greater precedence is not assigned to a
................................................................................
# collation sequence specified with the postfix COLLATE operator.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
  CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
  INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
} {}
foreach {tn select res} {
  1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
  2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
  3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
  4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
  5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}

  6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
  7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
  8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}

  9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}

} {
  do_execsql_test e_select-7.10.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
# any values when comparing rows as part of a compound SELECT.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
................................................................................
  CREATE TABLE w1(a TEXT, b NUMBER);
  CREATE TABLE w2(a, b TEXT);

  INSERT INTO w1 VALUES('1', 4.1);
  INSERT INTO w2 VALUES(1, 4.1);
} {}

foreach {tn select res} {
  1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
  2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
  3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
  4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}

  5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
  6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
................................................................................
  7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
  8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}

  9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
  10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
  11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
  12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}
} {
  do_execsql_test e_select-7.11.$tn $select [list {*}$res]
}


# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
# connected into a compound SELECT, they group from left to right. In
# other words, if "A", "B" and "C" are all simple SELECT statements, (A
# op B op C) is processed as ((A op B) op C).
................................................................................
  INSERT INTO d2 VALUES('lad', 'relenting');
} {}

# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results
# of evaluating the left-most expression in the ORDER BY list, then ties
# are broken by evaluating the second left-most expression and so on.
#
foreach {tn select res} {
  1  "SELECT * FROM d1 ORDER BY x, y, z" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
} {
  do_execsql_test e_select-8.1.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally
# followed by one of the keywords ASC (smaller values are returned
# first) or DESC (larger values are returned first).
#
#   Test cases e_select-8.2.* test the above.
................................................................................
#
# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows
# are sorted in ascending (smaller values first) order by default.
#
#   Test cases e_select-8.3.* test the above. All 8.3 test cases are
#   copies of 8.2 test cases with the explicit "ASC" removed.
#
foreach {tn select res} {
  2.1  "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2.2  "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
     2 4 93   2 5 -1     1 2 8      1 2 7    
     1 2 3    1 2 -20    1 4  93    1 5 -1   
  }
  3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" {
     2 4 93   2 5 -1     1 2 -20    1 2 3    
     1 2 7    1 2 8      1 4  93    1 5 -1   
  }
} {
  do_execsql_test e_select-8.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant
# integer K then the expression is considered an alias for the K-th
# column of the result set (columns are numbered from left to right
# starting with 1).
#
foreach {tn select res} {
  1  "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2  "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
     3 1     8 1    7 1   -20 1 
     93 1   -1 1   -1 2   93 2
  }
  9  "SELECT z, x FROM d1 ORDER BY 1" {
     -20 1  -1 2   -1 1   3 1     
     7 1     8 1   93 2   93 1   
  }
} {
  do_execsql_test e_select-8.4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
foreach {tn select res} {
  1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
    -19 0 0 4 8 9 94 94
  }
  2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
    94 94 9 8 4 0 0 -19
  }
  3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
    3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
  }
  4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
    -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
  }
} {
  do_execsql_test e_select-8.5.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the the returned value used
# to order the output rows.
#
# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
# then an ORDER BY may contain any arbitrary expressions.
#
foreach {tn select res} {
  1   "SELECT * FROM d1 ORDER BY x+y+z" {
    1 2 -20    1 5 -1    1 2 3    2 5 -1 
    1 2 7      1 2 8     1 4 93   2 4 93
  }
  2   "SELECT * FROM d1 ORDER BY x*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     1 4 93    2 4 93
  }
  3   "SELECT * FROM d1 ORDER BY y*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     2 4 93    1 4 93
  }
} {
  do_execsql_test e_select-8.6.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound
# SELECT, then ORDER BY expressions that are not aliases to output
# columns must be exactly the same as an expression used as an output
# column.
#
foreach {tn select violation} {


  1   "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z"        1st
  2   "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd
} {
  set err "$violation ORDER BY term does not match any column in the result set"
  do_catchsql_test e_select-8.7.1.$tn $select [list 1 $err]
}

foreach {tn select res} {
  1   "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" {
    -20 -2 -1 3 7 8 93 186 babied barked commercials gently 
    iterate lad pragmatist reemphasizes rejoicing solemnness
  }
  2   "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" {
    1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 
    babied charitableness barked interrupted commercials bathrobe gently
    failings iterate sexton lad relenting pragmatist guarded reemphasizes reply
    rejoicing liabilities solemnness annexed
  }
} {
  do_execsql_test e_select-8.7.2.$tn $select [list {*}$res]
}

do_execsql_test e_select-8.8.0 {
  CREATE TABLE d3(a);
  INSERT INTO d3 VALUES('text');
  INSERT INTO d3 VALUES(14.1);
  INSERT INTO d3 VALUES(13);
  INSERT INTO d3 VALUES(X'78787878');
................................................................................
  CREATE TABLE d9(y COLLATE nocase);

  INSERT INTO d8 VALUES('a');
  INSERT INTO d9 VALUES('B');
  INSERT INTO d8 VALUES('c');
  INSERT INTO d9 VALUES('D');
} {}
foreach {tn select res} {
  1   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY a
      } {1 2 3 4 5 6}
  2   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY c
      } {1 2 3 4 5 6}
  3   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
................................................................................
  10  { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 
      {f 2   c 5   4 c   1 f}

  11  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
      {2 f   5 c   c 5   f 2}
  12  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 
      {2 f   5 c   c 5   f 2}

} {
  do_execsql_test e_select-8.13.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in
# the result columns of any constituent SELECT, it is an error.
#
foreach {tn select idx} {


  1   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 }          1st
  2   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 }       2nd
  3   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' }  1st
  4   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah    }  1st
  5   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d }  3rd
  6   { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b  }  4th
} {
  set err "$idx ORDER BY term does not match any column in the result set"
  do_catchsql_test e_select-8.14.$tn $select [list 1 $err]
}

# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is
# processed separately and may be matched against result columns from
# different SELECT statements in the compound.
# 
foreach {tn select res} {
  1  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d }
     {1 e   1 f   4 b   4 c}
  2  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b }
     {1 e   1 f   4 b   4 c}
  3  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 }
     {1 e   1 f   4 b   4 c}
} {
  do_execsql_test e_select-8.15.$tn $select [list {*}$res]
}


#-------------------------------------------------------------------------
# Tests related to statements made about the LIMIT/OFFSET clause.
#
do_execsql_test e_select-9.0 {
  CREATE TABLE f1(a, b);
................................................................................
  INSERT INTO f1 VALUES(1, 'a');
} {}

# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the
# LIMIT clause, so long as it evaluates to an integer or a value that
# can be losslessly converted to an integer.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 
     {a b c d e}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e}
} {
  do_execsql_test e_select-9.1.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
# or any other value that cannot be losslessly converted to an integer,
# an error is returned.
#
foreach {tn select} {


  1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } 
  2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } 
  3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } 
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } 
  5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } 
} {
  do_catchsql_test e_select-9.2.$tn $select {1 {datatype mismatch}}
}

# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a
# negative value, then there is no upper bound on the number of rows
# returned.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT -1 } 
     {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}
  2  { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 
     {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}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 }
     {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}
} {
  do_execsql_test e_select-9.4.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
# rows of its result set only, where N is the value that the LIMIT
# expression evaluates to.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 0 } {}
  2  { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w}
  3  { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s}
  4  { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o}
} {
  do_execsql_test e_select-9.5.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
# less than N rows without a LIMIT clause, then the entire result set is
# returned.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z}
  2  { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5}
} {
  do_execsql_test e_select-9.6.$tn $select [list {*}$res]
}


# EVIDENCE-OF: R-24188-24349 The expression attached to the optional
# OFFSET clause that may follow a LIMIT clause must also evaluate to an
# integer, or a value that can be losslessly converted to an integer.
#
foreach {tn select} {
................................................................................

# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then
# the first M rows are omitted from the result set returned by the
# SELECT statement and the next N rows are returned, where M and N are
# the values that the OFFSET and LIMIT clauses evaluate to,
# respectively.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT  (SELECT a FROM f1 WHERE b='j') 
       OFFSET (SELECT a FROM f1 WHERE b='b') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e}
  6  { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {}
  7  { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r}
} {
  do_execsql_test e_select-9.8.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
# M+N rows if it did not have a LIMIT clause, then the first M rows are
# skipped and the remaining rows (if any) are returned.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z}
  2  { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1}
} {
  do_execsql_test e_select-9.9.$tn $select [list {*}$res]
}


# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a
# negative value, the results are the same as if it had evaluated to
# zero.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  } {a b c d e}
} {
  do_execsql_test e_select-9.10.$tn $select [list {*}$res]
}

# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
# LIMIT clause may specify two scalar expressions separated by a comma.
#
# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used
# as the OFFSET expression and the second as the LIMIT expression.
#
foreach {tn select res} {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e}
................................................................................

  8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
  9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}

  10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
  11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
  12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}
} {
  do_execsql_test e_select-9.11.$tn $select [list {*}$res]
}


finish_test







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|

|

|

|
|
|
|

|
|
|

|

|
|
|

|
|
|

|
|
|

|
|
|
|
|

|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|

|
|
|

|
|
|
|

|
|
|

|
|
|
|
|
|
|
|
|
|
|
|
|
|

|
|
|
|
|
|
|
|
<
<



>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








<
>
>
|
<
<
>
>
|
|
>








|





|
|
<
|
|
|
>
|
<
|
<
<
|
<
>











|
|
|
>
>
>
>
>
>
>







 







|
>




|
<
<







 







|
>
>



<
<
<
|







|


<
<
|







 







|
|
<
<
|
<
<
<
|
<
<
|
<
>
>





|
|
<
<
|
|
<
<
|
<
<
|
<
|
>






<
>
|
|
<

|
|
<

|
|
<

|
|
<

|
|
<

|
|
<
<
<
<
|










|
|
|
<
<
<
<







 







|







 







<
<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




<
<
<
<
<
|
|
|

<
|
<






<
|
<
>
|
<
>
>
>
>
>
>
>
>
>
>





|
|
|

|
|
|
<
<







 







|
|
|
|
|
|
|
|
<
<






|
|
|
|


<
<
|







 







|
>
>
>
>
>
|


|


|


|


<
<
|




|
|
|
<
|
<
<
<





|
|
|
|
|
|
|
<
<







 







>
>
>
>
>







 







|
|
|
|
|

|
|

|
<
<








|
|
|
|
|
|

<
<
|








|
|
|
|
|
|

<
<
|










|
|
|
|
<
<
<
<
|







 







|
>
|
>







 







|








<
<
|




>
|
>




|
|
|
|
|


<
<







 







|
>
>







 







<
<
<
<
<
|







 







|











<
<
|





|











<
<
|




|


<
<






|




<
<





|




<
<








|







 







<
<







 







|











<
<
<







 







|







 







<
<







 







|




<
<







 







|







 







<
<







|







 







<
<






|












<
<









|












<
<







|
>
>


<
<
<
|
>
|










<
<
|







 







|







 







|
<
<
<




|
>
>






<
<
<
|





|






<
<
|







 







|






<
<






<
>
>
|
|
|
|
|
<
<
|





|






<
<






|




<
<






|


<
<
|







 







|










<
<






|


<
<







|



<
<
|







|







 







<
<




69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
...
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269


270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368

369
370
371


372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392

393
394
395
396
397

398


399

400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
...
543
544
545
546
547
548
549
550
551
552
553
554
555
556


557
558
559
560
561
562
563
...
583
584
585
586
587
588
589
590
591
592
593
594
595



596
597
598
599
600
601
602
603
604
605
606


607
608
609
610
611
612
613
614
...
680
681
682
683
684
685
686
687
688


689



690


691

692
693
694
695
696
697
698
699
700


701
702


703


704

705
706
707
708
709
710
711
712

713
714
715

716
717
718

719
720
721

722
723
724

725
726
727

728
729
730




731
732
733
734
735
736
737
738
739
740
741
742
743
744




745
746
747
748
749
750
751
....
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
....
1416
1417
1418
1419
1420
1421
1422


1423
1424
1425
1426
1427
1428
1429
....
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485





1486
1487
1488
1489

1490

1491
1492
1493
1494
1495
1496

1497

1498
1499

1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521


1522
1523
1524
1525
1526
1527
1528
....
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557


1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569


1570
1571
1572
1573
1574
1575
1576
1577
....
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643


1644
1645
1646
1647
1648
1649
1650
1651

1652



1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664


1665
1666
1667
1668
1669
1670
1671
....
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
....
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740


1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755


1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771


1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786




1787
1788
1789
1790
1791
1792
1793
1794
....
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
....
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854


1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873


1874
1875
1876
1877
1878
1879
1880
....
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
....
1913
1914
1915
1916
1917
1918
1919





1920
1921
1922
1923
1924
1925
1926
1927
....
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051


2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069


2070
2071
2072
2073
2074
2075
2076
2077


2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088


2089
2090
2091
2092
2093
2094
2095
2096
2097
2098


2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
....
2120
2121
2122
2123
2124
2125
2126


2127
2128
2129
2130
2131
2132
2133
....
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152



2153
2154
2155
2156
2157
2158
2159
....
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
....
2175
2176
2177
2178
2179
2180
2181


2182
2183
2184
2185
2186
2187
2188
....
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273


2274
2275
2276
2277
2278
2279
2280
....
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
....
2311
2312
2313
2314
2315
2316
2317


2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
....
2355
2356
2357
2358
2359
2360
2361


2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380


2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402


2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414



2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427


2428
2429
2430
2431
2432
2433
2434
2435
....
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
....
2564
2565
2566
2567
2568
2569
2570
2571



2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584



2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597


2598
2599
2600
2601
2602
2603
2604
2605
....
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644


2645
2646
2647
2648
2649
2650

2651
2652
2653
2654
2655
2656
2657


2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670


2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681


2682
2683
2684
2685
2686
2687
2688
2689
2690


2691
2692
2693
2694
2695
2696
2697
2698
....
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726


2727
2728
2729
2730
2731
2732
2733
2734
2735


2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746


2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
....
2765
2766
2767
2768
2769
2770
2771


2772
2773
2774
2775
#
proc do_join_test {tn select res} {
  foreach {tn2 joinop} [list    1 ,    2 "CROSS JOIN"    3 "INNER JOIN"] {
    set S [string map [list %JOIN% $joinop] $select]
    uplevel do_execsql_test $tn.$tn2 [list $S] [list $res]
  }
}

#
#   Usage: do_select_tests PREFIX ?SWITCHES? TESTLIST
#
# Where switches are:
#
#   -errorformat FMTSTRING
#
proc do_select_tests {prefix args} {

  set testlist [lindex $args end]
  set switches [lrange $args 0 end-1]

  set errfmt ""
  set countonly 0

  for {set i 0} {$i < [llength $switches]} {incr i} {
    set s [lindex $switches $i]
    set n [string length $s]
    if {$n>=2 && [string equal -length $n $s "-errorformat"]} {
      set errfmt [lindex $switches [incr i]]
    } elseif {$n>=2 && [string equal -length $n $s "-count"]} {
      set countonly 1
    } else {
      error "unknown switch: $s"
    }
  }

  if {$countonly && $errfmt!=""} {
    error "Cannot use -count and -errorformat together"
  }
  set nTestlist [llength $testlist]
  if {$nTestlist%3 || $nTestlist==0 } {
    error "SELECT test list contains [llength $testlist] elements"
  }

  foreach {tn sql res} $testlist {
    if {$countonly} {
      set nRow 0
      db eval $sql {incr nRow}
      uplevel do_test ${prefix}.$tn [list [list set {} $nRow]] [list $res]
    } elseif {$errfmt==""} {
      uplevel do_execsql_test ${prefix}.${tn} [list $sql] [list [list {*}$res]]
    } else {
      set res [list 1 [string trim [format $errfmt $res]]]
      uplevel do_catchsql_test ${prefix}.${tn} [list $sql] [list $res]
    }
  }
}

#-------------------------------------------------------------------------
# The following tests check that all paths on the syntax diagrams on
# the lang_select.html page may be taken.
#
# EVIDENCE-OF: R-18428-22111 -- syntax diagram join-constraint
#
................................................................................
#   0: No WHERE clause
#   1: Has WHERE clause
#
#   0: No GROUP BY clause
#   1: Has GROUP BY clause
#   2: Has GROUP BY and HAVING clauses
#
do_select_tests e_select-0.2 {
  0000.1  "SELECT 1, 2, 3 " {1 2 3}
  1000.1  "SELECT DISTINCT 1, 2, 3 " {1 2 3}
  2000.1  "SELECT ALL 1, 2, 3 " {1 2 3}
  
  0100.1  "SELECT a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }
  1100.1  "SELECT DISTINCT a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }
  1200.1  "SELECT ALL a, b, a||b FROM t1 " {
    a one aone b two btwo c three cthree
  }

  0010.1  "SELECT 1, 2, 3 WHERE 1 " {1 2 3}
  0010.2  "SELECT 1, 2, 3 WHERE 0 " {}
  0010.3  "SELECT 1, 2, 3 WHERE NULL " {}

  1010.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 " {1 2 3}

  2010.1  "SELECT ALL 1, 2, 3 WHERE 1 " {1 2 3}

  0110.1  "SELECT a, b, a||b FROM t1 WHERE a!='x' " {
    a one aone b two btwo c three cthree
  }
  0110.2  "SELECT a, b, a||b FROM t1 WHERE a=='x'" {}

  1110.1  "SELECT DISTINCT a, b, a||b FROM t1 WHERE a!='x' " {
    a one aone b two btwo c three cthree
  }

  2110.0  "SELECT ALL a, b, a||b FROM t1 WHERE a=='x'" {}

  0001.1  "SELECT 1, 2, 3 GROUP BY 2" {1 2 3}
  0002.1  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  0002.2  "SELECT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  1001.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2" {1 2 3}
  1002.1  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  1002.2  "SELECT DISTINCT 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  2001.1  "SELECT ALL 1, 2, 3 GROUP BY 2" {1 2 3}
  2002.1  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)=1" {1 2 3}
  2002.2  "SELECT ALL 1, 2, 3 GROUP BY 2 HAVING count(*)>1" {}

  0101.1  "SELECT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  0102.1  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  0102.2  "SELECT count(*), max(a) FROM t1 GROUP BY b HAVING count(*)=2" { }

  1101.1  "SELECT DISTINCT count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  1102.1  "SELECT DISTINCT count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  1102.2  "SELECT DISTINCT count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=2" { 
  }

  2101.1  "SELECT ALL count(*), max(a) FROM t1 GROUP BY b" {1 a 1 c 1 b}
  2102.1  "SELECT ALL count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c 1 b
  }
  2102.2  "SELECT ALL count(*), max(a) FROM t1 
           GROUP BY b HAVING count(*)=2" { 
  }

  0011.1  "SELECT 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
  0012.1  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
  0012.2  "SELECT 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)>1" {}

  1011.1  "SELECT DISTINCT 1, 2, 3 WHERE 0 GROUP BY 2" {}
  1012.1  "SELECT DISTINCT 1, 2, 3 WHERE 1 GROUP BY 2 HAVING count(*)=1" 
          {1 2 3}
  1012.2  "SELECT DISTINCT 1, 2, 3 WHERE NULL GROUP BY 2 HAVING count(*)>1" {}

  2011.1  "SELECT ALL 1, 2, 3 WHERE 1 GROUP BY 2" {1 2 3}
  2012.1  "SELECT ALL 1, 2, 3 WHERE 0 GROUP BY 2 HAVING count(*)=1" {}
  2012.2  "SELECT ALL 1, 2, 3 WHERE 'abc' GROUP BY 2 HAVING count(*)>1" {}

  0111.1  "SELECT count(*), max(a) FROM t1 WHERE a='a' GROUP BY b" {1 a}
  0112.1  "SELECT count(*), max(a) FROM t1 
           WHERE a='c' GROUP BY b HAVING count(*)=1" {1 c}
  0112.2  "SELECT count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }
  1111.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a<'c' GROUP BY b" 
          {1 a 1 b}
  1112.1  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE a>'a'
           GROUP BY b HAVING count(*)=1" {
    1 c 1 b
  }
  1112.2  "SELECT DISTINCT count(*), max(a) FROM t1 WHERE 0
           GROUP BY b HAVING count(*)=2" { 
  }

  2111.1  "SELECT ALL count(*), max(a) FROM t1 WHERE b>'one' GROUP BY b" 
          {1 c 1 b}
  2112.1  "SELECT ALL count(*), max(a) FROM t1 WHERE a!='b'
           GROUP BY b HAVING count(*)=1" {
    1 a 1 c
  }
  2112.2  "SELECT ALL count(*), max(a) FROM t1 
           WHERE 0 GROUP BY b HAVING count(*)=2" { }


}


# EVIDENCE-OF: R-23316-20169 -- syntax diagram result-column
#
do_select_tests e_select-0.3 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT t1.* FROM t1" {a one b two c three}
  3  "SELECT 'x'||a||'x' FROM t1" {xax xbx xcx}
  4  "SELECT 'x'||a||'x' alias FROM t1" {xax xbx xcx}
  5  "SELECT 'x'||a||'x' AS alias FROM t1" {xax xbx xcx}
}

# EVIDENCE-OF: R-41233-21397 -- syntax diagram join-source
#
# EVIDENCE-OF: R-62821-57533 -- syntax diagram join-op
#
do_select_tests e_select-0.4 {
  1  "SELECT t1.rowid FROM t1" {1 2 3}
  2  "SELECT t1.rowid FROM t1,t2" {1 1 1 2 2 2 3 3 3}
  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}

  4  "SELECT t1.rowid FROM t1" {1 2 3}
  5  "SELECT t1.rowid FROM t1 JOIN t2" {1 1 1 2 2 2 3 3 3}
  6  "SELECT t1.rowid FROM t1 JOIN t2 JOIN t3" 
     {1 1 1 1 1 1 2 2 2 2 2 2 3 3 3 3 3 3}

  7  "SELECT t1.rowid FROM t1 NATURAL JOIN t3" {1 2}
  8  "SELECT t1.rowid FROM t1 NATURAL LEFT OUTER JOIN t3" {1 2 3}
  9  "SELECT t1.rowid FROM t1 NATURAL INNER JOIN t3" {1 2}
  10 "SELECT t1.rowid FROM t1 NATURAL CROSS JOIN t3" {1 2}

  11 "SELECT t1.rowid FROM t1 JOIN t3" {1 1 2 2 3 3}
  12 "SELECT t1.rowid FROM t1 LEFT OUTER JOIN t3" {1 1 2 2 3 3}
  13 "SELECT t1.rowid FROM t1 INNER JOIN t3" {1 1 2 2 3 3}
  14 "SELECT t1.rowid FROM t1 CROSS JOIN t3" {1 1 2 2 3 3}
}

# EVIDENCE-OF: R-56911-63533 -- syntax diagram compound-operator
#
do_select_tests e_select-0.5 {
  1  "SELECT rowid FROM t1 UNION ALL SELECT rowid+2 FROM t4" {1 2 3 3 4}
  2  "SELECT rowid FROM t1 UNION     SELECT rowid+2 FROM t4" {1 2 3 4}
  3  "SELECT rowid FROM t1 INTERSECT SELECT rowid+2 FROM t4" {3}
  4  "SELECT rowid FROM t1 EXCEPT    SELECT rowid+2 FROM t4" {1 2}
}

# EVIDENCE-OF: R-60388-27458 -- syntax diagram ordering-term
#
do_select_tests e_select-0.6 {
  1  "SELECT b||a FROM t1 ORDER BY b||a"                  {onea threec twob}
  2  "SELECT b||a FROM t1 ORDER BY (b||a) COLLATE nocase" {onea threec twob}
  3  "SELECT b||a FROM t1 ORDER BY (b||a) ASC"            {onea threec twob}
  4  "SELECT b||a FROM t1 ORDER BY (b||a) DESC"           {twob threec onea}
}

# EVIDENCE-OF: R-35843-38195 -- syntax diagram select-stmt
#
do_select_tests e_select-0.7 {
  1  "SELECT * FROM t1" {a one b two c three}
  2  "SELECT * FROM t1 ORDER BY b" {a one c three b two}
  3  "SELECT * FROM t1 ORDER BY b, a" {a one c three b two}

  4  "SELECT * FROM t1 LIMIT 10" {a one b two c three}
  5  "SELECT * FROM t1 LIMIT 10 OFFSET 5" {}
  6  "SELECT * FROM t1 LIMIT 10, 5" {}

  7  "SELECT * FROM t1 ORDER BY a LIMIT 10" {a one b two c three}
  8  "SELECT * FROM t1 ORDER BY b LIMIT 10 OFFSET 5" {}
  9  "SELECT * FROM t1 ORDER BY a,b LIMIT 10, 5" {}

  10  "SELECT * FROM t1 UNION SELECT b, a FROM t1" 
     {a one b two c three one a three c two b}
  11  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b" 
     {one a two b three c a one c three b two}
  12  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b, a" 
     {one a two b three c a one c three b two}
  13  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10" 
     {a one b two c three one a three c two b}
  14  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10 OFFSET 5" 
     {two b}
  15  "SELECT * FROM t1 UNION SELECT b, a FROM t1 LIMIT 10, 5" 
     {}
  16  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a LIMIT 10" 
     {a one b two c three one a three c two b}
  17  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY b LIMIT 10 OFFSET 5" 
     {b two}
  18  "SELECT * FROM t1 UNION SELECT b, a FROM t1 ORDER BY a,b LIMIT 10, 5" 
     {}
}

#-------------------------------------------------------------------------
# The following tests focus on FROM clause (join) processing.
#

# EVIDENCE-OF: R-16074-54196 If the FROM clause is omitted from a simple
# SELECT statement, then the input data is implicitly a single row zero
# columns wide
#

do_select_tests e_select-1.1 {
  1 "SELECT 'abc'"            {abc}
  2 "SELECT 'abc' WHERE NULL" {}


  3 "SELECT NULL"             {{}}
  4 "SELECT count(*)"         {1}
  5 "SELECT count(*) WHERE 0" {0}
  6 "SELECT count(*) WHERE 1" {1}
}

# EVIDENCE-OF: R-48114-33255 If there is only a single table in the
# join-source following the FROM clause, then the input data used by the
# SELECT statement is the contents of the named table.
#
#   The results of the SELECT queries suggest that they are operating on the
#   contents of the table 'xx'.
#
do_execsql_test e_select-1.2.0 {
  CREATE TABLE xx(x, y);
  INSERT INTO xx VALUES('IiJlsIPepMuAhU', X'10B00B897A15BAA02E3F98DCE8F2');
  INSERT INTO xx VALUES(NULL, -16.87);
  INSERT INTO xx VALUES(-17.89, 'linguistically');
} {}
do_select_tests e_select-1.2 {
  1  "SELECT quote(x), quote(y) FROM xx" {

     'IiJlsIPepMuAhU' X'10B00B897A15BAA02E3F98DCE8F2' 
     NULL             -16.87                          
     -17.89           'linguistically'                
  }


  2  "SELECT count(*), count(x), count(y) FROM xx" {3 2 3}


  3  "SELECT sum(x), sum(y) FROM xx"               {-17.89 -16.87}

}

# EVIDENCE-OF: R-23593-12456 If there is more than one table specified
# as part of the join-source following the FROM keyword, then the
# contents of each named table are joined into a single dataset for the
# simple SELECT statement to operate on.
#
#   There are more detailed tests for subsequent requirements that add 
#   more detail to this idea. We just add a single test that shows that
#   data is coming from each of the three tables following the FROM clause
#   here to show that the statement, vague as it is, is not incorrect.
#
do_select_tests e_select-1.3 {
  1 "SELECT * FROM t1, t2, t3" {
      a one a I a 1 a one a I b 2 a one b II a 1 
      a one b II b 2 a one c III a 1 a one c III b 2 
      b two a I a 1 b two a I b 2 b two b II a 1 
      b two b II b 2 b two c III a 1 b two c III b 2 
      c three a I a 1 c three a I b 2 c three b II a 1 
      c three b II b 2 c three c III a 1 c three c III b 2
  }
}

#
# The following block of tests - e_select-1.4.* - test that the description
# of cartesian joins in the SELECT documentation is consistent with SQLite.
# In doing so, we test the following three requirements as a side-effect:
#
# EVIDENCE-OF: R-46122-14930 If the join-op is "CROSS JOIN", "INNER
................................................................................
  expr {[llength [execsql {SELECT * FROM x3 INNER JOIN x3 AS x4}]] / 25}
} [expr 4+4]

# Some extra cartesian product tests using tables t1 and t2.
#
do_execsql_test e_select-1.4.4.1 { SELECT * FROM t1, t2 } $t1_cross_t2
do_execsql_test e_select-1.4.4.2 { SELECT * FROM t1 AS x, t1 AS y} $t1_cross_t1

do_select_tests e_select-1.4.5 [list                                   \
    1 { SELECT * FROM t1 CROSS JOIN t2 }           $t1_cross_t2        \
    2 { SELECT * FROM t1 AS y CROSS JOIN t1 AS x } $t1_cross_t1        \
    3 { SELECT * FROM t1 INNER JOIN t2 }           $t1_cross_t2        \
    4 { SELECT * FROM t1 AS y INNER JOIN t1 AS x } $t1_cross_t1        \
]




# EVIDENCE-OF: R-45641-53865 If there is an ON clause specified, then
# the ON expression is evaluated for each row of the cartesian product
# and the result cast to a numeric value as if by a CAST expression. All
# rows for which the expression evaluates to NULL or zero (integer value
# 0 or real value 0.0) are excluded from the dataset.
................................................................................
  do_join_test e_select-1.3.$tn $select $res
}

# EVIDENCE-OF: R-63358-54862 If there is a USING clause specified as
# part of the join-constraint, then each of the column names specified
# must exist in the datasets to both the left and right of the join-op.
#
do_select_tests e_select-1.4 -error {
  cannot join using column %s - column not present in both tables
} {
  1 { SELECT * FROM t1, t3 USING (b) }   "b"
  2 { SELECT * FROM t3, t1 USING (c) }   "c"
  3 { SELECT * FROM t3, (SELECT a AS b, b AS c FROM t1) USING (a) }   "a"



} 

# EVIDENCE-OF: R-42568-37000 For each pair of namesake columns, the
# expression "lhs.X = rhs.X" is evaluated for each row of the cartesian
# product and the result cast to a numeric value. All rows for which one
# or more of the expressions evaluates to NULL or zero are excluded from
# the result set.
#
do_select_tests e_select-1.5 {
  1 { SELECT * FROM t1, t3 USING (a)   }  {a one 1 b two 2}
  2 { SELECT * FROM t3, t4 USING (a,c) }  {b 2}


} 

# EVIDENCE-OF: R-54046-48600 When comparing values as a result of a
# USING clause, the normal rules for handling affinities, collation
# sequences and NULL values in comparisons apply.
#
# EVIDENCE-OF: R-35466-18578 The column from the dataset on the
# left-hand side of the join operator is considered to be on the
................................................................................
  INSERT INTO t7 VALUES('x', 'ex',  24);
  INSERT INTO t7 VALUES('y', 'why', 25);

  INSERT INTO t8 VALUES('x', 'abc', 24);
  INSERT INTO t8 VALUES('z', 'ghi', 26);
} {}

do_select_tests e_select-1.8 {
  1a "SELECT count(*) FROM t7 JOIN t8 ON (t7.a=t8.a)" {1}


  1b "SELECT count(*) FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" {2}



  2a "SELECT count(*) FROM t7 JOIN t8 USING (a)" {1}


  2b "SELECT count(*) FROM t7 LEFT JOIN t8 USING (a)" {2}

}


# EVIDENCE-OF: R-15607-52988 The added rows contain NULL values in the
# columns that would normally contain values copied from the right-hand
# input dataset.
#
do_select_tests e_select-1.9 {
  1a "SELECT * FROM t7 JOIN t8 ON (t7.a=t8.a)" {x ex 24 x abc 24}


  1b "SELECT * FROM t7 LEFT JOIN t8 ON (t7.a=t8.a)" 
     {x ex 24 x abc 24 y why 25 {} {} {}}


  2a "SELECT * FROM t7 JOIN t8 USING (a)" {x ex 24 abc 24}


  2b "SELECT * FROM t7 LEFT JOIN t8 USING (a)" {x ex 24 abc 24 y why 25 {} {}}

}

# EVIDENCE-OF: R-01809-52134 If the NATURAL keyword is added to any of
# the join-ops, then an implicit USING clause is added to the
# join-constraints. The implicit USING clause contains each of the
# column names that appear in both the left and right-hand input
# datasets.
#

do_select_tests e_select-1-10 {
  1a "SELECT * FROM t7 JOIN t8 USING (a)"        {x ex 24 abc 24}
  1b "SELECT * FROM t7 NATURAL JOIN t8"          {x ex 24 abc 24}


  2a "SELECT * FROM t8 JOIN t7 USING (a)"        {x abc 24 ex 24}
  2b "SELECT * FROM t8 NATURAL JOIN t7"          {x abc 24 ex 24}


  3a "SELECT * FROM t7 LEFT JOIN t8 USING (a)"   {x ex 24 abc 24 y why 25 {} {}}
  3b "SELECT * FROM t7 NATURAL LEFT JOIN t8"     {x ex 24 abc 24 y why 25 {} {}}


  4a "SELECT * FROM t8 LEFT JOIN t7 USING (a)"   {x abc 24 ex 24 z ghi 26 {} {}}
  4b "SELECT * FROM t8 NATURAL LEFT JOIN t7"     {x abc 24 ex 24 z ghi 26 {} {}}


  5a "SELECT * FROM t3 JOIN t4 USING (a,c)"      {b 2}
  5b "SELECT * FROM t3 NATURAL JOIN t4"          {b 2}


  6a "SELECT * FROM t3 LEFT JOIN t4 USING (a,c)" {a 1 b 2}
  6b "SELECT * FROM t3 NATURAL LEFT JOIN t4"     {a 1 b 2}




} 

# EVIDENCE-OF: R-49566-01570 If the left and right-hand input datasets
# feature no common column names, then the NATURAL keyword has no effect
# on the results of the join.
#
do_execsql_test e_select-1.11.0 {
  CREATE TABLE t10(x, y);
  INSERT INTO t10 VALUES(1, 'true');
  INSERT INTO t10 VALUES(0, 'false');
} {}
do_select_tests e_select-1-11 {
  1a "SELECT a, x FROM t1 CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}
  1b "SELECT a, x FROM t1 NATURAL CROSS JOIN t10" {a 1 a 0 b 1 b 0 c 1 c 0}




}

# EVIDENCE-OF: R-39625-59133 A USING or ON clause may not be added to a
# join that specifies the NATURAL keyword.
#
foreach {tn sql} {
  1 {SELECT * FROM t1 NATURAL LEFT JOIN t2 USING (a)}
................................................................................
# expression "*" then all columns in the input data are substituted for
# that one expression.
#
# EVIDENCE-OF: R-43693-30522 If the expression is the alias of a table
# or subquery in the FROM clause followed by ".*" then all columns from
# the named table or subquery are substituted for the single expression.
#
do_select_tests e_select-4.1 {
  1  "SELECT * FROM z1 LIMIT 1"             {51.65 -59.58 belfries}
  2  "SELECT * FROM z1,z2 LIMIT 1"          {51.65 -59.58 belfries {} 21}
  3  "SELECT z1.* FROM z1,z2 LIMIT 1"       {51.65 -59.58 belfries}
  4  "SELECT z2.* FROM z1,z2 LIMIT 1"       {{} 21}
  5  "SELECT z2.*, z1.* FROM z1,z2 LIMIT 1" {{} 21 51.65 -59.58 belfries}

  6  "SELECT count(*), * FROM z1"           {6 63 born -26}
................................................................................

  9  "SELECT *,* FROM z1,z2 LIMIT 1" {        
     51.65 -59.58 belfries {} 21 51.65 -59.58 belfries {} 21
  }
  10 "SELECT z1.*,z1.* FROM z2,z1 LIMIT 1" {        
     51.65 -59.58 belfries 51.65 -59.58 belfries
  }


}

# EVIDENCE-OF: R-61869-22578 It is an error to use a "*" or "alias.*"
# expression in any context other than than a result expression list.
#
# EVIDENCE-OF: R-44324-41166 It is also an error to use a "*" or
# "alias.*" expression in a simple SELECT query that does not have a
................................................................................
  7   "SELECT a, *, b, c FROM z1"                   6
} {
  set ::stmt [sqlite3_prepare_v2 db $select -1 DUMMY]
  do_test e_select-4.3.$tn { sqlite3_column_count $::stmt } $nCol
  sqlite3_finalize $::stmt
}



# In lang_select.html, a non-aggregate query is defined as any simple SELECT
# that has no GROUP BY clause and no aggregate expressions in the result
# expression list. Other queries are aggregate queries. Test cases
# e_select-4.4.* through e_select-4.12.*, inclusive, which test the part of
# simple SELECT that is different for aggregate and non-aggregate queries
# verify (in a way) that these definitions are consistent:
#
# EVIDENCE-OF: R-20637-43463 A simple SELECT statement is an aggregate
# query if it contains either a GROUP BY clause or one or more aggregate
# functions in the result-set.
#
# EVIDENCE-OF: R-23155-55597 Otherwise, if a simple SELECT contains no
# aggregate functions or a GROUP BY clause, it is a non-aggregate query.
#

# EVIDENCE-OF: R-44050-47362 If the SELECT statement is a non-aggregate
# query, then each expression in the result expression list is evaluated
# for each row in the dataset filtered by the WHERE clause.
#





do_select_tests e_select-4.4 {
  1 "SELECT a, b FROM z1"
    {51.65 -59.58 -5 {} -2.2 -23.18 {} 67 -1.04 -32.3 63 born}


  2 "SELECT a IS NULL, b+1, * FROM z1" {

        0 -58.58   51.65 -59.58 belfries
        0 {}       -5 {} 75            
        0 -22.18   -2.2 -23.18 suiters
        1 68       {} 67 quartets    
        0 -31.3    -1.04 -32.3 aspen
        0 1        63 born -26

  }


  3 "SELECT 32*32, d||e FROM z2" {1024 {} 1024 366}

}


# Test cases e_select-4.5.* and e_select-4.6.* together show that:
#
# EVIDENCE-OF: R-51988-01124 The single row of result-set data created
# by evaluating the aggregate and non-aggregate expressions in the
# result-set forms the result of an aggregate query without a GROUP BY
# clause.
#

# EVIDENCE-OF: R-57629-25253 If the SELECT statement is an aggregate
# query without a GROUP BY clause, then each aggregate expression in the
# result-set is evaluated once across the entire dataset.
#
do_select_tests e_select-4.5 {
  1 "SELECT count(a), max(a), count(b), max(b) FROM z1"      {5 63 5 born}
  2 "SELECT count(*), max(1)"                                {1 1}

  3 "SELECT sum(b+1) FROM z1 NATURAL LEFT JOIN z3"           {-43.06}
  4 "SELECT sum(b+2) FROM z1 NATURAL LEFT JOIN z3"           {-38.06}
  5 "SELECT sum(b IS NOT NULL) FROM z1 NATURAL LEFT JOIN z3" {5}


}

# EVIDENCE-OF: R-26684-40576 Each non-aggregate expression in the
# result-set is evaluated once for an arbitrarily selected row of the
# dataset.
#
# EVIDENCE-OF: R-27994-60376 The same arbitrarily selected row is used
................................................................................

  CREATE TABLE a2(one PRIMARY KEY, three);
  INSERT INTO a2 VALUES(1, 1);
  INSERT INTO a2 VALUES(3, 2);
  INSERT INTO a2 VALUES(6, 3);
  INSERT INTO a2 VALUES(10, 4);
} {}
do_select_tests e_select-4.6 {
  1 "SELECT one, two, count(*) FROM a1"                        {4 10 4} 
  2 "SELECT one, two, count(*) FROM a1 WHERE one<3"            {2 3 2} 
  3 "SELECT one, two, count(*) FROM a1 WHERE one>3"            {4 10 1} 
  4 "SELECT *, count(*) FROM a1 JOIN a2"                       {4 10 10 4 16} 
  5 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  6 "SELECT *, sum(three) FROM a1 NATURAL JOIN a2"             {3 6 2 3}
  7 "SELECT group_concat(three, ''), a1.* FROM a1 NATURAL JOIN a2" {12 3 6}


}

# EVIDENCE-OF: R-04486-07266 Or, if the dataset contains zero rows, then
# each non-aggregate expression is evaluated against a row consisting
# entirely of NULL values.
#
do_select_tests e_select-4.7 {
  1  "SELECT one, two, count(*) FROM a1 WHERE 0"           {{} {} 0}
  2  "SELECT sum(two), * FROM a1, a2 WHERE three>5"        {{} {} {} {} {}}
  3  "SELECT max(one) IS NULL, one IS NULL, two IS NULL FROM a1 WHERE two=7" {
    1 1 1
  }


} 

# EVIDENCE-OF: R-64138-28774 An aggregate query without a GROUP BY
# clause always returns exactly one row of data, even if there are zero
# rows of input data.
#
foreach {tn select} {
  8.1  "SELECT count(*) FROM a1"
................................................................................
# EVIDENCE-OF: R-57754-57109 If the SELECT statement is an aggregate
# query with a GROUP BY clause, then each of the expressions specified
# as part of the GROUP BY clause is evaluated for each row of the
# dataset. Each row is then assigned to a "group" based on the results;
# rows for which the results of evaluating the GROUP BY expressions are
# the same are assigned to the same group.
#
#   These tests also show that the following is not untrue:
#
# EVIDENCE-OF: R-25883-55063 The expressions in the GROUP BY clause do
# not have to be expressions that appear in the result.
#
do_select_tests e_select-4.9 {
  1  "SELECT group_concat(one), two FROM b1 GROUP BY two" {
    4,5 f   1 o   7,6   s 3,2 t
  }
  2  "SELECT group_concat(one), sum(one) FROM b1 GROUP BY (one>4)" {
    1,4,3,2 10    5,7,6 18
  }
  3  "SELECT group_concat(one) FROM b1 GROUP BY (two>'o'), one%2" {
    4  1,5    2,6   3,7
  }
  4  "SELECT group_concat(one) FROM b1 GROUP BY (one==2 OR two=='o')" {
    4,3,5,7,6    1,2
  }


}

# EVIDENCE-OF: R-14926-50129 For the purposes of grouping rows, NULL
# values are considered equal.
#
do_select_tests e_select-4.10 {
  1  "SELECT group_concat(y) FROM b2 GROUP BY x" {0,1   3   2,4}
  2  "SELECT count(*) FROM b2 GROUP BY CASE WHEN y<4 THEN NULL ELSE 0 END" {4 1}

} 




# EVIDENCE-OF: R-10470-30318 The usual rules for selecting a collation
# sequence with which to compare text values apply when evaluating
# expressions in a GROUP BY clause.
#
do_select_tests e_select-4.11 {
  1  "SELECT count(*) FROM b3 GROUP BY b"      {1 1 1 1}
  2  "SELECT count(*) FROM b3 GROUP BY a"      {2 2}
  3  "SELECT count(*) FROM b3 GROUP BY +b"     {1 1 1 1}
  4  "SELECT count(*) FROM b3 GROUP BY +a"     {2 2}
  5  "SELECT count(*) FROM b3 GROUP BY b||''"  {1 1 1 1}
  6  "SELECT count(*) FROM b3 GROUP BY a||''"  {1 1 1 1}


}

# EVIDENCE-OF: R-63573-50730 The expressions in a GROUP BY clause may
# not be aggregate expressions.
#
foreach {tn select} {
  12.1  "SELECT * FROM b3 GROUP BY count(*)"
................................................................................
#
# EVIDENCE-OF: R-28262-47447 If a HAVING clause is a non-aggregate
# expression, it is evaluated with respect to an arbitrarily selected
# row from the group.
#
#   Tested by e_select-4.13.2.*
#
#   Tests in this block also show that this is not untrue:
#
# EVIDENCE-OF: R-55403-13450 The HAVING expression may refer to values,
# even aggregate functions, that are not in the result.
#
do_execsql_test e_select-4.13.0 {
  CREATE TABLE c1(up, down);
  INSERT INTO c1 VALUES('x', 1);
  INSERT INTO c1 VALUES('x', 2);
  INSERT INTO c1 VALUES('x', 4);
  INSERT INTO c1 VALUES('x', 8);
  INSERT INTO c1 VALUES('y', 16);
................................................................................
  INSERT INTO c3 VALUES(2,  'helium');
  INSERT INTO c3 VALUES(3,  'lithium');
  INSERT INTO c3 VALUES(4,  'beryllium');
  INSERT INTO c3 VALUES(5,  'boron');
  INSERT INTO c3 VALUES(94, 'plutonium');
} {}

do_select_tests e_select-4.13 {
  1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}

  2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}

  2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}


}

# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
# evaluated once for each group of rows.
#
# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
# expression, it is evaluated across all rows in the group.
#
do_select_tests e_select-4.15 {
  1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
  2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
  3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
  4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
  5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
        {3 4.33 1 2.0}


} 

# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
# arbitrarily chosen row from within the group.
#
# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
# expression in the result-set, then all such expressions are evaluated
# for the same row.
#
do_select_tests e_select-4.15 {
  1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
  2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
  3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
  5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
        {2 5 boron   2 2 helium   1 3 lithium}


} 

# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
# contributes a single row to the set of result rows.
#
# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
# DISTINCT keyword, the number of rows returned by an aggregate query
# with a GROUP BY clause is the same as the number of groups of rows
# produced by applying the GROUP BY and HAVING clauses to the filtered
# input dataset.
#
do_select_tests e_select.4.16 -count {
  1  "SELECT i, j FROM c2 GROUP BY i%2"          2
  2  "SELECT i, j FROM c2 GROUP BY i"            9
  3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4




} 

#-------------------------------------------------------------------------
# The following tests attempt to verify statements made regarding the ALL
# and DISTINCT keywords.
#
drop_all_tables
do_execsql_test e_select-5.1.0 {
................................................................................
  INSERT INTO h3 VALUES(8, '2,4');
  INSERT INTO h3 VALUES(9, '3');
} {}

# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
# follow the SELECT keyword in a simple SELECT statement.
#
do_select_tests e_select-5.1 {
  1   "SELECT ALL a FROM h1"      {1 1 1 4 4 4}
  2   "SELECT DISTINCT a FROM h1" {1 4}
}

# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
# the entire set of result rows are returned by the SELECT.
#
# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
# then the behaviour is as if ALL were specified.
#
................................................................................
# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
# then duplicate rows are removed from the set of result rows before it
# is returned.
#
#   The three testable statements above are tested by e_select-5.2.*,
#   5.3.* and 5.4.* respectively.
#
do_select_tests e_select-5 {
  3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}

  3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
  3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}

  4.1 "SELECT DISTINCT x FROM h2" {four one three two}
  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}


} 

# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
# rows, two NULL values are considered to be equal.
#
do_select_tests e_select-5.5 {
  1  "SELECT DISTINCT d FROM h3" {{} 2 2,3 2,4 3}
}

# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
# sequence to compare text values with apply.
#
do_select_tests e_select-5.6 {
  1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
  2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
  3  "SELECT DISTINCT x FROM h2"                  {four one three two}
  4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
    Four One Three Two four one three two
  }


}

#-------------------------------------------------------------------------
# The following tests - e_select-7.* - test that statements made to do
# with compound SELECT statements are correct.
#

................................................................................
#
drop_all_tables
do_execsql_test e_select-7.1.0 {
  CREATE TABLE j1(a, b, c);
  CREATE TABLE j2(e, f);
  CREATE TABLE j3(g);
} {}
do_select_tests e_select-7.1 -error {
  SELECTs to the left and right of %s do not have the same number of result columns
} {
  1   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  2   "SELECT *    FROM j1    UNION ALL SELECT * FROM j3"    {UNION ALL}
  3   "SELECT a, b FROM j1    UNION ALL SELECT g FROM j3"    {UNION ALL}
  4   "SELECT a, b FROM j1    UNION ALL SELECT * FROM j3,j2" {UNION ALL}
  5   "SELECT *    FROM j3,j2 UNION ALL SELECT a, b FROM j1" {UNION ALL}

  6   "SELECT a, b FROM j1    UNION SELECT g FROM j3"        {UNION}
................................................................................
  15  "SELECT *    FROM j3,j2 INTERSECT SELECT a, b FROM j1" {INTERSECT}

  16  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  17  "SELECT *    FROM j1    EXCEPT SELECT * FROM j3"       {EXCEPT}
  18  "SELECT a, b FROM j1    EXCEPT SELECT g FROM j3"       {EXCEPT}
  19  "SELECT a, b FROM j1    EXCEPT SELECT * FROM j3,j2"    {EXCEPT}
  20  "SELECT *    FROM j3,j2 EXCEPT SELECT a, b FROM j1"    {EXCEPT}





} 

# EVIDENCE-OF: R-01450-11152 As the components of a compound SELECT must
# be simple SELECT statements, they may not contain ORDER BY or LIMIT
# clauses.
# 
foreach {tn select op1 op2} {
  1   "SELECT * FROM j1 ORDER BY a UNION ALL SELECT * FROM j2,j3" 
................................................................................
  INSERT INTO q2 VALUES('beauty', 2);
  INSERT INTO q2 VALUES(-65.91, 4);
  INSERT INTO q2 VALUES('emanating', -16.56);

  INSERT INTO q3 VALUES('beauty', 2);
  INSERT INTO q3 VALUES('beauty', 2);
} {}
do_select_tests e_select-7.4 {
  1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
      {16 legible beauty legible beauty -65.91 emanating}

  2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
      {3 -16.56}

  4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
      {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}


} 

# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
# UNION ALL, except that duplicate rows are removed from the final
# result set.
#
do_select_tests e_select-7.5 {
  1   {SELECT a FROM q1 UNION SELECT d FROM q2}
      {-65.91 16 beauty emanating legible}

  2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
      {16 -87.66 {} x legible 1}

  3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
      {-16.56 3}

  4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
      {-65.91 4 beauty 2 emanating -16.56 legible 1}


} 

# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
# intersection of the results of the left and right SELECTs.
#
do_select_tests e_select-7.6 {
  1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
  2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}


}

# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
# rows returned by the left SELECT that are not also returned by the
# right-hand SELECT.
#
do_select_tests e_select-7.7 {
  1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}

  2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
      {-65.91 4 emanating -16.56 legible 1}


}

# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
# of INTERSECT and EXCEPT operators before the result set is returned.
#
do_select_tests e_select-7.8 {
  0   {SELECT * FROM q3} {beauty 2 beauty 2}

  1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
  2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}


}

# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
# rows for the results of compound SELECT operators, NULL values are
# considered equal to other NULL values and distinct from all non-NULL
# values.
#
db nullvalue null
do_select_tests e_select-7.9 {
  1   {SELECT NULL UNION ALL SELECT NULL} {null null}
  2   {SELECT NULL UNION     SELECT NULL} {null}
  3   {SELECT NULL INTERSECT SELECT NULL} {null}
  4   {SELECT NULL EXCEPT    SELECT NULL} {}

  5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
  6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
................................................................................
  11  {SELECT NULL INTERSECT SELECT 0} {}
  12  {SELECT NULL EXCEPT    SELECT 0} {null}

  13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
  14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
  15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
  16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}


}
db nullvalue {} 

# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
# text values is determined as if the columns of the left and right-hand
# SELECT statements were the left and right-hand operands of the equals
# (=) operator, except that greater precedence is not assigned to a
................................................................................
# collation sequence specified with the postfix COLLATE operator.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
  CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
  INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
} {}
do_select_tests e_select-7.10 {
  1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
  2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
  3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
  4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
  5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}

  6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
  7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
  8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}

  9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}



}

# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
# any values when comparing rows as part of a compound SELECT.
#
drop_all_tables
do_execsql_test e_select-7.10.0 {
................................................................................
  CREATE TABLE w1(a TEXT, b NUMBER);
  CREATE TABLE w2(a, b TEXT);

  INSERT INTO w1 VALUES('1', 4.1);
  INSERT INTO w2 VALUES(1, 4.1);
} {}

do_select_tests e_select-7.11 {
  1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
  2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
  3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
  4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}

  5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
  6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
................................................................................
  7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
  8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}

  9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
  10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
  11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
  12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}


}


# EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
# connected into a compound SELECT, they group from left to right. In
# other words, if "A", "B" and "C" are all simple SELECT statements, (A
# op B op C) is processed as ((A op B) op C).
................................................................................
  INSERT INTO d2 VALUES('lad', 'relenting');
} {}

# EVIDENCE-OF: R-44988-41064 Rows are first sorted based on the results
# of evaluating the left-most expression in the ORDER BY list, then ties
# are broken by evaluating the second left-most expression and so on.
#
do_select_tests e_select-8.1 {
  1  "SELECT * FROM d1 ORDER BY x, y, z" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }


}

# EVIDENCE-OF: R-06617-54588 Each ORDER BY expression may be optionally
# followed by one of the keywords ASC (smaller values are returned
# first) or DESC (larger values are returned first).
#
#   Test cases e_select-8.2.* test the above.
................................................................................
#
# EVIDENCE-OF: R-18705-33393 If neither ASC or DESC are specified, rows
# are sorted in ascending (smaller values first) order by default.
#
#   Test cases e_select-8.3.* test the above. All 8.3 test cases are
#   copies of 8.2 test cases with the explicit "ASC" removed.
#
do_select_tests e_select-8 {
  2.1  "SELECT * FROM d1 ORDER BY x ASC, y ASC, z ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2.2  "SELECT * FROM d1 ORDER BY x DESC, y DESC, z DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
     2 4 93   2 5 -1     1 2 8      1 2 7    
     1 2 3    1 2 -20    1 4  93    1 5 -1   
  }
  3.4 "SELECT * FROM d1 ORDER BY x DESC, y, z" {
     2 4 93   2 5 -1     1 2 -20    1 2 3    
     1 2 7    1 2 8      1 4  93    1 5 -1   
  }


}

# EVIDENCE-OF: R-29779-04281 If the ORDER BY expression is a constant
# integer K then the expression is considered an alias for the K-th
# column of the result set (columns are numbered from left to right
# starting with 1).
#
do_select_tests e_select-8.4 {
  1  "SELECT * FROM d1 ORDER BY 1 ASC, 2 ASC, 3 ASC" {
     1 2 -20    1 2 3    1 2 7    1 2 8    
     1 4  93    1 5 -1   2 4 93   2 5 -1
  }
  2  "SELECT * FROM d1 ORDER BY 1 DESC, 2 DESC, 3 DESC" {
     2 5 -1     2 4 93   1 5 -1   1 4  93    
     1 2 8      1 2 7    1 2 3    1 2 -20    
................................................................................
     3 1     8 1    7 1   -20 1 
     93 1   -1 1   -1 2   93 2
  }
  9  "SELECT z, x FROM d1 ORDER BY 1" {
     -20 1  -1 2   -1 1   3 1     
     7 1     8 1   93 2   93 1   
  }


}

# EVIDENCE-OF: R-63286-51977 If the ORDER BY expression is an identifier
# that corresponds to the alias of one of the output columns, then the
# expression is considered an alias for that column.
#
do_select_tests e_select-8.5 {
  1   "SELECT z+1 AS abc FROM d1 ORDER BY abc" {
    -19 0 0 4 8 9 94 94
  }
  2   "SELECT z+1 AS abc FROM d1 ORDER BY abc DESC" {
    94 94 9 8 4 0 0 -19
  }
  3  "SELECT z AS x, x AS z FROM d1 ORDER BY z" {
    3 1    8 1    7 1    -20 1    93 1    -1 1    -1 2    93 2
  }
  4  "SELECT z AS x, x AS z FROM d1 ORDER BY x" {
    -20 1    -1 2    -1 1    3 1    7 1    8 1    93 2    93 1
  }


}

# EVIDENCE-OF: R-27923-38747 Otherwise, if the ORDER BY expression is
# any other expression, it is evaluated and the the returned value used
# to order the output rows.
#
# EVIDENCE-OF: R-03421-57988 If the SELECT statement is a simple SELECT,
# then an ORDER BY may contain any arbitrary expressions.
#
do_select_tests e_select-8.6 {
  1   "SELECT * FROM d1 ORDER BY x+y+z" {
    1 2 -20    1 5 -1    1 2 3    2 5 -1 
    1 2 7      1 2 8     1 4 93   2 4 93
  }
  2   "SELECT * FROM d1 ORDER BY x*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     1 4 93    2 4 93
  }
  3   "SELECT * FROM d1 ORDER BY y*z" {
    1 2 -20    2 5 -1    1 5 -1    1 2 3 
    1 2 7      1 2 8     2 4 93    1 4 93
  }


}

# EVIDENCE-OF: R-28853-08147 However, if the SELECT is a compound
# SELECT, then ORDER BY expressions that are not aliases to output
# columns must be exactly the same as an expression used as an output
# column.
#
do_select_tests e_select-8.7.1 -error {
  %s ORDER BY term does not match any column in the result set
} {
  1   "SELECT x FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z"        1st
  2   "SELECT x,z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" 2nd



} 

do_select_tests e_select-8.7.2 {
  1   "SELECT x*z FROM d1 UNION ALL SELECT a FROM d2 ORDER BY x*z" {
    -20 -2 -1 3 7 8 93 186 babied barked commercials gently 
    iterate lad pragmatist reemphasizes rejoicing solemnness
  }
  2   "SELECT x, x/z FROM d1 UNION ALL SELECT a,b FROM d2 ORDER BY x, x/z" {
    1 -1 1 0 1 0 1 0 1 0 1 0 2 -2 2 0 
    babied charitableness barked interrupted commercials bathrobe gently
    failings iterate sexton lad relenting pragmatist guarded reemphasizes reply
    rejoicing liabilities solemnness annexed
  }


} 

do_execsql_test e_select-8.8.0 {
  CREATE TABLE d3(a);
  INSERT INTO d3 VALUES('text');
  INSERT INTO d3 VALUES(14.1);
  INSERT INTO d3 VALUES(13);
  INSERT INTO d3 VALUES(X'78787878');
................................................................................
  CREATE TABLE d9(y COLLATE nocase);

  INSERT INTO d8 VALUES('a');
  INSERT INTO d9 VALUES('B');
  INSERT INTO d8 VALUES('c');
  INSERT INTO d9 VALUES('D');
} {}
do_select_tests e_select-8.13 {
  1   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY a
      } {1 2 3 4 5 6}
  2   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
         ORDER BY c
      } {1 2 3 4 5 6}
  3   { SELECT a FROM d5 UNION ALL SELECT c FROM d6 UNION ALL SELECT e FROM d7
................................................................................
  10  { SELECT a, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 2 } 
      {f 2   c 5   4 c   1 f}

  11  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY a+1 } 
      {2 f   5 c   c 5   f 2}
  12  { SELECT a+1, b FROM d5 UNION ALL SELECT b, a+1 FROM d5 ORDER BY 1 } 
      {2 f   5 c   c 5   f 2}
} 




# EVIDENCE-OF: R-39265-04070 If no matching expression can be found in
# the result columns of any constituent SELECT, it is an error.
#
do_select_tests e_select-8.14 -error {
  %s ORDER BY term does not match any column in the result set
} {
  1   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a+1 }          1st
  2   { SELECT a FROM d5 UNION SELECT c FROM d6 ORDER BY a, a+1 }       2nd
  3   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY 'hello' }  1st
  4   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY blah    }  1st
  5   { SELECT * FROM d5 INTERSECT SELECT * FROM d6 ORDER BY c,d,c+d }  3rd
  6   { SELECT * FROM d5 EXCEPT SELECT * FROM d7 ORDER BY 1,2,b,a/b  }  4th



} 

# EVIDENCE-OF: R-03407-11483 Each term of the ORDER BY clause is
# processed separately and may be matched against result columns from
# different SELECT statements in the compound.
# 
do_select_tests e_select-8.15 {
  1  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY a, d }
     {1 e   1 f   4 b   4 c}
  2  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY c-1, b }
     {1 e   1 f   4 b   4 c}
  3  { SELECT a, b FROM d5 UNION ALL SELECT c-1, d FROM d6 ORDER BY 1, 2 }
     {1 e   1 f   4 b   4 c}


} 


#-------------------------------------------------------------------------
# Tests related to statements made about the LIMIT/OFFSET clause.
#
do_execsql_test e_select-9.0 {
  CREATE TABLE f1(a, b);
................................................................................
  INSERT INTO f1 VALUES(1, 'a');
} {}

# EVIDENCE-OF: R-30481-56627 Any scalar expression may be used in the
# LIMIT clause, so long as it evaluates to an integer or a value that
# can be losslessly converted to an integer.
#
do_select_tests e_select-9.1 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT a FROM f1 WHERE b = 'e') } 
     {a b c d e}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.0 } {a b c d e}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' } {a b c d e}


}

# EVIDENCE-OF: R-46155-47219 If the expression evaluates to a NULL value
# or any other value that cannot be losslessly converted to an integer,
# an error is returned.
#


do_select_tests e_select-9.2 -error "datatype mismatch" {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 'hello' } {}
  2  { SELECT b FROM f1 ORDER BY a LIMIT NULL } {}
  3  { SELECT b FROM f1 ORDER BY a LIMIT X'ABCD' } {}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 5.1 } {}
  5  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT group_concat(b) FROM f1) } {}


} 

# EVIDENCE-OF: R-03014-26414 If the LIMIT expression evaluates to a
# negative value, then there is no upper bound on the number of rows
# returned.
#
do_select_tests e_select-9.4 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT -1 } 
     {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}
  2  { SELECT b FROM f1 ORDER BY a LIMIT length('abc')-100 } 
     {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}
  3  { SELECT b FROM f1 ORDER BY a LIMIT (SELECT count(*) FROM f1)/2 - 14 }
     {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}


}

# EVIDENCE-OF: R-33750-29536 Otherwise, the SELECT returns the first N
# rows of its result set only, where N is the value that the LIMIT
# expression evaluates to.
#
do_select_tests e_select-9.5 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 0 } {}
  2  { SELECT b FROM f1 ORDER BY a DESC LIMIT 4 } {z y x w}
  3  { SELECT b FROM f1 ORDER BY a DESC LIMIT 8 } {z y x w v u t s}
  4  { SELECT b FROM f1 ORDER BY a DESC LIMIT '12.0' } {z y x w v u t s r q p o}


}

# EVIDENCE-OF: R-54935-19057 Or, if the SELECT statement would return
# less than N rows without a LIMIT clause, then the entire result set is
# returned.
#
do_select_tests e_select-9.6 {
  1  { SELECT b FROM f1 WHERE a>21 ORDER BY a LIMIT 10 } {v w x y z}
  2  { SELECT count(*) FROM f1 GROUP BY a/5 ORDER BY 1 LIMIT 10 } {2 4 5 5 5 5}


} 


# EVIDENCE-OF: R-24188-24349 The expression attached to the optional
# OFFSET clause that may follow a LIMIT clause must also evaluate to an
# integer, or a value that can be losslessly converted to an integer.
#
foreach {tn select} {
................................................................................

# EVIDENCE-OF: R-20467-43422 If an expression has an OFFSET clause, then
# the first M rows are omitted from the result set returned by the
# SELECT statement and the next N rows are returned, where M and N are
# the values that the OFFSET and LIMIT clauses evaluate to,
# respectively.
#
do_select_tests e_select-9.8 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 5} {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 2+3 OFFSET 10} {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT  (SELECT a FROM f1 WHERE b='j') 
       OFFSET (SELECT a FROM f1 WHERE b='b') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 3.0 } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT '5' OFFSET 0 } {a b c d e}
  6  { SELECT b FROM f1 ORDER BY a LIMIT 0 OFFSET 10 } {}
  7  { SELECT b FROM f1 ORDER BY a LIMIT 3 OFFSET '1'||'5' } {p q r}


}

# EVIDENCE-OF: R-34648-44875 Or, if the SELECT would return less than
# M+N rows if it did not have a LIMIT clause, then the first M rows are
# skipped and the remaining rows (if any) are returned.
#
do_select_tests e_select-9.9 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 10 OFFSET 20} {u v w x y z}
  2  { SELECT a FROM f1 ORDER BY a DESC LIMIT 100 OFFSET 18+4} {4 3 2 1}


}


# EVIDENCE-OF: R-23293-62447 If the OFFSET clause evaluates to a
# negative value, the results are the same as if it had evaluated to
# zero.
#
do_select_tests e_select-9.10 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -1 } {a b c d e}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET -500 } {a b c d e}
  3  { SELECT b FROM f1 ORDER BY a LIMIT 5 OFFSET 0  } {a b c d e}


} 

# EVIDENCE-OF: R-19509-40356 Instead of a separate OFFSET clause, the
# LIMIT clause may specify two scalar expressions separated by a comma.
#
# EVIDENCE-OF: R-33788-46243 In this case, the first expression is used
# as the OFFSET expression and the second as the LIMIT expression.
#
do_select_tests e_select-9.11 {
  1  { SELECT b FROM f1 ORDER BY a LIMIT 5, 10 } {f g h i j k l m n o}
  2  { SELECT b FROM f1 ORDER BY a LIMIT 10, 2+3 } {k l m n o}
  3  { SELECT b FROM f1 ORDER BY a 
       LIMIT (SELECT a FROM f1 WHERE b='b'), (SELECT a FROM f1 WHERE b='j') 
     } {c d e f g h i j k l}
  4  { SELECT b FROM f1 ORDER BY a LIMIT 3.0, '5' } {d e f g h}
  5  { SELECT b FROM f1 ORDER BY a LIMIT 0, '5' } {a b c d e}
................................................................................

  8  { SELECT b FROM f1 ORDER BY a LIMIT 20, 10 } {u v w x y z}
  9  { SELECT a FROM f1 ORDER BY a DESC LIMIT 18+4, 100 } {4 3 2 1}

  10 { SELECT b FROM f1 ORDER BY a LIMIT -1, 5 } {a b c d e}
  11 { SELECT b FROM f1 ORDER BY a LIMIT -500, 5 } {a b c d e}
  12 { SELECT b FROM f1 ORDER BY a LIMIT 0, 5 } {a b c d e}


}


finish_test