AryaWu/sqlite

1# 2009 December 92#3# The author disclaims copyright to this source code.  In place of4# 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 make14# use of USING or NATURAL JOIN.  For such joins, the USING and15# NATURAL JOIN processing needs to search all tables to the left16# of the join looking for a match.  See ticket [f74beaabde]17# for additional information.18#19 20set testdir [file dirname $argv0]21source $testdir/tester.tcl22 23 24# The problem as initially reported on the mailing list:25#26do_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}37do_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 {}}43do_test join6-1.3 {44  execsql {45    SELECT t1.a, t3.b46      FROM t1 LEFT JOIN t2 ON t1.a=t2.a LEFT JOIN t3 ON t1.a=t3.a;47  }48} {1 2}49 50 51do_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}73do_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 80do_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}101do_test join6-3.2 {102  execsql {103    SELECT * FROM t1 JOIN t2 NATURAL JOIN t3;104  }105} {1 91 92 3 92 93 5}106do_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}111do_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}116do_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}121do_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 127ifcapable compound {128  do_test join6-4.1 {129    execsql {130      SELECT * FROM131         (SELECT 1 AS a, 91 AS x, 92 AS y UNION SELECT 2, 93, 94)132         NATURAL JOIN t2 NATURAL JOIN t3133    }134  } {1 91 92 3 93 5}135  do_test join6-4.2 {136    execsql {137      SELECT * FROM t1 NATURAL JOIN138         (SELECT 3 AS b, 92 AS y, 93 AS z UNION SELECT 4, 94, 95)139         NATURAL JOIN t3140    }141  } {1 91 92 3 93 5}142  do_test join6-4.3 {143    execsql {144      SELECT * FROM t1 NATURAL JOIN t2 NATURAL JOIN145         (SELECT 5 AS c, 91 AS x, 93 AS z UNION SELECT 6, 99, 95)146    }147  } {1 91 92 3 93 5}148}149 150do_execsql_test join6-5.1 {151  CREATE TABLE tx(a, b, c, d, e, f, g, h, i, j, k, l, m, n, o PRIMARY KEY) 152  WITHOUT ROWID;153  INSERT INTO tx VALUES(154    1,2,3,4,5,6,7,8,9,10,11,12,13,14,15155  );156} {}157do_execsql_test joint6-5.2 {158  SELECT o FROM tx NATURAL JOIN tx;159} {15}160 161do_execsql_test join6-5.3 {162  CREATE TABLE ty(a,Ñ,x6,x7,x8,Q,I,v,x1,L,E,x2,x3,x4,x5,s,g PRIMARY KEY,b,c)163  WITHOUT ROWID;164  SELECT a FROM ty NATURAL JOIN ty;165}166 167 168 169 170 171 172 173 174 175 176finish_test177