/ Check-in [43a99d9a]
Login

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

Overview
Comment:Add tests for "DISTINCT" and "ALL" to e_select.test.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 43a99d9a88b1c3a6978e79ca21d49de6be2c9def
User & Date: dan 2010-09-11 17:37:52
Context
2010-09-13
11:29
Alter some configurations in releasetest.tcl. Print out the wall-clock time taken for each test after it is executed. check-in: a91c5af3 user: dan tags: trunk
2010-09-11
17:37
Add tests for "DISTINCT" and "ALL" to e_select.test. check-in: 43a99d9a user: dan tags: trunk
16:25
Fix a couple of incorrect evidence marks on malloc(). check-in: f9b5c5cb user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

  1451   1451     CREATE TABLE c1(up, down);
  1452   1452     INSERT INTO c1 VALUES('x', 1);
  1453   1453     INSERT INTO c1 VALUES('x', 2);
  1454   1454     INSERT INTO c1 VALUES('x', 4);
  1455   1455     INSERT INTO c1 VALUES('x', 8);
  1456   1456     INSERT INTO c1 VALUES('y', 16);
  1457   1457     INSERT INTO c1 VALUES('y', 32);
         1458  +
         1459  +  CREATE TABLE c2(i, j);
         1460  +  INSERT INTO c2 VALUES(1, 0);
         1461  +  INSERT INTO c2 VALUES(2, 1);
         1462  +  INSERT INTO c2 VALUES(3, 3);
         1463  +  INSERT INTO c2 VALUES(4, 6);
         1464  +  INSERT INTO c2 VALUES(5, 10);
         1465  +  INSERT INTO c2 VALUES(6, 15);
         1466  +  INSERT INTO c2 VALUES(7, 21);
         1467  +  INSERT INTO c2 VALUES(8, 28);
         1468  +  INSERT INTO c2 VALUES(9, 36);
         1469  +
         1470  +  CREATE TABLE c3(i PRIMARY KEY, k TEXT);
         1471  +  INSERT INTO c3 VALUES(1,  'hydrogen');
         1472  +  INSERT INTO c3 VALUES(2,  'helium');
         1473  +  INSERT INTO c3 VALUES(3,  'lithium');
         1474  +  INSERT INTO c3 VALUES(4,  'beryllium');
         1475  +  INSERT INTO c3 VALUES(5,  'boron');
         1476  +  INSERT INTO c3 VALUES(94, 'plutonium');
  1458   1477   } {}
  1459   1478   
  1460   1479   foreach {tn select res} {
  1461   1480     13.1.1  "SELECT up FROM c1 GROUP BY up HAVING count(*)>3" {x}
  1462   1481     13.1.2  "SELECT up FROM c1 GROUP BY up HAVING sum(down)>16" {y}
  1463   1482     13.1.3  "SELECT up FROM c1 GROUP BY up HAVING sum(down)<16" {x}
  1464         -  13.1.3  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
         1483  +  13.1.4  "SELECT up||down FROM c1 GROUP BY (down<5) HAVING max(down)<10" {x4}
  1465   1484   
  1466   1485     13.2.1  "SELECT up FROM c1 GROUP BY up HAVING down>10" {y}
  1467   1486     13.2.2  "SELECT up FROM c1 GROUP BY up HAVING up='y'"  {y}
         1487  +
         1488  +  13.2.3  "SELECT i, j FROM c2 GROUP BY i>4 HAVING i>6"  {9 36}
         1489  +} {
         1490  +  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1491  +}
         1492  +
         1493  +# EVIDENCE-OF: R-23927-54081 Each expression in the result-set is then
         1494  +# evaluated once for each group of rows.
         1495  +#
         1496  +# EVIDENCE-OF: R-53735-47017 If the expression is an aggregate
         1497  +# expression, it is evaluated across all rows in the group.
         1498  +#
         1499  +foreach {tn select res} {
         1500  +  14.1  "SELECT sum(down) FROM c1 GROUP BY up" {15 48}
         1501  +  14.2  "SELECT sum(j), max(j) FROM c2 GROUP BY (i%3)"     {54 36 27 21 39 28}
         1502  +  14.3  "SELECT sum(j), max(j) FROM c2 GROUP BY (j%2)"     {80 36 40 21}
         1503  +  14.4  "SELECT 1+sum(j), max(j)+1 FROM c2 GROUP BY (j%2)" {81 37 41 22}
         1504  +  14.5  "SELECT count(*), round(avg(i),2) FROM c1, c2 ON (i=down) GROUP BY j%2"
         1505  +        {3 4.33 1 2.0}
         1506  +} {
         1507  +  do_execsql_test e_select-4.$tn $select [list {*}$res]
         1508  +}
         1509  +
         1510  +# EVIDENCE-OF: R-62913-19830 Otherwise, it is evaluated against a single
         1511  +# arbitrarily chosen row from within the group.
         1512  +#
         1513  +# EVIDENCE-OF: R-53924-08809 If there is more than one non-aggregate
         1514  +# expression in the result-set, then all such expressions are evaluated
         1515  +# for the same row.
         1516  +#
         1517  +foreach {tn select res} {
         1518  +  15.1  "SELECT i, j FROM c2 GROUP BY i%2"             {8 28   9 36}
         1519  +  15.2  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j<30" {8 28}
         1520  +  15.3  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
         1521  +  15.4  "SELECT i, j FROM c2 GROUP BY i%2 HAVING j>30" {9 36}
         1522  +  15.5  "SELECT count(*), i, k FROM c2 NATURAL JOIN c3 GROUP BY substr(k, 1, 1)"
         1523  +        {2 5 boron   2 2 helium   1 3 lithium}
  1468   1524   } {
  1469   1525     do_execsql_test e_select-4.$tn $select [list {*}$res]
  1470   1526   }
  1471   1527   
         1528  +# EVIDENCE-OF: R-19334-12811 Each group of input dataset rows
         1529  +# contributes a single row to the set of result rows.
         1530  +#
         1531  +# EVIDENCE-OF: R-02223-49279 Subject to filtering associated with the
         1532  +# DISTINCT keyword, the number of rows returned by an aggregate query
         1533  +# with a GROUP BY clause is the same as the number of groups of rows
         1534  +# produced by applying the GROUP BY and HAVING clauses to the filtered
         1535  +# input dataset.
         1536  +#
         1537  +foreach {tn select nRow} {
         1538  +  16.1  "SELECT i, j FROM c2 GROUP BY i%2"          2
         1539  +  16.2  "SELECT i, j FROM c2 GROUP BY i"            9
         1540  +  16.3  "SELECT i, j FROM c2 GROUP BY i HAVING i<5" 4
         1541  +} {
         1542  +  set rows 0
         1543  +  db eval $select {incr rows}
         1544  +  do_test e_select-4.$tn [list set rows] $nRow
         1545  +}
         1546  +
         1547  +#-------------------------------------------------------------------------
         1548  +# The following tests attempt to verify statements made regarding the ALL
         1549  +# and DISTINCT keywords.
         1550  +#
         1551  +drop_all_tables
         1552  +do_execsql_test e_select-5.1.0 {
         1553  +  CREATE TABLE h1(a, b);
         1554  +  INSERT INTO h1 VALUES(1, 'one');
         1555  +  INSERT INTO h1 VALUES(1, 'I');
         1556  +  INSERT INTO h1 VALUES(1, 'i');
         1557  +  INSERT INTO h1 VALUES(4, 'four');
         1558  +  INSERT INTO h1 VALUES(4, 'IV');
         1559  +  INSERT INTO h1 VALUES(4, 'iv');
         1560  +
         1561  +  CREATE TABLE h2(x COLLATE nocase);
         1562  +  INSERT INTO h2 VALUES('One');
         1563  +  INSERT INTO h2 VALUES('Two');
         1564  +  INSERT INTO h2 VALUES('Three');
         1565  +  INSERT INTO h2 VALUES('Four');
         1566  +  INSERT INTO h2 VALUES('one');
         1567  +  INSERT INTO h2 VALUES('two');
         1568  +  INSERT INTO h2 VALUES('three');
         1569  +  INSERT INTO h2 VALUES('four');
         1570  +
         1571  +  CREATE TABLE h3(c, d);
         1572  +  INSERT INTO h3 VALUES(1, NULL);
         1573  +  INSERT INTO h3 VALUES(2, NULL);
         1574  +  INSERT INTO h3 VALUES(3, NULL);
         1575  +  INSERT INTO h3 VALUES(4, '2');
         1576  +  INSERT INTO h3 VALUES(5, NULL);
         1577  +  INSERT INTO h3 VALUES(6, '2,3');
         1578  +  INSERT INTO h3 VALUES(7, NULL);
         1579  +  INSERT INTO h3 VALUES(8, '2,4');
         1580  +  INSERT INTO h3 VALUES(9, '3');
         1581  +} {}
         1582  +
         1583  +# EVIDENCE-OF: R-60770-10612 One of the ALL or DISTINCT keywords may
         1584  +# follow the SELECT keyword in a simple SELECT statement.
         1585  +#
         1586  +do_execsql_test e_select-5.1.1 { SELECT ALL a FROM h1      } {1 1 1 4 4 4}
         1587  +do_execsql_test e_select-5.1.2 { SELECT DISTINCT a FROM h1 } {1 4}
         1588  +
         1589  +# EVIDENCE-OF: R-08861-34280 If the simple SELECT is a SELECT ALL, then
         1590  +# the entire set of result rows are returned by the SELECT.
         1591  +#
         1592  +# EVIDENCE-OF: R-47911-02086 If neither ALL or DISTINCT are present,
         1593  +# then the behaviour is as if ALL were specified.
         1594  +#
         1595  +# EVIDENCE-OF: R-14442-41305 If the simple SELECT is a SELECT DISTINCT,
         1596  +# then duplicate rows are removed from the set of result rows before it
         1597  +# is returned.
         1598  +#
         1599  +#   The three testable statements above are tested by e_select-5.2.*,
         1600  +#   5.3.* and 5.4.* respectively.
         1601  +#
         1602  +foreach {tn select res} {
         1603  +  3.1 "SELECT ALL x FROM h2" {One Two Three Four one two three four}
         1604  +  3.2 "SELECT ALL x FROM h1, h2 ON (x=b)" {One one Four four}
         1605  +
         1606  +  3.1 "SELECT x FROM h2" {One Two Three Four one two three four}
         1607  +  3.2 "SELECT x FROM h1, h2 ON (x=b)" {One one Four four}
         1608  +
         1609  +  4.1 "SELECT DISTINCT x FROM h2" {four one three two}
         1610  +  4.2 "SELECT DISTINCT x FROM h1, h2 ON (x=b)" {four one}
         1611  +} {
         1612  +  do_execsql_test e_select-5.$tn $select [list {*}$res]
         1613  +}
         1614  +
         1615  +# EVIDENCE-OF: R-02054-15343 For the purposes of detecting duplicate
         1616  +# rows, two NULL values are considered to be equal.
         1617  +#
         1618  +do_execsql_test e_select-5.5.1 { SELECT DISTINCT d FROM h3 } {{} 2 2,3 2,4 3}
  1472   1619   
         1620  +# EVIDENCE-OF: R-58359-52112 The normal rules for selecting a collation
         1621  +# sequence to compare text values with apply.
         1622  +#
         1623  +foreach {tn select res} {
         1624  +  6.1  "SELECT DISTINCT b FROM h1"                  {I IV four i iv one}
         1625  +  6.2  "SELECT DISTINCT b COLLATE nocase FROM h1"   {four i iv one}
         1626  +  6.3  "SELECT DISTINCT x FROM h2"                  {four one three two}
         1627  +  6.4  "SELECT DISTINCT x COLLATE binary FROM h2"   {
         1628  +    Four One Three Two four one three two
         1629  +  }
         1630  +} {
         1631  +  do_execsql_test e_select-5.$tn $select [list {*}$res]
         1632  +}
  1473   1633   
  1474   1634   finish_test
  1475         -
  1476         -