Debian Codegraph @

test/upfrom4.test

raw
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 # Forum post https://sqlite.org/forum/forumpost/36ff78b2a3
131 #
132 ifcapable update_delete_limit {
133 reset_db
134 do_execsql_test 500 {
135 CREATE TABLE t1(abc INT, def INT);
136 INSERT INTO t1 VALUES(0,0);
137 INSERT INTO t1 VALUES(0,0);
138 INSERT INTO t1 VALUES(0,0);
139 CREATE TABLE dual(dummy TEXT);
140 INSERT INTO dual(dummy) VALUES('X');
141 } {}
142
143 do_execsql_test 510 {
144 UPDATE t1
145 SET (abc, def)=(SELECT x, 123)
146 FROM dual LEFT JOIN (SELECT 789 AS 'x' FROM dual) AS d2
147 LIMIT 2
148 }
149
150 do_execsql_test 520 {
151 SELECT * FROM t1
152 } {789 123 789 123 0 0}
153 }
154
155
156 finish_test