1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/subselect.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,210 @@
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 SELECT statements that are part of
1.16 +# expressions.
1.17 +#
1.18 +# $Id: subselect.test,v 1.16 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 +# Omit this whole file if the library is build without subquery support.
1.24 +ifcapable !subquery {
1.25 + finish_test
1.26 + return
1.27 +}
1.28 +
1.29 +# Basic sanity checking. Try a simple subselect.
1.30 +#
1.31 +do_test subselect-1.1 {
1.32 + execsql {
1.33 + CREATE TABLE t1(a int, b int);
1.34 + INSERT INTO t1 VALUES(1,2);
1.35 + INSERT INTO t1 VALUES(3,4);
1.36 + INSERT INTO t1 VALUES(5,6);
1.37 + }
1.38 + execsql {SELECT * FROM t1 WHERE a = (SELECT count(*) FROM t1)}
1.39 +} {3 4}
1.40 +
1.41 +# Try a select with more than one result column.
1.42 +#
1.43 +do_test subselect-1.2 {
1.44 + set v [catch {execsql {SELECT * FROM t1 WHERE a = (SELECT * FROM t1)}} msg]
1.45 + lappend v $msg
1.46 +} {1 {only a single result allowed for a SELECT that is part of an expression}}
1.47 +
1.48 +# A subselect without an aggregate.
1.49 +#
1.50 +do_test subselect-1.3a {
1.51 + execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=2)}
1.52 +} {2}
1.53 +do_test subselect-1.3b {
1.54 + execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=4)}
1.55 +} {4}
1.56 +do_test subselect-1.3c {
1.57 + execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=6)}
1.58 +} {6}
1.59 +do_test subselect-1.3d {
1.60 + execsql {SELECT b from t1 where a = (SELECT a FROM t1 WHERE b=8)}
1.61 +} {}
1.62 +ifcapable compound {
1.63 + do_test subselect-1.3e {
1.64 + execsql {
1.65 + SELECT b FROM t1
1.66 + WHERE a = (SELECT a FROM t1 UNION SELECT b FROM t1 ORDER BY 1);
1.67 + }
1.68 + } {2}
1.69 +}
1.70 +
1.71 +# What if the subselect doesn't return any value. We should get
1.72 +# NULL as the result. Check it out.
1.73 +#
1.74 +do_test subselect-1.4 {
1.75 + execsql {SELECT b from t1 where a = coalesce((SELECT a FROM t1 WHERE b=5),1)}
1.76 +} {2}
1.77 +
1.78 +# Try multiple subselects within a single expression.
1.79 +#
1.80 +do_test subselect-1.5 {
1.81 + execsql {
1.82 + CREATE TABLE t2(x int, y int);
1.83 + INSERT INTO t2 VALUES(1,2);
1.84 + INSERT INTO t2 VALUES(2,4);
1.85 + INSERT INTO t2 VALUES(3,8);
1.86 + INSERT INTO t2 VALUES(4,16);
1.87 + }
1.88 + execsql {
1.89 + SELECT y from t2
1.90 + WHERE x = (SELECT sum(b) FROM t1 where a notnull) - (SELECT sum(a) FROM t1)
1.91 + }
1.92 +} {8}
1.93 +
1.94 +# Try something useful. Delete every entry from t2 where the
1.95 +# x value is less than half of the maximum.
1.96 +#
1.97 +do_test subselect-1.6 {
1.98 + execsql {DELETE FROM t2 WHERE x < 0.5*(SELECT max(x) FROM t2)}
1.99 + execsql {SELECT x FROM t2 ORDER BY x}
1.100 +} {2 3 4}
1.101 +
1.102 +# Make sure sorting works for SELECTs there used as a scalar expression.
1.103 +#
1.104 +do_test subselect-2.1 {
1.105 + execsql {
1.106 + SELECT (SELECT a FROM t1 ORDER BY a), (SELECT a FROM t1 ORDER BY a DESC)
1.107 + }
1.108 +} {1 5}
1.109 +do_test subselect-2.2 {
1.110 + execsql {
1.111 + SELECT 1 IN (SELECT a FROM t1 ORDER BY a);
1.112 + }
1.113 +} {1}
1.114 +do_test subselect-2.3 {
1.115 + execsql {
1.116 + SELECT 2 IN (SELECT a FROM t1 ORDER BY a DESC);
1.117 + }
1.118 +} {0}
1.119 +
1.120 +# Verify that the ORDER BY clause is honored in a subquery.
1.121 +#
1.122 +ifcapable compound {
1.123 +do_test subselect-3.1 {
1.124 + execsql {
1.125 + CREATE TABLE t3(x int);
1.126 + INSERT INTO t3 SELECT a FROM t1 UNION ALL SELECT b FROM t1;
1.127 + SELECT * FROM t3 ORDER BY x;
1.128 + }
1.129 +} {1 2 3 4 5 6}
1.130 +} ;# ifcapable compound
1.131 +ifcapable !compound {
1.132 +do_test subselect-3.1 {
1.133 + execsql {
1.134 + CREATE TABLE t3(x int);
1.135 + INSERT INTO t3 SELECT a FROM t1;
1.136 + INSERT INTO t3 SELECT b FROM t1;
1.137 + SELECT * FROM t3 ORDER BY x;
1.138 + }
1.139 +} {1 2 3 4 5 6}
1.140 +} ;# ifcapable !compound
1.141 +
1.142 +do_test subselect-3.2 {
1.143 + execsql {
1.144 + SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x LIMIT 2);
1.145 + }
1.146 +} {3}
1.147 +do_test subselect-3.3 {
1.148 + execsql {
1.149 + SELECT sum(x) FROM (SELECT x FROM t3 ORDER BY x DESC LIMIT 2);
1.150 + }
1.151 +} {11}
1.152 +do_test subselect-3.4 {
1.153 + execsql {
1.154 + SELECT (SELECT x FROM t3 ORDER BY x);
1.155 + }
1.156 +} {1}
1.157 +do_test subselect-3.5 {
1.158 + execsql {
1.159 + SELECT (SELECT x FROM t3 ORDER BY x DESC);
1.160 + }
1.161 +} {6}
1.162 +do_test subselect-3.6 {
1.163 + execsql {
1.164 + SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1);
1.165 + }
1.166 +} {1}
1.167 +do_test subselect-3.7 {
1.168 + execsql {
1.169 + SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1);
1.170 + }
1.171 +} {6}
1.172 +do_test subselect-3.8 {
1.173 + execsql {
1.174 + SELECT (SELECT x FROM t3 ORDER BY x LIMIT 1 OFFSET 2);
1.175 + }
1.176 +} {3}
1.177 +do_test subselect-3.9 {
1.178 + execsql {
1.179 + SELECT (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
1.180 + }
1.181 +} {4}
1.182 +do_test subselect-3.10 {
1.183 + execsql {
1.184 + SELECT x FROM t3 WHERE x IN
1.185 + (SELECT x FROM t3 ORDER BY x DESC LIMIT 1 OFFSET 2);
1.186 + }
1.187 +} {4}
1.188 +
1.189 +# Ticket #2295.
1.190 +# Make sure type affinities work correctly on subqueries with
1.191 +# an ORDER BY clause.
1.192 +#
1.193 +do_test subselect-4.1 {
1.194 + execsql {
1.195 + CREATE TABLE t4(a TEXT, b TEXT);
1.196 + INSERT INTO t4 VALUES('a','1');
1.197 + INSERT INTO t4 VALUES('b','2');
1.198 + INSERT INTO t4 VALUES('c','3');
1.199 + SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b);
1.200 + }
1.201 +} {a b c}
1.202 +do_test subselect-4.2 {
1.203 + execsql {
1.204 + SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b LIMIT 1);
1.205 + }
1.206 +} {a}
1.207 +do_test subselect-4.3 {
1.208 + execsql {
1.209 + SELECT a FROM t4 WHERE b IN (SELECT b FROM t4 ORDER BY b DESC LIMIT 1);
1.210 + }
1.211 +} {c}
1.212 +
1.213 +finish_test