sqlite-3.40.0/test/returning1.test

# 2021-01-28
#
# 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.  The
# focus of this file is the new RETURNING clause
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix returning1

do_execsql_test 1.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c DEFAULT 'pax');
  INSERT INTO t1(b) VALUES(10),('happy'),(NULL) RETURNING a,b,c;
} {1 10 pax 2 happy pax 3 {} pax}
do_execsql_test 1.1 {
  SELECT * FROM t1;
} {1 10 pax 2 happy pax 3 {} pax}
do_execsql_test 1.2 {
  INSERT INTO t1(b,c) VALUES(5,99) RETURNING b,c,a,rowid;
} {5 99 4 4}
do_execsql_test 1.3 {
  SELECT * FROM t1;
} {1 10 pax 2 happy pax 3 {} pax 4 5 99}
do_execsql_test 1.4 {
  INSERT INTO t1 DEFAULT VALUES RETURNING *;
} {5 {} pax}
do_execsql_test 1.5 {
  SELECT * FROM t1;
} {1 10 pax 2 happy pax 3 {} pax 4 5 99 5 {} pax}
do_execsql_test 1.6 {
  CREATE TABLE t2(x,y,z);
  INSERT INTO t2 VALUES(11,12,13),(21,'b','c'),(31,'b-value',4.75);
}
do_execsql_test 1.7 {
  INSERT INTO t1 SELECT * FROM t2 RETURNING *;
} {11 12 13 21 b c 31 b-value 4.75}
do_execsql_test 1.8 {
  SELECT *, '|' FROM t1;
} {1 10 pax | 2 happy pax | 3 {} pax | 4 5 99 | 5 {} pax | 11 12 13 | 21 b c | 31 b-value 4.75 |}

do_execsql_test 2.1 {
  UPDATE t1 SET c='bellum' WHERE c='pax' RETURNING rowid, b, '|';
} {1 10 | 2 happy | 3 {} | 5 {} |}
do_execsql_test 2.2 {
  SELECT *, '|' FROM t1;
} {1 10 bellum | 2 happy bellum | 3 {} bellum | 4 5 99 | 5 {} bellum | 11 12 13 | 21 b c | 31 b-value 4.75 |}

do_execsql_test 3.1 {
  DELETE FROM t1 WHERE c='bellum' RETURNING rowid, *, '|';
} {1 1 10 bellum | 2 2 happy bellum | 3 3 {} bellum | 5 5 {} bellum |}
do_execsql_test 3.2 {
  SELECT *, '|' FROM t1;
} {4 5 99 | 11 12 13 | 21 b c | 31 b-value 4.75 |}

do_execsql_test 4.1 {
  CREATE TABLE t4(a INT, b INT DEFAULT 1234, c INT DEFAULT -16);
  CREATE UNIQUE INDEX t4a ON t4(a);
  INSERT INTO t4(a,b,c) VALUES(1,2,3);
} {}
do_execsql_test 4.2 {
  INSERT INTO t4(a,b,c) VALUES(1,22,33)
  ON CONFLICT(a) DO UPDATE SET b=44
  RETURNING *;
} {1 44 3}
do_execsql_test 4.3 {
  SELECT * FROM t4;
} {1 44 3}
do_execsql_test 4.4 {
  DELETE FROM t4;
  INSERT INTO t4 VALUES(1,2,3),(4,5,6),(7,8,9);
} {}
do_execsql_test 4.5 {
  INSERT INTO t4(a,b,c) VALUES(2,3,4),(4,5,6),(5,6,7)
  ON CONFLICT(a) DO UPDATE SET b=100
  RETURNING *, '|';
} {2 3 4 | 4 100 6 | 5 6 7 |}

#-------------------------------------------------------------------------
# Test RETURNING on a table with virtual columns.
#
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1(xyz);
  CREATE TABLE t2(a as (1+1), b);
}

do_execsql_test 5.1 {
  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
} {}

do_execsql_test 5.2 {
  INSERT INTO t2(b) VALUES('abc');
}

do_execsql_test 5.3 {
  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
} {{}}

do_execsql_test 5.4 {
  INSERT INTO t2(b) VALUES('abc');
  INSERT INTO t1(xyz) VALUES(1);
  UPDATE t2 SET b='123' WHERE b='abc' RETURNING b;
} {123}

do_execsql_test 5.5 {
  INSERT INTO t2(b) VALUES('abc');
  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
} {123}

# Ticket 132994c8b1063bfb
reset_db
do_catchsql_test 6.0 {
  CREATE TABLE t1(id INTEGER PRIMARY KEY);
  CREATE TABLE t2(x INT, y INT);
  INSERT INTO t1 VALUES(1),(2),(4),(9);
  INSERT INTO t2 VALUES(3,7), (4,25), (5,99);
  UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING t2.*;
} {1 {RETURNING may not use "TABLE.*" wildcards}}
do_catchsql_test 6.1 {
  UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING *, '|';
  SELECT * FROM t1 ORDER BY id;
} {0 {29 | 1 2 9 29}}

# Forum https://sqlite.org/forum/forumpost/85aef8bc01
# Do not silently ignore nonsense table names in the RETURNING clause.
# Raise an error.
#
reset_db
do_execsql_test 7.1 {
  CREATE TABLE t1(a INT, b INT);
  CREATE TABLE t2(x INT, y INT);
  INSERT INTO t1(a,b) VALUES(1,2);
  INSERT INTO t2(x,y) VALUES(1,30);
} {}
do_catchsql_test 7.2 {
  UPDATE t1 SET b=b+1 RETURNING new.b;
} {1 {no such column: new.b}}
do_catchsql_test 7.3 {
  UPDATE t1 SET b=b+1 RETURNING old.b;
} {1 {no such column: old.b}}
do_catchsql_test 7.4 {
  UPDATE t1 SET b=b+1 RETURNING another.b;
} {1 {no such column: another.b}}
do_catchsql_test 7.5 {
  UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t2.x;
} {1 {no such column: t2.x}}
do_catchsql_test 7.6 {
  UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t1.b;
} {0 32}

# This is goofy:  The RETURNING clause does not honor the alias
# for the table being modified.  This might change in the future.
#
do_catchsql_test 7.7 {
  UPDATE t1 AS alias SET b=123 RETURNING alias.b;
} {1 {no such column: alias.b}}
do_catchsql_test 7.8 {
  UPDATE t1 AS alias SET b=alias.b+1000 RETURNING t1.b;
} {0 1032}


finish_test