1# 2011-12-06 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# This file implements tests to verify that ticket [3a77c9714e] has been 14# fixed. 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18 19ifcapable !compound { 20 finish_test 21 return 22} 23 24set testprefix "tkt-3a77c9714e" 25 26do_execsql_test 1.1 { 27 CREATE TABLE t1(t1_id INTEGER PRIMARY KEY, t1_title TEXT); 28 CREATE TABLE t2(t2_id INTEGER PRIMARY KEY, t2_title TEXT); 29 CREATE TABLE t3(t3_id INTEGER PRIMARY KEY, t3_title TEXT); 30 31 INSERT INTO t1 (t1_id, t1_title) VALUES (888, 'ABCDEF'); 32 INSERT INTO t2 (t2_id, t2_title) VALUES (999, 'ABCDEF'); 33 INSERT INTO t3 (t3_id, t3_title) VALUES (999, 'ABCDEF'); 34} 35 36do_execsql_test 1.2 { 37 SELECT t1_title, t2_title 38 FROM t1 LEFT JOIN t2 39 WHERE 40 t2_id = (SELECT t3_id FROM 41 ( SELECT t3_id FROM t3 WHERE t3_title=t1_title LIMIT 500 ) 42 ) 43} {ABCDEF ABCDEF} 44 45do_execsql_test 2.1 { 46 CREATE TABLE [Beginnings] ( 47 [Id] INTEGER PRIMARY KEY AUTOINCREMENT,[Title] TEXT, [EndingId] INTEGER 48 ); 49 CREATE TABLE [Endings] (Id INT,Title TEXT,EndingId INT); 50 INSERT INTO Beginnings (Id, Title, EndingId) VALUES (1, 'FACTOR', 18); 51 INSERT INTO Beginnings (Id, Title, EndingId) VALUES (2, 'SWIMM', 18); 52 INSERT INTO Endings (Id, Title, EndingId) VALUES (1, 'ING', 18); 53} 54 55do_execsql_test 2.2 { 56 SELECT 57 SrcWord, Beginnings.Title 58 FROM 59 (SELECT 'FACTORING' AS SrcWord UNION SELECT 'SWIMMING' AS SrcWord ) 60 LEFT JOIN 61 Beginnings 62 WHERE Beginnings.Id= ( 63 SELECT BeginningId FROM ( 64 SELECT SrcWord, B.Id as BeginningId, B.Title || E.Title As Connected 65 FROM Beginnings B LEFT JOIN Endings E ON B.EndingId=E.EndingId 66 WHERE Connected=SrcWord LIMIT 1 67 ) 68 ) 69} {FACTORING FACTOR SWIMMING SWIMM} 70 71# Similar problem discovered by dbsqlfuzz on 2019-09-18 72# 73do_execsql_test 3.0 { 74 DROP TABLE IF EXISTS t1; 75 CREATE TABLE t1(i INT PRIMARY KEY, a, b); 76 INSERT INTO t1 VALUES(NULL,'one','i'); 77 CREATE INDEX i1a ON t1(a); 78 CREATE INDEX i1b ON t1(b); 79 SELECT (SELECT 1 80 FROM (SELECT 1 FROM t1 WHERE a=1 OR b='i') 81 WHERE a='o' 82 OR b IN (SELECT a=('b' IN (SELECT 'a')))) 83 FROM t1; 84} {{}} 85 86finish_test 87