1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/select1.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,1044 @@
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 the SELECT statement.
1.16 +#
1.17 +# $Id: select1.test,v 1.65 2008/08/04 03:51:24 danielk1977 Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Try to select on a non-existant table.
1.23 +#
1.24 +do_test select1-1.1 {
1.25 + set v [catch {execsql {SELECT * FROM test1}} msg]
1.26 + lappend v $msg
1.27 +} {1 {no such table: test1}}
1.28 +
1.29 +
1.30 +execsql {CREATE TABLE test1(f1 int, f2 int)}
1.31 +
1.32 +do_test select1-1.2 {
1.33 + set v [catch {execsql {SELECT * FROM test1, test2}} msg]
1.34 + lappend v $msg
1.35 +} {1 {no such table: test2}}
1.36 +do_test select1-1.3 {
1.37 + set v [catch {execsql {SELECT * FROM test2, test1}} msg]
1.38 + lappend v $msg
1.39 +} {1 {no such table: test2}}
1.40 +
1.41 +execsql {INSERT INTO test1(f1,f2) VALUES(11,22)}
1.42 +
1.43 +
1.44 +# Make sure the columns are extracted correctly.
1.45 +#
1.46 +do_test select1-1.4 {
1.47 + execsql {SELECT f1 FROM test1}
1.48 +} {11}
1.49 +do_test select1-1.5 {
1.50 + execsql {SELECT f2 FROM test1}
1.51 +} {22}
1.52 +do_test select1-1.6 {
1.53 + execsql {SELECT f2, f1 FROM test1}
1.54 +} {22 11}
1.55 +do_test select1-1.7 {
1.56 + execsql {SELECT f1, f2 FROM test1}
1.57 +} {11 22}
1.58 +do_test select1-1.8 {
1.59 + execsql {SELECT * FROM test1}
1.60 +} {11 22}
1.61 +do_test select1-1.8.1 {
1.62 + execsql {SELECT *, * FROM test1}
1.63 +} {11 22 11 22}
1.64 +do_test select1-1.8.2 {
1.65 + execsql {SELECT *, min(f1,f2), max(f1,f2) FROM test1}
1.66 +} {11 22 11 22}
1.67 +do_test select1-1.8.3 {
1.68 + execsql {SELECT 'one', *, 'two', * FROM test1}
1.69 +} {one 11 22 two 11 22}
1.70 +
1.71 +execsql {CREATE TABLE test2(r1 real, r2 real)}
1.72 +execsql {INSERT INTO test2(r1,r2) VALUES(1.1,2.2)}
1.73 +
1.74 +do_test select1-1.9 {
1.75 + execsql {SELECT * FROM test1, test2}
1.76 +} {11 22 1.1 2.2}
1.77 +do_test select1-1.9.1 {
1.78 + execsql {SELECT *, 'hi' FROM test1, test2}
1.79 +} {11 22 1.1 2.2 hi}
1.80 +do_test select1-1.9.2 {
1.81 + execsql {SELECT 'one', *, 'two', * FROM test1, test2}
1.82 +} {one 11 22 1.1 2.2 two 11 22 1.1 2.2}
1.83 +do_test select1-1.10 {
1.84 + execsql {SELECT test1.f1, test2.r1 FROM test1, test2}
1.85 +} {11 1.1}
1.86 +do_test select1-1.11 {
1.87 + execsql {SELECT test1.f1, test2.r1 FROM test2, test1}
1.88 +} {11 1.1}
1.89 +do_test select1-1.11.1 {
1.90 + execsql {SELECT * FROM test2, test1}
1.91 +} {1.1 2.2 11 22}
1.92 +do_test select1-1.11.2 {
1.93 + execsql {SELECT * FROM test1 AS a, test1 AS b}
1.94 +} {11 22 11 22}
1.95 +do_test select1-1.12 {
1.96 + execsql {SELECT max(test1.f1,test2.r1), min(test1.f2,test2.r2)
1.97 + FROM test2, test1}
1.98 +} {11 2.2}
1.99 +do_test select1-1.13 {
1.100 + execsql {SELECT min(test1.f1,test2.r1), max(test1.f2,test2.r2)
1.101 + FROM test1, test2}
1.102 +} {1.1 22}
1.103 +
1.104 +set long {This is a string that is too big to fit inside a NBFS buffer}
1.105 +do_test select1-2.0 {
1.106 + execsql "
1.107 + DROP TABLE test2;
1.108 + DELETE FROM test1;
1.109 + INSERT INTO test1 VALUES(11,22);
1.110 + INSERT INTO test1 VALUES(33,44);
1.111 + CREATE TABLE t3(a,b);
1.112 + INSERT INTO t3 VALUES('abc',NULL);
1.113 + INSERT INTO t3 VALUES(NULL,'xyz');
1.114 + INSERT INTO t3 SELECT * FROM test1;
1.115 + CREATE TABLE t4(a,b);
1.116 + INSERT INTO t4 VALUES(NULL,'$long');
1.117 + SELECT * FROM t3;
1.118 + "
1.119 +} {abc {} {} xyz 11 22 33 44}
1.120 +
1.121 +# Error messges from sqliteExprCheck
1.122 +#
1.123 +do_test select1-2.1 {
1.124 + set v [catch {execsql {SELECT count(f1,f2) FROM test1}} msg]
1.125 + lappend v $msg
1.126 +} {1 {wrong number of arguments to function count()}}
1.127 +do_test select1-2.2 {
1.128 + set v [catch {execsql {SELECT count(f1) FROM test1}} msg]
1.129 + lappend v $msg
1.130 +} {0 2}
1.131 +do_test select1-2.3 {
1.132 + set v [catch {execsql {SELECT Count() FROM test1}} msg]
1.133 + lappend v $msg
1.134 +} {0 2}
1.135 +do_test select1-2.4 {
1.136 + set v [catch {execsql {SELECT COUNT(*) FROM test1}} msg]
1.137 + lappend v $msg
1.138 +} {0 2}
1.139 +do_test select1-2.5 {
1.140 + set v [catch {execsql {SELECT COUNT(*)+1 FROM test1}} msg]
1.141 + lappend v $msg
1.142 +} {0 3}
1.143 +do_test select1-2.5.1 {
1.144 + execsql {SELECT count(*),count(a),count(b) FROM t3}
1.145 +} {4 3 3}
1.146 +do_test select1-2.5.2 {
1.147 + execsql {SELECT count(*),count(a),count(b) FROM t4}
1.148 +} {1 0 1}
1.149 +do_test select1-2.5.3 {
1.150 + execsql {SELECT count(*),count(a),count(b) FROM t4 WHERE b=5}
1.151 +} {0 0 0}
1.152 +do_test select1-2.6 {
1.153 + set v [catch {execsql {SELECT min(*) FROM test1}} msg]
1.154 + lappend v $msg
1.155 +} {1 {wrong number of arguments to function min()}}
1.156 +do_test select1-2.7 {
1.157 + set v [catch {execsql {SELECT Min(f1) FROM test1}} msg]
1.158 + lappend v $msg
1.159 +} {0 11}
1.160 +do_test select1-2.8 {
1.161 + set v [catch {execsql {SELECT MIN(f1,f2) FROM test1}} msg]
1.162 + lappend v [lsort $msg]
1.163 +} {0 {11 33}}
1.164 +do_test select1-2.8.1 {
1.165 + execsql {SELECT coalesce(min(a),'xyzzy') FROM t3}
1.166 +} {11}
1.167 +do_test select1-2.8.2 {
1.168 + execsql {SELECT min(coalesce(a,'xyzzy')) FROM t3}
1.169 +} {11}
1.170 +do_test select1-2.8.3 {
1.171 + execsql {SELECT min(b), min(b) FROM t4}
1.172 +} [list $long $long]
1.173 +do_test select1-2.9 {
1.174 + set v [catch {execsql {SELECT MAX(*) FROM test1}} msg]
1.175 + lappend v $msg
1.176 +} {1 {wrong number of arguments to function MAX()}}
1.177 +do_test select1-2.10 {
1.178 + set v [catch {execsql {SELECT Max(f1) FROM test1}} msg]
1.179 + lappend v $msg
1.180 +} {0 33}
1.181 +do_test select1-2.11 {
1.182 + set v [catch {execsql {SELECT max(f1,f2) FROM test1}} msg]
1.183 + lappend v [lsort $msg]
1.184 +} {0 {22 44}}
1.185 +do_test select1-2.12 {
1.186 + set v [catch {execsql {SELECT MAX(f1,f2)+1 FROM test1}} msg]
1.187 + lappend v [lsort $msg]
1.188 +} {0 {23 45}}
1.189 +do_test select1-2.13 {
1.190 + set v [catch {execsql {SELECT MAX(f1)+1 FROM test1}} msg]
1.191 + lappend v $msg
1.192 +} {0 34}
1.193 +do_test select1-2.13.1 {
1.194 + execsql {SELECT coalesce(max(a),'xyzzy') FROM t3}
1.195 +} {abc}
1.196 +do_test select1-2.13.2 {
1.197 + execsql {SELECT max(coalesce(a,'xyzzy')) FROM t3}
1.198 +} {xyzzy}
1.199 +do_test select1-2.14 {
1.200 + set v [catch {execsql {SELECT SUM(*) FROM test1}} msg]
1.201 + lappend v $msg
1.202 +} {1 {wrong number of arguments to function SUM()}}
1.203 +do_test select1-2.15 {
1.204 + set v [catch {execsql {SELECT Sum(f1) FROM test1}} msg]
1.205 + lappend v $msg
1.206 +} {0 44}
1.207 +do_test select1-2.16 {
1.208 + set v [catch {execsql {SELECT sum(f1,f2) FROM test1}} msg]
1.209 + lappend v $msg
1.210 +} {1 {wrong number of arguments to function sum()}}
1.211 +do_test select1-2.17 {
1.212 + set v [catch {execsql {SELECT SUM(f1)+1 FROM test1}} msg]
1.213 + lappend v $msg
1.214 +} {0 45}
1.215 +do_test select1-2.17.1 {
1.216 + execsql {SELECT sum(a) FROM t3}
1.217 +} {44.0}
1.218 +do_test select1-2.18 {
1.219 + set v [catch {execsql {SELECT XYZZY(f1) FROM test1}} msg]
1.220 + lappend v $msg
1.221 +} {1 {no such function: XYZZY}}
1.222 +do_test select1-2.19 {
1.223 + set v [catch {execsql {SELECT SUM(min(f1,f2)) FROM test1}} msg]
1.224 + lappend v $msg
1.225 +} {0 44}
1.226 +do_test select1-2.20 {
1.227 + set v [catch {execsql {SELECT SUM(min(f1)) FROM test1}} msg]
1.228 + lappend v $msg
1.229 +} {1 {misuse of aggregate function min()}}
1.230 +
1.231 +# Ticket #2526
1.232 +#
1.233 +do_test select1-2.21 {
1.234 + catchsql {
1.235 + SELECT min(f1) AS m FROM test1 GROUP BY f1 HAVING max(m+5)<10
1.236 + }
1.237 +} {1 {misuse of aliased aggregate m}}
1.238 +do_test select1-2.22 {
1.239 + catchsql {
1.240 + SELECT coalesce(min(f1)+5,11) AS m FROM test1
1.241 + GROUP BY f1
1.242 + HAVING max(m+5)<10
1.243 + }
1.244 +} {1 {misuse of aliased aggregate m}}
1.245 +do_test select1-2.23 {
1.246 + execsql {
1.247 + CREATE TABLE tkt2526(a,b,c PRIMARY KEY);
1.248 + INSERT INTO tkt2526 VALUES('x','y',NULL);
1.249 + INSERT INTO tkt2526 VALUES('x','z',NULL);
1.250 + }
1.251 + catchsql {
1.252 + SELECT count(a) AS cn FROM tkt2526 GROUP BY a HAVING cn<max(cn)
1.253 + }
1.254 +} {1 {misuse of aliased aggregate cn}}
1.255 +
1.256 +# WHERE clause expressions
1.257 +#
1.258 +do_test select1-3.1 {
1.259 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<11}} msg]
1.260 + lappend v $msg
1.261 +} {0 {}}
1.262 +do_test select1-3.2 {
1.263 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1<=11}} msg]
1.264 + lappend v $msg
1.265 +} {0 11}
1.266 +do_test select1-3.3 {
1.267 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1=11}} msg]
1.268 + lappend v $msg
1.269 +} {0 11}
1.270 +do_test select1-3.4 {
1.271 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>=11}} msg]
1.272 + lappend v [lsort $msg]
1.273 +} {0 {11 33}}
1.274 +do_test select1-3.5 {
1.275 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1>11}} msg]
1.276 + lappend v [lsort $msg]
1.277 +} {0 33}
1.278 +do_test select1-3.6 {
1.279 + set v [catch {execsql {SELECT f1 FROM test1 WHERE f1!=11}} msg]
1.280 + lappend v [lsort $msg]
1.281 +} {0 33}
1.282 +do_test select1-3.7 {
1.283 + set v [catch {execsql {SELECT f1 FROM test1 WHERE min(f1,f2)!=11}} msg]
1.284 + lappend v [lsort $msg]
1.285 +} {0 33}
1.286 +do_test select1-3.8 {
1.287 + set v [catch {execsql {SELECT f1 FROM test1 WHERE max(f1,f2)!=11}} msg]
1.288 + lappend v [lsort $msg]
1.289 +} {0 {11 33}}
1.290 +do_test select1-3.9 {
1.291 + set v [catch {execsql {SELECT f1 FROM test1 WHERE count(f1,f2)!=11}} msg]
1.292 + lappend v $msg
1.293 +} {1 {wrong number of arguments to function count()}}
1.294 +
1.295 +# ORDER BY expressions
1.296 +#
1.297 +do_test select1-4.1 {
1.298 + set v [catch {execsql {SELECT f1 FROM test1 ORDER BY f1}} msg]
1.299 + lappend v $msg
1.300 +} {0 {11 33}}
1.301 +do_test select1-4.2 {
1.302 + set v [catch {execsql {SELECT f1 FROM test1 ORDER BY -f1}} msg]
1.303 + lappend v $msg
1.304 +} {0 {33 11}}
1.305 +do_test select1-4.3 {
1.306 + set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1,f2)}} msg]
1.307 + lappend v $msg
1.308 +} {0 {11 33}}
1.309 +do_test select1-4.4 {
1.310 + set v [catch {execsql {SELECT f1 FROM test1 ORDER BY min(f1)}} msg]
1.311 + lappend v $msg
1.312 +} {1 {misuse of aggregate: min(f1)}}
1.313 +
1.314 +# The restriction not allowing constants in the ORDER BY clause
1.315 +# has been removed. See ticket #1768
1.316 +#do_test select1-4.5 {
1.317 +# catchsql {
1.318 +# SELECT f1 FROM test1 ORDER BY 8.4;
1.319 +# }
1.320 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.321 +#do_test select1-4.6 {
1.322 +# catchsql {
1.323 +# SELECT f1 FROM test1 ORDER BY '8.4';
1.324 +# }
1.325 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.326 +#do_test select1-4.7.1 {
1.327 +# catchsql {
1.328 +# SELECT f1 FROM test1 ORDER BY 'xyz';
1.329 +# }
1.330 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.331 +#do_test select1-4.7.2 {
1.332 +# catchsql {
1.333 +# SELECT f1 FROM test1 ORDER BY -8.4;
1.334 +# }
1.335 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.336 +#do_test select1-4.7.3 {
1.337 +# catchsql {
1.338 +# SELECT f1 FROM test1 ORDER BY +8.4;
1.339 +# }
1.340 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.341 +#do_test select1-4.7.4 {
1.342 +# catchsql {
1.343 +# SELECT f1 FROM test1 ORDER BY 4294967296; -- constant larger than 32 bits
1.344 +# }
1.345 +#} {1 {ORDER BY terms must not be non-integer constants}}
1.346 +
1.347 +do_test select1-4.5 {
1.348 + execsql {
1.349 + SELECT f1 FROM test1 ORDER BY 8.4
1.350 + }
1.351 +} {11 33}
1.352 +do_test select1-4.6 {
1.353 + execsql {
1.354 + SELECT f1 FROM test1 ORDER BY '8.4'
1.355 + }
1.356 +} {11 33}
1.357 +
1.358 +do_test select1-4.8 {
1.359 + execsql {
1.360 + CREATE TABLE t5(a,b);
1.361 + INSERT INTO t5 VALUES(1,10);
1.362 + INSERT INTO t5 VALUES(2,9);
1.363 + SELECT * FROM t5 ORDER BY 1;
1.364 + }
1.365 +} {1 10 2 9}
1.366 +do_test select1-4.9.1 {
1.367 + execsql {
1.368 + SELECT * FROM t5 ORDER BY 2;
1.369 + }
1.370 +} {2 9 1 10}
1.371 +do_test select1-4.9.2 {
1.372 + execsql {
1.373 + SELECT * FROM t5 ORDER BY +2;
1.374 + }
1.375 +} {2 9 1 10}
1.376 +do_test select1-4.10.1 {
1.377 + catchsql {
1.378 + SELECT * FROM t5 ORDER BY 3;
1.379 + }
1.380 +} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
1.381 +do_test select1-4.10.2 {
1.382 + catchsql {
1.383 + SELECT * FROM t5 ORDER BY -1;
1.384 + }
1.385 +} {1 {1st ORDER BY term out of range - should be between 1 and 2}}
1.386 +do_test select1-4.11 {
1.387 + execsql {
1.388 + INSERT INTO t5 VALUES(3,10);
1.389 + SELECT * FROM t5 ORDER BY 2, 1 DESC;
1.390 + }
1.391 +} {2 9 3 10 1 10}
1.392 +do_test select1-4.12 {
1.393 + execsql {
1.394 + SELECT * FROM t5 ORDER BY 1 DESC, b;
1.395 + }
1.396 +} {3 10 2 9 1 10}
1.397 +do_test select1-4.13 {
1.398 + execsql {
1.399 + SELECT * FROM t5 ORDER BY b DESC, 1;
1.400 + }
1.401 +} {1 10 3 10 2 9}
1.402 +
1.403 +
1.404 +# ORDER BY ignored on an aggregate query
1.405 +#
1.406 +do_test select1-5.1 {
1.407 + set v [catch {execsql {SELECT max(f1) FROM test1 ORDER BY f2}} msg]
1.408 + lappend v $msg
1.409 +} {0 33}
1.410 +
1.411 +execsql {CREATE TABLE test2(t1 text, t2 text)}
1.412 +execsql {INSERT INTO test2 VALUES('abc','xyz')}
1.413 +
1.414 +# Check for column naming
1.415 +#
1.416 +do_test select1-6.1 {
1.417 + set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
1.418 + lappend v $msg
1.419 +} {0 {f1 11 f1 33}}
1.420 +do_test select1-6.1.1 {
1.421 + db eval {PRAGMA full_column_names=on}
1.422 + set v [catch {execsql2 {SELECT f1 FROM test1 ORDER BY f2}} msg]
1.423 + lappend v $msg
1.424 +} {0 {test1.f1 11 test1.f1 33}}
1.425 +do_test select1-6.1.2 {
1.426 + set v [catch {execsql2 {SELECT f1 as 'f1' FROM test1 ORDER BY f2}} msg]
1.427 + lappend v $msg
1.428 +} {0 {f1 11 f1 33}}
1.429 +do_test select1-6.1.3 {
1.430 + set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
1.431 + lappend v $msg
1.432 +} {0 {f1 11 f2 22}}
1.433 +do_test select1-6.1.4 {
1.434 + set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
1.435 + db eval {PRAGMA full_column_names=off}
1.436 + lappend v $msg
1.437 +} {0 {f1 11 f2 22}}
1.438 +do_test select1-6.1.5 {
1.439 + set v [catch {execsql2 {SELECT * FROM test1 WHERE f1==11}} msg]
1.440 + lappend v $msg
1.441 +} {0 {f1 11 f2 22}}
1.442 +do_test select1-6.1.6 {
1.443 + set v [catch {execsql2 {SELECT DISTINCT * FROM test1 WHERE f1==11}} msg]
1.444 + lappend v $msg
1.445 +} {0 {f1 11 f2 22}}
1.446 +do_test select1-6.2 {
1.447 + set v [catch {execsql2 {SELECT f1 as xyzzy FROM test1 ORDER BY f2}} msg]
1.448 + lappend v $msg
1.449 +} {0 {xyzzy 11 xyzzy 33}}
1.450 +do_test select1-6.3 {
1.451 + set v [catch {execsql2 {SELECT f1 as "xyzzy" FROM test1 ORDER BY f2}} msg]
1.452 + lappend v $msg
1.453 +} {0 {xyzzy 11 xyzzy 33}}
1.454 +do_test select1-6.3.1 {
1.455 + set v [catch {execsql2 {SELECT f1 as 'xyzzy ' FROM test1 ORDER BY f2}} msg]
1.456 + lappend v $msg
1.457 +} {0 {{xyzzy } 11 {xyzzy } 33}}
1.458 +do_test select1-6.4 {
1.459 + set v [catch {execsql2 {SELECT f1+F2 as xyzzy FROM test1 ORDER BY f2}} msg]
1.460 + lappend v $msg
1.461 +} {0 {xyzzy 33 xyzzy 77}}
1.462 +do_test select1-6.4a {
1.463 + set v [catch {execsql2 {SELECT f1+F2 FROM test1 ORDER BY f2}} msg]
1.464 + lappend v $msg
1.465 +} {0 {f1+F2 33 f1+F2 77}}
1.466 +do_test select1-6.5 {
1.467 + set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
1.468 + lappend v $msg
1.469 +} {0 {test1.f1+F2 33 test1.f1+F2 77}}
1.470 +do_test select1-6.5.1 {
1.471 + execsql2 {PRAGMA full_column_names=on}
1.472 + set v [catch {execsql2 {SELECT test1.f1+F2 FROM test1 ORDER BY f2}} msg]
1.473 + execsql2 {PRAGMA full_column_names=off}
1.474 + lappend v $msg
1.475 +} {0 {test1.f1+F2 33 test1.f1+F2 77}}
1.476 +do_test select1-6.6 {
1.477 + set v [catch {execsql2 {SELECT test1.f1+F2, t1 FROM test1, test2
1.478 + ORDER BY f2}} msg]
1.479 + lappend v $msg
1.480 +} {0 {test1.f1+F2 33 t1 abc test1.f1+F2 77 t1 abc}}
1.481 +do_test select1-6.7 {
1.482 + set v [catch {execsql2 {SELECT A.f1, t1 FROM test1 as A, test2
1.483 + ORDER BY f2}} msg]
1.484 + lappend v $msg
1.485 +} {0 {f1 11 t1 abc f1 33 t1 abc}}
1.486 +do_test select1-6.8 {
1.487 + set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as B
1.488 + ORDER BY f2}} msg]
1.489 + lappend v $msg
1.490 +} {1 {ambiguous column name: f1}}
1.491 +do_test select1-6.8b {
1.492 + set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
1.493 + ORDER BY f2}} msg]
1.494 + lappend v $msg
1.495 +} {1 {ambiguous column name: f2}}
1.496 +do_test select1-6.8c {
1.497 + set v [catch {execsql2 {SELECT A.f1, f1 FROM test1 as A, test1 as A
1.498 + ORDER BY f2}} msg]
1.499 + lappend v $msg
1.500 +} {1 {ambiguous column name: A.f1}}
1.501 +do_test select1-6.9.1 {
1.502 + set v [catch {execsql {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
1.503 + ORDER BY A.f1, B.f1}} msg]
1.504 + lappend v $msg
1.505 +} {0 {11 11 11 33 33 11 33 33}}
1.506 +do_test select1-6.9.2 {
1.507 + set v [catch {execsql2 {SELECT A.f1, B.f1 FROM test1 as A, test1 as B
1.508 + ORDER BY A.f1, B.f1}} msg]
1.509 + lappend v $msg
1.510 +} {0 {f1 11 f1 11 f1 33 f1 33 f1 11 f1 11 f1 33 f1 33}}
1.511 +
1.512 +do_test select1-6.9.3 {
1.513 + db eval {
1.514 + PRAGMA short_column_names=OFF;
1.515 + PRAGMA full_column_names=OFF;
1.516 + }
1.517 + execsql2 {
1.518 + SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
1.519 + }
1.520 +} {{test1 . f1} 11 {test1 . f2} 22}
1.521 +do_test select1-6.9.4 {
1.522 + db eval {
1.523 + PRAGMA short_column_names=OFF;
1.524 + PRAGMA full_column_names=ON;
1.525 + }
1.526 + execsql2 {
1.527 + SELECT test1 . f1, test1 . f2 FROM test1 LIMIT 1
1.528 + }
1.529 +} {test1.f1 11 test1.f2 22}
1.530 +do_test select1-6.9.5 {
1.531 + db eval {
1.532 + PRAGMA short_column_names=OFF;
1.533 + PRAGMA full_column_names=ON;
1.534 + }
1.535 + execsql2 {
1.536 + SELECT 123.45;
1.537 + }
1.538 +} {123.45 123.45}
1.539 +do_test select1-6.9.6 {
1.540 + execsql2 {
1.541 + SELECT * FROM test1 a, test1 b LIMIT 1
1.542 + }
1.543 +} {a.f1 11 a.f2 22 b.f1 11 b.f2 22}
1.544 +do_test select1-6.9.7 {
1.545 + set x [execsql2 {
1.546 + SELECT * FROM test1 a, (select 5, 6) LIMIT 1
1.547 + }]
1.548 + regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
1.549 + set x
1.550 +} {a.f1 11 a.f2 22 sqlite_subquery.5 5 sqlite_subquery.6 6}
1.551 +do_test select1-6.9.8 {
1.552 + set x [execsql2 {
1.553 + SELECT * FROM test1 a, (select 5 AS x, 6 AS y) AS b LIMIT 1
1.554 + }]
1.555 + regsub -all {subquery_[0-9a-fA-F]+_} $x {subquery} x
1.556 + set x
1.557 +} {a.f1 11 a.f2 22 b.x 5 b.y 6}
1.558 +do_test select1-6.9.9 {
1.559 + execsql2 {
1.560 + SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
1.561 + }
1.562 +} {test1.f1 11 test1.f2 22}
1.563 +do_test select1-6.9.10 {
1.564 + execsql2 {
1.565 + SELECT f1, t1 FROM test1, test2 LIMIT 1
1.566 + }
1.567 +} {test1.f1 11 test2.t1 abc}
1.568 +do_test select1-6.9.11 {
1.569 + db eval {
1.570 + PRAGMA short_column_names=ON;
1.571 + PRAGMA full_column_names=ON;
1.572 + }
1.573 + execsql2 {
1.574 + SELECT a.f1, b.f2 FROM test1 a, test1 b LIMIT 1
1.575 + }
1.576 +} {test1.f1 11 test1.f2 22}
1.577 +do_test select1-6.9.12 {
1.578 + execsql2 {
1.579 + SELECT f1, t1 FROM test1, test2 LIMIT 1
1.580 + }
1.581 +} {test1.f1 11 test2.t1 abc}
1.582 +do_test select1-6.9.13 {
1.583 + db eval {
1.584 + PRAGMA short_column_names=ON;
1.585 + PRAGMA full_column_names=OFF;
1.586 + }
1.587 + execsql2 {
1.588 + SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
1.589 + }
1.590 +} {f1 11 f1 11}
1.591 +do_test select1-6.9.14 {
1.592 + execsql2 {
1.593 + SELECT f1, t1 FROM test1, test2 LIMIT 1
1.594 + }
1.595 +} {f1 11 t1 abc}
1.596 +do_test select1-6.9.15 {
1.597 + db eval {
1.598 + PRAGMA short_column_names=OFF;
1.599 + PRAGMA full_column_names=ON;
1.600 + }
1.601 + execsql2 {
1.602 + SELECT a.f1, b.f1 FROM test1 a, test1 b LIMIT 1
1.603 + }
1.604 +} {test1.f1 11 test1.f1 11}
1.605 +do_test select1-6.9.16 {
1.606 + execsql2 {
1.607 + SELECT f1, t1 FROM test1, test2 LIMIT 1
1.608 + }
1.609 +} {test1.f1 11 test2.t1 abc}
1.610 +
1.611 +
1.612 +db eval {
1.613 + PRAGMA short_column_names=ON;
1.614 + PRAGMA full_column_names=OFF;
1.615 +}
1.616 +
1.617 +ifcapable compound {
1.618 +do_test select1-6.10 {
1.619 + set v [catch {execsql2 {
1.620 + SELECT f1 FROM test1 UNION SELECT f2 FROM test1
1.621 + ORDER BY f2;
1.622 + }} msg]
1.623 + lappend v $msg
1.624 +} {0 {f1 11 f1 22 f1 33 f1 44}}
1.625 +do_test select1-6.11 {
1.626 + set v [catch {execsql2 {
1.627 + SELECT f1 FROM test1 UNION SELECT f2+100 FROM test1
1.628 + ORDER BY f2+101;
1.629 + }} msg]
1.630 + lappend v $msg
1.631 +} {1 {1st ORDER BY term does not match any column in the result set}}
1.632 +
1.633 +# Ticket #2296
1.634 +ifcapable subquery&&compound {
1.635 +do_test select1-6.20 {
1.636 + execsql {
1.637 + CREATE TABLE t6(a TEXT, b TEXT);
1.638 + INSERT INTO t6 VALUES('a','0');
1.639 + INSERT INTO t6 VALUES('b','1');
1.640 + INSERT INTO t6 VALUES('c','2');
1.641 + INSERT INTO t6 VALUES('d','3');
1.642 + SELECT a FROM t6 WHERE b IN
1.643 + (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
1.644 + ORDER BY 1 LIMIT 1)
1.645 + }
1.646 +} {a}
1.647 +do_test select1-6.21 {
1.648 + execsql {
1.649 + SELECT a FROM t6 WHERE b IN
1.650 + (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
1.651 + ORDER BY 1 DESC LIMIT 1)
1.652 + }
1.653 +} {d}
1.654 +do_test select1-6.22 {
1.655 + execsql {
1.656 + SELECT a FROM t6 WHERE b IN
1.657 + (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
1.658 + ORDER BY b LIMIT 2)
1.659 + ORDER BY a;
1.660 + }
1.661 +} {a b}
1.662 +do_test select1-6.23 {
1.663 + execsql {
1.664 + SELECT a FROM t6 WHERE b IN
1.665 + (SELECT b FROM t6 WHERE a<='b' UNION SELECT '3' AS x
1.666 + ORDER BY x DESC LIMIT 2)
1.667 + ORDER BY a;
1.668 + }
1.669 +} {b d}
1.670 +}
1.671 +
1.672 +} ;#ifcapable compound
1.673 +
1.674 +do_test select1-7.1 {
1.675 + set v [catch {execsql {
1.676 + SELECT f1 FROM test1 WHERE f2=;
1.677 + }} msg]
1.678 + lappend v $msg
1.679 +} {1 {near ";": syntax error}}
1.680 +ifcapable compound {
1.681 +do_test select1-7.2 {
1.682 + set v [catch {execsql {
1.683 + SELECT f1 FROM test1 UNION SELECT WHERE;
1.684 + }} msg]
1.685 + lappend v $msg
1.686 +} {1 {near "WHERE": syntax error}}
1.687 +} ;# ifcapable compound
1.688 +do_test select1-7.3 {
1.689 + set v [catch {execsql {SELECT f1 FROM test1 as 'hi', test2 as}} msg]
1.690 + lappend v $msg
1.691 +} {1 {near "as": syntax error}}
1.692 +do_test select1-7.4 {
1.693 + set v [catch {execsql {
1.694 + SELECT f1 FROM test1 ORDER BY;
1.695 + }} msg]
1.696 + lappend v $msg
1.697 +} {1 {near ";": syntax error}}
1.698 +do_test select1-7.5 {
1.699 + set v [catch {execsql {
1.700 + SELECT f1 FROM test1 ORDER BY f1 desc, f2 where;
1.701 + }} msg]
1.702 + lappend v $msg
1.703 +} {1 {near "where": syntax error}}
1.704 +do_test select1-7.6 {
1.705 + set v [catch {execsql {
1.706 + SELECT count(f1,f2 FROM test1;
1.707 + }} msg]
1.708 + lappend v $msg
1.709 +} {1 {near "FROM": syntax error}}
1.710 +do_test select1-7.7 {
1.711 + set v [catch {execsql {
1.712 + SELECT count(f1,f2+) FROM test1;
1.713 + }} msg]
1.714 + lappend v $msg
1.715 +} {1 {near ")": syntax error}}
1.716 +do_test select1-7.8 {
1.717 + set v [catch {execsql {
1.718 + SELECT f1 FROM test1 ORDER BY f2, f1+;
1.719 + }} msg]
1.720 + lappend v $msg
1.721 +} {1 {near ";": syntax error}}
1.722 +do_test select1-7.9 {
1.723 + catchsql {
1.724 + SELECT f1 FROM test1 LIMIT 5+3 OFFSET 11 ORDER BY f2;
1.725 + }
1.726 +} {1 {near "ORDER": syntax error}}
1.727 +
1.728 +do_test select1-8.1 {
1.729 + execsql {SELECT f1 FROM test1 WHERE 4.3+2.4 OR 1 ORDER BY f1}
1.730 +} {11 33}
1.731 +do_test select1-8.2 {
1.732 + execsql {
1.733 + SELECT f1 FROM test1 WHERE ('x' || f1) BETWEEN 'x10' AND 'x20'
1.734 + ORDER BY f1
1.735 + }
1.736 +} {11}
1.737 +do_test select1-8.3 {
1.738 + execsql {
1.739 + SELECT f1 FROM test1 WHERE 5-3==2
1.740 + ORDER BY f1
1.741 + }
1.742 +} {11 33}
1.743 +
1.744 +# TODO: This test is failing because f1 is now being loaded off the
1.745 +# disk as a vdbe integer, not a string. Hence the value of f1/(f1-11)
1.746 +# changes because of rounding. Disable the test for now.
1.747 +if 0 {
1.748 +do_test select1-8.4 {
1.749 + execsql {
1.750 + SELECT coalesce(f1/(f1-11),'x'),
1.751 + coalesce(min(f1/(f1-11),5),'y'),
1.752 + coalesce(max(f1/(f1-33),6),'z')
1.753 + FROM test1 ORDER BY f1
1.754 + }
1.755 +} {x y 6 1.5 1.5 z}
1.756 +}
1.757 +do_test select1-8.5 {
1.758 + execsql {
1.759 + SELECT min(1,2,3), -max(1,2,3)
1.760 + FROM test1 ORDER BY f1
1.761 + }
1.762 +} {1 -3 1 -3}
1.763 +
1.764 +
1.765 +# Check the behavior when the result set is empty
1.766 +#
1.767 +# SQLite v3 always sets r(*).
1.768 +#
1.769 +# do_test select1-9.1 {
1.770 +# catch {unset r}
1.771 +# set r(*) {}
1.772 +# db eval {SELECT * FROM test1 WHERE f1<0} r {}
1.773 +# set r(*)
1.774 +# } {}
1.775 +do_test select1-9.2 {
1.776 + execsql {PRAGMA empty_result_callbacks=on}
1.777 + catch {unset r}
1.778 + set r(*) {}
1.779 + db eval {SELECT * FROM test1 WHERE f1<0} r {}
1.780 + set r(*)
1.781 +} {f1 f2}
1.782 +ifcapable subquery {
1.783 + do_test select1-9.3 {
1.784 + set r(*) {}
1.785 + db eval {SELECT * FROM test1 WHERE f1<(select count(*) from test2)} r {}
1.786 + set r(*)
1.787 + } {f1 f2}
1.788 +}
1.789 +do_test select1-9.4 {
1.790 + set r(*) {}
1.791 + db eval {SELECT * FROM test1 ORDER BY f1} r {}
1.792 + set r(*)
1.793 +} {f1 f2}
1.794 +do_test select1-9.5 {
1.795 + set r(*) {}
1.796 + db eval {SELECT * FROM test1 WHERE f1<0 ORDER BY f1} r {}
1.797 + set r(*)
1.798 +} {f1 f2}
1.799 +unset r
1.800 +
1.801 +# Check for ORDER BY clauses that refer to an AS name in the column list
1.802 +#
1.803 +do_test select1-10.1 {
1.804 + execsql {
1.805 + SELECT f1 AS x FROM test1 ORDER BY x
1.806 + }
1.807 +} {11 33}
1.808 +do_test select1-10.2 {
1.809 + execsql {
1.810 + SELECT f1 AS x FROM test1 ORDER BY -x
1.811 + }
1.812 +} {33 11}
1.813 +do_test select1-10.3 {
1.814 + execsql {
1.815 + SELECT f1-23 AS x FROM test1 ORDER BY abs(x)
1.816 + }
1.817 +} {10 -12}
1.818 +do_test select1-10.4 {
1.819 + execsql {
1.820 + SELECT f1-23 AS x FROM test1 ORDER BY -abs(x)
1.821 + }
1.822 +} {-12 10}
1.823 +do_test select1-10.5 {
1.824 + execsql {
1.825 + SELECT f1-22 AS x, f2-22 as y FROM test1
1.826 + }
1.827 +} {-11 0 11 22}
1.828 +do_test select1-10.6 {
1.829 + execsql {
1.830 + SELECT f1-22 AS x, f2-22 as y FROM test1 WHERE x>0 AND y<50
1.831 + }
1.832 +} {11 22}
1.833 +do_test select1-10.7 {
1.834 + execsql {
1.835 + SELECT f1 COLLATE nocase AS x FROM test1 ORDER BY x
1.836 + }
1.837 +} {11 33}
1.838 +
1.839 +# Check the ability to specify "TABLE.*" in the result set of a SELECT
1.840 +#
1.841 +do_test select1-11.1 {
1.842 + execsql {
1.843 + DELETE FROM t3;
1.844 + DELETE FROM t4;
1.845 + INSERT INTO t3 VALUES(1,2);
1.846 + INSERT INTO t4 VALUES(3,4);
1.847 + SELECT * FROM t3, t4;
1.848 + }
1.849 +} {1 2 3 4}
1.850 +do_test select1-11.2.1 {
1.851 + execsql {
1.852 + SELECT * FROM t3, t4;
1.853 + }
1.854 +} {1 2 3 4}
1.855 +do_test select1-11.2.2 {
1.856 + execsql2 {
1.857 + SELECT * FROM t3, t4;
1.858 + }
1.859 +} {a 3 b 4 a 3 b 4}
1.860 +do_test select1-11.4.1 {
1.861 + execsql {
1.862 + SELECT t3.*, t4.b FROM t3, t4;
1.863 + }
1.864 +} {1 2 4}
1.865 +do_test select1-11.4.2 {
1.866 + execsql {
1.867 + SELECT "t3".*, t4.b FROM t3, t4;
1.868 + }
1.869 +} {1 2 4}
1.870 +do_test select1-11.5.1 {
1.871 + execsql2 {
1.872 + SELECT t3.*, t4.b FROM t3, t4;
1.873 + }
1.874 +} {a 1 b 4 b 4}
1.875 +do_test select1-11.6 {
1.876 + execsql2 {
1.877 + SELECT x.*, y.b FROM t3 AS x, t4 AS y;
1.878 + }
1.879 +} {a 1 b 4 b 4}
1.880 +do_test select1-11.7 {
1.881 + execsql {
1.882 + SELECT t3.b, t4.* FROM t3, t4;
1.883 + }
1.884 +} {2 3 4}
1.885 +do_test select1-11.8 {
1.886 + execsql2 {
1.887 + SELECT t3.b, t4.* FROM t3, t4;
1.888 + }
1.889 +} {b 4 a 3 b 4}
1.890 +do_test select1-11.9 {
1.891 + execsql2 {
1.892 + SELECT x.b, y.* FROM t3 AS x, t4 AS y;
1.893 + }
1.894 +} {b 4 a 3 b 4}
1.895 +do_test select1-11.10 {
1.896 + catchsql {
1.897 + SELECT t5.* FROM t3, t4;
1.898 + }
1.899 +} {1 {no such table: t5}}
1.900 +do_test select1-11.11 {
1.901 + catchsql {
1.902 + SELECT t3.* FROM t3 AS x, t4;
1.903 + }
1.904 +} {1 {no such table: t3}}
1.905 +ifcapable subquery {
1.906 + do_test select1-11.12 {
1.907 + execsql2 {
1.908 + SELECT t3.* FROM t3, (SELECT max(a), max(b) FROM t4)
1.909 + }
1.910 + } {a 1 b 2}
1.911 + do_test select1-11.13 {
1.912 + execsql2 {
1.913 + SELECT t3.* FROM (SELECT max(a), max(b) FROM t4), t3
1.914 + }
1.915 + } {a 1 b 2}
1.916 + do_test select1-11.14 {
1.917 + execsql2 {
1.918 + SELECT * FROM t3, (SELECT max(a), max(b) FROM t4) AS 'tx'
1.919 + }
1.920 + } {a 1 b 2 max(a) 3 max(b) 4}
1.921 + do_test select1-11.15 {
1.922 + execsql2 {
1.923 + SELECT y.*, t3.* FROM t3, (SELECT max(a), max(b) FROM t4) AS y
1.924 + }
1.925 + } {max(a) 3 max(b) 4 a 1 b 2}
1.926 +}
1.927 +do_test select1-11.16 {
1.928 + execsql2 {
1.929 + SELECT y.* FROM t3 as y, t4 as z
1.930 + }
1.931 +} {a 1 b 2}
1.932 +
1.933 +# Tests of SELECT statements without a FROM clause.
1.934 +#
1.935 +do_test select1-12.1 {
1.936 + execsql2 {
1.937 + SELECT 1+2+3
1.938 + }
1.939 +} {1+2+3 6}
1.940 +do_test select1-12.2 {
1.941 + execsql2 {
1.942 + SELECT 1,'hello',2
1.943 + }
1.944 +} {1 1 'hello' hello 2 2}
1.945 +do_test select1-12.3 {
1.946 + execsql2 {
1.947 + SELECT 1 AS 'a','hello' AS 'b',2 AS 'c'
1.948 + }
1.949 +} {a 1 b hello c 2}
1.950 +do_test select1-12.4 {
1.951 + execsql {
1.952 + DELETE FROM t3;
1.953 + INSERT INTO t3 VALUES(1,2);
1.954 + }
1.955 +} {}
1.956 +
1.957 +ifcapable compound {
1.958 +do_test select1-12.5 {
1.959 + execsql {
1.960 + SELECT * FROM t3 UNION SELECT 3 AS 'a', 4 ORDER BY a;
1.961 + }
1.962 +} {1 2 3 4}
1.963 +
1.964 +do_test select1-12.6 {
1.965 + execsql {
1.966 + SELECT 3, 4 UNION SELECT * FROM t3;
1.967 + }
1.968 +} {1 2 3 4}
1.969 +} ;# ifcapable compound
1.970 +
1.971 +ifcapable subquery {
1.972 + do_test select1-12.7 {
1.973 + execsql {
1.974 + SELECT * FROM t3 WHERE a=(SELECT 1);
1.975 + }
1.976 + } {1 2}
1.977 + do_test select1-12.8 {
1.978 + execsql {
1.979 + SELECT * FROM t3 WHERE a=(SELECT 2);
1.980 + }
1.981 + } {}
1.982 +}
1.983 +
1.984 +ifcapable {compound && subquery} {
1.985 + do_test select1-12.9 {
1.986 + execsql2 {
1.987 + SELECT x FROM (
1.988 + SELECT a AS x, b AS y FROM t3 UNION SELECT a,b FROM t4 ORDER BY a,b
1.989 + ) ORDER BY x;
1.990 + }
1.991 + } {x 1 x 3}
1.992 + do_test select1-12.10 {
1.993 + execsql2 {
1.994 + SELECT z.x FROM (
1.995 + SELECT a AS x,b AS y FROM t3 UNION SELECT a, b FROM t4 ORDER BY a,b
1.996 + ) AS 'z' ORDER BY x;
1.997 + }
1.998 + } {x 1 x 3}
1.999 +} ;# ifcapable compound
1.1000 +
1.1001 +
1.1002 +# Check for a VDBE stack growth problem that existed at one point.
1.1003 +#
1.1004 +ifcapable subquery {
1.1005 + do_test select1-13.1 {
1.1006 + execsql {
1.1007 + BEGIN;
1.1008 + create TABLE abc(a, b, c, PRIMARY KEY(a, b));
1.1009 + INSERT INTO abc VALUES(1, 1, 1);
1.1010 + }
1.1011 + for {set i 0} {$i<10} {incr i} {
1.1012 + execsql {
1.1013 + INSERT INTO abc SELECT a+(select max(a) FROM abc),
1.1014 + b+(select max(a) FROM abc), c+(select max(a) FROM abc) FROM abc;
1.1015 + }
1.1016 + }
1.1017 + execsql {COMMIT}
1.1018 +
1.1019 + # This used to seg-fault when the problem existed.
1.1020 + execsql {
1.1021 + SELECT count(
1.1022 + (SELECT a FROM abc WHERE a = NULL AND b >= upper.c)
1.1023 + ) FROM abc AS upper;
1.1024 + }
1.1025 + } {0}
1.1026 +}
1.1027 +
1.1028 +db close
1.1029 +file delete -force test.db
1.1030 +sqlite3 db test.db
1.1031 +do_test select1-14.1 {
1.1032 + execsql {
1.1033 + SELECT * FROM sqlite_master WHERE rowid>10;
1.1034 + SELECT * FROM sqlite_master WHERE rowid=10;
1.1035 + SELECT * FROM sqlite_master WHERE rowid<10;
1.1036 + SELECT * FROM sqlite_master WHERE rowid<=10;
1.1037 + SELECT * FROM sqlite_master WHERE rowid>=10;
1.1038 + SELECT * FROM sqlite_master;
1.1039 + }
1.1040 +} {}
1.1041 +do_test select1-14.2 {
1.1042 + execsql {
1.1043 + SELECT 10 IN (SELECT rowid FROM sqlite_master);
1.1044 + }
1.1045 +} {0}
1.1046 +
1.1047 +finish_test