github.com/jdgcs/sqlite3@v1.12.1-0.20210908114423-bc5f96e4dd51/testdata/tcl/tkt-fc7bd6358f.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 [fc7bd6358f]:
    13  #
    14  # The following SQL yields an incorrect result (zero rows) in all
    15  # versions of SQLite between 3.6.14 and 3.7.15.2:
    16  #
    17  #    CREATE TABLE t(textid TEXT);
    18  #    INSERT INTO t VALUES('12');
    19  #    INSERT INTO t VALUES('34');
    20  #    CREATE TABLE i(intid INTEGER PRIMARY KEY);
    21  #    INSERT INTO i VALUES(12);
    22  #    INSERT INTO i VALUES(34);
    23  #
    24  #    SELECT t1.textid AS a, i.intid AS b, t2.textid AS c
    25  #      FROM t t1, i, t t2
    26  #     WHERE t1.textid = i.intid
    27  #       AND t1.textid = t2.textid;
    28  #
    29  # The correct result should be two rows, one with 12|12|12 and the other
    30  # with 34|34|34. With this bug, no rows are returned. Bisecting shows that
    31  # this bug was introduced with check-in [dd4d67a67454] on 2009-04-23. 
    32  #
    33  
    34  set testdir [file dirname $argv0]
    35  source $testdir/tester.tcl
    36  
    37  do_test tkt-fc7bd6358f.100 {
    38    db eval {
    39      CREATE TABLE t(textid TEXT);
    40      INSERT INTO t VALUES('12');
    41      INSERT INTO t VALUES('34');
    42      CREATE TABLE i(intid INTEGER PRIMARY KEY);
    43      INSERT INTO i VALUES(12);
    44      INSERT INTO i VALUES(34);
    45    }
    46  } {}
    47  unset -nocomplain from
    48  unset -nocomplain where
    49  unset -nocomplain a
    50  unset -nocomplain b
    51  foreach {a from} {
    52    1 {FROM t t1, i, t t2}
    53    2 {FROM i, t t1, t t2}
    54    3 {FROM t t1, t t2, i}
    55  } {
    56    foreach {b where} {
    57      1 {WHERE t1.textid=i.intid AND t1.textid=t2.textid}
    58      2 {WHERE i.intid=t1.textid AND t1.textid=t2.textid}
    59      3 {WHERE t1.textid=i.intid AND i.intid=t2.textid}
    60      4 {WHERE t1.textid=i.intid AND t2.textid=i.intid}
    61      5 {WHERE i.intid=t1.textid AND i.intid=t2.textid}
    62      6 {WHERE i.intid=t1.textid AND t2.textid=i.intid}
    63      7 {WHERE t1.textid=t2.textid AND i.intid=t2.textid}
    64      8 {WHERE t1.textid=t2.textid AND t2.textid=i.intid}
    65    } {
    66      do_test tkt-fc7bd6358f.110.$a.$b.1 {
    67         db eval {PRAGMA automatic_index=ON}
    68         db eval "SELECT t1.textid, i.intid, t2.textid $from $where"
    69      } {12 12 12 34 34 34}
    70      do_test tkt-fc7bd6358f.110.$a.$b.2 {
    71         db eval {PRAGMA automatic_index=OFF}
    72         db eval "SELECT t1.textid, i.intid, t2.textid $from $where"
    73      } {12 12 12 34 34 34}
    74    }
    75  }
    76  
    77      
    78  finish_test