Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / selectB.test
blob05ec9c6bdb0f5fc96b13444b612354f80086a3eb
1 # 2008 June 24
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. 
13 # $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
18 ifcapable !compound {
19   finish_test
20   return
23 proc 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 }
29   do_test $testname.transform {
30     set ::vdbe1
31   } $::vdbe2
33   set ::sql1 $sql1
34   do_test $testname.sql1 {
35     execsql $::sql1
36   } $results
38   set ::sql2 $sql2
39   do_test $testname.sql2 {
40     execsql $::sql2
41   } $results
44 do_test selectB-1.1 {
45   execsql {
46     CREATE TABLE t1(a, b, c);
47     CREATE TABLE t2(d, e, f);
49     INSERT INTO t1 VALUES( 2,  4,  6);
50     INSERT INTO t1 VALUES( 8, 10, 12);
51     INSERT INTO t1 VALUES(14, 16, 18);
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 } {}
59 for {set ii 1} {$ii <= 2} {incr ii} {
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   }
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}
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}
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}
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}
190 do_test selectB-3.0 {
191   execsql {
192     DROP INDEX i1;
193     DROP INDEX i2;
194   }
195 } {}
197 for {set ii 3} {$ii <= 6} {incr ii} {
199   switch $ii {
200     4 {
201       optimization_control db query-flattener off
202     }
203     5 {
204       optimization_control db query-flattener on
205       do_test selectB-5.0 {
206         execsql {
207           CREATE INDEX i1 ON t1(a);
208           CREATE INDEX i2 ON t1(b);
209           CREATE INDEX i3 ON t1(c);
210           CREATE INDEX i4 ON t2(d);
211           CREATE INDEX i5 ON t2(e);
212           CREATE INDEX i6 ON t2(f);
213         }
214       } {}
215     }
216     6 {
217       optimization_control db query-flattener off
218     }
219   }
221   do_test selectB-$ii.1 {
222     execsql {
223       SELECT DISTINCT * FROM 
224         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
225       ORDER BY 1;
226     }
227   } {6 12 15 18 24}
228   
229   do_test selectB-$ii.2 {
230     execsql {
231       SELECT c, count(*) FROM 
232         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
233       GROUP BY c ORDER BY 1;
234     }
235   } {6 2 12 1 15 1 18 1 24 1}
236   do_test selectB-$ii.3 {
237     execsql {
238       SELECT c, count(*) FROM 
239         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
240       GROUP BY c HAVING count(*)>1;
241     }
242   } {6 2}
243   do_test selectB-$ii.4 {
244     execsql {
245       SELECT t4.c, t3.a FROM 
246         (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
247       WHERE t3.a=14
248       ORDER BY 1
249     }
250   } {6 14 6 14 12 14 15 14 18 14 24 14}
251   
252   do_test selectB-$ii.5 {
253     execsql {
254       SELECT d FROM t2 
255       EXCEPT 
256       SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
257     }
258   } {}
259   do_test selectB-$ii.6 {
260     execsql {
261       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
262       EXCEPT 
263       SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
264     }
265   } {}
266   do_test selectB-$ii.7 {
267     execsql {
268       SELECT c FROM t1
269       EXCEPT 
270       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
271     }
272   } {12}
273   do_test selectB-$ii.8 {
274     execsql {
275       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
276       EXCEPT 
277       SELECT c FROM t1
278     }
279   } {9 15 24 27}
280   do_test selectB-$ii.9 {
281     execsql {
282       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
283       EXCEPT 
284       SELECT c FROM t1
285       ORDER BY c DESC
286     }
287   } {27 24 15 9}
288   
289   do_test selectB-$ii.10 {
290     execsql {
291       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
292       UNION 
293       SELECT c FROM t1
294       ORDER BY c DESC
295     }
296   } {27 24 18 15 12 9 6}
297   do_test selectB-$ii.11 {
298     execsql {
299       SELECT c FROM t1
300       UNION 
301       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
302       ORDER BY c
303     }
304   } {6 9 12 15 18 24 27}
305   do_test selectB-$ii.12 {
306     execsql {
307       SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
308       ORDER BY c
309     }
310   } {6 9 12 15 18 18 24 27}
311   do_test selectB-$ii.13 {
312     execsql {
313       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
314       UNION 
315       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
316       ORDER BY 1
317     }
318   } {6 9 15 18 24 27}
319   
320   do_test selectB-$ii.14 {
321     execsql {
322       SELECT c FROM t1
323       INTERSECT 
324       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
325       ORDER BY 1
326     }
327   } {6 18}
328   do_test selectB-$ii.15 {
329     execsql {
330       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
331       INTERSECT 
332       SELECT c FROM t1
333       ORDER BY 1
334     }
335   } {6 18}
336   do_test selectB-$ii.16 {
337     execsql {
338       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
339       INTERSECT 
340       SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
341       ORDER BY 1
342     }
343   } {6 9 15 18 24 27}
345   do_test selectB-$ii.17 {
346     execsql {
347       SELECT * FROM (
348         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
349       ) LIMIT 2
350     }
351   } {2 8}
353   do_test selectB-$ii.18 {
354     execsql {
355       SELECT * FROM (
356         SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
357       ) LIMIT 2
358     }
359   } {14 3}
361   do_test selectB-$ii.19 {
362     execsql {
363       SELECT * FROM (
364         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
365       )
366     }
367   } {0 1 1 0}
369   do_test selectB-$ii.20 {
370     execsql {
371       SELECT DISTINCT * FROM (
372         SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
373       )
374     }
375   } {0 1}
377   do_test selectB-$ii.21 {
378     execsql {
379       SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
380     }
381   } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
383   do_test selectB-$ii.22 {
384     execsql {
385       SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
386     }
387   } {3 12 21 345}
389   do_test selectB-$ii.23 {
390     execsql {
391       SELECT x, y FROM (
392         SELECT a AS x, b AS y FROM t1
393         UNION ALL
394         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
395         UNION ALL
396         SELECT a*100, b*100 FROM t1
397       ) ORDER BY 1;
398     }
399   } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}
401   do_test selectB-$ii.24 {
402     execsql {
403       SELECT x, y FROM (
404         SELECT a AS x, b AS y FROM t1
405         UNION ALL
406         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
407         UNION ALL
408         SELECT a*100, b*100 FROM t1
409       ) ORDER BY 1;
410     }
411   } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}
413   do_test selectB-$ii.25 {
414     execsql {
415       SELECT x+y FROM (
416         SELECT a AS x, b AS y FROM t1
417         UNION ALL
418         SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
419         UNION ALL
420         SELECT a*100, b*100 FROM t1
421       ) WHERE y+x NOT NULL ORDER BY 1;
422     }
423   } {6 18 30 260.2 600 1800 3000}
426 finish_test