xref: /sqlite-3.40.0/test/selectB.test (revision eca7e01a) 
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.3 2008/07/01 16:05:26 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}
149
150
151do_test selectB-2.1 {
152  execsql {
153    SELECT DISTINCT * FROM
154      (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
155    ORDER BY 1;
156  }
157} {6 12 15 18 24}
158
159do_test selectB-2.2 {
160  execsql {
161    SELECT c, count(*) FROM
162      (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
163    GROUP BY c ORDER BY 1;
164  }
165} {6 2 12 1 15 1 18 1 24 1}
166do_test selectB-2.3 {
167  execsql {
168    SELECT c, count(*) FROM
169      (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
170    GROUP BY c HAVING count(*)>1;
171  }
172} {6 2}
173do_test selectB-2.4 {
174  execsql {
175    SELECT t4.c, t3.a FROM
176      (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
177    WHERE t3.a=14
178    ORDER BY 1
179  }
180} {6 14 6 14 12 14 15 14 18 14 24 14}
181
182do_test selectB-2.5 {
183  execsql {
184    SELECT d FROM t2
185    EXCEPT
186    SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
187  }
188} {}
189
190do_test selectB-2.6 {
191  execsql {
192    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
193    EXCEPT
194    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
195  }
196} {}
197
198finish_test
199
200