AryaWu/sqlite

1# 2010 July 142#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library. Specifically,12# it tests that ticket [80e031a00f45dca877ed92b225209cfa09280f4f] has been13# resolved.  That ticket is about IN and NOT IN operators with empty-set14# right-hand sides.  Such expressions should always return TRUE or FALSE15# even if the left-hand side is NULL.16#17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20source $testdir/lock_common.tcl21source $testdir/malloc_common.tcl22 23# EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the24# result of IN is false and the result of NOT IN is true, regardless of25# the left operand and even if the left operand is NULL.26#27# EVIDENCE-OF: R-64309-54027 Note that SQLite allows the parenthesized28# list of scalar values on the right-hand side of an IN or NOT IN29# operator to be an empty list but most other SQL database engines and30# the SQL92 standard require the list to contain at least one element.31#32do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 033do_execsql_test tkt-80e031a00f.1b {SELECT 1 IN (2)} 034do_execsql_test tkt-80e031a00f.1c {SELECT 1 IN (2,3,4,5,6,7,8,9)} 035do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 136do_execsql_test tkt-80e031a00f.2b {SELECT 1 NOT IN (2)} 137do_execsql_test tkt-80e031a00f.2c {SELECT 1 NOT IN (2,3,4,5,6,7,8,9)} 138do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 039do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 140do_execsql_test tkt-80e031a00f.5 {41  CREATE TABLE t1(x);42  SELECT 1 IN t1;43} 044do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 145do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 046do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 147do_execsql_test tkt-80e031a00f.9 {48  CREATE TABLE t2(y INTEGER PRIMARY KEY);49  SELECT 1 IN t2;50} 051do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 152do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 053do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 154do_execsql_test tkt-80e031a00f.13 {55  CREATE TABLE t3(z INT UNIQUE);56  SELECT 1 IN t3;57} 058do_execsql_test tkt-80e031a00f.14 {SELECT 1 NOT IN t3} 159do_execsql_test tkt-80e031a00f.15 {SELECT null IN t3} 060do_execsql_test tkt-80e031a00f.16 {SELECT null NOT IN t3} 161do_execsql_test tkt-80e031a00f.17 {SELECT 1 IN (SELECT x+y FROM t1, t2)} 062do_execsql_test tkt-80e031a00f.18 {SELECT 1 NOT IN (SELECT x+y FROM t1,t2)} 163do_execsql_test tkt-80e031a00f.19 {SELECT null IN (SELECT x+y FROM t1,t2)} 064do_execsql_test tkt-80e031a00f.20 {SELECT null NOT IN (SELECT x+y FROM t1,t2)} 165do_execsql_test tkt-80e031a00f.21 {SELECT 1.23 IN ()} 066do_execsql_test tkt-80e031a00f.22 {SELECT 1.23 NOT IN ()} 167do_execsql_test tkt-80e031a00f.23 {SELECT 1.23 IN t1} 068do_execsql_test tkt-80e031a00f.24 {SELECT 1.23 NOT IN t1} 169do_execsql_test tkt-80e031a00f.25 {SELECT 'hello' IN ()} 070do_execsql_test tkt-80e031a00f.26 {SELECT 'hello' NOT IN ()} 171do_execsql_test tkt-80e031a00f.27 {SELECT 'hello' IN t1} 072do_execsql_test tkt-80e031a00f.28 {SELECT 'hello' NOT IN t1} 173do_execsql_test tkt-80e031a00f.29 {SELECT x'303132' IN ()} 074do_execsql_test tkt-80e031a00f.30 {SELECT x'303132' NOT IN ()} 175do_execsql_test tkt-80e031a00f.31 {SELECT x'303132' IN t1} 076do_execsql_test tkt-80e031a00f.32 {SELECT x'303132' NOT IN t1} 177 78# EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is79# determined by the following matrix: Left operand is NULL Right operand80# contains NULL Right operand is an empty set Left operand found within81# right operand Result of IN operator Result of NOT IN operator no no no82# no false true does not matter no yes no false true no does not matter83# no yes true false no yes no no NULL NULL yes does not matter no does84# not matter NULL NULL85#86# Row 1:87do_execsql_test tkt-80e031a00f.100 {SELECT 1 IN (2,3,4)} 088do_execsql_test tkt-80e031a00f.101 {SELECT 1 NOT IN (2,3,4)} 189do_execsql_test tkt-80e031a00f.102 {SELECT 'a' IN ('b','c','d')} 090do_execsql_test tkt-80e031a00f.103 {SELECT 'a' NOT IN ('b','c','d')} 191do_test tkt-80e031a00f.104 {92  db eval {93     CREATE TABLE t4(a UNIQUE);94     CREATE TABLE t5(b INTEGER PRIMARY KEY);95     CREATE TABLE t6(c);96     INSERT INTO t4 VALUES(2);97     INSERT INTO t4 VALUES(3);98     INSERT INTO t4 VALUES(4);99     INSERT INTO t5 SELECT * FROM t4;100     INSERT INTO t6 SELECT * FROM t4;101     CREATE TABLE t4n(a UNIQUE);102     CREATE TABLE t6n(c);103     INSERT INTO t4n SELECT * FROM t4;104     INSERT INTO t4n VALUES(null);105     INSERT INTO t6n SELECT * FROM t4n;106     CREATE TABLE t7(a UNIQUE);107     CREATE TABLE t8(c);108     INSERT INTO t7 VALUES('b');109     INSERT INTO t7 VALUES('c');110     INSERT INTO t7 VALUES('d');111     INSERT INTO t8 SELECT * FROM t7;112     CREATE TABLE t7n(a UNIQUE);113     CREATE TABLE t8n(c);114     INSERT INTO t7n SELECT * FROM t7;115     INSERT INTO t7n VALUES(null);116     INSERT INTO t8n SELECT * FROM t7n;117  }118  execsql {SELECT 1 IN t4}119} 0120do_execsql_test tkt-80e031a00f.105 {SELECT 1 NOT IN t4} 1121do_execsql_test tkt-80e031a00f.106 {SELECT 1 IN t5} 0122do_execsql_test tkt-80e031a00f.107 {SELECT 1 NOT IN t5} 1123do_execsql_test tkt-80e031a00f.108 {SELECT 1 IN t6} 0124do_execsql_test tkt-80e031a00f.109 {SELECT 1 NOT IN t6} 1125do_execsql_test tkt-80e031a00f.110 {SELECT 'a' IN t7} 0126do_execsql_test tkt-80e031a00f.111 {SELECT 'a' NOT IN t7} 1127do_execsql_test tkt-80e031a00f.112 {SELECT 'a' IN t8} 0128do_execsql_test tkt-80e031a00f.113 {SELECT 'a' NOT IN t8} 1129#130# Row 2 is tested by cases 1-32 above.131# Row 3:132do_execsql_test tkt-80e031a00f.300 {SELECT 2 IN (2,3,4,null)} 1133do_execsql_test tkt-80e031a00f.301 {SELECT 3 NOT IN (2,3,4,null)} 0134do_execsql_test tkt-80e031a00f.302 {SELECT 4 IN (2,3,4)} 1135do_execsql_test tkt-80e031a00f.303 {SELECT 2 NOT IN (2,3,4)} 0136do_execsql_test tkt-80e031a00f.304 {SELECT 'b' IN ('b','c','d')} 1137do_execsql_test tkt-80e031a00f.305 {SELECT 'c' NOT IN ('b','c','d')} 0138do_execsql_test tkt-80e031a00f.306 {SELECT 'd' IN ('b','c',null,'d')} 1139do_execsql_test tkt-80e031a00f.307 {SELECT 'b' NOT IN (null,'b','c','d')} 0140do_execsql_test tkt-80e031a00f.308 {SELECT 2 IN t4} 1141do_execsql_test tkt-80e031a00f.309 {SELECT 3 NOT IN t4} 0142do_execsql_test tkt-80e031a00f.310 {SELECT 4 IN t4n} 1143do_execsql_test tkt-80e031a00f.311 {SELECT 2 NOT IN t4n} 0144do_execsql_test tkt-80e031a00f.312 {SELECT 2 IN t5} 1145do_execsql_test tkt-80e031a00f.313 {SELECT 3 NOT IN t5} 0146do_execsql_test tkt-80e031a00f.314 {SELECT 2 IN t6} 1147do_execsql_test tkt-80e031a00f.315 {SELECT 3 NOT IN t6} 0148do_execsql_test tkt-80e031a00f.316 {SELECT 4 IN t6n} 1149do_execsql_test tkt-80e031a00f.317 {SELECT 2 NOT IN t6n} 0150do_execsql_test tkt-80e031a00f.318 {SELECT 'b' IN t7} 1151do_execsql_test tkt-80e031a00f.319 {SELECT 'c' NOT IN t7} 0152do_execsql_test tkt-80e031a00f.320 {SELECT 'c' IN t7n} 1153do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0154do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1155do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0156do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1157do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0158do_execsql_test tkt-80e031a00f.326 {SELECT 'a' IN (NULL,'a')} 1159do_execsql_test tkt-80e031a00f.327 {SELECT 'a' IN (NULL,'b')} {{}}160do_execsql_test tkt-80e031a00f.328 {SELECT 'a' NOT IN (NULL,'a')} 0161do_execsql_test tkt-80e031a00f.329 {SELECT 'a' NOT IN (NULL,'b')} {{}}162#163# Row 4:164do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}}165do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}}166do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}}167do_execsql_test tkt-80e031a00f.403 {SELECT 'a' NOT IN (null,'b','c','d')} {{}}168do_execsql_test tkt-80e031a00f.404 {SELECT 1 IN t4n} {{}}169do_execsql_test tkt-80e031a00f.405 {SELECT 5 NOT IN t4n} {{}}170do_execsql_test tkt-80e031a00f.406 {SELECT 6 IN t6n} {{}}171do_execsql_test tkt-80e031a00f.407 {SELECT 7 NOT IN t6n} {{}}172do_execsql_test tkt-80e031a00f.408 {SELECT 'a' IN t7n} {{}}173do_execsql_test tkt-80e031a00f.409 {SELECT 'e' NOT IN t7n} {{}}174do_execsql_test tkt-80e031a00f.410 {SELECT 'f' IN t8n} {{}}175do_execsql_test tkt-80e031a00f.411 {SELECT 'g' NOT IN t8n} {{}}176#177# Row 5:178do_execsql_test tkt-80e031a00f.500 {SELECT null IN (2,3,4,null)} {{}}179do_execsql_test tkt-80e031a00f.501 {SELECT null NOT IN (2,3,4,null)} {{}}180do_execsql_test tkt-80e031a00f.502 {SELECT null IN (2,3,4)} {{}}181do_execsql_test tkt-80e031a00f.503 {SELECT null NOT IN (2,3,4)} {{}}182do_execsql_test tkt-80e031a00f.504 {SELECT null IN ('b','c','d')} {{}}183do_execsql_test tkt-80e031a00f.505 {SELECT null NOT IN ('b','c','d')} {{}}184do_execsql_test tkt-80e031a00f.506 {SELECT null IN ('b','c',null,'d')} {{}}185do_execsql_test tkt-80e031a00f.507 {SELECT null NOT IN (null,'b','c','d')} {{}}186do_execsql_test tkt-80e031a00f.508 {SELECT null IN t4} {{}}187do_execsql_test tkt-80e031a00f.509 {SELECT null NOT IN t4} {{}}188do_execsql_test tkt-80e031a00f.510 {SELECT null IN t4n} {{}}189do_execsql_test tkt-80e031a00f.511 {SELECT null NOT IN t4n} {{}}190do_execsql_test tkt-80e031a00f.512 {SELECT null IN t5} {{}}191do_execsql_test tkt-80e031a00f.513 {SELECT null NOT IN t5} {{}}192do_execsql_test tkt-80e031a00f.514 {SELECT null IN t6} {{}}193do_execsql_test tkt-80e031a00f.515 {SELECT null NOT IN t6} {{}}194do_execsql_test tkt-80e031a00f.516 {SELECT null IN t6n} {{}}195do_execsql_test tkt-80e031a00f.517 {SELECT null NOT IN t6n} {{}}196do_execsql_test tkt-80e031a00f.518 {SELECT null IN t7} {{}}197do_execsql_test tkt-80e031a00f.519 {SELECT null NOT IN t7} {{}}198do_execsql_test tkt-80e031a00f.520 {SELECT null IN t7n} {{}}199do_execsql_test tkt-80e031a00f.521 {SELECT null NOT IN t7n} {{}}200do_execsql_test tkt-80e031a00f.522 {SELECT null IN t8} {{}}201do_execsql_test tkt-80e031a00f.523 {SELECT null NOT IN t8} {{}}202do_execsql_test tkt-80e031a00f.524 {SELECT null IN t8n} {{}}203do_execsql_test tkt-80e031a00f.525 {SELECT null NOT IN t8n} {{}}204 205finish_test206