MySQL Bugs: #83139: Resultset for insert, update and delete

Bug #83139	Resultset for insert, update and delete
Submitted:	25 Sep 2016 11:32	Modified:	5 Oct 2016 7:07
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	returning

Description:
With the CTE support of MySQL 8.0 (optimizer labs release) it would be nice to support returning a resultset for update and delete statements.

For this the RETURNING statement should be implemented.

A use case would be to fill a logging table with values from a delete or update statement without having to use triggers (e.g. only do this for batch operations)

How to repeat:
In PostgreSQL:

test=# create table t1 (id serial, name varchar(100));
CREATE TABLE
test=# insert into t1(name) values ('foo'),('bar');
INSERT 0 2
test=# with deleted_names as (delete from t1 where id = 2 returning name)
test-# select name from deleted_names;
 name 
------
 bar
(1 row)

This is also implemented in MariaDB:
https://mariadb.com/kb/en/mariadb/delete/

Oracle has a very similar RETURNING INTO clause:
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#BABHDGIG

This could also be used to return the values for fields for a insert query for which a values was not specified in the query. This would return the applied column defaults.

Hello Daniël,

Thank you for the feature request!

Thanks,
Umesh

PostgreSQL (as well as Oracle PL/SQL (but not SQL), and Firebird, MariaDB) support RETURNING clauses at the end of all DML statements, e.g.:

INSERT INTO t (a, b) VALUES (1, 2) RETURNING c, d;
UPDATE t SET a = 1 RETURNING b;
DELETE t RETURNING b;

SQL Server has a similar OUTPUT clause on all DML statements.

Db2 implements the standard SQL data change delta table feature (which I would recommend following):

SELECT * FROM FINAL TABLE (INSERT INTO t (a, b) VALUES (1, 2));
SELECT * FROM NEW TABLE (UPDATE t SET a = 1);
SELECT * FROM OLD TABLE (DELETE t);

Notice that standard SQL allows for accessing data before the DML operation (OLD), after the DML operation (NEW), as well as after firing triggers (FINAL). SQL Server's OUTPUT also allows for this to some extent. The RETURNING syntax returns "FINAL" semantics for INSERT/UPDATE, and "OLD" semantics for DELETE