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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix upfrom4 16 17do_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 40do_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 47do_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 55reset_db 56db null - 57do_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} {} 67do_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} 85do_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} {} 93do_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# 112reset_db 113db null - 114do_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 130finish_test 131