Return to subquery2.test CVS log

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

Annotation of embedaddon/sqlite3/test/subquery2.test, revision 1.1

1.1     ! misho       1: # 2011 September 16
        !             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.  The
        !            12: # focus of this script is testing correlated subqueries
        !            13: #
        !            14: #
        !            15: 
        !            16: set testdir [file dirname $argv0]
        !            17: source $testdir/tester.tcl
        !            18: 
        !            19: ifcapable !subquery {
        !            20:   finish_test
        !            21:   return
        !            22: }
        !            23: 
        !            24: do_test subquery2-1.1 {
        !            25:   execsql {
        !            26:     BEGIN;
        !            27:     CREATE TABLE t1(a,b);
        !            28:     INSERT INTO t1 VALUES(1,2);
        !            29:     INSERT INTO t1 VALUES(3,4);
        !            30:     INSERT INTO t1 VALUES(5,6);
        !            31:     INSERT INTO t1 VALUES(7,8);
        !            32:     CREATE TABLE t2(c,d);
        !            33:     INSERT INTO t2 VALUES(1,1);
        !            34:     INSERT INTO t2 VALUES(3,9);
        !            35:     INSERT INTO t2 VALUES(5,25);
        !            36:     INSERT INTO t2 VALUES(7,49);
        !            37:     CREATE TABLE t3(e,f);
        !            38:     INSERT INTO t3 VALUES(1,1);
        !            39:     INSERT INTO t3 VALUES(3,27);
        !            40:     INSERT INTO t3 VALUES(5,125);
        !            41:     INSERT INTO t3 VALUES(7,343);
        !            42:     COMMIT;
        !            43:   }
        !            44:   execsql {
        !            45:     SELECT a FROM t1
        !            46:      WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
        !            47:   }
        !            48: } {1 3 5 7}
        !            49: do_test subquery2-1.2 {
        !            50:   execsql {
        !            51:     CREATE INDEX t1b ON t1(b);
        !            52:     SELECT a FROM t1
        !            53:      WHERE b IN (SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
        !            54:   }
        !            55: } {1 3 5 7}
        !            56: 
        !            57: do_test subquery2-1.11 {
        !            58:   execsql {
        !            59:     SELECT a FROM t1
        !            60:      WHERE +b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
        !            61:   }
        !            62: } {1}
        !            63: do_test subquery2-1.12 {
        !            64:   execsql {
        !            65:     SELECT a FROM t1
        !            66:      WHERE b=(SELECT x+1 FROM (SELECT DISTINCT f/(a*a) AS x FROM t3));
        !            67:   }
        !            68: } {1}
        !            69: 
        !            70: do_test subquery2-1.21 {
        !            71:   execsql {
        !            72:     SELECT a FROM t1
        !            73:      WHERE +b=(SELECT x+1 FROM 
        !            74:                  (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
        !            75:   }
        !            76: } {1 3 5 7}
        !            77: do_test subquery2-1.22 {
        !            78:   execsql {
        !            79:     SELECT a FROM t1
        !            80:      WHERE b=(SELECT x+1 FROM 
        !            81:                  (SELECT DISTINCT f/d AS x FROM t2 JOIN t3 ON d*a=f))
        !            82:   }
        !            83: } {1 3 5 7}
        !            84: 
        !            85: 
        !            86: finish_test