gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/tkt-3a77c9714e.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 ifcapable !compound { 20 finish_test 21 return 22 } 23 24 set testprefix "tkt-3a77c9714e" 25 26 do_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 36 do_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 45 do_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 55 do_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 # 73 do_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 86 finish_test