diff options
author | Sergei Golubchik <serg@mariadb.org> | 2019-10-11 14:39:05 +0200 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2019-10-14 10:29:31 +0200 |
commit | b1c2c4ee1b246144033c95d849d59ed0a1192829 (patch) | |
tree | ac3ab866b111a5369372b1b11de8525a79cf3b30 /mysql-test/main/insert_returning.test | |
parent | 904dc93439c25568c7318d2b98b8a583faae5be8 (diff) | |
download | mariadb-git-b1c2c4ee1b246144033c95d849d59ed0a1192829.tar.gz |
MDEV-10014 Add RETURNING to INSERT
post-review fixes:
* test for dependent subqueries
* test for triggers and routines
* disallow INSERT...RETURNING in triggers and stored functions
* don't return anything if INSERT IGNORE ignored an error
Diffstat (limited to 'mysql-test/main/insert_returning.test')
-rw-r--r-- | mysql-test/main/insert_returning.test | 36 |
1 files changed, 32 insertions, 4 deletions
diff --git a/mysql-test/main/insert_returning.test b/mysql-test/main/insert_returning.test index a8c5dc3a3f2..b4fc75c28bb 100644 --- a/mysql-test/main/insert_returning.test +++ b/mysql-test/main/insert_returning.test @@ -25,7 +25,7 @@ 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=id2+1); +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; @@ -53,7 +53,7 @@ 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=id2+1); +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'); @@ -81,7 +81,7 @@ 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=id2+1); +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; @@ -114,7 +114,7 @@ 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=id2+1); +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'; @@ -272,6 +272,34 @@ INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING(SELECT INSERT INTO t2(id2,val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT id2 FROM t2); +--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; |