gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/upfrom1.test (about) 1 # 2020 April 22 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 14 #################################################### 15 # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 16 #################################################### 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 set testprefix upfrom1 21 22 do_execsql_test 1.1.0 { 23 DROP TABLE IF EXISTS t2; 24 CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) WITHOUT ROWID; 25 INSERT INTO t2 VALUES(1, 2, 3); 26 INSERT INTO t2 VALUES(4, 5, 6); 27 INSERT INTO t2 VALUES(7, 8, 9); 28 29 DROP TABLE IF EXISTS chng; 30 CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); 31 INSERT INTO chng VALUES(1, 100, 1000); 32 INSERT INTO chng VALUES(7, 700, 7000); 33 } {} 34 35 do_execsql_test 1.1.1 { 36 SELECT * FROM t2; 37 } {1 2 3 4 5 6 7 8 9} 38 39 do_execsql_test 1.1.2 { 40 UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; 41 SELECT * FROM t2 ORDER BY a; 42 } {1 100 1000 4 5 6 7 700 7000} 43 44 do_execsql_test 1.1.3 { 45 DELETE FROM t2; 46 INSERT INTO t2 VALUES(1, 2, 3); 47 INSERT INTO t2 VALUES(4, 5, 6); 48 INSERT INTO t2 VALUES(7, 8, 9); 49 } {} 50 51 do_execsql_test 1.1.4 { 52 UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) 53 WHERE a IN (SELECT a FROM chng); 54 SELECT * FROM t2 ORDER BY a; 55 } {1 100 1000 4 5 6 7 700 7000} 56 57 do_execsql_test 1.1.5 { 58 DROP TABLE IF EXISTS t3; 59 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) WITHOUT ROWID; 60 INSERT INTO t3 VALUES(1, 1, 'one'); 61 INSERT INTO t3 VALUES(2, 2, 'two'); 62 INSERT INTO t3 VALUES(3, 3, 'three'); 63 64 DROP TABLE IF EXISTS t4; 65 CREATE TABLE t4(x TEXT); 66 INSERT INTO t4 VALUES('five'); 67 68 SELECT * FROM t3 ORDER BY a; 69 } {1 1 one 2 2 two 3 3 three} 70 71 do_execsql_test 1.1.6 { 72 UPDATE t3 SET c=x FROM t4; 73 SELECT * FROM t3 ORDER BY a; 74 } {1 1 five 2 2 five 3 3 five} 75 76 do_execsql_test 1.2.0 { 77 DROP TABLE IF EXISTS t2; 78 CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) ; 79 INSERT INTO t2 VALUES(1, 2, 3); 80 INSERT INTO t2 VALUES(4, 5, 6); 81 INSERT INTO t2 VALUES(7, 8, 9); 82 83 DROP TABLE IF EXISTS chng; 84 CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); 85 INSERT INTO chng VALUES(1, 100, 1000); 86 INSERT INTO chng VALUES(7, 700, 7000); 87 } {} 88 89 do_execsql_test 1.2.1 { 90 SELECT * FROM t2; 91 } {1 2 3 4 5 6 7 8 9} 92 93 do_execsql_test 1.2.2 { 94 UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; 95 SELECT * FROM t2 ORDER BY a; 96 } {1 100 1000 4 5 6 7 700 7000} 97 98 do_execsql_test 1.2.3 { 99 DELETE FROM t2; 100 INSERT INTO t2 VALUES(1, 2, 3); 101 INSERT INTO t2 VALUES(4, 5, 6); 102 INSERT INTO t2 VALUES(7, 8, 9); 103 } {} 104 105 do_execsql_test 1.2.4 { 106 UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) 107 WHERE a IN (SELECT a FROM chng); 108 SELECT * FROM t2 ORDER BY a; 109 } {1 100 1000 4 5 6 7 700 7000} 110 111 do_execsql_test 1.2.5 { 112 DROP TABLE IF EXISTS t3; 113 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) ; 114 INSERT INTO t3 VALUES(1, 1, 'one'); 115 INSERT INTO t3 VALUES(2, 2, 'two'); 116 INSERT INTO t3 VALUES(3, 3, 'three'); 117 118 DROP TABLE IF EXISTS t4; 119 CREATE TABLE t4(x TEXT); 120 INSERT INTO t4 VALUES('five'); 121 122 SELECT * FROM t3 ORDER BY a; 123 } {1 1 one 2 2 two 3 3 three} 124 125 do_execsql_test 1.2.6 { 126 UPDATE t3 SET c=x FROM t4; 127 SELECT * FROM t3 ORDER BY a; 128 } {1 1 five 2 2 five 3 3 five} 129 130 do_execsql_test 2.1 { 131 DROP TABLE IF EXISTS t5; 132 DROP TABLE IF EXISTS m1; 133 DROP TABLE IF EXISTS m2; 134 CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); 135 CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); 136 CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); 137 138 INSERT INTO t5 VALUES(1, 'one', 'ONE'); 139 INSERT INTO t5 VALUES(2, 'two', 'TWO'); 140 INSERT INTO t5 VALUES(3, 'three', 'THREE'); 141 INSERT INTO t5 VALUES(4, 'four', 'FOUR'); 142 143 INSERT INTO m1 VALUES(1, 'i'); 144 INSERT INTO m1 VALUES(2, 'ii'); 145 INSERT INTO m1 VALUES(3, 'iii'); 146 147 INSERT INTO m2 VALUES(1, 'I'); 148 INSERT INTO m2 VALUES(3, 'II'); 149 INSERT INTO m2 VALUES(4, 'III'); 150 } {} 151 152 do_execsql_test 2.2 { 153 UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; 154 SELECT * FROM t5 ORDER BY a; 155 } {1 i I 2 ii {} 3 iii II 4 four FOUR} 156 157 # PG says ERROR: table name "t5" specified more than once 158 do_test 2.3.1 { catch { execsql { 159 UPDATE t5 SET b=1 FROM t5; 160 } } } 1 161 162 # PG says ERROR: table name "apples" specified more than once 163 do_test 2.3.2 { catch { execsql { 164 UPDATE t5 AS apples SET b=1 FROM t5 AS apples; 165 } } } 1 166 167 # Problem found by OSSFuzz on 2020-07-20 168 # https://bugs.chromium.org/p/oss-fuzz/issues/detail?id=24282 169 # 170 reset_db 171 do_execsql_test 3.1 { 172 CREATE TABLE t0(a); 173 CREATE TABLE t1(b); 174 UPDATE t1 SET b=sum(a) FROM t0; 175 SELECT * FROM t0, t1; 176 } {} 177 178 # Problem described by forum post https://sqlite.org/forum/forumpost/a274248080 179 # 180 reset_db 181 do_execsql_test 4.1 { 182 CREATE TABLE t1(x INT); INSERT INTO t1 VALUES(1); 183 CREATE TABLE t2(y INT); INSERT INTO t2 VALUES(2); 184 WITH t1 AS (SELECT y+100 AS x FROM t2) 185 UPDATE t1 SET x=(SELECT x FROM t1); 186 SELECT x, y FROM t1, t2; 187 } {102 2} 188 do_execsql_test 4.2 { 189 WITH t1 AS (SELECT y+100 AS x FROM t2) 190 UPDATE t1 SET x=x+y FROM t2; 191 SELECT x, y FROM t1, t2; 192 } {104 2} 193 194 # 2021-05-20 195 # Forum https://sqlite.org/forum/forumpost/339f487de5 by Yu Liang 196 # A bad assert() 197 # 198 reset_db 199 do_execsql_test 5.1 { 200 CREATE TABLE t1(a); 201 INSERT INTO t1(a) VALUES(5); 202 CREATE VIEW t2 AS SELECT a FROM t1 UNION ALL SELECT a FROM t1; 203 CREATE TABLE t3(b,c); 204 INSERT INTO t3(b,c) VALUES(1,2); 205 UPDATE t3 SET (c,b) = (SELECT 3,4) FROM t1, t2; 206 SELECT * FROM t3; 207 } {4 3} 208 209 210 finish_test