gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/upfrom4.test (about) 1 # 2022-05-24 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 # 12 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix upfrom4 16 17 do_execsql_test 100 { 18 DROP TABLE IF EXISTS t5; 19 DROP TABLE IF EXISTS m1; 20 DROP TABLE IF EXISTS m2; 21 CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); 22 CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); 23 CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); 24 25 INSERT INTO t5 VALUES(1, 'one', 'ONE'); 26 INSERT INTO t5 VALUES(2, 'two', 'TWO'); 27 INSERT INTO t5 VALUES(3, 'three', 'THREE'); 28 INSERT INTO t5 VALUES(4, 'four', 'FOUR'); 29 30 INSERT INTO m1 VALUES(1, 'i'); 31 INSERT INTO m1 VALUES(2, 'ii'); 32 INSERT INTO m1 VALUES(3, 'iii'); 33 34 INSERT INTO m2 VALUES(1, 'I'); 35 INSERT INTO m2 VALUES(3, 'II'); 36 INSERT INTO m2 VALUES(4, 'III'); 37 SELECT * FROM t5; 38 } {1 one ONE 2 two TWO 3 three THREE 4 four FOUR} 39 40 do_execsql_test 110 { 41 BEGIN; 42 UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; 43 SELECT * FROM t5 ORDER BY a; 44 ROLLBACK; 45 } {1 i I 2 ii {} 3 iii II 4 four FOUR} 46 47 do_execsql_test 120 { 48 BEGIN; 49 UPDATE t5 SET b=y, c=v FROM m2 RIGHT JOIN m1 ON (x=u) WHERE x=a; 50 SELECT * FROM t5 ORDER BY a; 51 ROLLBACK; 52 } {1 i I 2 ii {} 3 iii II 4 four FOUR} 53 54 55 reset_db 56 db null - 57 do_execsql_test 200 { 58 CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT); 59 INSERT INTO t1(a) VALUES(1),(2),(8),(19); 60 CREATE TABLE c1(x INTEGER PRIMARY KEY, b INT); 61 INSERT INTO c1(x,b) VALUES(1,1),(8,8),(17,17),(NULL,NULL); 62 CREATE TABLE c2(x INT,c INT); 63 INSERT INTO c2(x,c) VALUES(2,2),(8,8),(NULL,NULL); 64 CREATE TABLE dual(dummy TEXT); 65 INSERT INTO dual VALUES('X'); 66 } {} 67 do_execsql_test 210 { 68 BEGIN; 69 SELECT * FROM t1 ORDER BY a; 70 UPDATE t1 SET b=c1.b, c=c2.c 71 FROM dual, c1 NATURAL RIGHT JOIN c2 72 WHERE x=a; 73 SELECT * FROM t1 ORDER BY a; 74 ROLLBACK; 75 } { 76 1 - - 77 2 - - 78 8 - - 79 19 - - 80 1 - - 81 2 - 2 82 8 8 8 83 19 - - 84 } 85 do_execsql_test 300 { 86 CREATE TABLE t2(x); 87 CREATE TRIGGER AFTER INSERT ON t2 BEGIN 88 UPDATE t1 SET b=c1.b, c=c2.c 89 FROM dual, c1 NATURAL RIGHT JOIN c2 90 WHERE x=a; 91 END; 92 } {} 93 do_execsql_test 310 { 94 BEGIN; 95 SELECT * FROM t1 ORDER BY a; 96 INSERT INTO t2(x) VALUES(1); 97 SELECT * FROM t1 ORDER BY a; 98 ROLLBACK; 99 } { 100 1 - - 101 2 - - 102 8 - - 103 19 - - 104 1 - - 105 2 - 2 106 8 8 8 107 19 - - 108 } 109 110 # 2022-05-26 dbsqlfuzz crash-9401d6ba699f1257d352a657de236286bf2b14da 111 # 112 reset_db 113 db null - 114 do_execsql_test 400 { 115 CREATE TABLE t2(x,y,z PRIMARY KEY) WITHOUT ROWID; 116 INSERT INTO t2 VALUES(89,-89,6); 117 CREATE TABLE t1(a INT,b TEXT,c TEXT,d REAL) STRICT; 118 INSERT INTO t1 VALUES(1,'xyz','def',4.5); 119 CREATE TRIGGER t1tr BEFORE UPDATE ON t1 BEGIN 120 INSERT INTO t1(a,b) VALUES(1000,'uvw'); 121 UPDATE t1 SET b=NULL FROM (SELECT CAST(a AS varchar) FROM t1 ORDER BY b) NATURAL LEFT FULL JOIN t1 AS text; 122 END; 123 UPDATE t1 SET b=b|100; 124 SELECT * FROM t1 ORDER BY a; 125 } { 126 1 100 def 4.5 127 1000 - - - 128 } 129 130 finish_test