xref: /sqlite-3.40.0/test/tkt-38cb5df375.test (revision 2f56da3f) 
1# 2010 October 6
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. Specifically,
12# it tests that ticket [38cb5df375078d3f9711482d2a1615d09f6b3f33] has
13# been resolved.
14#
15
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18
19ifcapable !compound {
20  finish_test
21  return
22}
23
24do_test tkt-38cb5df375.0 {
25  execsql {
26    CREATE TABLE t1(a);
27    INSERT INTO t1 VALUES(1);
28    INSERT INTO t1 VALUES(2);
29    INSERT INTO t1 SELECT a+2 FROM t1;
30    INSERT INTO t1 SELECT a+4 FROM t1;
31  }
32} {}
33
34foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
35  do_test tkt-38cb5df375.1.$ii {
36    execsql {
37      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
38      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
39      LIMIT $::ii;
40    }
41  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
42}
43foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
44  do_test tkt-38cb5df375.2.$ii {
45    execsql {
46      SELECT 9 FROM (SELECT * FROM t1)
47      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
48      LIMIT $::ii;
49    }
50  } [lrange {9 9 9 9 9 9 9 9 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
51}
52foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
53  do_test tkt-38cb5df375.3.$ii {
54    execsql {
55      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
56      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
57      LIMIT $::ii;
58    }
59  } [lrange {1 2 3 4 5 6 7 8 1 2 3 4 5 6 7 8} 0 [expr {$ii-1}]]
60}
61foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
62  do_test tkt-38cb5df375.4.$ii {
63    execsql {
64      SELECT 0 FROM (SELECT * FROM t1)
65      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
66      LIMIT $::ii;
67    }
68  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
69}
70foreach ii {1 2 3 4} {
71  do_test tkt-38cb5df375.5.$ii {
72    execsql {
73      SELECT 0 FROM (SELECT * FROM t1)
74      UNION SELECT 9 FROM (SELECT a FROM t1)
75      LIMIT $::ii;
76    }
77  } [lrange {0 9} 0 [expr {$ii-1}]]
78}
79
80foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
81  do_test tkt-38cb5df375.11.$ii {
82    execsql {
83      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
84      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
85      LIMIT $::ii;
86    }
87  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
88}
89foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
90  do_test tkt-38cb5df375.12.$ii {
91    execsql {
92      SELECT 9 FROM (SELECT * FROM t1)
93      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
94      LIMIT $::ii;
95    }
96  } [lrange {9 9 9 9 9 9 9 9 1 2 3} 0 [expr {$ii-1}]]
97}
98foreach ii {1 2 3 4 5 6} {
99  do_test tkt-38cb5df375.13.$ii {
100    execsql {
101      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
102      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
103      LIMIT $::ii;
104    }
105  } [lrange {1 2 3 1 2 3} 0 [expr {$ii-1}]]
106}
107foreach ii {1 2 3 4 5 6} {
108  do_test tkt-38cb5df375.14.$ii {
109    execsql {
110      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
111      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 3)
112      LIMIT $::ii;
113    }
114  } [lrange {0 0 0 9 9 9} 0 [expr {$ii-1}]]
115}
116
117foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
118  do_test tkt-38cb5df375.21.$ii {
119    execsql {
120      SELECT * FROM (SELECT * FROM t1 ORDER BY a)
121      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
122      ORDER BY 1
123      LIMIT $::ii;
124    }
125  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
126}
127foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
128  do_test tkt-38cb5df375.22.$ii {
129    execsql {
130      SELECT 9 FROM (SELECT * FROM t1)
131      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
132      ORDER BY 1
133      LIMIT $::ii;
134    }
135  } [lrange {1 2 3 4 5 6 7 8 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
136}
137foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
138  do_test tkt-38cb5df375.23.$ii {
139    execsql {
140      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
141      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a)
142      ORDER BY 1 DESC
143      LIMIT $::ii;
144    }
145  } [lrange {8 8 7 7 6 6 5 5 4 4 3 3 2 2 1 1} 0 [expr {$ii-1}]]
146}
147foreach ii {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} {
148  do_test tkt-38cb5df375.24.$ii {
149    execsql {
150      SELECT 0 FROM (SELECT * FROM t1)
151      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
152      ORDER BY 1
153      LIMIT $::ii;
154    }
155  } [lrange {0 0 0 0 0 0 0 0 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
156}
157
158foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
159  do_test tkt-38cb5df375.31.$ii {
160    execsql {
161      SELECT * FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
162      UNION ALL SELECT 9 FROM (SELECT a FROM t1)
163      ORDER BY 1
164      LIMIT $::ii;
165    }
166  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
167}
168foreach ii {1 2 3 4 5 6 7 8 9 10 11} {
169  do_test tkt-38cb5df375.32.$ii {
170    execsql {
171      SELECT 9 FROM (SELECT * FROM t1)
172      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
173      ORDER BY 1
174      LIMIT $::ii;
175    }
176  } [lrange {1 2 3 9 9 9 9 9 9 9 9} 0 [expr {$ii-1}]]
177}
178foreach ii {1 2 3 4 5 6 7} {
179  do_test tkt-38cb5df375.33.$ii {
180    execsql {
181      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 4)
182      UNION ALL SELECT 90+a FROM (SELECT a FROM t1 ORDER BY a LIMIT 3)
183      ORDER BY 1
184      LIMIT $::ii;
185    }
186  } [lrange {1 2 3 4 91 92 93} 0 [expr {$ii-1}]]
187}
188foreach ii {1 2 3 4 5 6 7} {
189  do_test tkt-38cb5df375.34.$ii {
190    execsql {
191      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 2)
192      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 5)
193      ORDER BY 1
194      LIMIT $::ii;
195    }
196  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
197}
198foreach ii {1 2 3 4 5 6 7} {
199  do_test tkt-38cb5df375.35.$ii {
200    execsql {
201      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
202      UNION ALL SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
203      ORDER BY 1
204      LIMIT $::ii;
205    }
206  } [lrange {1 1 2 2 3 4 5} 0 [expr {$ii-1}]]
207}
208foreach ii {1 2 3 4 5 6 7} {
209  do_test tkt-38cb5df375.35b.$ii {
210    execsql {
211      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
212      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
213      ORDER BY 1
214      LIMIT $::ii;
215    }
216  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
217}
218foreach ii {1 2 3 4 5 6 7} {
219  do_test tkt-38cb5df375.35c.$ii {
220    execsql {
221      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
222      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
223      ORDER BY 1
224      LIMIT $::ii;
225    }
226  } [lrange {1 2 3 4 5 11 12} 0 [expr {$ii-1}]]
227}
228foreach ii {1 2 3 4 5 6 7} {
229  do_test tkt-38cb5df375.35d.$ii {
230    execsql {
231      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
232      INTERSECT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
233      ORDER BY 1
234      LIMIT $::ii;
235    }
236  } [lrange {1 2} 0 [expr {$ii-1}]]
237}
238foreach ii {1 2 3 4 5 6 7} {
239  do_test tkt-38cb5df375.35e.$ii {
240    execsql {
241      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 5)
242      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
243      ORDER BY 1
244      LIMIT $::ii;
245    }
246  } [lrange {3 4 5} 0 [expr {$ii-1}]]
247}
248foreach ii {1 2 3 4 5 6 7} {
249  do_test tkt-38cb5df375.36.$ii {
250    execsql {
251      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
252      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
253      ORDER BY 1
254      LIMIT $::ii;
255    }
256  } [lrange {0 0 0 9 9 9 9} 0 [expr {$ii-1}]]
257}
258foreach ii {1 2 3 4 5 6 7} {
259  do_test tkt-38cb5df375.37.$ii {
260    execsql {
261      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
262      UNION SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
263      ORDER BY 1
264      LIMIT $::ii;
265    }
266  } [lrange {0 9} 0 [expr {$ii-1}]]
267}
268foreach ii {1 2 3 4 5 6 7} {
269  do_test tkt-38cb5df375.38.$ii {
270    execsql {
271      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
272      EXCEPT SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
273      ORDER BY 1
274      LIMIT $::ii;
275    }
276  } [lrange {0} 0 [expr {$ii-1}]]
277}
278
279foreach ii {1 2 3 4 5 6 7 8 9} {
280  do_test tkt-38cb5df375.41.$ii {
281    execsql {
282      SELECT 0 FROM (SELECT * FROM t1 LIMIT 3)
283      UNION ALL SELECT 9 FROM (SELECT a FROM t1 LIMIT 4)
284      UNION ALL SELECT 88 FROM (SELECT a FROM t1 LIMIT 2)
285      ORDER BY 1
286      LIMIT $::ii;
287    }
288  } [lrange {0 0 0 9 9 9 9 88 88} 0 [expr {$ii-1}]]
289}
290foreach ii {1 2 3 4 5 6 7 8 9} {
291  do_test tkt-38cb5df375.42.$ii {
292    execsql {
293      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
294      UNION ALL SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
295      UNION ALL SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
296      ORDER BY 1
297      LIMIT $::ii;
298    }
299  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
300}
301foreach ii {1 2 3 4 5 6 7 8 9} {
302  do_test tkt-38cb5df375.43.$ii {
303    execsql {
304      SELECT a FROM (SELECT * FROM t1 ORDER BY a LIMIT 3)
305      UNION SELECT a+10 FROM (SELECT a FROM t1 ORDER BY a LIMIT 4)
306      UNION SELECT a+20 FROM (SELECT a FROM t1 ORDER BY a LIMIT 2)
307      ORDER BY 1
308      LIMIT $::ii;
309    }
310  } [lrange {1 2 3 11 12 13 14 21 22} 0 [expr {$ii-1}]]
311}
312
313foreach ii {1 2 3 4 5 6 7} {
314  set jj [expr {7-$ii}]
315  do_test tkt-38cb5df375.51.$ii {
316    execsql {
317      SELECT a FROM (SELECT * FROM t1 ORDER BY a)
318      EXCEPT SELECT a FROM (SELECT a FROM t1 ORDER BY a LIMIT $::ii)
319      ORDER BY a DESC
320      LIMIT $::jj;
321    }
322  } [lrange {8 7 6 5 4 3 2 1} 0 [expr {$jj-1}]]
323}
324
325
326finish_test
327