AryaWu/sqlite

1# 2002 May 242#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 left outer joins containing WHERE14# clauses that restrict the scope of the left term of the join.15#16# $Id: join4.test,v 1.4 2005/03/29 03:11:00 danielk1977 Exp $17 18set testdir [file dirname $argv0]19source $testdir/tester.tcl20 21ifcapable tempdb {22  do_test join4-1.1 {23    execsql {24      create temp table t1(a integer, b varchar(10));25      insert into t1 values(1,'one');26      insert into t1 values(2,'two');27      insert into t1 values(3,'three');28      insert into t1 values(4,'four');29  30      create temp table t2(x integer, y varchar(10), z varchar(10));31      insert into t2 values(2,'niban','ok');32      insert into t2 values(4,'yonban','err');33    }34    execsql {35      select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'36    }37  } {2 two 2 niban ok}38} else {39  do_test join4-1.1 {40    execsql {41      create table t1(a integer, b varchar(10));42      insert into t1 values(1,'one');43      insert into t1 values(2,'two');44      insert into t1 values(3,'three');45      insert into t1 values(4,'four');46  47      create table t2(x integer, y varchar(10), z varchar(10));48      insert into t2 values(2,'niban','ok');49      insert into t2 values(4,'yonban','err');50    }51    execsql {52      select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'53    }54  } {2 two 2 niban ok}55}56do_test join4-1.2 {57  execsql {58    select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok'59  }60} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}61do_test join4-1.3 {62  execsql {63    create index i2 on t2(z);64  }65  execsql {66    select * from t1 left outer join t2 on t1.a=t2.x where t2.z='ok'67  }68} {2 two 2 niban ok}69do_test join4-1.4 {70  execsql {71    select * from t1 left outer join t2 on t1.a=t2.x and t2.z='ok'72  }73} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}74do_test join4-1.5 {75  execsql {76    select * from t1 left outer join t2 on t1.a=t2.x where t2.z>='ok'77  }78} {2 two 2 niban ok}79do_test join4-1.4 {80  execsql {81    select * from t1 left outer join t2 on t1.a=t2.x and t2.z>='ok'82  }83} {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}84ifcapable subquery {85  do_test join4-1.6 {86    execsql {87      select * from t1 left outer join t2 on t1.a=t2.x where t2.z IN ('ok')88    }89  } {2 two 2 niban ok}90  do_test join4-1.7 {91    execsql {92      select * from t1 left outer join t2 on t1.a=t2.x and t2.z IN ('ok')93    }94  } {1 one {} {} {} 2 two 2 niban ok 3 three {} {} {} 4 four {} {} {}}95}96 97 98finish_test99