mysql-test/main/insert_returning.test

--echo # Test for INSERT...RETURNING

CREATE TABLE t1(id1 INT PRIMARY KEY AUTO_INCREMENT, val1 VARCHAR(1));
CREATE TABLE t2(id2 INT PRIMARY KEY AUTO_INCREMENT, val2 VARCHAR(1));
CREATE VIEW v1 AS SELECT id1, val1 FROM t1;
CREATE VIEW v2 AS SELECT id2,val2 FROM t2;
INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c');

DELIMITER |;

CREATE FUNCTION f(arg INT) RETURNS TEXT
BEGIN
  RETURN (SELECT arg+arg);
END|

DELIMITER ;|

--echo #
--echo # Simple insert statement...RETURNING
--echo #
INSERT INTO t1 (id1, val1) VALUES (1, 'a');
INSERT INTO t1 (id1, val1) VALUES (2, 'b') RETURNING *;
INSERT INTO t1 (id1, val1) VALUES (3, 'c') RETURNING id1+id1 as total,val1,
id1 && id1, id1 | id1, UPPER(val1),f(id1);
INSERT INTO t1(id1,val1) VALUES (4,'d') RETURNING(SELECT GROUP_CONCAT(val2)
FROM t2 WHERE id2=1);
INSERT INTO t1(id1,val1) VALUES(5,'e') RETURNING(SELECT GROUP_CONCAT(val2)
FROM t2 GROUP BY id2 HAVING id2=id1-2);
PREPARE stmt FROM "INSERT INTO t1 (id1,val1) VALUES (6,'f') RETURNING (SELECT id2 FROM  t2 WHERE val2='b')";
EXECUTE stmt;
DELETE FROM t1 WHERE id1=6;
SELECT * FROM t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT IGNORE INTO t1(id1,val1) VALUES (7,'h') RETURNING *;
SELECT * FROM t1;
EXPLAIN INSERT INTO t1(id1,val1) VALUES (8,'i') RETURNING *;
EXPLAIN EXTENDED INSERT INTO t1(id1,val1) VALUES(9,'j') RETURNING id1;
EXPLAIN FORMAT="json" INSERT INTO t1(id1,val1) VALUES (10,'k') RETURNING id1;
SELECT * FROM t1;
INSERT INTO v1(id1, val1) VALUES (12, 'a') RETURNING id1+id1 as total,val1,
id1 && id1, id1  id1, UPPER(val1),f(id1);
ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *;
INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*;
TRUNCATE TABLE t1;

--echo #
--echo # Multiple values in one insert statement...RETURNING
--echo #
INSERT INTO t1 VALUES (1,'a'),(2,'b');
INSERT INTO t1 VALUES (3,'c'),(4,'d') RETURNING *;
INSERT INTO t1 VALUES (5,'e'),(6,'f') RETURNING id1 as id,val1,
id1 && id1, id1|id1, UPPER(val1),f(id1);
INSERT INTO t1 VALUES (7,'g'),(8,'h') RETURNING(SELECT GROUP_CONCAT(val2)
FROM t2 WHERE id2=1);
INSERT INTO t1 VALUES (9,'i'),(10,'j') RETURNING(SELECT GROUP_CONCAT(val2)
FROM t2 GROUP BY id2 HAVING id2=id1-8);
PREPARE stmt FROM "INSERT INTO t1 VALUES (11,'k'),(12,'l') RETURNING (SELECT id2 FROM t2 WHERE val2='b')";
EXECUTE stmt;
DELETE FROM t1 WHERE val1 IN ('k','l');
SELECT * FROM t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT IGNORE INTO t1 VALUES(13,'o'),(14,'p') RETURNING *;
EXPLAIN INSERT INTO t1 VALUES(15,'q'),(16,'r') RETURNING *;
EXPLAIN EXTENDED INSERT INTO t1 VALUES (17,'s'),(18,'t') RETURNING *;
EXPLAIN FORMAT="json" INSERT INTO t1 VALUES(19,'u'),(20,'v') RETURNING id1;
SELECT * FROM t1;
INSERT INTO v1 VALUES(23,'y'),(24,'z') RETURNING id1 as id,val1,
id1 && id1, id1|id1, UPPER(val1),f(id1);
ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *;
ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*;

