github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/join4.test (about)

     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