1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/selectB.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,379 @@
1.4 +# 2008 June 24
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# $Id: selectB.test,v 1.9 2008/08/04 03:51:24 danielk1977 Exp $
1.17 +
1.18 +set testdir [file dirname $argv0]
1.19 +source $testdir/tester.tcl
1.20 +
1.21 +ifcapable !compound {
1.22 + finish_test
1.23 + return
1.24 +}
1.25 +
1.26 +proc test_transform {testname sql1 sql2 results} {
1.27 + set ::vdbe1 [list]
1.28 + set ::vdbe2 [list]
1.29 + db eval "explain $sql1" { lappend ::vdbe1 $opcode }
1.30 + db eval "explain $sql2" { lappend ::vdbe2 $opcode }
1.31 +
1.32 + do_test $testname.transform {
1.33 + set ::vdbe1
1.34 + } $::vdbe2
1.35 +
1.36 + set ::sql1 $sql1
1.37 + do_test $testname.sql1 {
1.38 + execsql $::sql1
1.39 + } $results
1.40 +
1.41 + set ::sql2 $sql2
1.42 + do_test $testname.sql2 {
1.43 + execsql $::sql2
1.44 + } $results
1.45 +}
1.46 +
1.47 +do_test selectB-1.1 {
1.48 + execsql {
1.49 + CREATE TABLE t1(a, b, c);
1.50 + CREATE TABLE t2(d, e, f);
1.51 +
1.52 + INSERT INTO t1 VALUES( 2, 4, 6);
1.53 + INSERT INTO t1 VALUES( 8, 10, 12);
1.54 + INSERT INTO t1 VALUES(14, 16, 18);
1.55 +
1.56 + INSERT INTO t2 VALUES(3, 6, 9);
1.57 + INSERT INTO t2 VALUES(12, 15, 18);
1.58 + INSERT INTO t2 VALUES(21, 24, 27);
1.59 + }
1.60 +} {}
1.61 +
1.62 +for {set ii 1} {$ii <= 2} {incr ii} {
1.63 +
1.64 + if {$ii == 2} {
1.65 + do_test selectB-2.1 {
1.66 + execsql {
1.67 + CREATE INDEX i1 ON t1(a);
1.68 + CREATE INDEX i2 ON t2(d);
1.69 + }
1.70 + } {}
1.71 + }
1.72 +
1.73 + test_transform selectB-$ii.2 {
1.74 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.75 + } {
1.76 + SELECT a FROM t1 UNION ALL SELECT d FROM t2
1.77 + } {2 8 14 3 12 21}
1.78 +
1.79 + test_transform selectB-$ii.3 {
1.80 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
1.81 + } {
1.82 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
1.83 + } {2 3 8 12 14 21}
1.84 +
1.85 + test_transform selectB-$ii.4 {
1.86 + SELECT * FROM
1.87 + (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.88 + WHERE a>10 ORDER BY 1
1.89 + } {
1.90 + SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
1.91 + } {12 14 21}
1.92 +
1.93 + test_transform selectB-$ii.5 {
1.94 + SELECT * FROM
1.95 + (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.96 + WHERE a>10 ORDER BY a
1.97 + } {
1.98 + SELECT a FROM t1 WHERE a>10
1.99 + UNION ALL
1.100 + SELECT d FROM t2 WHERE d>10
1.101 + ORDER BY a
1.102 + } {12 14 21}
1.103 +
1.104 + test_transform selectB-$ii.6 {
1.105 + SELECT * FROM
1.106 + (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12)
1.107 + WHERE a>10 ORDER BY a
1.108 + } {
1.109 + SELECT a FROM t1 WHERE a>10
1.110 + UNION ALL
1.111 + SELECT d FROM t2 WHERE d>12 AND d>10
1.112 + ORDER BY a
1.113 + } {14 21}
1.114 +
1.115 + test_transform selectB-$ii.7 {
1.116 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
1.117 + LIMIT 2
1.118 + } {
1.119 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
1.120 + } {2 3}
1.121 +
1.122 + test_transform selectB-$ii.8 {
1.123 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
1.124 + LIMIT 2 OFFSET 3
1.125 + } {
1.126 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
1.127 + } {12 14}
1.128 +
1.129 + test_transform selectB-$ii.9 {
1.130 + SELECT * FROM (
1.131 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
1.132 + )
1.133 + } {
1.134 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
1.135 + } {2 8 14 3 12 21 6 12 18}
1.136 +
1.137 + test_transform selectB-$ii.10 {
1.138 + SELECT * FROM (
1.139 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
1.140 + ) ORDER BY 1
1.141 + } {
1.142 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
1.143 + ORDER BY 1
1.144 + } {2 3 6 8 12 12 14 18 21}
1.145 +
1.146 + test_transform selectB-$ii.11 {
1.147 + SELECT * FROM (
1.148 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
1.149 + ) WHERE a>=10 ORDER BY 1 LIMIT 3
1.150 + } {
1.151 + SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
1.152 + UNION ALL SELECT c FROM t1 WHERE c>=10
1.153 + ORDER BY 1 LIMIT 3
1.154 + } {12 12 14}
1.155 +
1.156 + test_transform selectB-$ii.12 {
1.157 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
1.158 + } {
1.159 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
1.160 + } {2 8}
1.161 +
1.162 + test_transform selectB-$ii.13 {
1.163 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
1.164 + } {
1.165 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
1.166 + } {2 3 8 12 14 21}
1.167 +
1.168 + test_transform selectB-$ii.14 {
1.169 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
1.170 + } {
1.171 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
1.172 + } {21 14 12 8 3 2}
1.173 +
1.174 + test_transform selectB-$ii.14 {
1.175 + SELECT * FROM (
1.176 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
1.177 + ) LIMIT 2 OFFSET 2
1.178 + } {
1.179 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC LIMIT 2 OFFSET 2
1.180 + } {12 8}
1.181 +
1.182 + test_transform selectB-$ii.15 {
1.183 + SELECT * FROM (
1.184 + SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
1.185 + )
1.186 + } {
1.187 + SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
1.188 + } {2 4 3 6 8 10 12 15 14 16 21 24}
1.189 +}
1.190 +
1.191 +do_test selectB-3.0 {
1.192 + execsql {
1.193 + DROP INDEX i1;
1.194 + DROP INDEX i2;
1.195 + }
1.196 +} {}
1.197 +
1.198 +for {set ii 3} {$ii <= 4} {incr ii} {
1.199 +
1.200 + if {$ii == 4} {
1.201 + do_test selectB-4.0 {
1.202 + execsql {
1.203 + CREATE INDEX i1 ON t1(a);
1.204 + CREATE INDEX i2 ON t1(b);
1.205 + CREATE INDEX i3 ON t1(c);
1.206 + CREATE INDEX i4 ON t2(d);
1.207 + CREATE INDEX i5 ON t2(e);
1.208 + CREATE INDEX i6 ON t2(f);
1.209 + }
1.210 + } {}
1.211 + }
1.212 +
1.213 + do_test selectB-$ii.1 {
1.214 + execsql {
1.215 + SELECT DISTINCT * FROM
1.216 + (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
1.217 + ORDER BY 1;
1.218 + }
1.219 + } {6 12 15 18 24}
1.220 +
1.221 + do_test selectB-$ii.2 {
1.222 + execsql {
1.223 + SELECT c, count(*) FROM
1.224 + (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
1.225 + GROUP BY c ORDER BY 1;
1.226 + }
1.227 + } {6 2 12 1 15 1 18 1 24 1}
1.228 + do_test selectB-$ii.3 {
1.229 + execsql {
1.230 + SELECT c, count(*) FROM
1.231 + (SELECT c FROM t1 UNION ALL SELECT e FROM t2)
1.232 + GROUP BY c HAVING count(*)>1;
1.233 + }
1.234 + } {6 2}
1.235 + do_test selectB-$ii.4 {
1.236 + execsql {
1.237 + SELECT t4.c, t3.a FROM
1.238 + (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
1.239 + WHERE t3.a=14
1.240 + ORDER BY 1
1.241 + }
1.242 + } {6 14 6 14 12 14 15 14 18 14 24 14}
1.243 +
1.244 + do_test selectB-$ii.5 {
1.245 + execsql {
1.246 + SELECT d FROM t2
1.247 + EXCEPT
1.248 + SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.249 + }
1.250 + } {}
1.251 + do_test selectB-$ii.6 {
1.252 + execsql {
1.253 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.254 + EXCEPT
1.255 + SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
1.256 + }
1.257 + } {}
1.258 + do_test selectB-$ii.7 {
1.259 + execsql {
1.260 + SELECT c FROM t1
1.261 + EXCEPT
1.262 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.263 + }
1.264 + } {12}
1.265 + do_test selectB-$ii.8 {
1.266 + execsql {
1.267 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.268 + EXCEPT
1.269 + SELECT c FROM t1
1.270 + }
1.271 + } {9 15 24 27}
1.272 + do_test selectB-$ii.9 {
1.273 + execsql {
1.274 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.275 + EXCEPT
1.276 + SELECT c FROM t1
1.277 + ORDER BY c DESC
1.278 + }
1.279 + } {27 24 15 9}
1.280 +
1.281 + do_test selectB-$ii.10 {
1.282 + execsql {
1.283 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.284 + UNION
1.285 + SELECT c FROM t1
1.286 + ORDER BY c DESC
1.287 + }
1.288 + } {27 24 18 15 12 9 6}
1.289 + do_test selectB-$ii.11 {
1.290 + execsql {
1.291 + SELECT c FROM t1
1.292 + UNION
1.293 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.294 + ORDER BY c
1.295 + }
1.296 + } {6 9 12 15 18 24 27}
1.297 + do_test selectB-$ii.12 {
1.298 + execsql {
1.299 + SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
1.300 + ORDER BY c
1.301 + }
1.302 + } {6 9 12 15 18 18 24 27}
1.303 + do_test selectB-$ii.13 {
1.304 + execsql {
1.305 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.306 + UNION
1.307 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.308 + ORDER BY 1
1.309 + }
1.310 + } {6 9 15 18 24 27}
1.311 +
1.312 + do_test selectB-$ii.14 {
1.313 + execsql {
1.314 + SELECT c FROM t1
1.315 + INTERSECT
1.316 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.317 + ORDER BY 1
1.318 + }
1.319 + } {6 18}
1.320 + do_test selectB-$ii.15 {
1.321 + execsql {
1.322 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.323 + INTERSECT
1.324 + SELECT c FROM t1
1.325 + ORDER BY 1
1.326 + }
1.327 + } {6 18}
1.328 + do_test selectB-$ii.16 {
1.329 + execsql {
1.330 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.331 + INTERSECT
1.332 + SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
1.333 + ORDER BY 1
1.334 + }
1.335 + } {6 9 15 18 24 27}
1.336 +
1.337 + do_test selectB-$ii.17 {
1.338 + execsql {
1.339 + SELECT * FROM (
1.340 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
1.341 + ) LIMIT 2
1.342 + }
1.343 + } {2 8}
1.344 +
1.345 + do_test selectB-$ii.18 {
1.346 + execsql {
1.347 + SELECT * FROM (
1.348 + SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
1.349 + ) LIMIT 2
1.350 + }
1.351 + } {14 3}
1.352 +
1.353 + do_test selectB-$ii.19 {
1.354 + execsql {
1.355 + SELECT * FROM (
1.356 + SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
1.357 + )
1.358 + }
1.359 + } {0 1 0 1}
1.360 +
1.361 + do_test selectB-$ii.20 {
1.362 + execsql {
1.363 + SELECT DISTINCT * FROM (
1.364 + SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
1.365 + )
1.366 + }
1.367 + } {0 1}
1.368 +
1.369 + do_test selectB-$ii.21 {
1.370 + execsql {
1.371 + SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
1.372 + }
1.373 + } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}
1.374 +
1.375 + do_test selectB-$ii.21 {
1.376 + execsql {
1.377 + SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
1.378 + }
1.379 + } {3 12 21 345}
1.380 +}
1.381 +
1.382 +finish_test