diff options
Diffstat (limited to '')
| -rw-r--r-- | test/upfrom1.test | 210 |
1 files changed, 210 insertions, 0 deletions
diff --git a/test/upfrom1.test b/test/upfrom1.test new file mode 100644 index 0000000..204f351 --- /dev/null +++ b/test/upfrom1.test @@ -0,0 +1,210 @@ +# 2020 April 22 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# + +#################################################### +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! +#################################################### + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix upfrom1 + +do_execsql_test 1.1.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) WITHOUT ROWID; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.1.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.1.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.1.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.1.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) WITHOUT ROWID; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.1.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 1.2.0 { + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER) ; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); + + DROP TABLE IF EXISTS chng; + CREATE TABLE chng(a INTEGER, b INTEGER, c INTEGER); + INSERT INTO chng VALUES(1, 100, 1000); + INSERT INTO chng VALUES(7, 700, 7000); +} {} + +do_execsql_test 1.2.1 { + SELECT * FROM t2; +} {1 2 3 4 5 6 7 8 9} + +do_execsql_test 1.2.2 { + UPDATE t2 SET b = chng.b, c = chng.c FROM chng WHERE chng.a = t2.a; + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.3 { + DELETE FROM t2; + INSERT INTO t2 VALUES(1, 2, 3); + INSERT INTO t2 VALUES(4, 5, 6); + INSERT INTO t2 VALUES(7, 8, 9); +} {} + +do_execsql_test 1.2.4 { + UPDATE t2 SET (b, c) = (SELECT b, c FROM chng WHERE a=t2.a) + WHERE a IN (SELECT a FROM chng); + SELECT * FROM t2 ORDER BY a; +} {1 100 1000 4 5 6 7 700 7000} + +do_execsql_test 1.2.5 { + DROP TABLE IF EXISTS t3; + CREATE TABLE t3(a INTEGER PRIMARY KEY, b INTEGER, c TEXT) ; + INSERT INTO t3 VALUES(1, 1, 'one'); + INSERT INTO t3 VALUES(2, 2, 'two'); + INSERT INTO t3 VALUES(3, 3, 'three'); + + DROP TABLE IF EXISTS t4; + CREATE TABLE t4(x TEXT); + INSERT INTO t4 VALUES('five'); + + SELECT * FROM t3 ORDER BY a; +} {1 1 one 2 2 two 3 3 three} + +do_execsql_test 1.2.6 { + UPDATE t3 SET c=x FROM t4; + SELECT * FROM t3 ORDER BY a; +} {1 1 five 2 2 five 3 3 five} + +do_execsql_test 2.1 { + DROP TABLE IF EXISTS t5; + DROP TABLE IF EXISTS m1; + DROP TABLE IF EXISTS m2; + CREATE TABLE t5(a INTEGER PRIMARY KEY, b TEXT, c TEXT); + CREATE TABLE m1(x INTEGER PRIMARY KEY, y TEXT); + CREATE TABLE m2(u INTEGER PRIMARY KEY, v TEXT); + + INSERT INTO t5 VALUES(1, 'one', 'ONE'); + INSERT INTO t5 VALUES(2, 'two', 'TWO'); + INSERT INTO t5 VALUES(3, 'three', 'THREE'); + INSERT INTO t5 VALUES(4, 'four', 'FOUR'); + + INSERT INTO m1 VALUES(1, 'i'); + INSERT INTO m1 VALUES(2, 'ii'); + INSERT INTO m1 VALUES(3, 'iii'); + + INSERT INTO m2 VALUES(1, 'I'); + INSERT INTO m2 VALUES(3, 'II'); + INSERT INTO m2 VALUES(4, 'III'); +} {} + +do_execsql_test 2.2 { + UPDATE t5 SET b=y, c=v FROM m1 LEFT JOIN m2 ON (x=u) WHERE x=a; + SELECT * FROM t5 ORDER BY a; +} {1 i I 2 ii {} 3 iii II 4 four FOUR} + +# PG says ERROR: table name "t5" specified more than once +do_test 2.3.1 { catch { execsql { + UPDATE t5 SET b=1 FROM t5; +} } } 1 + +# PG says ERROR: table name "apples" specified more than once +do_test 2.3.2 { catch { execsql { + UPDATE t5 AS apples SET b=1 FROM t5 AS apples; +} } } 1 + +# Problem found by OSSFuzz on 2020-07-20 +# https://bugs.chromium.org/p/oss-fuzz/issues/detail?id=24282 +# +reset_db +do_execsql_test 3.1 { + CREATE TABLE t0(a); + CREATE TABLE t1(b); + UPDATE t1 SET b=sum(a) FROM t0; + SELECT * FROM t0, t1; +} {} + +# Problem described by forum post https://sqlite.org/forum/forumpost/a274248080 +# +reset_db +do_execsql_test 4.1 { + CREATE TABLE t1(x INT); INSERT INTO t1 VALUES(1); + CREATE TABLE t2(y INT); INSERT INTO t2 VALUES(2); + WITH t1 AS (SELECT y+100 AS x FROM t2) + UPDATE t1 SET x=(SELECT x FROM t1); + SELECT x, y FROM t1, t2; +} {102 2} +do_execsql_test 4.2 { + WITH t1 AS (SELECT y+100 AS x FROM t2) + UPDATE t1 SET x=x+y FROM t2; + SELECT x, y FROM t1, t2; +} {104 2} + +# 2021-05-20 +# Forum https://sqlite.org/forum/forumpost/339f487de5 by Yu Liang +# A bad assert() +# +reset_db +do_execsql_test 5.1 { + CREATE TABLE t1(a); + INSERT INTO t1(a) VALUES(5); + CREATE VIEW t2 AS SELECT a FROM t1 UNION ALL SELECT a FROM t1; + CREATE TABLE t3(b,c); + INSERT INTO t3(b,c) VALUES(1,2); + UPDATE t3 SET (c,b) = (SELECT 3,4) FROM t1, t2; + SELECT * FROM t3; +} {4 3} + + +finish_test |