gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/joinH.test (about)

     1  # 2022 May 17
     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  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  set testprefix joinH
    17  
    18  do_execsql_test 1.0 {
    19    CREATE TABLE t1(a INT);
    20    CREATE TABLE t2(b INT);
    21    INSERT INTO t2(b) VALUES(NULL);
    22  }
    23  
    24  db nullvalue NULL
    25  
    26  do_execsql_test 1.1 {
    27    SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);
    28  } {NULL}
    29  do_execsql_test 1.2 {
    30    SELECT a FROM t1 FULL JOIN t2 ON true;
    31  } {NULL}
    32  do_execsql_test 1.3 {
    33    SELECT a FROM t1 FULL JOIN t2 ON true WHERE (b ISNULL);
    34  } {NULL}
    35  do_execsql_test 1.4 {
    36    SELECT DISTINCT a FROM t1 FULL JOIN t2 ON true;
    37  } {NULL}
    38  
    39  #-----------------------------------------------------------
    40  
    41  reset_db
    42  do_execsql_test 2.0 {
    43    CREATE TABLE r3(x);
    44    CREATE TABLE r4(y INTEGER PRIMARY KEY);
    45    INSERT INTO r4 VALUES(55);
    46  }
    47  
    48  do_execsql_test 2.1 {
    49    SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x);
    50  } {value!}
    51  
    52  do_execsql_test 2.2 {
    53    SELECT 'value!' FROM r3 FULL JOIN r4 ON (y=x) WHERE +y=55;
    54  } {value!}
    55  
    56  #-----------------------------------------------------------
    57  reset_db
    58  do_execsql_test 3.1 {
    59    CREATE TABLE t0 (c0);
    60    CREATE TABLE t1 (c0);
    61    CREATE TABLE t2 (c0 , c1 , c2 , UNIQUE (c0), UNIQUE (c2 DESC));
    62    INSERT INTO t2 VALUES ('x', 'y', 'z');
    63    ANALYZE;
    64    CREATE VIEW v0(c0) AS SELECT FALSE;
    65  }
    66  
    67  do_catchsql_test 3.2 {
    68    SELECT * FROM t0 LEFT OUTER JOIN t1 ON v0.c0 INNER JOIN v0 INNER JOIN t2 ON (t2.c2 NOT NULL); 
    69  } {1 {ON clause references tables to its right}}
    70  
    71  
    72  finish_test