Annotation of embedaddon/sqlite3/test/tkt-80e031a00f.test, revision 1.1
1.1 ! misho 1: # 2010 July 14
! 2: #
! 3: # The author disclaims copyright to this source code. In place of
! 4: # 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 been
! 13: # resolved. That ticket is about IN and NOT IN operators with empty-set
! 14: # right-hand sides. Such expressions should always return TRUE or FALSE
! 15: # even if the left-hand side is NULL.
! 16: #
! 17:
! 18: set testdir [file dirname $argv0]
! 19: source $testdir/tester.tcl
! 20: source $testdir/lock_common.tcl
! 21: source $testdir/malloc_common.tcl
! 22:
! 23: # EVIDENCE-OF: R-58875-56087 The IN and NOT IN operators take a single
! 24: # scalar operand on the left and a vector operand on the right formed by
! 25: # an explicit list of zero or more scalars or by a single subquery.
! 26: #
! 27: # EVIDENCE-OF: R-52275-55503 When the right operand is an empty set, the
! 28: # result of IN is false and the result of NOT IN is true, regardless of
! 29: # the left operand and even if the left operand is NULL.
! 30: #
! 31: # EVIDENCE-OF: R-13595-45863 Note that SQLite allows the parenthesized
! 32: # list of scalar values on the right-hand side of an IN or NOT IN
! 33: # operator to be an empty list but most other SQL database database
! 34: # engines and the SQL92 standard require the list to contain at least
! 35: # one element.
! 36: #
! 37: do_execsql_test tkt-80e031a00f.1 {SELECT 1 IN ()} 0
! 38: do_execsql_test tkt-80e031a00f.1b {SELECT 1 IN (2)} 0
! 39: do_execsql_test tkt-80e031a00f.1c {SELECT 1 IN (2,3,4,5,6,7,8,9)} 0
! 40: do_execsql_test tkt-80e031a00f.2 {SELECT 1 NOT IN ()} 1
! 41: do_execsql_test tkt-80e031a00f.2b {SELECT 1 NOT IN (2)} 1
! 42: do_execsql_test tkt-80e031a00f.2c {SELECT 1 NOT IN (2,3,4,5,6,7,8,9)} 1
! 43: do_execsql_test tkt-80e031a00f.3 {SELECT null IN ()} 0
! 44: do_execsql_test tkt-80e031a00f.4 {SELECT null NOT IN ()} 1
! 45: do_execsql_test tkt-80e031a00f.5 {
! 46: CREATE TABLE t1(x);
! 47: SELECT 1 IN t1;
! 48: } 0
! 49: do_execsql_test tkt-80e031a00f.6 {SELECT 1 NOT IN t1} 1
! 50: do_execsql_test tkt-80e031a00f.7 {SELECT null IN t1} 0
! 51: do_execsql_test tkt-80e031a00f.8 {SELECT null NOT IN t1} 1
! 52: do_execsql_test tkt-80e031a00f.9 {
! 53: CREATE TABLE t2(y INTEGER PRIMARY KEY);
! 54: SELECT 1 IN t2;
! 55: } 0
! 56: do_execsql_test tkt-80e031a00f.10 {SELECT 1 NOT IN t2} 1
! 57: do_execsql_test tkt-80e031a00f.11 {SELECT null IN t2} 0
! 58: do_execsql_test tkt-80e031a00f.12 {SELECT null NOT IN t2} 1
! 59: do_execsql_test tkt-80e031a00f.13 {
! 60: CREATE TABLE t3(z INT UNIQUE);
! 61: SELECT 1 IN t3;
! 62: } 0
! 63: do_execsql_test tkt-80e031a00f.14 {SELECT 1 NOT IN t3} 1
! 64: do_execsql_test tkt-80e031a00f.15 {SELECT null IN t3} 0
! 65: do_execsql_test tkt-80e031a00f.16 {SELECT null NOT IN t3} 1
! 66: do_execsql_test tkt-80e031a00f.17 {SELECT 1 IN (SELECT x+y FROM t1, t2)} 0
! 67: do_execsql_test tkt-80e031a00f.18 {SELECT 1 NOT IN (SELECT x+y FROM t1,t2)} 1
! 68: do_execsql_test tkt-80e031a00f.19 {SELECT null IN (SELECT x+y FROM t1,t2)} 0
! 69: do_execsql_test tkt-80e031a00f.20 {SELECT null NOT IN (SELECT x+y FROM t1,t2)} 1
! 70: do_execsql_test tkt-80e031a00f.21 {SELECT 1.23 IN ()} 0
! 71: do_execsql_test tkt-80e031a00f.22 {SELECT 1.23 NOT IN ()} 1
! 72: do_execsql_test tkt-80e031a00f.23 {SELECT 1.23 IN t1} 0
! 73: do_execsql_test tkt-80e031a00f.24 {SELECT 1.23 NOT IN t1} 1
! 74: do_execsql_test tkt-80e031a00f.25 {SELECT 'hello' IN ()} 0
! 75: do_execsql_test tkt-80e031a00f.26 {SELECT 'hello' NOT IN ()} 1
! 76: do_execsql_test tkt-80e031a00f.27 {SELECT 'hello' IN t1} 0
! 77: do_execsql_test tkt-80e031a00f.28 {SELECT 'hello' NOT IN t1} 1
! 78: do_execsql_test tkt-80e031a00f.29 {SELECT x'303132' IN ()} 0
! 79: do_execsql_test tkt-80e031a00f.30 {SELECT x'303132' NOT IN ()} 1
! 80: do_execsql_test tkt-80e031a00f.31 {SELECT x'303132' IN t1} 0
! 81: do_execsql_test tkt-80e031a00f.32 {SELECT x'303132' NOT IN t1} 1
! 82:
! 83: # EVIDENCE-OF: R-50221-42915 The result of an IN or NOT IN operator is
! 84: # determined by the following matrix: Left operand is NULL Right operand
! 85: # contains NULL Right operand is an empty set Left operand found within
! 86: # right operand Result of IN operator Result of NOT IN operator no no no
! 87: # no false true does not matter no yes no false true no does not matter
! 88: # no yes true false no yes no no NULL NULL yes does not matter no does
! 89: # not matter NULL NULL
! 90: #
! 91: # Row 1:
! 92: do_execsql_test tkt-80e031a00f.100 {SELECT 1 IN (2,3,4)} 0
! 93: do_execsql_test tkt-80e031a00f.101 {SELECT 1 NOT IN (2,3,4)} 1
! 94: do_execsql_test tkt-80e031a00f.102 {SELECT 'a' IN ('b','c','d')} 0
! 95: do_execsql_test tkt-80e031a00f.103 {SELECT 'a' NOT IN ('b','c','d')} 1
! 96: do_test tkt-80e031a00f.104 {
! 97: db eval {
! 98: CREATE TABLE t4(a UNIQUE);
! 99: CREATE TABLE t5(b INTEGER PRIMARY KEY);
! 100: CREATE TABLE t6(c);
! 101: INSERT INTO t4 VALUES(2);
! 102: INSERT INTO t4 VALUES(3);
! 103: INSERT INTO t4 VALUES(4);
! 104: INSERT INTO t5 SELECT * FROM t4;
! 105: INSERT INTO t6 SELECT * FROM t4;
! 106: CREATE TABLE t4n(a UNIQUE);
! 107: CREATE TABLE t6n(c);
! 108: INSERT INTO t4n SELECT * FROM t4;
! 109: INSERT INTO t4n VALUES(null);
! 110: INSERT INTO t6n SELECT * FROM t4n;
! 111: CREATE TABLE t7(a UNIQUE);
! 112: CREATE TABLE t8(c);
! 113: INSERT INTO t7 VALUES('b');
! 114: INSERT INTO t7 VALUES('c');
! 115: INSERT INTO t7 VALUES('d');
! 116: INSERT INTO t8 SELECT * FROM t7;
! 117: CREATE TABLE t7n(a UNIQUE);
! 118: CREATE TABLE t8n(c);
! 119: INSERT INTO t7n SELECT * FROM t7;
! 120: INSERT INTO t7n VALUES(null);
! 121: INSERT INTO t8n SELECT * FROM t7n;
! 122: }
! 123: execsql {SELECT 1 IN t4}
! 124: } 0
! 125: do_execsql_test tkt-80e031a00f.105 {SELECT 1 NOT IN t4} 1
! 126: do_execsql_test tkt-80e031a00f.106 {SELECT 1 IN t5} 0
! 127: do_execsql_test tkt-80e031a00f.107 {SELECT 1 NOT IN t5} 1
! 128: do_execsql_test tkt-80e031a00f.108 {SELECT 1 IN t6} 0
! 129: do_execsql_test tkt-80e031a00f.109 {SELECT 1 NOT IN t6} 1
! 130: do_execsql_test tkt-80e031a00f.110 {SELECT 'a' IN t7} 0
! 131: do_execsql_test tkt-80e031a00f.111 {SELECT 'a' NOT IN t7} 1
! 132: do_execsql_test tkt-80e031a00f.112 {SELECT 'a' IN t8} 0
! 133: do_execsql_test tkt-80e031a00f.113 {SELECT 'a' NOT IN t8} 1
! 134: #
! 135: # Row 2 is tested by cases 1-32 above.
! 136: # Row 3:
! 137: do_execsql_test tkt-80e031a00f.300 {SELECT 2 IN (2,3,4,null)} 1
! 138: do_execsql_test tkt-80e031a00f.301 {SELECT 3 NOT IN (2,3,4,null)} 0
! 139: do_execsql_test tkt-80e031a00f.302 {SELECT 4 IN (2,3,4)} 1
! 140: do_execsql_test tkt-80e031a00f.303 {SELECT 2 NOT IN (2,3,4)} 0
! 141: do_execsql_test tkt-80e031a00f.304 {SELECT 'b' IN ('b','c','d')} 1
! 142: do_execsql_test tkt-80e031a00f.305 {SELECT 'c' NOT IN ('b','c','d')} 0
! 143: do_execsql_test tkt-80e031a00f.306 {SELECT 'd' IN ('b','c',null,'d')} 1
! 144: do_execsql_test tkt-80e031a00f.307 {SELECT 'b' NOT IN (null,'b','c','d')} 0
! 145: do_execsql_test tkt-80e031a00f.308 {SELECT 2 IN t4} 1
! 146: do_execsql_test tkt-80e031a00f.309 {SELECT 3 NOT IN t4} 0
! 147: do_execsql_test tkt-80e031a00f.310 {SELECT 4 IN t4n} 1
! 148: do_execsql_test tkt-80e031a00f.311 {SELECT 2 NOT IN t4n} 0
! 149: do_execsql_test tkt-80e031a00f.312 {SELECT 2 IN t5} 1
! 150: do_execsql_test tkt-80e031a00f.313 {SELECT 3 NOT IN t5} 0
! 151: do_execsql_test tkt-80e031a00f.314 {SELECT 2 IN t6} 1
! 152: do_execsql_test tkt-80e031a00f.315 {SELECT 3 NOT IN t6} 0
! 153: do_execsql_test tkt-80e031a00f.316 {SELECT 4 IN t6n} 1
! 154: do_execsql_test tkt-80e031a00f.317 {SELECT 2 NOT IN t6n} 0
! 155: do_execsql_test tkt-80e031a00f.318 {SELECT 'b' IN t7} 1
! 156: do_execsql_test tkt-80e031a00f.319 {SELECT 'c' NOT IN t7} 0
! 157: do_execsql_test tkt-80e031a00f.320 {SELECT 'c' IN t7n} 1
! 158: do_execsql_test tkt-80e031a00f.321 {SELECT 'd' NOT IN t7n} 0
! 159: do_execsql_test tkt-80e031a00f.322 {SELECT 'b' IN t8} 1
! 160: do_execsql_test tkt-80e031a00f.323 {SELECT 'c' NOT IN t8} 0
! 161: do_execsql_test tkt-80e031a00f.324 {SELECT 'c' IN t8n} 1
! 162: do_execsql_test tkt-80e031a00f.325 {SELECT 'd' NOT IN t8n} 0
! 163: #
! 164: # Row 4:
! 165: do_execsql_test tkt-80e031a00f.400 {SELECT 1 IN (2,3,4,null)} {{}}
! 166: do_execsql_test tkt-80e031a00f.401 {SELECT 1 NOT IN (2,3,4,null)} {{}}
! 167: do_execsql_test tkt-80e031a00f.402 {SELECT 'a' IN ('b','c',null,'d')} {{}}
! 168: do_execsql_test tkt-80e031a00f.403 {SELECT 'a' NOT IN (null,'b','c','d')} {{}}
! 169: do_execsql_test tkt-80e031a00f.404 {SELECT 1 IN t4n} {{}}
! 170: do_execsql_test tkt-80e031a00f.405 {SELECT 5 NOT IN t4n} {{}}
! 171: do_execsql_test tkt-80e031a00f.406 {SELECT 6 IN t6n} {{}}
! 172: do_execsql_test tkt-80e031a00f.407 {SELECT 7 NOT IN t6n} {{}}
! 173: do_execsql_test tkt-80e031a00f.408 {SELECT 'a' IN t7n} {{}}
! 174: do_execsql_test tkt-80e031a00f.409 {SELECT 'e' NOT IN t7n} {{}}
! 175: do_execsql_test tkt-80e031a00f.410 {SELECT 'f' IN t8n} {{}}
! 176: do_execsql_test tkt-80e031a00f.411 {SELECT 'g' NOT IN t8n} {{}}
! 177: #
! 178: # Row 5:
! 179: do_execsql_test tkt-80e031a00f.500 {SELECT null IN (2,3,4,null)} {{}}
! 180: do_execsql_test tkt-80e031a00f.501 {SELECT null NOT IN (2,3,4,null)} {{}}
! 181: do_execsql_test tkt-80e031a00f.502 {SELECT null IN (2,3,4)} {{}}
! 182: do_execsql_test tkt-80e031a00f.503 {SELECT null NOT IN (2,3,4)} {{}}
! 183: do_execsql_test tkt-80e031a00f.504 {SELECT null IN ('b','c','d')} {{}}
! 184: do_execsql_test tkt-80e031a00f.505 {SELECT null NOT IN ('b','c','d')} {{}}
! 185: do_execsql_test tkt-80e031a00f.506 {SELECT null IN ('b','c',null,'d')} {{}}
! 186: do_execsql_test tkt-80e031a00f.507 {SELECT null NOT IN (null,'b','c','d')} {{}}
! 187: do_execsql_test tkt-80e031a00f.508 {SELECT null IN t4} {{}}
! 188: do_execsql_test tkt-80e031a00f.509 {SELECT null NOT IN t4} {{}}
! 189: do_execsql_test tkt-80e031a00f.510 {SELECT null IN t4n} {{}}
! 190: do_execsql_test tkt-80e031a00f.511 {SELECT null NOT IN t4n} {{}}
! 191: do_execsql_test tkt-80e031a00f.512 {SELECT null IN t5} {{}}
! 192: do_execsql_test tkt-80e031a00f.513 {SELECT null NOT IN t5} {{}}
! 193: do_execsql_test tkt-80e031a00f.514 {SELECT null IN t6} {{}}
! 194: do_execsql_test tkt-80e031a00f.515 {SELECT null NOT IN t6} {{}}
! 195: do_execsql_test tkt-80e031a00f.516 {SELECT null IN t6n} {{}}
! 196: do_execsql_test tkt-80e031a00f.517 {SELECT null NOT IN t6n} {{}}
! 197: do_execsql_test tkt-80e031a00f.518 {SELECT null IN t7} {{}}
! 198: do_execsql_test tkt-80e031a00f.519 {SELECT null NOT IN t7} {{}}
! 199: do_execsql_test tkt-80e031a00f.520 {SELECT null IN t7n} {{}}
! 200: do_execsql_test tkt-80e031a00f.521 {SELECT null NOT IN t7n} {{}}
! 201: do_execsql_test tkt-80e031a00f.522 {SELECT null IN t8} {{}}
! 202: do_execsql_test tkt-80e031a00f.523 {SELECT null NOT IN t8} {{}}
! 203: do_execsql_test tkt-80e031a00f.524 {SELECT null IN t8n} {{}}
! 204: do_execsql_test tkt-80e031a00f.525 {SELECT null NOT IN t8n} {{}}
! 205:
! 206: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to tkt-80e031a00f.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test