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

     1  # 2020-12-22
     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.  The
    12  # focus of this file is flattening UNION ALL sub-queries.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  set testprefix unionall2
    18  
    19  do_execsql_test 1.0 {
    20    CREATE TABLE t1(a, b);
    21    CREATE TABLE t2(c, d);
    22  
    23    CREATE VIEW v1 AS SELECT * FROM t1, t2;
    24    CREATE VIEW v2 AS SELECT * FROM t1, t2;
    25  
    26    CREATE VIEW vA AS
    27      SELECT * FROM v1, (
    28        SELECT * FROM t1 LEFT JOIN t2 ON (a=c)
    29      )
    30      UNION ALL
    31      SELECT * FROM v1, v2
    32  }
    33  
    34  do_execsql_test 1.1 {
    35    SELECT 1 FROM vA, vA, vA, vA, vA, vA, vA, vA, vA, vA
    36  }
    37  
    38  #-------------------------------------------------------------------------
    39  
    40  do_execsql_test 2.1 {
    41    CREATE TABLE y1(a INTEGER, b);
    42    CREATE TABLE y2(c INTEGER, d);
    43  
    44    CREATE TABLE x3_a(a INTEGER PRIMARY KEY, b TEXT);
    45    CREATE TABLE x3_b(c INTEGER PRIMARY KEY, d TEXT);
    46  }
    47  
    48  do_execsql_test 2.2 {
    49  
    50    SELECT * FROM y1 CROSS JOIN y2 WHERE y1.a=y2.c AND y2.c IN (
    51      SELECT a FROM x3_a UNION ALL
    52      SELECT c FROM x3_b ORDER BY 1
    53    )
    54  }
    55  
    56  
    57  
    58  finish_test