Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add tests for compound SELECT statements to e_select.test. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
8b0b009f292e0b52bffb08662aef67d9 |
| User & Date: | dan 2010-09-13 18:58:33.000 |
Context
|
2010-09-13
| ||
| 19:03 | Fix walmode.test so that all tests pass with SQLITE_TEMP_STORE=3. (check-in: 6ba6e59a9d user: dan tags: trunk) | |
| 18:58 | Add tests for compound SELECT statements to e_select.test. (check-in: 8b0b009f29 user: dan tags: trunk) | |
| 14:38 | Fix a couple of test files so that they work with DEFAULT_AUTOVACUUM. (check-in: 8cb39306f4 user: dan tags: trunk) | |
Changes
Changes to test/e_select.test.
| ︙ | ︙ | |||
1626 1627 1628 1629 1630 1631 1632 1633 1634 |
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]
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 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 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 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 |
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.
#
# EVIDENCE-OF: R-39368-64333 In a compound SELECT, all the constituent
# SELECTs must return the same number of result columns.
#
# All the other tests in this section use compound SELECTs created
# using component SELECTs that do return the same number of columns.
# So the tests here just show that it is an error to attempt otherwise.
#
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}
7 "SELECT * FROM j1 UNION SELECT * FROM j3" {UNION}
8 "SELECT a, b FROM j1 UNION SELECT g FROM j3" {UNION}
9 "SELECT a, b FROM j1 UNION SELECT * FROM j3,j2" {UNION}
10 "SELECT * FROM j3,j2 UNION SELECT a, b FROM j1" {UNION}
11 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT}
12 "SELECT * FROM j1 INTERSECT SELECT * FROM j3" {INTERSECT}
13 "SELECT a, b FROM j1 INTERSECT SELECT g FROM j3" {INTERSECT}
14 "SELECT a, b FROM j1 INTERSECT SELECT * FROM j3,j2" {INTERSECT}
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"
{ORDER BY} {UNION ALL}
2 "SELECT count(*) FROM j1 ORDER BY 1 UNION ALL SELECT max(e) FROM j2"
{ORDER BY} {UNION ALL}
3 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION ALL SELECT *,* FROM j2"
{ORDER BY} {UNION ALL}
4 "SELECT * FROM j1 LIMIT 10 UNION ALL SELECT * FROM j2,j3"
LIMIT {UNION ALL}
5 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION ALL SELECT * FROM j2,j3"
LIMIT {UNION ALL}
6 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION ALL SELECT g FROM j2,j3"
LIMIT {UNION ALL}
7 "SELECT * FROM j1 ORDER BY a UNION SELECT * FROM j2,j3"
{ORDER BY} {UNION}
8 "SELECT count(*) FROM j1 ORDER BY 1 UNION SELECT max(e) FROM j2"
{ORDER BY} {UNION}
9 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 UNION SELECT *,* FROM j2"
{ORDER BY} {UNION}
10 "SELECT * FROM j1 LIMIT 10 UNION SELECT * FROM j2,j3"
LIMIT {UNION}
11 "SELECT * FROM j1 LIMIT 10 OFFSET 5 UNION SELECT * FROM j2,j3"
LIMIT {UNION}
12 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) UNION SELECT g FROM j2,j3"
LIMIT {UNION}
13 "SELECT * FROM j1 ORDER BY a EXCEPT SELECT * FROM j2,j3"
{ORDER BY} {EXCEPT}
14 "SELECT count(*) FROM j1 ORDER BY 1 EXCEPT SELECT max(e) FROM j2"
{ORDER BY} {EXCEPT}
15 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 EXCEPT SELECT *,* FROM j2"
{ORDER BY} {EXCEPT}
16 "SELECT * FROM j1 LIMIT 10 EXCEPT SELECT * FROM j2,j3"
LIMIT {EXCEPT}
17 "SELECT * FROM j1 LIMIT 10 OFFSET 5 EXCEPT SELECT * FROM j2,j3"
LIMIT {EXCEPT}
18 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) EXCEPT SELECT g FROM j2,j3"
LIMIT {EXCEPT}
19 "SELECT * FROM j1 ORDER BY a INTERSECT SELECT * FROM j2,j3"
{ORDER BY} {INTERSECT}
20 "SELECT count(*) FROM j1 ORDER BY 1 INTERSECT SELECT max(e) FROM j2"
{ORDER BY} {INTERSECT}
21 "SELECT count(*), * FROM j1 ORDER BY 1,2,3 INTERSECT SELECT *,* FROM j2"
{ORDER BY} {INTERSECT}
22 "SELECT * FROM j1 LIMIT 10 INTERSECT SELECT * FROM j2,j3"
LIMIT {INTERSECT}
23 "SELECT * FROM j1 LIMIT 10 OFFSET 5 INTERSECT SELECT * FROM j2,j3"
LIMIT {INTERSECT}
24 "SELECT a FROM j1 LIMIT (SELECT e FROM j2) INTERSECT SELECT g FROM j2,j3"
LIMIT {INTERSECT}
} {
set err "$op1 clause should come after $op2 not before"
do_catchsql_test e_select-7.2.$tn $select [list 1 $err]
}
# EVIDENCE-OF: R-22874-32655 ORDER BY and LIMIT clauses may only occur
# at the end of the entire compound SELECT.
#
foreach {tn select} {
1 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 ORDER BY a"
2 "SELECT count(*) FROM j1 UNION ALL SELECT max(e) FROM j2 ORDER BY 1"
3 "SELECT count(*), * FROM j1 UNION ALL SELECT *,* FROM j2 ORDER BY 1,2,3"
4 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10"
5 "SELECT * FROM j1 UNION ALL SELECT * FROM j2,j3 LIMIT 10 OFFSET 5"
6 "SELECT a FROM j1 UNION ALL SELECT g FROM j2,j3 LIMIT (SELECT 10)"
7 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 ORDER BY a"
8 "SELECT count(*) FROM j1 UNION SELECT max(e) FROM j2 ORDER BY 1"
9 "SELECT count(*), * FROM j1 UNION SELECT *,* FROM j2 ORDER BY 1,2,3"
10 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10"
11 "SELECT * FROM j1 UNION SELECT * FROM j2,j3 LIMIT 10 OFFSET 5"
12 "SELECT a FROM j1 UNION SELECT g FROM j2,j3 LIMIT (SELECT 10)"
13 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 ORDER BY a"
14 "SELECT count(*) FROM j1 EXCEPT SELECT max(e) FROM j2 ORDER BY 1"
15 "SELECT count(*), * FROM j1 EXCEPT SELECT *,* FROM j2 ORDER BY 1,2,3"
16 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10"
17 "SELECT * FROM j1 EXCEPT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5"
18 "SELECT a FROM j1 EXCEPT SELECT g FROM j2,j3 LIMIT (SELECT 10)"
19 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 ORDER BY a"
20 "SELECT count(*) FROM j1 INTERSECT SELECT max(e) FROM j2 ORDER BY 1"
21 "SELECT count(*), * FROM j1 INTERSECT SELECT *,* FROM j2 ORDER BY 1,2,3"
22 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10"
23 "SELECT * FROM j1 INTERSECT SELECT * FROM j2,j3 LIMIT 10 OFFSET 5"
24 "SELECT a FROM j1 INTERSECT SELECT g FROM j2,j3 LIMIT (SELECT 10)"
} {
do_test e_select-7.3.$tn { catch {execsql $select} msg } 0
}
# EVIDENCE-OF: R-08531-36543 A compound SELECT created using UNION ALL
# 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}
1b "(3) UNION (1,2) INTERSECT (1)" {1}
2a "(1,2) UNION (3) UNION ALL (1)" {1 2 3 1}
2b "(1) UNION ALL (3) UNION (1,2)" {1 2 3}
3a "(1,2) UNION (3) EXCEPT (1)" {2 3}
3b "(1,2) EXCEPT (3) UNION (1)" {1 2}
4a "(1,2) INTERSECT (1) UNION ALL (3)" {1 3}
4b "(3) UNION (1,2) INTERSECT (1)" {1}
5a "(1,2) INTERSECT (2) EXCEPT (2)" {}
5b "(2,3) EXCEPT (2) INTERSECT (2)" {}
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
|