Return to join6.test CVS log

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

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

1.1     ! misho       1: # 2009 December 9
        !             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.
        !            12: #
        !            13: # This file implements tests for N-way joins (N>2) which make
        !            14: # use of USING or NATURAL JOIN.  For such joins, the USING and
        !            15: # NATURAL JOIN processing needs to search all tables to the left
        !            16: # of the join looking for a match.  See ticket [f74beaabde]
        !            17: # for additional information.
        !            18: #
        !            19: 
        !            20: set testdir [file dirname $argv0]
        !            21: source $testdir/tester.tcl
        !            22: 
        !            23: 
        !            24: # The problem as initially reported on the mailing list:
        !            25: #
        !            26: do_test join6-1.1 {
        !            27:   execsql {
        !            28:     CREATE TABLE t1(a);
        !            29:     CREATE TABLE t2(a);
        !            30:     CREATE TABLE t3(a,b);
        !            31:     INSERT INTO t1 VALUES(1);
        !            32:     INSERT INTO t3 VALUES(1,2);
        !            33: 
        !            34:     SELECT * FROM t1 LEFT JOIN t2 USING(a) LEFT JOIN t3 USING(a);
        !            35:   }
        !            36: } {1 2}
        !            37: do_test join6-1.2 {
        !            38:   execsql {
        !            39:     SELECT t1.a, t3.b 
        !            40:       FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t2.a=t3.a;
        !            41:   }
        !            42: } {1 {}}
        !            43: do_test join6-1.3 {
        !            44:   execsql {
        !            45:     SELECT t1.a, t3.b
        !            46:       FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;
        !            47:   }
        !            48: } {1 2}
        !            49: 
        !            50: 
        !            51: do_test join6-2.1 {
        !            52:   execsql {
        !            53:     DROP TABLE t1;
        !            54:     DROP TABLE t2;
        !            55:     DROP TABLE t3;
        !            56: 
        !            57:     CREATE TABLE t1(x,y);
        !            58:     CREATE TABLE t2(y,z);
        !            59:     CREATE TABLE t3(x,z);
        !            60: 
        !            61:     INSERT INTO t1 VALUES(1,2);
        !            62:     INSERT INTO t1 VALUES(3,4);
        !            63: 
        !            64:     INSERT INTO t2 VALUES(2,3);
        !            65:     INSERT INTO t2 VALUES(4,5);
        !            66: 
        !            67:     INSERT INTO t3 VALUES(1,3);
        !            68:     INSERT INTO t3 VALUES(3,5);
        !            69: 
        !            70:     SELECT * FROM t1 JOIN t2 USING (y) JOIN t3 USING(x);
        !            71:   }
        !            72: } {1 2 3 3 3 4 5 5}
        !            73: do_test join6-2.2 {
        !            74:   execsql {
        !            75:     SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
        !            76:   }
        !            77: } {1 2 3 3 4 5}
        !            78: 
        !            79: 
        !            80: do_test join6-3.1 {
        !            81:   execsql {
        !            82:     DROP TABLE t1;
        !            83:     DROP TABLE t2;
        !            84:     DROP TABLE t3;
        !            85: 
        !            86:     CREATE TABLE t1(a,x,y);
        !            87:     INSERT INTO t1 VALUES(1,91,92);
        !            88:     INSERT INTO t1 VALUES(2,93,94);
        !            89:     
        !            90:     CREATE TABLE t2(b,y,z);
        !            91:     INSERT INTO t2 VALUES(3,92,93);
        !            92:     INSERT INTO t2 VALUES(4,94,95);
        !            93:     
        !            94:     CREATE TABLE t3(c,x,z);
        !            95:     INSERT INTO t3 VALUES(5,91,93);
        !            96:     INSERT INTO t3 VALUES(6,99,95);
        !            97:     
        !            98:     SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
        !            99:   }
        !           100: } {1 91 92 3 93 5}
        !           101: do_test join6-3.2 {
        !           102:   execsql {
        !           103:     SELECT * FROM t1 JOIN t2 NATURAL JOIN t3;
        !           104:   }
        !           105: } {1 91 92 3 92 93 5}
        !           106: do_test join6-3.3 {
        !           107:   execsql {
        !           108:     SELECT * FROM t1 JOIN t2 USING(y) NATURAL JOIN t3;
        !           109:   }
        !           110: } {1 91 92 3 93 5}
        !           111: do_test join6-3.4 {
        !           112:   execsql {
        !           113:     SELECT * FROM t1 NATURAL JOIN t2 JOIN t3 USING(x,z);
        !           114:   }
        !           115: } {1 91 92 3 93 5}
        !           116: do_test join6-3.5 {
        !           117:   execsql {
        !           118:     SELECT * FROM t1 NATURAL JOIN t2 JOIN t3 USING(x);
        !           119:   }
        !           120: } {1 91 92 3 93 5 93}
        !           121: do_test join6-3.6 {
        !           122:   execsql {
        !           123:     SELECT * FROM t1 NATURAL JOIN t2 JOIN t3 USING(z);
        !           124:   }
        !           125: } {1 91 92 3 93 5 91 2 93 94 4 95 6 99}
        !           126: 
        !           127: do_test join6-4.1 {
        !           128:   execsql {
        !           129:     SELECT * FROM
        !           130:        (SELECT 1 AS a, 91 AS x, 92 AS y UNION SELECT 2, 93, 94)
        !           131:        NATURAL JOIN t2 NATURAL JOIN t3
        !           132:   }
        !           133: } {1 91 92 3 93 5}
        !           134: do_test join6-4.2 {
        !           135:   execsql {
        !           136:     SELECT * FROM t1 NATURAL JOIN
        !           137:        (SELECT 3 AS b, 92 AS y, 93 AS z UNION SELECT 4, 94, 95)
        !           138:        NATURAL JOIN t3
        !           139:   }
        !           140: } {1 91 92 3 93 5}
        !           141: do_test join6-4.3 {
        !           142:   execsql {
        !           143:     SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN
        !           144:        (SELECT 5 AS c, 91 AS x, 93 AS z UNION SELECT 6, 99, 95)
        !           145:   }
        !           146: } {1 91 92 3 93 5}
        !           147: 
        !           148: 
        !           149: 
        !           150: 
        !           151: 
        !           152: 
        !           153: 
        !           154: 
        !           155: 
        !           156: 
        !           157: 
        !           158: finish_test