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

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