xref: /sqlite-3.40.0/test/selectB.test (revision 5e7ad508) 
1# 2008 June 24
2#
3# The author disclaims copyright to this source code.  In place of
4# a legal notice, here is a blessing:
5#
6#    May you do good and not evil.
7#    May you find forgiveness for yourself and forgive others.
8#    May you share freely, never taking more than you give.
9#
10#***********************************************************************
11# This file implements regression tests for SQLite library.
12#
13# $Id: selectB.test,v 1.4 2008/07/01 17:39:28 danielk1977 Exp $
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18proc test_transform {testname sql1 sql2 results} {
19  set ::vdbe1 [list]
20  set ::vdbe2 [list]
21  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
22  db eval "explain $sql2" { lappend ::vdbe2 $opcode }
23
24  do_test $testname.transform {
25    set ::vdbe1
26  } $::vdbe2
27
28  set ::sql1 $sql1
29  do_test $testname.sql1 {
30    execsql $::sql1
31  } $results
32
33  set ::sql2 $sql2
34  do_test $testname.sql2 {
35    execsql $::sql2
36  } $results
37}
38
39do_test selectB-1.1 {
40  execsql {
41    CREATE TABLE t1(a, b, c);
42    CREATE TABLE t2(d, e, f);
43
44    INSERT INTO t1 VALUES( 2,  4,  6);
45    INSERT INTO t1 VALUES( 8, 10, 12);
46    INSERT INTO t1 VALUES(14, 16, 18);
47
48    INSERT INTO t2 VALUES(3,   6,  9);
49    INSERT INTO t2 VALUES(12, 15, 18);
50    INSERT INTO t2 VALUES(21, 24, 27);
51  }
52} {}
53
54for {set ii 1} {$ii <= 2} {incr ii} {
55
56  if {$ii == 2} {
57    do_test selectB-2.1 {
58      execsql {
59        CREATE INDEX i1 ON t1(a);
60        CREATE INDEX i2 ON t2(d);
61      }
62    } {}
63  }
64
65  test_transform selectB-$ii.2 {
66    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
67  } {
68    SELECT a FROM t1 UNION ALL SELECT d FROM t2
69  } {2 8 14 3 12 21}
70
71  test_transform selectB-$ii.3 {
72    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
73  } {
74    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
75  } {2 3 8 12 14 21}
76
77  test_transform selectB-$ii.4 {
78    SELECT * FROM
79      (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
80    WHERE a>10 ORDER BY 1
81  } {
82    SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
83  } {12 14 21}
84
85  test_transform selectB-$ii.5 {
86    SELECT * FROM
87      (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
88    WHERE a>10 ORDER BY a
89  } {
90    SELECT a FROM t1 WHERE a>10
91      UNION ALL
92    SELECT d FROM t2 WHERE d>10
93    ORDER BY a
94  } {12 14 21}
95
96  test_transform selectB-$ii.6 {
97    SELECT * FROM
98      (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
99    WHERE a>10 ORDER BY a
100  } {
101    SELECT a FROM t1 WHERE a>10
102      UNION ALL
103    SELECT d FROM t2 WHERE d>12 AND d>10
104    ORDER BY a
105  } {14 21}
106
107  test_transform selectB-$ii.7 {
108    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
109    LIMIT 2
110  } {
111    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
112  } {2 3}
113
114  test_transform selectB-$ii.8 {
115    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
116    LIMIT 2 OFFSET 3
117  } {
118    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
119  } {12 14}
120
121  test_transform selectB-$ii.9 {
122    SELECT * FROM (
123      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
124    )
125  } {
126    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
127  } {2 8 14 3 12 21 6 12 18}
128
129  test_transform selectB-$ii.10 {
130    SELECT * FROM (
131      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
132    ) ORDER BY 1
133  } {
134    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
135    ORDER BY 1
136  } {2 3 6 8 12 12 14 18 21}
137
138  test_transform selectB-$ii.11 {
139    SELECT * FROM (
140      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
141    ) WHERE a>=10 ORDER BY 1 LIMIT 3
142  } {
143    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
144    UNION ALL SELECT c FROM t1 WHERE c>=10
145    ORDER BY 1 LIMIT 3
146  } {12 12 14}
147}
148
149do_test selectB-3.0 {
150  execsql {
151    DROP INDEX i1;
152    DROP INDEX i2;
153  }
154} {}
155
156for {set ii 3} {$ii <= 4} {incr ii} {
157
158  if {$ii == 4} {
159    do_test selectB-4.0 {
160      execsql {
161        CREATE INDEX i1 ON t1(a);
162        CREATE INDEX i2 ON t1(b);
163        CREATE INDEX i3 ON t1(c);
164        CREATE INDEX i4 ON t2(d);
165        CREATE INDEX i5 ON t2(e);
166        CREATE INDEX i6 ON t2(f);
167      }
168    } {}
169  }
170
171  do_test selectB-$ii.1 {
172    execsql {
173      SELECT DISTINCT * FROM
174        (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
175      ORDER BY 1;
176    }
177  } {6 12 15 18 24}
178
179  do_test selectB-$ii.2 {
180    execsql {
181      SELECT c, count(*) FROM
182        (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
183      GROUP BY c ORDER BY 1;
184    }
185  } {6 2 12 1 15 1 18 1 24 1}
186  do_test selectB-$ii.3 {
187    execsql {
188      SELECT c, count(*) FROM
189        (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
190      GROUP BY c HAVING count(*)>1;
191    }
192  } {6 2}
193  do_test selectB-$ii.4 {
194    execsql {
195      SELECT t4.c, t3.a FROM
196        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
197      WHERE t3.a=14
198      ORDER BY 1
199    }
200  } {6 14 6 14 12 14 15 14 18 14 24 14}
201
202  do_test selectB-$ii.5 {
203    execsql {
204      SELECT d FROM t2
205      EXCEPT
206      SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
207    }
208  } {}
209  do_test selectB-$ii.6 {
210    execsql {
211      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
212      EXCEPT
213      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
214    }
215  } {}
216  do_test selectB-$ii.7 {
217    execsql {
218      SELECT c FROM t1
219      EXCEPT
220      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
221    }
222  } {12}
223  do_test selectB-$ii.8 {
224    execsql {
225      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
226      EXCEPT
227      SELECT c FROM t1
228    }
229  } {9 15 24 27}
230  do_test selectB-$ii.9 {
231    execsql {
232      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
233      EXCEPT
234      SELECT c FROM t1
235      ORDER BY c DESC
236    }
237  } {27 24 15 9}
238
239  do_test selectB-$ii.10 {
240    execsql {
241      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
242      UNION
243      SELECT c FROM t1
244      ORDER BY c DESC
245    }
246  } {27 24 18 15 12 9 6}
247  do_test selectB-$ii.11 {
248    execsql {
249      SELECT c FROM t1
250      UNION
251      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
252      ORDER BY c
253    }
254  } {6 9 12 15 18 24 27}
255  do_test selectB-$ii.12 {
256    execsql {
257      SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
258      ORDER BY c
259    }
260  } {6 9 12 15 18 18 24 27}
261  do_test selectB-$ii.13 {
262    execsql {
263      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
264      UNION
265      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
266      ORDER BY 1
267    }
268  } {6 9 15 18 24 27}
269
270  do_test selectB-$ii.14 {
271    execsql {
272      SELECT c FROM t1
273      INTERSECT
274      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
275      ORDER BY 1
276    }
277  } {6 18}
278  do_test selectB-$ii.15 {
279    execsql {
280      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
281      INTERSECT
282      SELECT c FROM t1
283      ORDER BY 1
284    }
285  } {6 18}
286  do_test selectB-$ii.16 {
287    execsql {
288      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
289      INTERSECT
290      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
291      ORDER BY 1
292    }
293  } {6 9 15 18 24 27}
294}
295
296finish_test
297
298