Return to in4.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/in4.test, revision 1.1.1.1

1.1       misho       1: # 2008 September 1
                      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: #
                     12: # $Id: in4.test,v 1.4 2009/06/05 17:09:12 drh Exp $
                     13: 
                     14: set testdir [file dirname $argv0]
                     15: source $testdir/tester.tcl
                     16: 
                     17: do_test in4-1.1 {
                     18:   execsql {
                     19:     CREATE TABLE t1(a, b);
                     20:     CREATE INDEX i1 ON t1(a);
                     21:   }
                     22: } {}
                     23: do_test in4-1.2 {
                     24:   execsql {
                     25:     SELECT * FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
                     26:   }
                     27: } {}
                     28: do_test in4-1.3 {
                     29:   execsql {
                     30:     INSERT INTO t1 VALUES('aaa', 1);
                     31:     INSERT INTO t1 VALUES('ddd', 2);
                     32:     INSERT INTO t1 VALUES('ccc', 3);
                     33:     INSERT INTO t1 VALUES('eee', 4);
                     34:     SELECT b FROM t1 WHERE a IN ('aaa', 'bbb', 'ccc');
                     35:   }
                     36: } {1 3}
                     37: do_test in4-1.4 {
                     38:   execsql {
                     39:     SELECT a FROM t1 WHERE rowid IN (1, 3);
                     40:   }
                     41: } {aaa ccc}
                     42: do_test in4-1.5 {
                     43:   execsql {
                     44:     SELECT a FROM t1 WHERE rowid IN ();
                     45:   }
                     46: } {}
                     47: do_test in4-1.6 {
                     48:   execsql {
                     49:     SELECT a FROM t1 WHERE a IN ('ddd');
                     50:   }
                     51: } {ddd}
                     52: 
                     53: do_test in4-2.1 {
                     54:   execsql {
                     55:     CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
                     56:     INSERT INTO t2 VALUES(-1, '-one');
                     57:     INSERT INTO t2 VALUES(0, 'zero');
                     58:     INSERT INTO t2 VALUES(1, 'one');
                     59:     INSERT INTO t2 VALUES(2, 'two');
                     60:     INSERT INTO t2 VALUES(3, 'three');
                     61:   }
                     62: } {}
                     63: 
                     64: do_test in4-2.2 {
                     65:   execsql { SELECT b FROM t2 WHERE a IN (0, 2) }
                     66: } {zero two}
                     67: 
                     68: do_test in4-2.3 {
                     69:   execsql { SELECT b FROM t2 WHERE a IN (2, 0) }
                     70: } {zero two}
                     71: 
                     72: do_test in4-2.4 {
                     73:   execsql { SELECT b FROM t2 WHERE a IN (2, -1) }
                     74: } {-one two}
                     75: 
                     76: do_test in4-2.5 {
                     77:   execsql { SELECT b FROM t2 WHERE a IN (NULL, 3) }
                     78: } {three}
                     79: 
                     80: do_test in4-2.6 {
                     81:   execsql { SELECT b FROM t2 WHERE a IN (1.0, 2.1) }
                     82: } {one}
                     83: 
                     84: do_test in4-2.7 {
                     85:   execsql { SELECT b FROM t2 WHERE a IN ('1', '2') }
                     86: } {one two}
                     87: 
                     88: do_test in4-2.8 {
                     89:   execsql { SELECT b FROM t2 WHERE a IN ('', '0.0.0', '2') }
                     90: } {two}
                     91: 
                     92: # The following block of tests test expressions of the form:
                     93: #
                     94: #    <expr> IN ()
                     95: #
                     96: # i.e. IN expressions with a literal empty set. 
                     97: # 
                     98: # This has led to crashes on more than one occasion. Test case in4-3.2 
                     99: # was added in reponse to a bug reported on the mailing list on 11/7/2008.
                    100: # See also tickets #3602 and #185.
                    101: #
                    102: do_test in4-3.1 {
                    103:   execsql {
                    104:     DROP TABLE IF EXISTS t1;
                    105:     DROP TABLE IF EXISTS t2;
                    106:     CREATE TABLE t1(x, id);
                    107:     CREATE TABLE t2(x, id);
                    108:     INSERT INTO t1 VALUES(NULL, NULL);
                    109:     INSERT INTO t1 VALUES(0, NULL);
                    110:     INSERT INTO t1 VALUES(1, 3);
                    111:     INSERT INTO t1 VALUES(2, 4);
                    112:     INSERT INTO t1 VALUES(3, 5);
                    113:     INSERT INTO t1 VALUES(4, 6);
                    114:     INSERT INTO t2 VALUES(0, NULL);
                    115:     INSERT INTO t2 VALUES(4, 1);
                    116:     INSERT INTO t2 VALUES(NULL, 1);
                    117:     INSERT INTO t2 VALUES(NULL, NULL);
                    118:   }
                    119: } {}
                    120: do_test in4-3.2 {
                    121:   execsql {
                    122:     SELECT x FROM t1 WHERE id IN () AND x IN (SELECT x FROM t2 WHERE id=1)
                    123:   }
                    124: } {}
                    125: do_test in4-3.3 {
                    126:   execsql {
                    127:     CREATE TABLE t3(x, y, z);
                    128:     CREATE INDEX t3i1 ON t3(x, y);
                    129:     INSERT INTO t3 VALUES(1, 1, 1);
                    130:     INSERT INTO t3 VALUES(10, 10, 10);
                    131:   }
                    132:   execsql { SELECT * FROM t3 WHERE x IN () }
                    133: } {}
                    134: do_test in4-3.4 {
                    135:   execsql { SELECT * FROM t3 WHERE x = 10 AND y IN () }
                    136: } {}
                    137: do_test in4-3.5 {
                    138:   execsql { SELECT * FROM t3 WHERE x IN () AND y = 10 }
                    139: } {}
                    140: do_test in4-3.6 {
                    141:   execsql { SELECT * FROM t3 WHERE x IN () OR x = 10 }
                    142: } {10 10 10}
                    143: do_test in4-3.7 {
                    144:   execsql { SELECT * FROM t3 WHERE y IN () }
                    145: } {}
                    146: do_test in4-3.8 {
                    147:   execsql { SELECT x IN() AS a FROM t3 WHERE a }
                    148: } {}
                    149: do_test in4-3.9 {
                    150:   execsql { SELECT x IN() AS a FROM t3 WHERE NOT a }
                    151: } {0 0}
                    152: do_test in4-3.10 {
                    153:   execsql { SELECT * FROM t3 WHERE oid IN () }
                    154: } {}
                    155: do_test in4-3.11 {
                    156:   execsql { SELECT * FROM t3 WHERE x IN (1, 2) OR y IN ()}
                    157: } {1 1 1}
                    158: do_test in4-3.12 {
                    159:   execsql { SELECT * FROM t3 WHERE x IN (1, 2) AND y IN ()}
                    160: } {}
                    161: 
                    162: finish_test