AryaWu/sqlite
0
1# 2010 October 62#3# The author disclaims copyright to this source code. In place of4# 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] has13# been resolved.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19ifcapable !compound {20 finish_test21 return22}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 1123 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 1133 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 DESC143 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 1153 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 1164 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 1174 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 1184 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 1194 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 1204 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 1214 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 1224 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 1234 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 1244 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 1254 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 1264 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 1274 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 1286 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 1297 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 1308 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 DESC320 LIMIT $::jj;321 }322 } [lrange {8 7 6 5 4 3 2 1} 0 [expr {$jj-1}]]323}324 325 326finish_test327