Home | History | Annotate | Download | only in test 
      1 # 2002 May 24
      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 left outer joins containing WHERE
     14 # 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 
     18 set testdir [file dirname $argv0]
     19 source $testdir/tester.tcl
     20 
     21 ifcapable 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 }
     56 do_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 {} {} {}}
     61 do_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}
     69 do_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 {} {} {}}
     74 do_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}
     79 do_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 {} {} {}}
     84 ifcapable 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 
     98 finish_test
     99