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 34set testdir [file dirname $argv0] 35source $testdir/tester.tcl 36 37do_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} {} 47unset -nocomplain from 48unset -nocomplain where 49unset -nocomplain a 50unset -nocomplain b 51foreach {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 78finish_test 79