modernc.org/cc@v1.0.1/v2/testdata/_sqlite/test/join2.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 joins, including outer joins.
    14  #
    15  
    16  set testdir [file dirname $argv0]
    17  source $testdir/tester.tcl
    18  set testprefix join2
    19  
    20  do_test join2-1.1 {
    21    execsql {
    22      CREATE TABLE t1(a,b);
    23      INSERT INTO t1 VALUES(1,11);
    24      INSERT INTO t1 VALUES(2,22);
    25      INSERT INTO t1 VALUES(3,33);
    26      SELECT * FROM t1;
    27    }  
    28  } {1 11 2 22 3 33}
    29  do_test join2-1.2 {
    30    execsql {
    31      CREATE TABLE t2(b,c);
    32      INSERT INTO t2 VALUES(11,111);
    33      INSERT INTO t2 VALUES(33,333);
    34      INSERT INTO t2 VALUES(44,444);
    35      SELECT * FROM t2;
    36    }  
    37  } {11 111 33 333 44 444};
    38  do_test join2-1.3 {
    39    execsql {
    40      CREATE TABLE t3(c,d);
    41      INSERT INTO t3 VALUES(111,1111);
    42      INSERT INTO t3 VALUES(444,4444);
    43      INSERT INTO t3 VALUES(555,5555);
    44      SELECT * FROM t3;
    45    }  
    46  } {111 1111 444 4444 555 5555}
    47  
    48  do_test join2-1.4 {
    49    execsql {
    50      SELECT * FROM
    51        t1 NATURAL JOIN t2 NATURAL JOIN t3
    52    }
    53  } {1 11 111 1111}
    54  do_test join2-1.5 {
    55    execsql {
    56      SELECT * FROM
    57        t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3
    58    }
    59  } {1 11 111 1111 3 33 333 {}}
    60  do_test join2-1.6 {
    61    execsql {
    62      SELECT * FROM
    63        t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3
    64    }
    65  } {1 11 111 1111}
    66  ifcapable subquery {
    67    do_test join2-1.7 {
    68      execsql {
    69        SELECT * FROM
    70          t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3)
    71      }
    72    } {1 11 111 1111 2 22 {} {} 3 33 {} {}}
    73  }
    74  
    75  #-------------------------------------------------------------------------
    76  # Check that ticket [25e335f802ddc] has been resolved. It should be an
    77  # error for the ON clause of a LEFT JOIN to refer to a table to its right.
    78  #
    79  do_execsql_test 2.0 {
    80    CREATE TABLE aa(a);
    81    CREATE TABLE bb(b);
    82    CREATE TABLE cc(c);
    83    INSERT INTO aa VALUES('one');
    84    INSERT INTO bb VALUES('one');
    85    INSERT INTO cc VALUES('one');
    86  }
    87  
    88  do_catchsql_test 2.1 {
    89    SELECT * FROM aa LEFT JOIN cc ON (a=b) JOIN bb ON (b=c);
    90  } {1 {ON clause references tables to its right}}
    91  do_catchsql_test 2.2 {
    92    SELECT * FROM aa JOIN cc ON (a=b) JOIN bb ON (b=c);
    93  } {0 {one one one}}
    94  
    95  finish_test