xref: /sqlite-3.40.0/test/returning1.test (revision 9cffb0ff) 
1# 2021-01-28
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.  The
12# focus of this file is the new RETURNING clause
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix returning1
18
19do_execsql_test 1.0 {
20  CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c DEFAULT 'pax');
21  INSERT INTO t1(b) VALUES(10),('happy'),(NULL) RETURNING a,b,c;
22} {1 10 pax 2 happy pax 3 {} pax}
23do_execsql_test 1.1 {
24  SELECT * FROM t1;
25} {1 10 pax 2 happy pax 3 {} pax}
26do_execsql_test 1.2 {
27  INSERT INTO t1(b,c) VALUES(5,99) RETURNING b,c,a,rowid;
28} {5 99 4 4}
29do_execsql_test 1.3 {
30  SELECT * FROM t1;
31} {1 10 pax 2 happy pax 3 {} pax 4 5 99}
32do_execsql_test 1.4 {
33  INSERT INTO t1 DEFAULT VALUES RETURNING *;
34} {5 {} pax}
35do_execsql_test 1.5 {
36  SELECT * FROM t1;
37} {1 10 pax 2 happy pax 3 {} pax 4 5 99 5 {} pax}
38do_execsql_test 1.6 {
39  CREATE TABLE t2(x,y,z);
40  INSERT INTO t2 VALUES(11,12,13),(21,'b','c'),(31,'b-value',4.75);
41}
42do_execsql_test 1.7 {
43  INSERT INTO t1 SELECT * FROM t2 RETURNING *;
44} {11 12 13 21 b c 31 b-value 4.75}
45do_execsql_test 1.8 {
46  SELECT *, '|' FROM t1;
47} {1 10 pax | 2 happy pax | 3 {} pax | 4 5 99 | 5 {} pax | 11 12 13 | 21 b c | 31 b-value 4.75 |}
48
49do_execsql_test 2.1 {
50  UPDATE t1 SET c='bellum' WHERE c='pax' RETURNING rowid, b, '|';
51} {1 10 | 2 happy | 3 {} | 5 {} |}
52do_execsql_test 2.2 {
53  SELECT *, '|' FROM t1;
54} {1 10 bellum | 2 happy bellum | 3 {} bellum | 4 5 99 | 5 {} bellum | 11 12 13 | 21 b c | 31 b-value 4.75 |}
55
56do_execsql_test 3.1 {
57  DELETE FROM t1 WHERE c='bellum' RETURNING rowid, *, '|';
58} {1 1 10 bellum | 2 2 happy bellum | 3 3 {} bellum | 5 5 {} bellum |}
59do_execsql_test 3.2 {
60  SELECT *, '|' FROM t1;
61} {4 5 99 | 11 12 13 | 21 b c | 31 b-value 4.75 |}
62
63do_execsql_test 4.1 {
64  CREATE TABLE t4(a INT, b INT DEFAULT 1234, c INT DEFAULT -16);
65  CREATE UNIQUE INDEX t4a ON t4(a);
66  INSERT INTO t4(a,b,c) VALUES(1,2,3);
67} {}
68do_execsql_test 4.2 {
69  INSERT INTO t4(a,b,c) VALUES(1,22,33)
70  ON CONFLICT(a) DO UPDATE SET b=44
71  RETURNING *;
72} {1 44 3}
73do_execsql_test 4.3 {
74  SELECT * FROM t4;
75} {1 44 3}
76do_execsql_test 4.4 {
77  DELETE FROM t4;
78  INSERT INTO t4 VALUES(1,2,3),(4,5,6),(7,8,9);
79} {}
80do_execsql_test 4.5 {
81  INSERT INTO t4(a,b,c) VALUES(2,3,4),(4,5,6),(5,6,7)
82  ON CONFLICT(a) DO UPDATE SET b=100
83  RETURNING *, '|';
84} {2 3 4 | 4 100 6 | 5 6 7 |}
85
86#-------------------------------------------------------------------------
87# Test RETURNING on a table with virtual columns.
88#
89reset_db
90do_execsql_test 5.0 {
91  CREATE TABLE t1(xyz);
92  CREATE TABLE t2(a as (1+1), b);
93}
94
95do_execsql_test 5.1 {
96  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
97} {}
98
99do_execsql_test 5.2 {
100  INSERT INTO t2(b) VALUES('abc');
101}
102
103do_execsql_test 5.3 {
104  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
105} {{}}
106
107do_execsql_test 5.4 {
108  INSERT INTO t2(b) VALUES('abc');
109  INSERT INTO t1(xyz) VALUES(1);
110  UPDATE t2 SET b='123' WHERE b='abc' RETURNING b;
111} {123}
112
113do_execsql_test 5.5 {
114  INSERT INTO t2(b) VALUES('abc');
115  UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
116} {123}
117
118finish_test
119