--echo #
--echo # INSERT...ON DUPLICATE KEY UPDATE...RETURNING
--echo #
CREATE TABLE ins_duplicate (id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(1));
INSERT INTO ins_duplicate VALUES (1,'a');
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING *;
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='c'
RETURNING id+id AS total, val, id && id, id|id, UPPER(val),f(id);
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='d'
RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1);
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='e'
RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id);
PREPARE stmt FROM "INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE
KEY UPDATE val='f' RETURNING (SELECT id2 FROM  t2 WHERE val2='b')";
EXECUTE stmt;
SELECT * FROM t2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATE
KEY UPDATE val='g' RETURNING id;
EXPLAIN INSERT INTO ins_duplicate(id,val) VALUES (2,'b') ON DUPLICATE KEY
UPDATE val='h' RETURNING val;
EXPLAIN EXTENDED INSERT INTO ins_duplicate(id,val) VALUES (2,'b')
ON DUPLICATE KEY UPDATE val='i' RETURNING val;
EXPLAIN FORMAT="json" INSERT INTO ins_duplicate(id,val) VALUES (2,'b')
ON DUPLICATE KEY UPDATE val='j' RETURNING val;
INSERT INTO v1(id1, val1) VALUES (2,'d') ON DUPLICATE KEY UPDATE
val1='d' RETURNING id1+id1 AS total, val1, id1 && id1, id1|id1, UPPER(val1),
f(id1);
ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
val='k' RETURNING *;
INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE
val='l' RETURNING ins_duplicate.*;
SELECT * FROM ins_duplicate;

--echo #
--echo # INSERT...SET...RETURNING
--echo #
TRUNCATE TABLE t1;
INSERT INTO t1 SET id1= 1, val1= 'a';
INSERT INTO t1 SET id1= 2, val1= 'b' RETURNING *;
INSERT INTO t1 SET id1= 3, val1= 'c' RETURNING id1+id1 AS total,val1,
id1 && id1, id1|id1, UPPER(val1),f(id1);
INSERT INTO t1 SET id1= 4, val1= 'd' RETURNING (SELECT GROUP_CONCAT(val2)
FROM t2 WHERE id2=1);
INSERT INTO t1 SET id1= 5, val1='e' RETURNING (SELECT GROUP_CONCAT(val2)
FROM t2 GROUP BY id2 HAVING id2=id1-3);
PREPARE stmt FROM "INSERT INTO t1 SET id1= 6, val1='f' RETURNING (SELECT id2 FROM  t2 WHERE val2='b')";
EXECUTE stmt;
DELETE FROM t1 WHERE val1='f';
SELECT * FROM t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO t1 SET id1= 7, val1= 'g' RETURNING f(id1);
INSERT INTO t1 SET val1= 'n' RETURNING *;
INSERT IGNORE INTO t1 SET id1= 8, val1= 'h' RETURNING *;
EXPLAIN INSERT INTO t1 SET id1=9, val1='i' RETURNING id1;
EXPLAIN EXTENDED INSERT INTO t1 SET id1=10, val1='j' RETURNING val1;
EXPLAIN FORMAT="json" INSERT INTO t1 SET id1=11, val1='k' RETURNING val1;
INSERT INTO v1 SET id1=26, val1='Z' RETURNING id1+id1 AS total,val1,
id1 && id1, id1|id1, UPPER(val1),f(id1);
ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *;
INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*;
SELECT * FROM t1;

--echo #
--echo # INSERT...SELECT...RETURNING
--echo #
TRUNCATE TABLE t2;
INSERT INTO t2(id2,val2) SELECT * FROM t1;
TRUNCATE TABLE t2;
INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING *;
INSERT INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING id2+id2 AS total,
val2,id2 && id2, id2|id2, UPPER(val2),f(id2);
INSERT INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1);
PREPARE stmt FROM "INSERT INTO t2 SELECT * FROM t1 WHERE id1=4 RETURNING (SELECT id1 FROM t1 WHERE val1='b')";
EXECUTE stmt;
DELETE FROM t2 WHERE id2=4;
SELECT * FROM t1;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
INSERT INTO t2 SELECT * FROM t1 WHERE id1=6 RETURNING
(SELECT id1+id2 FROM t1 WHERE id1=1);
INSERT INTO t2 SELECT * FROM t1 WHERE id1=7 RETURNING f(id2);
EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING id2;
EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t1 WHERE id1=9 RETURNING val1;
EXPLAIN FORMAT="json" INSERT INTO t1 SELECT * FROM t1 WHERE id1=10 RETURNING val1;
INSERT IGNORE INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING *;
INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *;
ANALYZE INSERT INTO t2 SELECT * FROM t1 WHERE id1=11 RETURNING *;
SELECT * FROM t2;
TRUNCATE TABLE t2;
INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*;
INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*;
SELECT * FROM t2;

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE ins_duplicate;

--echo #
--echo # Error message test
--echo #

CREATE TABLE t1(id1 INT,val1 VARCHAR(1));
CREATE TABLE t2(id2 INT,val2 VARCHAR(1));
CREATE TABLE ins_duplicate (id INT PRIMARY KEY, val VARCHAR(1));

INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c');

