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.10 2009/04/02 16:59:47 drh Exp $
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17
18ifcapable !compound {
19  finish_test
20  return
21}
22
23proc test_transform {testname sql1 sql2 results} {
24  set ::vdbe1 [list]
25  set ::vdbe2 [list]
26  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
27  db eval "explain $sql2" { lappend ::vdbe2 $opcode }
28
29  do_test $testname.transform {
30    set ::vdbe1
31  } $::vdbe2
32
33  set ::sql1 $sql1
34  do_test $testname.sql1 {
35    execsql $::sql1
36  } $results
37
38  set ::sql2 $sql2
39  do_test $testname.sql2 {
40    execsql $::sql2
41  } $results
42}
43
44do_test selectB-1.1 {
45  execsql {
46    CREATE TABLE t1(a, b, c);
47    CREATE TABLE t2(d, e, f);
48
49    INSERT INTO t1 VALUES( 2,  4,  6);
50    INSERT INTO t1 VALUES( 8, 10, 12);
51    INSERT INTO t1 VALUES(14, 16, 18);
52
53    INSERT INTO t2 VALUES(3,   6,  9);
54    INSERT INTO t2 VALUES(12, 15, 18);
55    INSERT INTO t2 VALUES(21, 24, 27);
56  }
57} {}
58
59for {set ii 1} {$ii <= 2} {incr ii} {
60
61  if {$ii == 2} {
62    do_test selectB-2.1 {
63      execsql {
64        CREATE INDEX i1 ON t1(a);
65        CREATE INDEX i2 ON t2(d);
66      }
67    } {}
68  }
69
70  test_transform selectB-$ii.2 {
71    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
72  } {
73    SELECT a FROM t1 UNION ALL SELECT d FROM t2
74  } {2 8 14 3 12 21}
75  
76  test_transform selectB-$ii.3 {
77    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
78  } {
79    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
80  } {2 3 8 12 14 21}
81  
82  test_transform selectB-$ii.4 {
83    SELECT * FROM 
84      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
85    WHERE a>10 ORDER BY 1
86  } {
87    SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
88  } {12 14 21}
89  
90  test_transform selectB-$ii.5 {
91    SELECT * FROM 
92      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
93    WHERE a>10 ORDER BY a
94  } {
95    SELECT a FROM t1 WHERE a>10 
96      UNION ALL 
97    SELECT d FROM t2 WHERE d>10 
98    ORDER BY a
99  } {12 14 21}
100  
101  test_transform selectB-$ii.6 {
102    SELECT * FROM 
103      (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 
104    WHERE a>10 ORDER BY a
105  } {
106    SELECT a FROM t1 WHERE a>10
107      UNION ALL 
108    SELECT d FROM t2 WHERE d>12 AND d>10
109    ORDER BY a
110  } {14 21}
111  
112  test_transform selectB-$ii.7 {
113    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
114    LIMIT 2
115  } {
116    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
117  } {2 3}
118  
119  test_transform selectB-$ii.8 {
120    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
121    LIMIT 2 OFFSET 3
122  } {
123    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
124  } {12 14}
125
126  test_transform selectB-$ii.9 {
127    SELECT * FROM (
128      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
129    ) 
130  } {
131    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
132  } {2 8 14 3 12 21 6 12 18}
133  
134  test_transform selectB-$ii.10 {
135    SELECT * FROM (
136      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
137    ) ORDER BY 1
138  } {
139    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
140    ORDER BY 1
141  } {2 3 6 8 12 12 14 18 21}
142  
143  test_transform selectB-$ii.11 {
144    SELECT * FROM (
145      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
146    ) WHERE a>=10 ORDER BY 1 LIMIT 3
147  } {
148    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
149    UNION ALL SELECT c FROM t1 WHERE c>=10
150    ORDER BY 1 LIMIT 3
151  } {12 12 14}
152
153  test_transform selectB-$ii.12 {
154    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
155  } {
156    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
157  } {2 8}
158
159  # An ORDER BY in a compound subqueries defeats flattening.  Ticket #3773
160  # test_transform selectB-$ii.13 {
161  #   SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
162  # } {
163  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
164  # } {2 3 8 12 14 21}
165  # 
166  # test_transform selectB-$ii.14 {
167  #  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
168  # } {
169  #  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
170  # } {21 14 12 8 3 2}
171  #
172  # test_transform selectB-$ii.14 {
173  #   SELECT * FROM (
174  #     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
175  #   ) LIMIT 2 OFFSET 2
176  # } {
177  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
178  #    LIMIT 2 OFFSET 2
179  # } {12 8}
180  #
181  # test_transform selectB-$ii.15 {
182  #   SELECT * FROM (
183  #     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
184  #  )
185  # } {
186  #   SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
187  # } {2 4 3 6 8 10 12 15 14 16 21 24}
188}
189
190do_test selectB-3.0 {
191  execsql {
192    DROP INDEX i1;
193    DROP INDEX i2;
194  }
195} {}
196
197for {set ii 3} {$ii <= 4} {incr ii} {
198
199  if {$ii == 4} {
200    do_test selectB-4.0 {
201      execsql {
202        CREATE INDEX i1 ON t1(a);
203        CREATE INDEX i2 ON t1(b);
204        CREATE INDEX i3 ON t1(c);
205        CREATE INDEX i4 ON t2(d);
206        CREATE INDEX i5 ON t2(e);
207        CREATE INDEX i6 ON t2(f);
208      }
209    } {}
210  }
211
212  do_test selectB-$ii.1 {
213    execsql {
214      SELECT DISTINCT * FROM 
215        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
216      ORDER BY 1;
217    }
218  } {6 12 15 18 24}
219  
220  do_test selectB-$ii.2 {
221    execsql {
222      SELECT c, count(*) FROM 
223        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
224      GROUP BY c ORDER BY 1;
225    }
226  } {6 2 12 1 15 1 18 1 24 1}
227  do_test selectB-$ii.3 {
228    execsql {
229      SELECT c, count(*) FROM 
230        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
231      GROUP BY c HAVING count(*)>1;
232    }
233  } {6 2}
234  do_test selectB-$ii.4 {
235    execsql {
236      SELECT t4.c, t3.a FROM 
237        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
238      WHERE t3.a=14
239      ORDER BY 1
240    }
241  } {6 14 6 14 12 14 15 14 18 14 24 14}
242  
243  do_test selectB-$ii.5 {
244    execsql {
245      SELECT d FROM t2 
246      EXCEPT 
247      SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
248    }
249  } {}
250  do_test selectB-$ii.6 {
251    execsql {
252      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
253      EXCEPT 
254      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
255    }
256  } {}
257  do_test selectB-$ii.7 {
258    execsql {
259      SELECT c FROM t1
260      EXCEPT 
261      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
262    }
263  } {12}
264  do_test selectB-$ii.8 {
265    execsql {
266      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
267      EXCEPT 
268      SELECT c FROM t1
269    }
270  } {9 15 24 27}
271  do_test selectB-$ii.9 {
272    execsql {
273      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
274      EXCEPT 
275      SELECT c FROM t1
276      ORDER BY c DESC
277    }
278  } {27 24 15 9}
279  
280  do_test selectB-$ii.10 {
281    execsql {
282      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
283      UNION 
284      SELECT c FROM t1
285      ORDER BY c DESC
286    }
287  } {27 24 18 15 12 9 6}
288  do_test selectB-$ii.11 {
289    execsql {
290      SELECT c FROM t1
291      UNION 
292      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
293      ORDER BY c
294    }
295  } {6 9 12 15 18 24 27}
296  do_test selectB-$ii.12 {
297    execsql {
298      SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
299      ORDER BY c
300    }
301  } {6 9 12 15 18 18 24 27}
302  do_test selectB-$ii.13 {
303    execsql {
304      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
305      UNION 
306      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
307      ORDER BY 1
308    }
309  } {6 9 15 18 24 27}
310  
311  do_test selectB-$ii.14 {
312    execsql {
313      SELECT c FROM t1
314      INTERSECT 
315      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
316      ORDER BY 1
317    }
318  } {6 18}
319  do_test selectB-$ii.15 {
320    execsql {
321      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
322      INTERSECT 
323      SELECT c FROM t1
324      ORDER BY 1
325    }
326  } {6 18}
327  do_test selectB-$ii.16 {
328    execsql {
329      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
330      INTERSECT 
331      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
332      ORDER BY 1
333    }
334  } {6 9 15 18 24 27}
335
336  do_test selectB-$ii.17 {
337    execsql {
338      SELECT * FROM (
339        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
340      ) LIMIT 2
341    }
342  } {2 8}
343
344  do_test selectB-$ii.18 {
345    execsql {
346      SELECT * FROM (
347        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
348      ) LIMIT 2
349    }
350  } {14 3}
351
352  do_test selectB-$ii.19 {
353    execsql {
354      SELECT * FROM (
355        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
356      )
357    }
358  } {0 1 0 1}
359
360  do_test selectB-$ii.20 {
361    execsql {
362      SELECT DISTINCT * FROM (
363        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
364      )
365    }
366  } {0 1}
367
368  do_test selectB-$ii.21 {
369    execsql {
370      SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
371    }
372  } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
373
374  do_test selectB-$ii.21 {
375    execsql {
376      SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
377    }
378  } {3 12 21 345}
379}
380
381finish_test
382