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     ! 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