/ Check-in [a0f01eba]
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: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 Unified Diffs Ignore Whitespace Patch

Changes to test/e_select.test.

1780
1781
1782
1783
1784
1785
1786

1787
1788
1789
1790
1791
1792
1793
1794
1795

1796
1797

1798
1799
1800
1801
1802
1803
1804
1805
1806




1807
1808
1809
1810



1811














































































































































1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
....
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
# operator returns all the rows from the SELECT to the left of the UNION
# ALL operator, and all the rows from the SELECT to the right of it.
#
drop_all_tables
do_execsql_test e_select-7.4.0 {
  CREATE TABLE q1(a TEXT, b INTEGER, c);
  CREATE TABLE q2(d NUMBER, e BLOB);


  INSERT INTO q1 VALUES(16, -87.66, NULL);
  INSERT INTO q1 VALUES('legible', 94, -42.47);
  INSERT INTO q1 VALUES('beauty', 36, NULL);

  INSERT INTO q2 VALUES('legible', 1);
  INSERT INTO q2 VALUES('beauty', 2);
  INSERT INTO q2 VALUES(-65.91, 4);
  INSERT INTO q2 VALUES('emanating', -16.56);

  INSERT INTO q2 VALUES(NULL, -22.82);
  INSERT INTO q2 VALUES(7.48, 'example');

} {}
foreach {tn select res} {
  1   "SELECT a FROM q1 UNION ALL SELECT d FROM q2" 
      {16 legible beauty legible beauty -65.91 emanating {} 7.48}

  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 -22.82}




} {
  do_execsql_test e_select-7.4.$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).
#
#   e_select-7.X.1: Precedence of UNION vs. INTERSECT 
#   e_select-7.X.2: Precedence of UNION vs. UNION ALL 
#   e_select-7.X.3: Precedence of UNION vs. EXCEPT
#   e_select-7.X.4: Precedence of INTERSECT vs. UNION ALL 
#   e_select-7.X.5: Precedence of INTERSECT vs. EXCEPT
#   e_select-7.X.6: Precedence of UNION ALL vs. EXCEPT
#   e_select-7.X.7: Check that "a EXCEPT b EXCEPT c" is processed as 
#                   "(a EXCEPT b) EXCEPT c".
#
# The INTERSECT and EXCEPT operations are mutually commutative. So
# the e_select-7.X.5 test cases do not prove very much.
#
drop_all_tables
do_execsql_test e_select-7.X.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
} {}
foreach {tn select res} {
  1a "(1,2) INTERSECT (1)   UNION     (3)"   {1 3}
................................................................................

  6a "(2)   UNION ALL (2)   EXCEPT    (2)"   {}
  6b "(2)   EXCEPT    (2)   UNION ALL (2)"   {2}

  7  "(2,3) EXCEPT    (2)   EXCEPT    (3)"   {}
} {
  set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select]
  do_execsql_test e_select-7.X.$tn $select [list {*}$res]
}

finish_test







>









>
|
<
>


|
|

|


|
>
>
>
>




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





|
|
|
|
|
|
|



|


|







 







|



1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798

1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
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
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
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
....
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
# operator returns all the rows from the SELECT to the left of the UNION
# ALL operator, and all the rows from the SELECT to the right of it.
#
drop_all_tables
do_execsql_test e_select-7.4.0 {
  CREATE TABLE q1(a TEXT, b INTEGER, c);
  CREATE TABLE q2(d NUMBER, e BLOB);
  CREATE TABLE q3(f REAL, g);

  INSERT INTO q1 VALUES(16, -87.66, NULL);
  INSERT INTO q1 VALUES('legible', 94, -42.47);
  INSERT INTO q1 VALUES('beauty', 36, NULL);

  INSERT INTO q2 VALUES('legible', 1);
  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}
  7   {SELECT NULL INTERSECT SELECT 'ab'} {}
  8   {SELECT NULL EXCEPT    SELECT 'ab'} {null}

  9   {SELECT NULL UNION ALL SELECT 0} {null 0}
  10  {SELECT NULL UNION     SELECT 0} {null 0}
  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).
#
#   e_select-7.12.1: Precedence of UNION vs. INTERSECT 
#   e_select-7.12.2: Precedence of UNION vs. UNION ALL 
#   e_select-7.12.3: Precedence of UNION vs. EXCEPT
#   e_select-7.12.4: Precedence of INTERSECT vs. UNION ALL 
#   e_select-7.12.5: Precedence of INTERSECT vs. EXCEPT
#   e_select-7.12.6: Precedence of UNION ALL vs. EXCEPT
#   e_select-7.12.7: Check that "a EXCEPT b EXCEPT c" is processed as 
#                   "(a EXCEPT b) EXCEPT c".
#
# The INTERSECT and EXCEPT operations are mutually commutative. So
# the e_select-7.12.5 test cases do not prove very much.
#
drop_all_tables
do_execsql_test e_select-7.12.0 {
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
} {}
foreach {tn select res} {
  1a "(1,2) INTERSECT (1)   UNION     (3)"   {1 3}
................................................................................

  6a "(2)   UNION ALL (2)   EXCEPT    (2)"   {}
  6b "(2)   EXCEPT    (2)   UNION ALL (2)"   {2}

  7  "(2,3) EXCEPT    (2)   EXCEPT    (3)"   {}
} {
  set select [string map {( {SELECT x FROM t1 WHERE x IN (}} $select]
  do_execsql_test e_select-7.12.$tn $select [list {*}$res]
}

finish_test