test/upfrom1.test

# 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