--echo #
--echo # SIMLPE INSERT STATEMENT
--echo #
--error ER_BAD_FIELD_ERROR
INSERT INTO t2(id2,val2) VALUES(1,'a') RETURNING id1;
--error ER_INVALID_GROUP_FUNC_USE
INSERT INTO t2(id2,val2) values(2,'b') RETURNING SUM(id2);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO t2(id2,val2) VALUES(3,'c') RETURNING (SELECT id1 FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t2);
INSERT INTO t2(id2,val2) VALUES(5,'e') RETURNING id2, (SELECT id1+id2 FROM
t1 WHERE id1=1);
--error ER_UPDATE_TABLE_USED
INSERT INTO t2(id2,val2) VALUES(5,'f') RETURNING (SELECT id2 FROM t2);
--error ER_BAD_TABLE_ERROR
INSERT INTO t2 (id2, val2) VALUES (6,'f') RETURNING t1.*;

--echo #
--echo # Multiple rows in single insert statement
--echo #
--error ER_BAD_FIELD_ERROR
INSERT INTO t2 VALUES(1,'a'),(2,'b') RETURNING id1;
--error ER_INVALID_GROUP_FUNC_USE
INSERT INTO t2 VALUES(3,'c'),(4,'d') RETURNING MAX(id2);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO t2 VALUES(5,'c'),(6,'f') RETURNING (SELECT id1 FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2 VALUES(7,'g'),(8,'h') RETURNING (SELECT * FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2 VALUES(9,'g'),(10,'h') RETURNING (SELECT * FROM t2);
INSERT INTO t2 VALUES(11,'e'),(12,'f') RETURNING id2, (SELECT id1+id2 FROM
t1 WHERE id1=1);
--error ER_UPDATE_TABLE_USED
INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING (SELECT id2 FROM t2);
--error ER_BAD_TABLE_ERROR
INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING t1.*;

--echo #
--echo # INSERT ... SET
--echo #
--error ER_BAD_FIELD_ERROR
INSERT INTO t2 SET id2=1, val2='a' RETURNING id1;
--error ER_INVALID_GROUP_FUNC_USE
INSERT INTO t2 SET id2=2, val2='b' RETURNING COUNT(id2);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO t2 SET id2=3, val2='c' RETURNING (SELECT id1 FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t2);
INSERT INTO t2 SET id2=5, val2='e' RETURNING id2, (SELECT id1+id2 FROM t1
WHERE id1=1);
--error ER_UPDATE_TABLE_USED
INSERT INTO t2 SET id2=5, val2='f' RETURNING (SELECT id2 FROM t2);
--error ER_BAD_TABLE_ERROR
INSERT INTO t2 SET id2=5, val2='f' RETURNING t1.*;

--echo #
--echo # INSERT...ON DUPLICATE KEY UPDATE
--echo #
--error ER_BAD_FIELD_ERROR
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING id1;
--error ER_INVALID_GROUP_FUNC_USE
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING MAX(id);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING (SELECT id1 FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING (SELECT * FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING (SELECT * FROM ins_duplicate);
--error ER_BAD_FIELD_ERROR
INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING id2, (SELECT id1+id FROM t1 WHERE id1=1);
--error ER_UPDATE_TABLE_USED
INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING (SELECT id FROM ins_duplicate);
--error ER_BAD_TABLE_ERROR
INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b'
RETURNING t1.*;

--echo #
--echo # INSERT...SELECT
--echo #
--error ER_BAD_FIELD_ERROR
INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=1 RETURNING id1;
--error ER_INVALID_GROUP_FUNC_USE
INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING MAX(id2);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT
id1 FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT
* FROM t1);
--error ER_OPERAND_COLUMNS
INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING(SELECT
* FROM t2);
--error ER_SUBQUERY_NO_1_ROW
INSERT INTO t2(id2,val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT
id2 FROM t2);
--error ER_BAD_TABLE_ERROR
INSERT INTO t2(id2,val2) SELECT t1.* FROM t1 WHERE id1=2 RETURNING t1.*;

--echo #
--echo # TRIGGER
--echo #
CREATE TRIGGER bi1 before insert on t1 for each row set NEW.val1= 'z';
INSERT INTO t1 VALUES (4, 'd'), (5, 'e') RETURNING *;

--error ER_SP_NO_RETSET
CREATE TRIGGER bi2 before insert on t2 for each row
  INSERT INTO t1 VALUES (NEW.id2, NEW.val2) RETURNING *;

--echo #
--echo # SP
--echo #
delimiter |;
--error ER_SP_NO_RETSET
CREATE FUNCTION f1(arg INT) RETURNS TEXT
BEGIN
  INSERT INTO t1 VALUES (arg, arg) RETURNING *;
  RETURN arg;
END|
delimiter ;|

CREATE PROCEDURE sp1(arg INT)
  INSERT INTO t1 VALUES (arg, arg) RETURNING *;

CALL sp1(0);

DROP PROCEDURE sp1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE ins_duplicate;
DROP VIEW v1;
DROP VIEW v2;
DROP FUNCTION f;