000001  # 2010 July 14
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library. Specifically,
000012  # it tests that ticket [80e031a00f45dca877ed92b225209cfa09280f4f] has been
000013  # resolved.  That ticket is about IN and NOT IN operators with empty-set
000014  # right-hand sides.  Such expressions should always return TRUE or FALSE
000015  # even if the left-hand side is NULL.
000016  #
000017  
000018  set testdir [file dirname $argv0]
000019  source $testdir/tester.tcl
000020  source $testdir/lock_common.tcl
000021  source $testdir/malloc_common.tcl
000022  
000023  # EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
000024  # result of IN is false and the result of NOT IN is true, regardless of
000025  # the left operand and even if the left operand is NULL.
000026  #
000027  # EVIDENCE-OF: R-64309-54027 Note that SQLite allows the parenthesized
000028  # list of scalar values on the right-hand side of an IN or NOT IN
000029  # operator to be an empty list but most other SQL database engines and
000030  # the SQL92 standard require the list to contain at least one element.
000031  #
000032  do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0
000033  do_execsql_test tkt-80e031a00f.1b {SELECT 1 IN (2)} 0
000034  do_execsql_test tkt-80e031a00f.1c {SELECT 1 IN (2,3,4,5,6,7,8,9)} 0
000035  do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 1
000036  do_execsql_test tkt-80e031a00f.2b {SELECT 1 NOT IN (2)} 1
000037  do_execsql_test tkt-80e031a00f.2c {SELECT 1 NOT IN (2,3,4,5,6,7,8,9)} 1
000038  do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 0
000039  do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 1
000040  do_execsql_test tkt-80e031a00f.5 {
000041    CREATE TABLE t1(x);
000042    SELECT 1 IN t1;
000043  } 0
000044  do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 1
000045  do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 0
000046  do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 1
000047  do_execsql_test tkt-80e031a00f.9 {
000048    CREATE TABLE t2(y INTEGER PRIMARY KEY);
000049    SELECT 1 IN t2;
000050  } 0
000051  do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 1
000052  do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 0
000053  do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 1
000054  do_execsql_test tkt-80e031a00f.13 {
000055    CREATE TABLE t3(z INT UNIQUE);
000056    SELECT 1 IN t3;
000057  } 0
000058  do_execsql_test tkt-80e031a00f.14 {SELECT 1 NOT IN t3} 1
000059  do_execsql_test tkt-80e031a00f.15 {SELECT null IN t3} 0
000060  do_execsql_test tkt-80e031a00f.16 {SELECT null NOT IN t3} 1
000061  do_execsql_test tkt-80e031a00f.17 {SELECT 1 IN (SELECT x+y FROM t1, t2)} 0
000062  do_execsql_test tkt-80e031a00f.18 {SELECT 1 NOT IN (SELECT x+y FROM t1,t2)} 1
000063  do_execsql_test tkt-80e031a00f.19 {SELECT null IN (SELECT x+y FROM t1,t2)} 0
000064  do_execsql_test tkt-80e031a00f.20 {SELECT null NOT IN (SELECT x+y FROM t1,t2)} 1
000065  do_execsql_test tkt-80e031a00f.21 {SELECT 1.23 IN ()} 0
000066  do_execsql_test tkt-80e031a00f.22 {SELECT 1.23 NOT IN ()} 1
000067  do_execsql_test tkt-80e031a00f.23 {SELECT 1.23 IN t1} 0
000068  do_execsql_test tkt-80e031a00f.24 {SELECT 1.23 NOT IN t1} 1
000069  do_execsql_test tkt-80e031a00f.25 {SELECT 'hello' IN ()} 0
000070  do_execsql_test tkt-80e031a00f.26 {SELECT 'hello' NOT IN ()} 1
000071  do_execsql_test tkt-80e031a00f.27 {SELECT 'hello' IN t1} 0
000072  do_execsql_test tkt-80e031a00f.28 {SELECT 'hello' NOT IN t1} 1
000073  do_execsql_test tkt-80e031a00f.29 {SELECT x'303132' IN ()} 0
000074  do_execsql_test tkt-80e031a00f.30 {SELECT x'303132' NOT IN ()} 1
000075  do_execsql_test tkt-80e031a00f.31 {SELECT x'303132' IN t1} 0
000076  do_execsql_test tkt-80e031a00f.32 {SELECT x'303132' NOT IN t1} 1
000077  
000078  # EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is
000079  # determined by the following matrix: Left operand is NULL Right operand
000080  # contains NULL Right operand is an empty set Left operand found within
000081  # right operand Result of IN operator Result of NOT IN operator no no no
000082  # no false true does not matter no yes no false true no does not matter
000083  # no yes true false no yes no no NULL NULL yes does not matter no does
000084  # not matter NULL NULL
000085  #
000086  # Row 1:
000087  do_execsql_test tkt-80e031a00f.100 {SELECT 1 IN (2,3,4)} 0
000088  do_execsql_test tkt-80e031a00f.101 {SELECT 1 NOT IN (2,3,4)} 1
000089  do_execsql_test tkt-80e031a00f.102 {SELECT 'a' IN ('b','c','d')} 0
000090  do_execsql_test tkt-80e031a00f.103 {SELECT 'a' NOT IN ('b','c','d')} 1
000091  do_test tkt-80e031a00f.104 {
000092    db eval {
000093       CREATE TABLE t4(a UNIQUE);
000094       CREATE TABLE t5(b INTEGER PRIMARY KEY);
000095       CREATE TABLE t6(c);
000096       INSERT INTO t4 VALUES(2);
000097       INSERT INTO t4 VALUES(3);
000098       INSERT INTO t4 VALUES(4);
000099       INSERT INTO t5 SELECT * FROM t4;
000100       INSERT INTO t6 SELECT * FROM t4;
000101       CREATE TABLE t4n(a UNIQUE);
000102       CREATE TABLE t6n(c);
000103       INSERT INTO t4n SELECT * FROM t4;
000104       INSERT INTO t4n VALUES(null);
000105       INSERT INTO t6n SELECT * FROM t4n;
000106       CREATE TABLE t7(a UNIQUE);
000107       CREATE TABLE t8(c);
000108       INSERT INTO t7 VALUES('b');
000109       INSERT INTO t7 VALUES('c');
000110       INSERT INTO t7 VALUES('d');
000111       INSERT INTO t8 SELECT * FROM t7;
000112       CREATE TABLE t7n(a UNIQUE);
000113       CREATE TABLE t8n(c);
000114       INSERT INTO t7n SELECT * FROM t7;
000115       INSERT INTO t7n VALUES(null);
000116       INSERT INTO t8n SELECT * FROM t7n;
000117    }
000118    execsql {SELECT 1 IN t4}
000119  } 0
000120  do_execsql_test tkt-80e031a00f.105 {SELECT 1 NOT IN t4} 1
000121  do_execsql_test tkt-80e031a00f.106 {SELECT 1 IN t5} 0
000122  do_execsql_test tkt-80e031a00f.107 {SELECT 1 NOT IN t5} 1
000123  do_execsql_test tkt-80e031a00f.108 {SELECT 1 IN t6} 0
000124  do_execsql_test tkt-80e031a00f.109 {SELECT 1 NOT IN t6} 1
000125  do_execsql_test tkt-80e031a00f.110 {SELECT 'a' IN t7} 0
000126  do_execsql_test tkt-80e031a00f.111 {SELECT 'a' NOT IN t7} 1
000127  do_execsql_test tkt-80e031a00f.112 {SELECT 'a' IN t8} 0
000128  do_execsql_test tkt-80e031a00f.113 {SELECT 'a' NOT IN t8} 1
000129  #
000130  # Row 2 is tested by cases 1-32 above.
000131  # Row 3:
000132  do_execsql_test tkt-80e031a00f.300 {SELECT 2 IN (2,3,4,null)} 1
000133  do_execsql_test tkt-80e031a00f.301 {SELECT 3 NOT IN (2,3,4,null)} 0
000134  do_execsql_test tkt-80e031a00f.302 {SELECT 4 IN (2,3,4)} 1
000135  do_execsql_test tkt-80e031a00f.303 {SELECT 2 NOT IN (2,3,4)} 0
000136  do_execsql_test tkt-80e031a00f.304 {SELECT 'b' IN ('b','c','d')} 1
000137  do_execsql_test tkt-80e031a00f.305 {SELECT 'c' NOT IN ('b','c','d')} 0
000138  do_execsql_test tkt-80e031a00f.306 {SELECT 'd' IN ('b','c',null,'d')} 1
000139  do_execsql_test tkt-80e031a00f.307 {SELECT 'b' NOT IN (null,'b','c','d')} 0
000140  do_execsql_test tkt-80e031a00f.308 {SELECT 2 IN t4} 1
000141  do_execsql_test tkt-80e031a00f.309 {SELECT 3 NOT IN t4} 0
000142  do_execsql_test tkt-80e031a00f.310 {SELECT 4 IN t4n} 1
000143  do_execsql_test tkt-80e031a00f.311 {SELECT 2 NOT IN t4n} 0
000144  do_execsql_test tkt-80e031a00f.312 {SELECT 2 IN t5} 1
000145  do_execsql_test tkt-80e031a00f.313 {SELECT 3 NOT IN t5} 0
000146  do_execsql_test tkt-80e031a00f.314 {SELECT 2 IN t6} 1
000147  do_execsql_test tkt-80e031a00f.315 {SELECT 3 NOT IN t6} 0
000148  do_execsql_test tkt-80e031a00f.316 {SELECT 4 IN t6n} 1
000149  do_execsql_test tkt-80e031a00f.317 {SELECT 2 NOT IN t6n} 0
000150  do_execsql_test tkt-80e031a00f.318 {SELECT 'b' IN t7} 1
000151  do_execsql_test tkt-80e031a00f.319 {SELECT 'c' NOT IN t7} 0
000152  do_execsql_test tkt-80e031a00f.320 {SELECT 'c' IN t7n} 1
000153  do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0
000154  do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1
000155  do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0
000156  do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1
000157  do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0
000158  do_execsql_test tkt-80e031a00f.326 {SELECT 'a' IN (NULL,'a')} 1
000159  do_execsql_test tkt-80e031a00f.327 {SELECT 'a' IN (NULL,'b')} {{}}
000160  do_execsql_test tkt-80e031a00f.328 {SELECT 'a' NOT IN (NULL,'a')} 0
000161  do_execsql_test tkt-80e031a00f.329 {SELECT 'a' NOT IN (NULL,'b')} {{}}
000162  #
000163  # Row 4:
000164  do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}}
000165  do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}}
000166  do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}}
000167  do_execsql_test tkt-80e031a00f.403 {SELECT 'a' NOT IN (null,'b','c','d')} {{}}
000168  do_execsql_test tkt-80e031a00f.404 {SELECT 1 IN t4n} {{}}
000169  do_execsql_test tkt-80e031a00f.405 {SELECT 5 NOT IN t4n} {{}}
000170  do_execsql_test tkt-80e031a00f.406 {SELECT 6 IN t6n} {{}}
000171  do_execsql_test tkt-80e031a00f.407 {SELECT 7 NOT IN t6n} {{}}
000172  do_execsql_test tkt-80e031a00f.408 {SELECT 'a' IN t7n} {{}}
000173  do_execsql_test tkt-80e031a00f.409 {SELECT 'e' NOT IN t7n} {{}}
000174  do_execsql_test tkt-80e031a00f.410 {SELECT 'f' IN t8n} {{}}
000175  do_execsql_test tkt-80e031a00f.411 {SELECT 'g' NOT IN t8n} {{}}
000176  #
000177  # Row 5:
000178  do_execsql_test tkt-80e031a00f.500 {SELECT null IN (2,3,4,null)} {{}}
000179  do_execsql_test tkt-80e031a00f.501 {SELECT null NOT IN (2,3,4,null)} {{}}
000180  do_execsql_test tkt-80e031a00f.502 {SELECT null IN (2,3,4)} {{}}
000181  do_execsql_test tkt-80e031a00f.503 {SELECT null NOT IN (2,3,4)} {{}}
000182  do_execsql_test tkt-80e031a00f.504 {SELECT null IN ('b','c','d')} {{}}
000183  do_execsql_test tkt-80e031a00f.505 {SELECT null NOT IN ('b','c','d')} {{}}
000184  do_execsql_test tkt-80e031a00f.506 {SELECT null IN ('b','c',null,'d')} {{}}
000185  do_execsql_test tkt-80e031a00f.507 {SELECT null NOT IN (null,'b','c','d')} {{}}
000186  do_execsql_test tkt-80e031a00f.508 {SELECT null IN t4} {{}}
000187  do_execsql_test tkt-80e031a00f.509 {SELECT null NOT IN t4} {{}}
000188  do_execsql_test tkt-80e031a00f.510 {SELECT null IN t4n} {{}}
000189  do_execsql_test tkt-80e031a00f.511 {SELECT null NOT IN t4n} {{}}
000190  do_execsql_test tkt-80e031a00f.512 {SELECT null IN t5} {{}}
000191  do_execsql_test tkt-80e031a00f.513 {SELECT null NOT IN t5} {{}}
000192  do_execsql_test tkt-80e031a00f.514 {SELECT null IN t6} {{}}
000193  do_execsql_test tkt-80e031a00f.515 {SELECT null NOT IN t6} {{}}
000194  do_execsql_test tkt-80e031a00f.516 {SELECT null IN t6n} {{}}
000195  do_execsql_test tkt-80e031a00f.517 {SELECT null NOT IN t6n} {{}}
000196  do_execsql_test tkt-80e031a00f.518 {SELECT null IN t7} {{}}
000197  do_execsql_test tkt-80e031a00f.519 {SELECT null NOT IN t7} {{}}
000198  do_execsql_test tkt-80e031a00f.520 {SELECT null IN t7n} {{}}
000199  do_execsql_test tkt-80e031a00f.521 {SELECT null NOT IN t7n} {{}}
000200  do_execsql_test tkt-80e031a00f.522 {SELECT null IN t8} {{}}
000201  do_execsql_test tkt-80e031a00f.523 {SELECT null NOT IN t8} {{}}
000202  do_execsql_test tkt-80e031a00f.524 {SELECT null IN t8n} {{}}
000203  do_execsql_test tkt-80e031a00f.525 {SELECT null NOT IN t8n} {{}}
000204  
000205  finish_test