/ Check-in [a0f01eba]
Login

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

Overview
Comment:Further tests for compound SELECT statements.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: a0f01ebab9bd42fca9ce5d97f8cbf44afd803f4e
User & Date: dan 2010-09-14 18:56:53
Context
2010-09-15
11:42
Add trivial test case to make sure shared-cache mode does not automatically turn on exclusive-locking mode. check-in: a0ab3902 user: dan tags: trunk
2010-09-14
18:56
Further tests for compound SELECT statements. check-in: a0f01eba user: dan tags: trunk
18:23
Clarify the documentation to better explain when an automatic re-prepare can be induced by rebinding parameters. Add evidence marks to the automatic re-prepare logic. check-in: 3e11f515 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

  1780   1780   # operator returns all the rows from the SELECT to the left of the UNION
  1781   1781   # ALL operator, and all the rows from the SELECT to the right of it.
  1782   1782   #
  1783   1783   drop_all_tables
  1784   1784   do_execsql_test e_select-7.4.0 {
  1785   1785     CREATE TABLE q1(a TEXT, b INTEGER, c);
  1786   1786     CREATE TABLE q2(d NUMBER, e BLOB);
         1787  +  CREATE TABLE q3(f REAL, g);
  1787   1788   
  1788   1789     INSERT INTO q1 VALUES(16, -87.66, NULL);
  1789   1790     INSERT INTO q1 VALUES('legible', 94, -42.47);
  1790   1791     INSERT INTO q1 VALUES('beauty', 36, NULL);
  1791   1792   
  1792   1793     INSERT INTO q2 VALUES('legible', 1);
  1793   1794     INSERT INTO q2 VALUES('beauty', 2);
  1794   1795     INSERT INTO q2 VALUES(-65.91, 4);
  1795   1796     INSERT INTO q2 VALUES('emanating', -16.56);
  1796         -  INSERT INTO q2 VALUES(NULL, -22.82);
  1797         -  INSERT INTO q2 VALUES(7.48, 'example');
         1797  +
         1798  +  INSERT INTO q3 VALUES('beauty', 2);
         1799  +  INSERT INTO q3 VALUES('beauty', 2);
  1798   1800   } {}
  1799   1801   foreach {tn select res} {
  1800         -  1   "SELECT a FROM q1 UNION ALL SELECT d FROM q2" 
  1801         -      {16 legible beauty legible beauty -65.91 emanating {} 7.48}
         1802  +  1   {SELECT a FROM q1 UNION ALL SELECT d FROM q2}
         1803  +      {16 legible beauty legible beauty -65.91 emanating}
  1802   1804   
  1803         -  2   "SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1" 
         1805  +  2   {SELECT * FROM q1 WHERE a=16 UNION ALL SELECT 'x', * FROM q2 WHERE oid=1}
  1804   1806         {16 -87.66 {} x legible 1}
  1805   1807   
  1806         -  3   "SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2" {3 -22.82}
         1808  +  3   {SELECT count(*) FROM q1 UNION ALL SELECT min(e) FROM q2} 
         1809  +      {3 -16.56}
         1810  +
         1811  +  4   {SELECT * FROM q2 UNION ALL SELECT * FROM q3} 
         1812  +      {legible 1 beauty 2 -65.91 4 emanating -16.56 beauty 2 beauty 2}
  1807   1813   } {
  1808   1814     do_execsql_test e_select-7.4.$tn $select [list {*}$res]
  1809   1815   }
         1816  +
         1817  +# EVIDENCE-OF: R-20560-39162 The UNION operator works the same way as
         1818  +# UNION ALL, except that duplicate rows are removed from the final
         1819  +# result set.
         1820  +#
         1821  +foreach {tn select res} {
         1822  +  1   {SELECT a FROM q1 UNION SELECT d FROM q2}
         1823  +      {-65.91 16 beauty emanating legible}
         1824  +
         1825  +  2   {SELECT * FROM q1 WHERE a=16 UNION SELECT 'x', * FROM q2 WHERE oid=1}
         1826  +      {16 -87.66 {} x legible 1}
         1827  +
         1828  +  3   {SELECT count(*) FROM q1 UNION SELECT min(e) FROM q2} 
         1829  +      {-16.56 3}
         1830  +
         1831  +  4   {SELECT * FROM q2 UNION SELECT * FROM q3} 
         1832  +      {-65.91 4 beauty 2 emanating -16.56 legible 1}
         1833  +} {
         1834  +  do_execsql_test e_select-7.5.$tn $select [list {*}$res]
         1835  +}
         1836  +
         1837  +# EVIDENCE-OF: R-45764-31737 The INTERSECT operator returns the
         1838  +# intersection of the results of the left and right SELECTs.
         1839  +#
         1840  +foreach {tn select res} {
         1841  +  1   {SELECT a FROM q1 INTERSECT SELECT d FROM q2} {beauty legible}
         1842  +  2   {SELECT * FROM q2 INTERSECT SELECT * FROM q3} {beauty 2}
         1843  +} {
         1844  +  do_execsql_test e_select-7.6.$tn $select [list {*}$res]
         1845  +}
         1846  +
         1847  +# EVIDENCE-OF: R-25787-28949 The EXCEPT operator returns the subset of
         1848  +# rows returned by the left SELECT that are not also returned by the
         1849  +# right-hand SELECT.
         1850  +#
         1851  +foreach {tn select res} {
         1852  +  1   {SELECT a FROM q1 EXCEPT SELECT d FROM q2} {16}
         1853  +
         1854  +  2   {SELECT * FROM q2 EXCEPT SELECT * FROM q3} 
         1855  +      {-65.91 4 emanating -16.56 legible 1}
         1856  +} {
         1857  +  do_execsql_test e_select-7.7.$tn $select [list {*}$res]
         1858  +}
         1859  +
         1860  +# EVIDENCE-OF: R-40729-56447 Duplicate rows are removed from the results
         1861  +# of INTERSECT and EXCEPT operators before the result set is returned.
         1862  +#
         1863  +foreach {tn select res} {
         1864  +  0   {SELECT * FROM q3} {beauty 2 beauty 2}
         1865  +
         1866  +  1   {SELECT * FROM q3 INTERSECT SELECT * FROM q3} {beauty 2}
         1867  +  2   {SELECT * FROM q3 EXCEPT SELECT a,b FROM q1}  {beauty 2}
         1868  +} {
         1869  +  do_execsql_test e_select-7.8.$tn $select [list {*}$res]
         1870  +}
         1871  +
         1872  +# EVIDENCE-OF: R-46765-43362 For the purposes of determining duplicate
         1873  +# rows for the results of compound SELECT operators, NULL values are
         1874  +# considered equal to other NULL values and distinct from all non-NULL
         1875  +# values.
         1876  +#
         1877  +db nullvalue null
         1878  +foreach {tn select res} {
         1879  +  1   {SELECT NULL UNION ALL SELECT NULL} {null null}
         1880  +  2   {SELECT NULL UNION     SELECT NULL} {null}
         1881  +  3   {SELECT NULL INTERSECT SELECT NULL} {null}
         1882  +  4   {SELECT NULL EXCEPT    SELECT NULL} {}
         1883  +
         1884  +  5   {SELECT NULL UNION ALL SELECT 'ab'} {null ab}
         1885  +  6   {SELECT NULL UNION     SELECT 'ab'} {null ab}
         1886  +  7   {SELECT NULL INTERSECT SELECT 'ab'} {}
         1887  +  8   {SELECT NULL EXCEPT    SELECT 'ab'} {null}
         1888  +
         1889  +  9   {SELECT NULL UNION ALL SELECT 0} {null 0}
         1890  +  10  {SELECT NULL UNION     SELECT 0} {null 0}
         1891  +  11  {SELECT NULL INTERSECT SELECT 0} {}
         1892  +  12  {SELECT NULL EXCEPT    SELECT 0} {null}
         1893  +
         1894  +  13  {SELECT c FROM q1 UNION ALL SELECT g FROM q3} {null -42.47 null 2 2}
         1895  +  14  {SELECT c FROM q1 UNION     SELECT g FROM q3} {null -42.47 2}
         1896  +  15  {SELECT c FROM q1 INTERSECT SELECT g FROM q3} {}
         1897  +  16  {SELECT c FROM q1 EXCEPT    SELECT g FROM q3} {null -42.47}
         1898  +} {
         1899  +  do_execsql_test e_select-7.9.$tn $select [list {*}$res]
         1900  +}
         1901  +db nullvalue {} 
         1902  +
         1903  +# EVIDENCE-OF: R-51232-50224 The collation sequence used to compare two
         1904  +# text values is determined as if the columns of the left and right-hand
         1905  +# SELECT statements were the left and right-hand operands of the equals
         1906  +# (=) operator, except that greater precedence is not assigned to a
         1907  +# collation sequence specified with the postfix COLLATE operator.
         1908  +#
         1909  +drop_all_tables
         1910  +do_execsql_test e_select-7.10.0 {
         1911  +  CREATE TABLE y1(a COLLATE nocase, b COLLATE binary, c);
         1912  +  INSERT INTO y1 VALUES('Abc', 'abc', 'aBC');
         1913  +} {}
         1914  +foreach {tn select res} {
         1915  +  1   {SELECT 'abc'                UNION SELECT 'ABC'} {ABC abc}
         1916  +  2   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC'} {ABC}
         1917  +  3   {SELECT 'abc'                UNION SELECT 'ABC' COLLATE nocase} {ABC}
         1918  +  4   {SELECT 'abc' COLLATE binary UNION SELECT 'ABC' COLLATE nocase} {ABC abc}
         1919  +  5   {SELECT 'abc' COLLATE nocase UNION SELECT 'ABC' COLLATE binary} {ABC}
         1920  +
         1921  +  6   {SELECT a FROM y1 UNION SELECT b FROM y1}                {abc}
         1922  +  7   {SELECT b FROM y1 UNION SELECT a FROM y1}                {Abc abc}
         1923  +  8   {SELECT a FROM y1 UNION SELECT c FROM y1}                {aBC}
         1924  +
         1925  +  9   {SELECT a FROM y1 UNION SELECT c COLLATE binary FROM y1} {aBC}
         1926  +
         1927  +} {
         1928  +  do_execsql_test e_select-7.10.$tn $select [list {*}$res]
         1929  +}
         1930  +
         1931  +# EVIDENCE-OF: R-32706-07403 No affinity transformations are applied to
         1932  +# any values when comparing rows as part of a compound SELECT.
         1933  +#
         1934  +drop_all_tables
         1935  +do_execsql_test e_select-7.10.0 {
         1936  +  CREATE TABLE w1(a TEXT, b NUMBER);
         1937  +  CREATE TABLE w2(a, b TEXT);
         1938  +
         1939  +  INSERT INTO w1 VALUES('1', 4.1);
         1940  +  INSERT INTO w2 VALUES(1, 4.1);
         1941  +} {}
         1942  +
         1943  +foreach {tn select res} {
         1944  +  1  { SELECT a FROM w1 UNION SELECT a FROM w2 } {1 1}
         1945  +  2  { SELECT a FROM w2 UNION SELECT a FROM w1 } {1 1}
         1946  +  3  { SELECT b FROM w1 UNION SELECT b FROM w2 } {4.1 4.1}
         1947  +  4  { SELECT b FROM w2 UNION SELECT b FROM w1 } {4.1 4.1}
         1948  +
         1949  +  5  { SELECT a FROM w1 INTERSECT SELECT a FROM w2 } {}
         1950  +  6  { SELECT a FROM w2 INTERSECT SELECT a FROM w1 } {}
         1951  +  7  { SELECT b FROM w1 INTERSECT SELECT b FROM w2 } {}
         1952  +  8  { SELECT b FROM w2 INTERSECT SELECT b FROM w1 } {}
         1953  +
         1954  +  9  { SELECT a FROM w1 EXCEPT SELECT a FROM w2 } {1}
         1955  +  10 { SELECT a FROM w2 EXCEPT SELECT a FROM w1 } {1}
         1956  +  11 { SELECT b FROM w1 EXCEPT SELECT b FROM w2 } {4.1}
         1957  +  12 { SELECT b FROM w2 EXCEPT SELECT b FROM w1 } {4.1}
         1958  +} {
         1959  +  do_execsql_test e_select-7.11.$tn $select [list {*}$res]
         1960  +}
  1810   1961   
  1811   1962   
  1812   1963   # EVIDENCE-OF: R-32562-20566 When three or more simple SELECTs are
  1813   1964   # connected into a compound SELECT, they group from left to right. In
  1814   1965   # other words, if "A", "B" and "C" are all simple SELECT statements, (A
  1815   1966   # op B op C) is processed as ((A op B) op C).
  1816   1967   #
  1817         -#   e_select-7.X.1: Precedence of UNION vs. INTERSECT 
  1818         -#   e_select-7.X.2: Precedence of UNION vs. UNION ALL 
  1819         -#   e_select-7.X.3: Precedence of UNION vs. EXCEPT
  1820         -#   e_select-7.X.4: Precedence of INTERSECT vs. UNION ALL 
  1821         -#   e_select-7.X.5: Precedence of INTERSECT vs. EXCEPT
  1822         -#   e_select-7.X.6: Precedence of UNION ALL vs. EXCEPT
  1823         -#   e_select-7.X.7: Check that "a EXCEPT b EXCEPT c" is processed as 
         1968  +#   e_select-7.12.1: Precedence of UNION vs. INTERSECT 
         1969  +#   e_select-7.12.2: Precedence of UNION vs. UNION ALL 
         1970  +#   e_select-7.12.3: Precedence of UNION vs. EXCEPT
         1971  +#   e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL 
         1972  +#   e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT
         1973  +#   e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT
         1974  +#   e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as 
  1824   1975   #                   "(a EXCEPT b) EXCEPT c".
  1825   1976   #
  1826   1977   # The INTERSECT and EXCEPT operations are mutually commutative. So
  1827         -# the e_select-7.X.5 test cases do not prove very much.
         1978  +# the e_select-7.12.5 test cases do not prove very much.
  1828   1979   #
  1829   1980   drop_all_tables
  1830         -do_execsql_test e_select-7.X.0 {
         1981  +do_execsql_test e_select-7.12.0 {
  1831   1982     CREATE TABLE t1(x);
  1832   1983     INSERT INTO t1 VALUES(1);
  1833   1984     INSERT INTO t1 VALUES(2);
  1834   1985     INSERT INTO t1 VALUES(3);
  1835   1986   } {}
  1836   1987   foreach {tn select res} {
  1837   1988     1a "(1,2) INTERSECT (1)   UNION     (3)"   {1 3}
................................................................................
  1851   2002   
  1852   2003     6a "(2)   UNION ALL (2)   EXCEPT    (2)"   {}
  1853   2004     6b "(2)   EXCEPT    (2)   UNION ALL (2)"   {2}
  1854   2005   
  1855   2006     7  "(2,3) EXCEPT    (2)   EXCEPT    (3)"   {}
  1856   2007   } {
  1857   2008     set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select]
  1858         -  do_execsql_test e_select-7.X.$tn $select [list {*}$res]
         2009  +  do_execsql_test e_select-7.12.$tn $select [list {*}$res]
  1859   2010   }
  1860   2011   
  1861   2012   finish_test