1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/null.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,283 @@
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.
1.15 +#
1.16 +# This file implements tests for proper treatment of the special
1.17 +# value NULL.
1.18 +#
1.19 +
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# Create a table and some data to work with.
1.24 +#
1.25 +do_test null-1.0 {
1.26 + execsql {
1.27 + begin;
1.28 + create table t1(a,b,c);
1.29 + insert into t1 values(1,0,0);
1.30 + insert into t1 values(2,0,1);
1.31 + insert into t1 values(3,1,0);
1.32 + insert into t1 values(4,1,1);
1.33 + insert into t1 values(5,null,0);
1.34 + insert into t1 values(6,null,1);
1.35 + insert into t1 values(7,null,null);
1.36 + commit;
1.37 + select * from t1;
1.38 + }
1.39 +} {1 0 0 2 0 1 3 1 0 4 1 1 5 {} 0 6 {} 1 7 {} {}}
1.40 +
1.41 +# Check for how arithmetic expressions handle NULL
1.42 +#
1.43 +do_test null-1.1 {
1.44 + execsql {
1.45 + select ifnull(a+b,99) from t1;
1.46 + }
1.47 +} {1 2 4 5 99 99 99}
1.48 +do_test null-1.2 {
1.49 + execsql {
1.50 + select ifnull(b*c,99) from t1;
1.51 + }
1.52 +} {0 0 0 1 99 99 99}
1.53 +
1.54 +# Check to see how the CASE expression handles NULL values. The
1.55 +# first WHEN for which the test expression is TRUE is selected.
1.56 +# FALSE and UNKNOWN test expressions are skipped.
1.57 +#
1.58 +do_test null-2.1 {
1.59 + execsql {
1.60 + select ifnull(case when b<>0 then 1 else 0 end, 99) from t1;
1.61 + }
1.62 +} {0 0 1 1 0 0 0}
1.63 +do_test null-2.2 {
1.64 + execsql {
1.65 + select ifnull(case when not b<>0 then 1 else 0 end, 99) from t1;
1.66 + }
1.67 +} {1 1 0 0 0 0 0}
1.68 +do_test null-2.3 {
1.69 + execsql {
1.70 + select ifnull(case when b<>0 and c<>0 then 1 else 0 end, 99) from t1;
1.71 + }
1.72 +} {0 0 0 1 0 0 0}
1.73 +do_test null-2.4 {
1.74 + execsql {
1.75 + select ifnull(case when not (b<>0 and c<>0) then 1 else 0 end, 99) from t1;
1.76 + }
1.77 +} {1 1 1 0 1 0 0}
1.78 +do_test null-2.5 {
1.79 + execsql {
1.80 + select ifnull(case when b<>0 or c<>0 then 1 else 0 end, 99) from t1;
1.81 + }
1.82 +} {0 1 1 1 0 1 0}
1.83 +do_test null-2.6 {
1.84 + execsql {
1.85 + select ifnull(case when not (b<>0 or c<>0) then 1 else 0 end, 99) from t1;
1.86 + }
1.87 +} {1 0 0 0 0 0 0}
1.88 +do_test null-2.7 {
1.89 + execsql {
1.90 + select ifnull(case b when c then 1 else 0 end, 99) from t1;
1.91 + }
1.92 +} {1 0 0 1 0 0 0}
1.93 +do_test null-2.8 {
1.94 + execsql {
1.95 + select ifnull(case c when b then 1 else 0 end, 99) from t1;
1.96 + }
1.97 +} {1 0 0 1 0 0 0}
1.98 +
1.99 +# Check to see that NULL values are ignored in aggregate functions.
1.100 +#
1.101 +do_test null-3.1 {
1.102 + execsql {
1.103 + select count(*), count(b), count(c), sum(b), sum(c),
1.104 + avg(b), avg(c), min(b), max(b) from t1;
1.105 + }
1.106 +} {7 4 6 2 3 0.5 0.5 0 1}
1.107 +
1.108 +# The sum of zero entries is a NULL, but the total of zero entries is 0.
1.109 +#
1.110 +do_test null-3.2 {
1.111 + execsql {
1.112 + SELECT sum(b), total(b) FROM t1 WHERE b<0
1.113 + }
1.114 +} {{} 0.0}
1.115 +
1.116 +# Check to see how WHERE clauses handle NULL values. A NULL value
1.117 +# is the same as UNKNOWN. The WHERE clause should only select those
1.118 +# rows that are TRUE. FALSE and UNKNOWN rows are rejected.
1.119 +#
1.120 +do_test null-4.1 {
1.121 + execsql {
1.122 + select a from t1 where b<10
1.123 + }
1.124 +} {1 2 3 4}
1.125 +do_test null-4.2 {
1.126 + execsql {
1.127 + select a from t1 where not b>10
1.128 + }
1.129 +} {1 2 3 4}
1.130 +do_test null-4.3 {
1.131 + execsql {
1.132 + select a from t1 where b<10 or c=1;
1.133 + }
1.134 +} {1 2 3 4 6}
1.135 +do_test null-4.4 {
1.136 + execsql {
1.137 + select a from t1 where b<10 and c=1;
1.138 + }
1.139 +} {2 4}
1.140 +do_test null-4.5 {
1.141 + execsql {
1.142 + select a from t1 where not (b<10 and c=1);
1.143 + }
1.144 +} {1 3 5}
1.145 +
1.146 +# The DISTINCT keyword on a SELECT statement should treat NULL values
1.147 +# as distinct
1.148 +#
1.149 +do_test null-5.1 {
1.150 + execsql {
1.151 + select distinct b from t1 order by b;
1.152 + }
1.153 +} {{} 0 1}
1.154 +
1.155 +# A UNION to two queries should treat NULL values
1.156 +# as distinct.
1.157 +#
1.158 +# (Later:) We also take this opportunity to test the ability
1.159 +# of an ORDER BY clause to bind to either SELECT of a UNION.
1.160 +# The left-most SELECT is preferred. In standard SQL, only
1.161 +# the left SELECT can be used. The ability to match an ORDER
1.162 +# BY term to the right SELECT is an SQLite extension.
1.163 +#
1.164 +ifcapable compound {
1.165 + do_test null-6.1 {
1.166 + execsql {
1.167 + select b from t1 union select c from t1 order by b;
1.168 + }
1.169 + } {{} 0 1}
1.170 + do_test null-6.2 {
1.171 + execsql {
1.172 + select b from t1 union select c from t1 order by 1;
1.173 + }
1.174 + } {{} 0 1}
1.175 + do_test null-6.3 {
1.176 + execsql {
1.177 + select b from t1 union select c from t1 order by t1.b;
1.178 + }
1.179 + } {{} 0 1}
1.180 + do_test null-6.4 {
1.181 + execsql {
1.182 + select b from t1 union select c from t1 order by main.t1.b;
1.183 + }
1.184 + } {{} 0 1}
1.185 + do_test null-6.5 {
1.186 + catchsql {
1.187 + select b from t1 union select c from t1 order by t1.a;
1.188 + }
1.189 + } {1 {1st ORDER BY term does not match any column in the result set}}
1.190 + do_test null-6.6 {
1.191 + catchsql {
1.192 + select b from t1 union select c from t1 order by main.t1.a;
1.193 + }
1.194 + } {1 {1st ORDER BY term does not match any column in the result set}}
1.195 +} ;# ifcapable compound
1.196 +
1.197 +# The UNIQUE constraint only applies to non-null values
1.198 +#
1.199 +ifcapable conflict {
1.200 +do_test null-7.1 {
1.201 + execsql {
1.202 + create table t2(a, b unique on conflict ignore);
1.203 + insert into t2 values(1,1);
1.204 + insert into t2 values(2,null);
1.205 + insert into t2 values(3,null);
1.206 + insert into t2 values(4,1);
1.207 + select a from t2;
1.208 + }
1.209 + } {1 2 3}
1.210 + do_test null-7.2 {
1.211 + execsql {
1.212 + create table t3(a, b, c, unique(b,c) on conflict ignore);
1.213 + insert into t3 values(1,1,1);
1.214 + insert into t3 values(2,null,1);
1.215 + insert into t3 values(3,null,1);
1.216 + insert into t3 values(4,1,1);
1.217 + select a from t3;
1.218 + }
1.219 + } {1 2 3}
1.220 +}
1.221 +
1.222 +# Ticket #461 - Make sure nulls are handled correctly when doing a
1.223 +# lookup using an index.
1.224 +#
1.225 +do_test null-8.1 {
1.226 + execsql {
1.227 + CREATE TABLE t4(x,y);
1.228 + INSERT INTO t4 VALUES(1,11);
1.229 + INSERT INTO t4 VALUES(2,NULL);
1.230 + SELECT x FROM t4 WHERE y=NULL;
1.231 + }
1.232 +} {}
1.233 +ifcapable subquery {
1.234 + do_test null-8.2 {
1.235 + execsql {
1.236 + SELECT x FROM t4 WHERE y IN (33,NULL);
1.237 + }
1.238 + } {}
1.239 +}
1.240 +do_test null-8.3 {
1.241 + execsql {
1.242 + SELECT x FROM t4 WHERE y<33 ORDER BY x;
1.243 + }
1.244 +} {1}
1.245 +do_test null-8.4 {
1.246 + execsql {
1.247 + SELECT x FROM t4 WHERE y>6 ORDER BY x;
1.248 + }
1.249 +} {1}
1.250 +do_test null-8.5 {
1.251 + execsql {
1.252 + SELECT x FROM t4 WHERE y!=33 ORDER BY x;
1.253 + }
1.254 +} {1}
1.255 +do_test null-8.11 {
1.256 + execsql {
1.257 + CREATE INDEX t4i1 ON t4(y);
1.258 + SELECT x FROM t4 WHERE y=NULL;
1.259 + }
1.260 +} {}
1.261 +ifcapable subquery {
1.262 + do_test null-8.12 {
1.263 + execsql {
1.264 + SELECT x FROM t4 WHERE y IN (33,NULL);
1.265 + }
1.266 + } {}
1.267 +}
1.268 +do_test null-8.13 {
1.269 + execsql {
1.270 + SELECT x FROM t4 WHERE y<33 ORDER BY x;
1.271 + }
1.272 +} {1}
1.273 +do_test null-8.14 {
1.274 + execsql {
1.275 + SELECT x FROM t4 WHERE y>6 ORDER BY x;
1.276 + }
1.277 +} {1}
1.278 +do_test null-8.15 {
1.279 + execsql {
1.280 + SELECT x FROM t4 WHERE y!=33 ORDER BY x;
1.281 + }
1.282 +} {1}
1.283 +
1.284 +
1.285 +
1.286 +finish_test