gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt-868145d012.test (about)

     1  # 2013 March 05
     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. Specifically,
    12  # it tests that ticket [868145d012a1] is fixed.
    13  #
    14  
    15  set testdir [file dirname $argv0]
    16  source $testdir/tester.tcl
    17  
    18  do_execsql_test tkt-868145d012.100 {
    19    CREATE TABLE p (
    20      id INTEGER PRIMARY KEY,
    21      uid VARCHAR(36),
    22      t INTEGER
    23    );
    24    
    25    CREATE TABLE pa (
    26      id INTEGER PRIMARY KEY,
    27      a_uid VARCHAR(36)
    28    );
    29    
    30    CREATE TABLE a (
    31      id INTEGER PRIMARY KEY,
    32      uid VARCHAR(36),
    33      t INTEGER
    34    );
    35    
    36    INSERT INTO pa VALUES(1,'1234');
    37    INSERT INTO pa VALUES(2,'2345');
    38    INSERT INTO p VALUES(3,'1234',97);
    39    INSERT INTO p VALUES(4,'1234',98);
    40    INSERT INTO a VALUES(5,'1234',98);
    41    INSERT INTO a VALUES(6,'1234',99);
    42  } {}
    43  do_execsql_test tkt-868145d012.110 {
    44    SELECT DISTINCT pa.id, p.id, a.id
    45    FROM
    46      pa
    47      LEFT JOIN p ON p.uid='1234'
    48      LEFT JOIN a ON a.uid=pa.a_uid
    49    WHERE
    50      a.t=p.t
    51    ;
    52  } {1 4 5}
    53  do_execsql_test tkt-868145d012.120 {
    54    SELECT DISTINCT pa.id, p.id, a.id
    55    FROM
    56      pa
    57      LEFT JOIN p ON p.uid='1234'
    58      LEFT JOIN a ON a.uid=pa.a_uid AND a.t=p.t
    59    ORDER BY 1, 2, 3
    60    ;
    61  } {1 3 {} 1 4 5 2 3 {} 2 4 {}}
    62  
    63      
    64  finish_test