1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select4.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,796 @@
1.4 +# 2001 September 15
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. The
1.15 +# focus of this file is testing UNION, INTERSECT and EXCEPT operators
1.16 +# in SELECT statements.
1.17 +#
1.18 +# $Id: select4.test,v 1.29 2008/08/04 03:51:24 danielk1977 Exp $
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Most tests in this file depend on compound-select. But there are a couple
1.24 +# right at the end that test DISTINCT, so we cannot omit the entire file.
1.25 +#
1.26 +ifcapable compound {
1.27 +
1.28 +# Build some test data
1.29 +#
1.30 +execsql {
1.31 + CREATE TABLE t1(n int, log int);
1.32 + BEGIN;
1.33 +}
1.34 +for {set i 1} {$i<32} {incr i} {
1.35 + for {set j 0} {(1<<$j)<$i} {incr j} {}
1.36 + execsql "INSERT INTO t1 VALUES($i,$j)"
1.37 +}
1.38 +execsql {
1.39 + COMMIT;
1.40 +}
1.41 +
1.42 +do_test select4-1.0 {
1.43 + execsql {SELECT DISTINCT log FROM t1 ORDER BY log}
1.44 +} {0 1 2 3 4 5}
1.45 +
1.46 +# Union All operator
1.47 +#
1.48 +do_test select4-1.1a {
1.49 + lsort [execsql {SELECT DISTINCT log FROM t1}]
1.50 +} {0 1 2 3 4 5}
1.51 +do_test select4-1.1b {
1.52 + lsort [execsql {SELECT n FROM t1 WHERE log=3}]
1.53 +} {5 6 7 8}
1.54 +do_test select4-1.1c {
1.55 + execsql {
1.56 + SELECT DISTINCT log FROM t1
1.57 + UNION ALL
1.58 + SELECT n FROM t1 WHERE log=3
1.59 + ORDER BY log;
1.60 + }
1.61 +} {0 1 2 3 4 5 5 6 7 8}
1.62 +do_test select4-1.1d {
1.63 + execsql {
1.64 + CREATE TABLE t2 AS
1.65 + SELECT DISTINCT log FROM t1
1.66 + UNION ALL
1.67 + SELECT n FROM t1 WHERE log=3
1.68 + ORDER BY log;
1.69 + SELECT * FROM t2;
1.70 + }
1.71 +} {0 1 2 3 4 5 5 6 7 8}
1.72 +execsql {DROP TABLE t2}
1.73 +do_test select4-1.1e {
1.74 + execsql {
1.75 + CREATE TABLE t2 AS
1.76 + SELECT DISTINCT log FROM t1
1.77 + UNION ALL
1.78 + SELECT n FROM t1 WHERE log=3
1.79 + ORDER BY log DESC;
1.80 + SELECT * FROM t2;
1.81 + }
1.82 +} {8 7 6 5 5 4 3 2 1 0}
1.83 +execsql {DROP TABLE t2}
1.84 +do_test select4-1.1f {
1.85 + execsql {
1.86 + SELECT DISTINCT log FROM t1
1.87 + UNION ALL
1.88 + SELECT n FROM t1 WHERE log=2
1.89 + }
1.90 +} {0 1 2 3 4 5 3 4}
1.91 +do_test select4-1.1g {
1.92 + execsql {
1.93 + CREATE TABLE t2 AS
1.94 + SELECT DISTINCT log FROM t1
1.95 + UNION ALL
1.96 + SELECT n FROM t1 WHERE log=2;
1.97 + SELECT * FROM t2;
1.98 + }
1.99 +} {0 1 2 3 4 5 3 4}
1.100 +execsql {DROP TABLE t2}
1.101 +ifcapable subquery {
1.102 + do_test select4-1.2 {
1.103 + execsql {
1.104 + SELECT log FROM t1 WHERE n IN
1.105 + (SELECT DISTINCT log FROM t1 UNION ALL
1.106 + SELECT n FROM t1 WHERE log=3)
1.107 + ORDER BY log;
1.108 + }
1.109 + } {0 1 2 2 3 3 3 3}
1.110 +}
1.111 +do_test select4-1.3 {
1.112 + set v [catch {execsql {
1.113 + SELECT DISTINCT log FROM t1 ORDER BY log
1.114 + UNION ALL
1.115 + SELECT n FROM t1 WHERE log=3
1.116 + ORDER BY log;
1.117 + }} msg]
1.118 + lappend v $msg
1.119 +} {1 {ORDER BY clause should come after UNION ALL not before}}
1.120 +
1.121 +# Union operator
1.122 +#
1.123 +do_test select4-2.1 {
1.124 + execsql {
1.125 + SELECT DISTINCT log FROM t1
1.126 + UNION
1.127 + SELECT n FROM t1 WHERE log=3
1.128 + ORDER BY log;
1.129 + }
1.130 +} {0 1 2 3 4 5 6 7 8}
1.131 +ifcapable subquery {
1.132 + do_test select4-2.2 {
1.133 + execsql {
1.134 + SELECT log FROM t1 WHERE n IN
1.135 + (SELECT DISTINCT log FROM t1 UNION
1.136 + SELECT n FROM t1 WHERE log=3)
1.137 + ORDER BY log;
1.138 + }
1.139 + } {0 1 2 2 3 3 3 3}
1.140 +}
1.141 +do_test select4-2.3 {
1.142 + set v [catch {execsql {
1.143 + SELECT DISTINCT log FROM t1 ORDER BY log
1.144 + UNION
1.145 + SELECT n FROM t1 WHERE log=3
1.146 + ORDER BY log;
1.147 + }} msg]
1.148 + lappend v $msg
1.149 +} {1 {ORDER BY clause should come after UNION not before}}
1.150 +
1.151 +# Except operator
1.152 +#
1.153 +do_test select4-3.1.1 {
1.154 + execsql {
1.155 + SELECT DISTINCT log FROM t1
1.156 + EXCEPT
1.157 + SELECT n FROM t1 WHERE log=3
1.158 + ORDER BY log;
1.159 + }
1.160 +} {0 1 2 3 4}
1.161 +do_test select4-3.1.2 {
1.162 + execsql {
1.163 + CREATE TABLE t2 AS
1.164 + SELECT DISTINCT log FROM t1
1.165 + EXCEPT
1.166 + SELECT n FROM t1 WHERE log=3
1.167 + ORDER BY log;
1.168 + SELECT * FROM t2;
1.169 + }
1.170 +} {0 1 2 3 4}
1.171 +execsql {DROP TABLE t2}
1.172 +do_test select4-3.1.3 {
1.173 + execsql {
1.174 + CREATE TABLE t2 AS
1.175 + SELECT DISTINCT log FROM t1
1.176 + EXCEPT
1.177 + SELECT n FROM t1 WHERE log=3
1.178 + ORDER BY log DESC;
1.179 + SELECT * FROM t2;
1.180 + }
1.181 +} {4 3 2 1 0}
1.182 +execsql {DROP TABLE t2}
1.183 +ifcapable subquery {
1.184 + do_test select4-3.2 {
1.185 + execsql {
1.186 + SELECT log FROM t1 WHERE n IN
1.187 + (SELECT DISTINCT log FROM t1 EXCEPT
1.188 + SELECT n FROM t1 WHERE log=3)
1.189 + ORDER BY log;
1.190 + }
1.191 + } {0 1 2 2}
1.192 +}
1.193 +do_test select4-3.3 {
1.194 + set v [catch {execsql {
1.195 + SELECT DISTINCT log FROM t1 ORDER BY log
1.196 + EXCEPT
1.197 + SELECT n FROM t1 WHERE log=3
1.198 + ORDER BY log;
1.199 + }} msg]
1.200 + lappend v $msg
1.201 +} {1 {ORDER BY clause should come after EXCEPT not before}}
1.202 +
1.203 +# Intersect operator
1.204 +#
1.205 +do_test select4-4.1.1 {
1.206 + execsql {
1.207 + SELECT DISTINCT log FROM t1
1.208 + INTERSECT
1.209 + SELECT n FROM t1 WHERE log=3
1.210 + ORDER BY log;
1.211 + }
1.212 +} {5}
1.213 +
1.214 +do_test select4-4.1.2 {
1.215 + execsql {
1.216 + SELECT DISTINCT log FROM t1
1.217 + UNION ALL
1.218 + SELECT 6
1.219 + INTERSECT
1.220 + SELECT n FROM t1 WHERE log=3
1.221 + ORDER BY t1.log;
1.222 + }
1.223 +} {5 6}
1.224 +
1.225 +do_test select4-4.1.3 {
1.226 + execsql {
1.227 + CREATE TABLE t2 AS
1.228 + SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
1.229 + INTERSECT
1.230 + SELECT n FROM t1 WHERE log=3
1.231 + ORDER BY log;
1.232 + SELECT * FROM t2;
1.233 + }
1.234 +} {5 6}
1.235 +execsql {DROP TABLE t2}
1.236 +do_test select4-4.1.4 {
1.237 + execsql {
1.238 + CREATE TABLE t2 AS
1.239 + SELECT DISTINCT log FROM t1 UNION ALL SELECT 6
1.240 + INTERSECT
1.241 + SELECT n FROM t1 WHERE log=3
1.242 + ORDER BY log DESC;
1.243 + SELECT * FROM t2;
1.244 + }
1.245 +} {6 5}
1.246 +execsql {DROP TABLE t2}
1.247 +ifcapable subquery {
1.248 + do_test select4-4.2 {
1.249 + execsql {
1.250 + SELECT log FROM t1 WHERE n IN
1.251 + (SELECT DISTINCT log FROM t1 INTERSECT
1.252 + SELECT n FROM t1 WHERE log=3)
1.253 + ORDER BY log;
1.254 + }
1.255 + } {3}
1.256 +}
1.257 +do_test select4-4.3 {
1.258 + set v [catch {execsql {
1.259 + SELECT DISTINCT log FROM t1 ORDER BY log
1.260 + INTERSECT
1.261 + SELECT n FROM t1 WHERE log=3
1.262 + ORDER BY log;
1.263 + }} msg]
1.264 + lappend v $msg
1.265 +} {1 {ORDER BY clause should come after INTERSECT not before}}
1.266 +
1.267 +# Various error messages while processing UNION or INTERSECT
1.268 +#
1.269 +do_test select4-5.1 {
1.270 + set v [catch {execsql {
1.271 + SELECT DISTINCT log FROM t2
1.272 + UNION ALL
1.273 + SELECT n FROM t1 WHERE log=3
1.274 + ORDER BY log;
1.275 + }} msg]
1.276 + lappend v $msg
1.277 +} {1 {no such table: t2}}
1.278 +do_test select4-5.2 {
1.279 + set v [catch {execsql {
1.280 + SELECT DISTINCT log AS "xyzzy" FROM t1
1.281 + UNION ALL
1.282 + SELECT n FROM t1 WHERE log=3
1.283 + ORDER BY xyzzy;
1.284 + }} msg]
1.285 + lappend v $msg
1.286 +} {0 {0 1 2 3 4 5 5 6 7 8}}
1.287 +do_test select4-5.2b {
1.288 + set v [catch {execsql {
1.289 + SELECT DISTINCT log AS xyzzy FROM t1
1.290 + UNION ALL
1.291 + SELECT n FROM t1 WHERE log=3
1.292 + ORDER BY "xyzzy";
1.293 + }} msg]
1.294 + lappend v $msg
1.295 +} {0 {0 1 2 3 4 5 5 6 7 8}}
1.296 +do_test select4-5.2c {
1.297 + set v [catch {execsql {
1.298 + SELECT DISTINCT log FROM t1
1.299 + UNION ALL
1.300 + SELECT n FROM t1 WHERE log=3
1.301 + ORDER BY "xyzzy";
1.302 + }} msg]
1.303 + lappend v $msg
1.304 +} {1 {1st ORDER BY term does not match any column in the result set}}
1.305 +do_test select4-5.2d {
1.306 + set v [catch {execsql {
1.307 + SELECT DISTINCT log FROM t1
1.308 + INTERSECT
1.309 + SELECT n FROM t1 WHERE log=3
1.310 + ORDER BY "xyzzy";
1.311 + }} msg]
1.312 + lappend v $msg
1.313 +} {1 {1st ORDER BY term does not match any column in the result set}}
1.314 +do_test select4-5.2e {
1.315 + set v [catch {execsql {
1.316 + SELECT DISTINCT log FROM t1
1.317 + UNION ALL
1.318 + SELECT n FROM t1 WHERE log=3
1.319 + ORDER BY n;
1.320 + }} msg]
1.321 + lappend v $msg
1.322 +} {0 {0 1 2 3 4 5 5 6 7 8}}
1.323 +do_test select4-5.2f {
1.324 + catchsql {
1.325 + SELECT DISTINCT log FROM t1
1.326 + UNION ALL
1.327 + SELECT n FROM t1 WHERE log=3
1.328 + ORDER BY log;
1.329 + }
1.330 +} {0 {0 1 2 3 4 5 5 6 7 8}}
1.331 +do_test select4-5.2g {
1.332 + catchsql {
1.333 + SELECT DISTINCT log FROM t1
1.334 + UNION ALL
1.335 + SELECT n FROM t1 WHERE log=3
1.336 + ORDER BY 1;
1.337 + }
1.338 +} {0 {0 1 2 3 4 5 5 6 7 8}}
1.339 +do_test select4-5.2h {
1.340 + catchsql {
1.341 + SELECT DISTINCT log FROM t1
1.342 + UNION ALL
1.343 + SELECT n FROM t1 WHERE log=3
1.344 + ORDER BY 2;
1.345 + }
1.346 +} {1 {1st ORDER BY term out of range - should be between 1 and 1}}
1.347 +do_test select4-5.2i {
1.348 + catchsql {
1.349 + SELECT DISTINCT 1, log FROM t1
1.350 + UNION ALL
1.351 + SELECT 2, n FROM t1 WHERE log=3
1.352 + ORDER BY 2, 1;
1.353 + }
1.354 +} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
1.355 +do_test select4-5.2j {
1.356 + catchsql {
1.357 + SELECT DISTINCT 1, log FROM t1
1.358 + UNION ALL
1.359 + SELECT 2, n FROM t1 WHERE log=3
1.360 + ORDER BY 1, 2 DESC;
1.361 + }
1.362 +} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}
1.363 +do_test select4-5.2k {
1.364 + catchsql {
1.365 + SELECT DISTINCT 1, log FROM t1
1.366 + UNION ALL
1.367 + SELECT 2, n FROM t1 WHERE log=3
1.368 + ORDER BY n, 1;
1.369 + }
1.370 +} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}
1.371 +do_test select4-5.3 {
1.372 + set v [catch {execsql {
1.373 + SELECT DISTINCT log, n FROM t1
1.374 + UNION ALL
1.375 + SELECT n FROM t1 WHERE log=3
1.376 + ORDER BY log;
1.377 + }} msg]
1.378 + lappend v $msg
1.379 +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
1.380 +do_test select4-5.4 {
1.381 + set v [catch {execsql {
1.382 + SELECT log FROM t1 WHERE n=2
1.383 + UNION ALL
1.384 + SELECT log FROM t1 WHERE n=3
1.385 + UNION ALL
1.386 + SELECT log FROM t1 WHERE n=4
1.387 + UNION ALL
1.388 + SELECT log FROM t1 WHERE n=5
1.389 + ORDER BY log;
1.390 + }} msg]
1.391 + lappend v $msg
1.392 +} {0 {1 2 2 3}}
1.393 +
1.394 +do_test select4-6.1 {
1.395 + execsql {
1.396 + SELECT log, count(*) as cnt FROM t1 GROUP BY log
1.397 + UNION
1.398 + SELECT log, n FROM t1 WHERE n=7
1.399 + ORDER BY cnt, log;
1.400 + }
1.401 +} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
1.402 +do_test select4-6.2 {
1.403 + execsql {
1.404 + SELECT log, count(*) FROM t1 GROUP BY log
1.405 + UNION
1.406 + SELECT log, n FROM t1 WHERE n=7
1.407 + ORDER BY count(*), log;
1.408 + }
1.409 +} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}
1.410 +
1.411 +# NULLs are indistinct for the UNION operator.
1.412 +# Make sure the UNION operator recognizes this
1.413 +#
1.414 +do_test select4-6.3 {
1.415 + execsql {
1.416 + SELECT NULL UNION SELECT NULL UNION
1.417 + SELECT 1 UNION SELECT 2 AS 'x'
1.418 + ORDER BY x;
1.419 + }
1.420 +} {{} 1 2}
1.421 +do_test select4-6.3.1 {
1.422 + execsql {
1.423 + SELECT NULL UNION ALL SELECT NULL UNION ALL
1.424 + SELECT 1 UNION ALL SELECT 2 AS 'x'
1.425 + ORDER BY x;
1.426 + }
1.427 +} {{} {} 1 2}
1.428 +
1.429 +# Make sure the DISTINCT keyword treats NULLs as indistinct.
1.430 +#
1.431 +ifcapable subquery {
1.432 + do_test select4-6.4 {
1.433 + execsql {
1.434 + SELECT * FROM (
1.435 + SELECT NULL, 1 UNION ALL SELECT NULL, 1
1.436 + );
1.437 + }
1.438 + } {{} 1 {} 1}
1.439 + do_test select4-6.5 {
1.440 + execsql {
1.441 + SELECT DISTINCT * FROM (
1.442 + SELECT NULL, 1 UNION ALL SELECT NULL, 1
1.443 + );
1.444 + }
1.445 + } {{} 1}
1.446 + do_test select4-6.6 {
1.447 + execsql {
1.448 + SELECT DISTINCT * FROM (
1.449 + SELECT 1,2 UNION ALL SELECT 1,2
1.450 + );
1.451 + }
1.452 + } {1 2}
1.453 +}
1.454 +
1.455 +# Test distinctness of NULL in other ways.
1.456 +#
1.457 +do_test select4-6.7 {
1.458 + execsql {
1.459 + SELECT NULL EXCEPT SELECT NULL
1.460 + }
1.461 +} {}
1.462 +
1.463 +
1.464 +# Make sure column names are correct when a compound select appears as
1.465 +# an expression in the WHERE clause.
1.466 +#
1.467 +do_test select4-7.1 {
1.468 + execsql {
1.469 + CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;
1.470 + SELECT * FROM t2 ORDER BY x;
1.471 + }
1.472 +} {0 1 1 1 2 2 3 4 4 8 5 15}
1.473 +ifcapable subquery {
1.474 + do_test select4-7.2 {
1.475 + execsql2 {
1.476 + SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)
1.477 + ORDER BY n
1.478 + }
1.479 + } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}
1.480 + do_test select4-7.3 {
1.481 + execsql2 {
1.482 + SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)
1.483 + ORDER BY n LIMIT 2
1.484 + }
1.485 + } {n 6 log 3 n 7 log 3}
1.486 + do_test select4-7.4 {
1.487 + execsql2 {
1.488 + SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)
1.489 + ORDER BY n LIMIT 2
1.490 + }
1.491 + } {n 1 log 0 n 2 log 1}
1.492 +} ;# ifcapable subquery
1.493 +
1.494 +} ;# ifcapable compound
1.495 +
1.496 +# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.
1.497 +do_test select4-8.1 {
1.498 + execsql {
1.499 + BEGIN;
1.500 + CREATE TABLE t3(a text, b float, c text);
1.501 + INSERT INTO t3 VALUES(1, 1.1, '1.1');
1.502 + INSERT INTO t3 VALUES(2, 1.10, '1.10');
1.503 + INSERT INTO t3 VALUES(3, 1.10, '1.1');
1.504 + INSERT INTO t3 VALUES(4, 1.1, '1.10');
1.505 + INSERT INTO t3 VALUES(5, 1.2, '1.2');
1.506 + INSERT INTO t3 VALUES(6, 1.3, '1.3');
1.507 + COMMIT;
1.508 + }
1.509 + execsql {
1.510 + SELECT DISTINCT b FROM t3 ORDER BY c;
1.511 + }
1.512 +} {1.1 1.2 1.3}
1.513 +do_test select4-8.2 {
1.514 + execsql {
1.515 + SELECT DISTINCT c FROM t3 ORDER BY c;
1.516 + }
1.517 +} {1.1 1.10 1.2 1.3}
1.518 +
1.519 +# Make sure the names of columns are taken from the right-most subquery
1.520 +# right in a compound query. Ticket #1721
1.521 +#
1.522 +ifcapable compound {
1.523 +
1.524 +do_test select4-9.1 {
1.525 + execsql2 {
1.526 + SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1
1.527 + }
1.528 +} {x 0 y 1}
1.529 +do_test select4-9.2 {
1.530 + execsql2 {
1.531 + SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1
1.532 + }
1.533 +} {x 0 y 1}
1.534 +do_test select4-9.3 {
1.535 + execsql2 {
1.536 + SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
1.537 + }
1.538 +} {x 0 y 1}
1.539 +do_test select4-9.4 {
1.540 + execsql2 {
1.541 + SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
1.542 + }
1.543 +} {x 0 y 1}
1.544 +do_test select4-9.5 {
1.545 + execsql2 {
1.546 + SELECT 0 AS x, 1 AS y
1.547 + UNION
1.548 + SELECT 2 AS p, 3 AS q
1.549 + UNION
1.550 + SELECT 4 AS a, 5 AS b
1.551 + ORDER BY x LIMIT 1
1.552 + }
1.553 +} {x 0 y 1}
1.554 +
1.555 +ifcapable subquery {
1.556 +do_test select4-9.6 {
1.557 + execsql2 {
1.558 + SELECT * FROM (
1.559 + SELECT 0 AS x, 1 AS y
1.560 + UNION
1.561 + SELECT 2 AS p, 3 AS q
1.562 + UNION
1.563 + SELECT 4 AS a, 5 AS b
1.564 + ) ORDER BY 1 LIMIT 1;
1.565 + }
1.566 +} {x 0 y 1}
1.567 +do_test select4-9.7 {
1.568 + execsql2 {
1.569 + SELECT * FROM (
1.570 + SELECT 0 AS x, 1 AS y
1.571 + UNION
1.572 + SELECT 2 AS p, 3 AS q
1.573 + UNION
1.574 + SELECT 4 AS a, 5 AS b
1.575 + ) ORDER BY x LIMIT 1;
1.576 + }
1.577 +} {x 0 y 1}
1.578 +} ;# ifcapable subquery
1.579 +
1.580 +do_test select4-9.8 {
1.581 + execsql {
1.582 + SELECT 0 AS x, 1 AS y
1.583 + UNION
1.584 + SELECT 2 AS y, -3 AS x
1.585 + ORDER BY x LIMIT 1;
1.586 + }
1.587 +} {0 1}
1.588 +
1.589 +do_test select4-9.9.1 {
1.590 + execsql2 {
1.591 + SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
1.592 + }
1.593 +} {a 1 b 2 a 3 b 4}
1.594 +
1.595 +ifcapable subquery {
1.596 +do_test select4-9.9.2 {
1.597 + execsql2 {
1.598 + SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
1.599 + WHERE b=3
1.600 + }
1.601 +} {}
1.602 +do_test select4-9.10 {
1.603 + execsql2 {
1.604 + SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
1.605 + WHERE b=2
1.606 + }
1.607 +} {a 1 b 2}
1.608 +do_test select4-9.11 {
1.609 + execsql2 {
1.610 + SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
1.611 + WHERE b=2
1.612 + }
1.613 +} {a 1 b 2}
1.614 +do_test select4-9.12 {
1.615 + execsql2 {
1.616 + SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
1.617 + WHERE b>0
1.618 + }
1.619 +} {a 1 b 2 a 3 b 4}
1.620 +} ;# ifcapable subquery
1.621 +
1.622 +# Try combining DISTINCT, LIMIT, and OFFSET. Make sure they all work
1.623 +# together.
1.624 +#
1.625 +do_test select4-10.1 {
1.626 + execsql {
1.627 + SELECT DISTINCT log FROM t1 ORDER BY log
1.628 + }
1.629 +} {0 1 2 3 4 5}
1.630 +do_test select4-10.2 {
1.631 + execsql {
1.632 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
1.633 + }
1.634 +} {0 1 2 3}
1.635 +do_test select4-10.3 {
1.636 + execsql {
1.637 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
1.638 + }
1.639 +} {}
1.640 +do_test select4-10.4 {
1.641 + execsql {
1.642 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
1.643 + }
1.644 +} {0 1 2 3 4 5}
1.645 +do_test select4-10.5 {
1.646 + execsql {
1.647 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
1.648 + }
1.649 +} {2 3 4 5}
1.650 +do_test select4-10.6 {
1.651 + execsql {
1.652 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
1.653 + }
1.654 +} {2 3 4}
1.655 +do_test select4-10.7 {
1.656 + execsql {
1.657 + SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
1.658 + }
1.659 +} {}
1.660 +do_test select4-10.8 {
1.661 + execsql {
1.662 + SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
1.663 + }
1.664 +} {}
1.665 +do_test select4-10.9 {
1.666 + execsql {
1.667 + SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
1.668 + }
1.669 +} {31 5}
1.670 +
1.671 +# Make sure compound SELECTs with wildly different numbers of columns
1.672 +# do not cause assertion faults due to register allocation issues.
1.673 +#
1.674 +do_test select4-11.1 {
1.675 + catchsql {
1.676 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.677 + UNION
1.678 + SELECT x FROM t2
1.679 + }
1.680 +} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
1.681 +do_test select4-11.2 {
1.682 + catchsql {
1.683 + SELECT x FROM t2
1.684 + UNION
1.685 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.686 + }
1.687 +} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
1.688 +do_test select4-11.3 {
1.689 + catchsql {
1.690 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.691 + UNION ALL
1.692 + SELECT x FROM t2
1.693 + }
1.694 +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
1.695 +do_test select4-11.4 {
1.696 + catchsql {
1.697 + SELECT x FROM t2
1.698 + UNION ALL
1.699 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.700 + }
1.701 +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
1.702 +do_test select4-11.5 {
1.703 + catchsql {
1.704 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.705 + EXCEPT
1.706 + SELECT x FROM t2
1.707 + }
1.708 +} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
1.709 +do_test select4-11.6 {
1.710 + catchsql {
1.711 + SELECT x FROM t2
1.712 + EXCEPT
1.713 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.714 + }
1.715 +} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
1.716 +do_test select4-11.7 {
1.717 + catchsql {
1.718 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.719 + INTERSECT
1.720 + SELECT x FROM t2
1.721 + }
1.722 +} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
1.723 +do_test select4-11.8 {
1.724 + catchsql {
1.725 + SELECT x FROM t2
1.726 + INTERSECT
1.727 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.728 + }
1.729 +} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
1.730 +
1.731 +do_test select4-11.11 {
1.732 + catchsql {
1.733 + SELECT x FROM t2
1.734 + UNION
1.735 + SELECT x FROM t2
1.736 + UNION ALL
1.737 + SELECT x FROM t2
1.738 + EXCEPT
1.739 + SELECT x FROM t2
1.740 + INTERSECT
1.741 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.742 + }
1.743 +} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
1.744 +do_test select4-11.12 {
1.745 + catchsql {
1.746 + SELECT x FROM t2
1.747 + UNION
1.748 + SELECT x FROM t2
1.749 + UNION ALL
1.750 + SELECT x FROM t2
1.751 + EXCEPT
1.752 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.753 + EXCEPT
1.754 + SELECT x FROM t2
1.755 + }
1.756 +} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
1.757 +do_test select4-11.13 {
1.758 + catchsql {
1.759 + SELECT x FROM t2
1.760 + UNION
1.761 + SELECT x FROM t2
1.762 + UNION ALL
1.763 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.764 + UNION ALL
1.765 + SELECT x FROM t2
1.766 + EXCEPT
1.767 + SELECT x FROM t2
1.768 + }
1.769 +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
1.770 +do_test select4-11.14 {
1.771 + catchsql {
1.772 + SELECT x FROM t2
1.773 + UNION
1.774 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.775 + UNION
1.776 + SELECT x FROM t2
1.777 + UNION ALL
1.778 + SELECT x FROM t2
1.779 + EXCEPT
1.780 + SELECT x FROM t2
1.781 + }
1.782 +} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
1.783 +do_test select4-11.15 {
1.784 + catchsql {
1.785 + SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
1.786 + UNION
1.787 + SELECT x FROM t2
1.788 + INTERSECT
1.789 + SELECT x FROM t2
1.790 + UNION ALL
1.791 + SELECT x FROM t2
1.792 + EXCEPT
1.793 + SELECT x FROM t2
1.794 + }
1.795 +} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
1.796 +
1.797 +} ;# ifcapable compound
1.798 +
1.799 +finish_test