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