diff options
Diffstat (limited to 'mysql-test/main/insert_returning.result')
| -rw-r--r-- | mysql-test/main/insert_returning.result | 685 |
1 files changed, 685 insertions, 0 deletions
diff --git a/mysql-test/main/insert_returning.result b/mysql-test/main/insert_returning.result new file mode 100644 index 00000000000..b2ed9c90e51 --- /dev/null +++ b/mysql-test/main/insert_returning.result @@ -0,0 +1,685 @@ +# 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'); +CREATE FUNCTION f(arg INT) RETURNS TEXT +BEGIN +RETURN (SELECT arg+arg); +END| +# +# Simple insert statement...RETURNING +# +INSERT INTO t1 (id1, val1) VALUES (1, 'a'); +INSERT INTO t1 (id1, val1) VALUES (2, 'b') RETURNING *; +id1 val1 +2 b +INSERT INTO t1 (id1, val1) VALUES (3, 'c') RETURNING id1+id1 as total,val1, +id1 && id1, id1 | id1, UPPER(val1),f(id1); +total val1 id1 && id1 id1 | id1 UPPER(val1) f(id1) +6 c 1 3 C 6 +INSERT INTO t1(id1,val1) VALUES (4,'d') RETURNING(SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1); +(SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1) +a +INSERT INTO t1(id1,val1) VALUES(5,'e') RETURNING(SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-2); +(SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-2) +c +PREPARE stmt FROM "INSERT INTO t1 (id1,val1) VALUES (6,'f') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +DELETE FROM t1 WHERE id1=6; +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +DEALLOCATE PREPARE stmt; +INSERT IGNORE INTO t1(id1,val1) VALUES (7,'h') RETURNING *; +id1 val1 +7 h +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 h +EXPLAIN INSERT INTO t1(id1,val1) VALUES (8,'i') RETURNING *; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +EXPLAIN EXTENDED INSERT INTO t1(id1,val1) VALUES(9,'j') RETURNING id1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 insert into `test`.`t1`(id1,val1) values (9,'j') +EXPLAIN FORMAT="json" INSERT INTO t1(id1,val1) VALUES (10,'k') RETURNING id1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1" + } + } +} +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 h +INSERT INTO v1(id1, val1) VALUES (12, 'a') RETURNING id1+id1 as total,val1, +id1 && id1, id1 id1, UPPER(val1),f(id1); +total val1 id1 && id1 id1 UPPER(val1) f(id1) +24 a 1 12 A 24 +ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*; +id1 val1 +14 m +TRUNCATE TABLE t1; +# +# Multiple values in one insert statement...RETURNING +# +INSERT INTO t1 VALUES (1,'a'),(2,'b'); +INSERT INTO t1 VALUES (3,'c'),(4,'d') RETURNING *; +id1 val1 +3 c +4 d +INSERT INTO t1 VALUES (5,'e'),(6,'f') RETURNING id1 as id,val1, +id1 && id1, id1|id1, UPPER(val1),f(id1); +id val1 id1 && id1 id1|id1 UPPER(val1) f(id1) +5 e 1 5 E 10 +6 f 1 6 F 12 +INSERT INTO t1 VALUES (7,'g'),(8,'h') RETURNING(SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1); +(SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1) +a +a +INSERT INTO t1 VALUES (9,'i'),(10,'j') RETURNING(SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-8); +(SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-8) +a +b +PREPARE stmt FROM "INSERT INTO t1 VALUES (11,'k'),(12,'l') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +2 +DELETE FROM t1 WHERE val1 IN ('k','l'); +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 h +9 i +10 j +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +2 +DEALLOCATE PREPARE stmt; +INSERT IGNORE INTO t1 VALUES(13,'o'),(14,'p') RETURNING *; +id1 val1 +13 o +14 p +EXPLAIN INSERT INTO t1 VALUES(15,'q'),(16,'r') RETURNING *; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +EXPLAIN EXTENDED INSERT INTO t1 VALUES (17,'s'),(18,'t') RETURNING *; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 insert into `test`.`t1` values (17,'s'),(18,'t') +EXPLAIN FORMAT="json" INSERT INTO t1 VALUES(19,'u'),(20,'v') RETURNING id1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1" + } + } +} +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 h +9 i +10 j +12 l +11 k +13 o +14 p +INSERT INTO v1 VALUES(23,'y'),(24,'z') RETURNING id1 as id,val1, +id1 && id1, id1|id1, UPPER(val1),f(id1); +id val1 id1 && id1 id1|id1 UPPER(val1) f(id1) +23 y 1 23 Y 46 +24 z 1 24 Z 48 +ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +# +# INSERT...ON DUPLICATE KEY UPDATE...RETURNING +# +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 *; +id val +2 b +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); +total val id && id id|id UPPER(val) f(id) +4 c 1 2 C 4 +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='d' +RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1); +(SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1) +a +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); +(SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id) +b +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 id2 FROM t2 WHERE val2='b') +2 +SELECT * FROM t2; +id2 val2 +1 a +2 b +3 c +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +DEALLOCATE PREPARE stmt; +INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATE +KEY UPDATE val='g' RETURNING id; +id +3 +4 +EXPLAIN INSERT INTO ins_duplicate(id,val) VALUES (2,'b') ON DUPLICATE KEY +UPDATE val='h' RETURNING val; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL NULL +EXPLAIN EXTENDED INSERT INTO ins_duplicate(id,val) VALUES (2,'b') +ON DUPLICATE KEY UPDATE val='i' RETURNING val; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 insert into `test`.`ins_duplicate`(id,val) values (2,'b') on duplicate key update `test`.`ins_duplicate`.`val` = 'i' +EXPLAIN FORMAT="json" INSERT INTO ins_duplicate(id,val) VALUES (2,'b') +ON DUPLICATE KEY UPDATE val='j' RETURNING val; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "ins_duplicate" + } + } +} +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); +total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) +4 d 1 2 D 4 +ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE +val='k' RETURNING *; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE +val='l' RETURNING ins_duplicate.*; +id val +2 l +SELECT * FROM ins_duplicate; +id val +1 a +2 l +3 c +4 d +# +# INSERT...SET...RETURNING +# +TRUNCATE TABLE t1; +INSERT INTO t1 SET id1= 1, val1= 'a'; +INSERT INTO t1 SET id1= 2, val1= 'b' RETURNING *; +id1 val1 +2 b +INSERT INTO t1 SET id1= 3, val1= 'c' RETURNING id1+id1 AS total,val1, +id1 && id1, id1|id1, UPPER(val1),f(id1); +total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) +6 c 1 3 C 6 +INSERT INTO t1 SET id1= 4, val1= 'd' RETURNING (SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1); +(SELECT GROUP_CONCAT(val2) +FROM t2 WHERE id2=1) +a +INSERT INTO t1 SET id1= 5, val1='e' RETURNING (SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-3); +(SELECT GROUP_CONCAT(val2) +FROM t2 GROUP BY id2 HAVING id2=id1-3) +b +PREPARE stmt FROM "INSERT INTO t1 SET id1= 6, val1='f' RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +DELETE FROM t1 WHERE val1='f'; +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +EXECUTE stmt; +(SELECT id2 FROM t2 WHERE val2='b') +2 +DEALLOCATE PREPARE stmt; +INSERT INTO t1 SET id1= 7, val1= 'g' RETURNING f(id1); +f(id1) +14 +INSERT INTO t1 SET val1= 'n' RETURNING *; +id1 val1 +8 n +INSERT IGNORE INTO t1 SET id1= 8, val1= 'h' RETURNING *; +id1 val1 +Warnings: +Warning 1062 Duplicate entry '8' for key 'PRIMARY' +EXPLAIN INSERT INTO t1 SET id1=9, val1='i' RETURNING id1; +id select_type table type possible_keys key key_len ref rows Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL +EXPLAIN EXTENDED INSERT INTO t1 SET id1=10, val1='j' RETURNING val1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL +Warnings: +Note 1003 insert into `test`.`t1`(id1,val1) values (10,'j') +EXPLAIN FORMAT="json" INSERT INTO t1 SET id1=11, val1='k' RETURNING val1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1" + } + } +} +INSERT INTO v1 SET id1=26, val1='Z' RETURNING id1+id1 AS total,val1, +id1 && id1, id1|id1, UPPER(val1),f(id1); +total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) +52 Z 1 26 Z 52 +ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL +INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*; +id1 val1 +13 m +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 n +26 Z +12 l +13 m +# +# INSERT...SELECT...RETURNING +# +TRUNCATE TABLE t2; +INSERT INTO t2(id2,val2) SELECT * FROM t1; +TRUNCATE TABLE t2; +INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING *; +id2 val2 +1 a +INSERT INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING id2+id2 AS total, +val2,id2 && id2, id2|id2, UPPER(val2),f(id2); +total val2 id2 && id2 id2|id2 UPPER(val2) f(id2) +4 b 1 2 B 4 +INSERT INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1); +(SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1) +NULL +PREPARE stmt FROM "INSERT INTO t2 SELECT * FROM t1 WHERE id1=4 RETURNING (SELECT id1 FROM t1 WHERE val1='b')"; +EXECUTE stmt; +(SELECT id1 FROM t1 WHERE val1='b') +2 +DELETE FROM t2 WHERE id2=4; +SELECT * FROM t1; +id1 val1 +1 a +2 b +3 c +4 d +5 e +6 f +7 g +8 n +26 Z +12 l +13 m +EXECUTE stmt; +(SELECT id1 FROM t1 WHERE val1='b') +2 +DEALLOCATE PREPARE stmt; +INSERT INTO t2 SELECT * FROM t1 WHERE id1=6 RETURNING +(SELECT id1+id2 FROM t1 WHERE id1=1); +(SELECT id1+id2 FROM t1 WHERE id1=1) +7 +INSERT INTO t2 SELECT * FROM t1 WHERE id1=7 RETURNING f(id2); +f(id2) +14 +EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING id2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 +EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t1 WHERE id1=9 RETURNING val1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1003 insert into `test`.`t1` select sql_buffer_result NULL AS `id1`,NULL AS `val1` from `test`.`t1` where 0 +EXPLAIN FORMAT="json" INSERT INTO t1 SELECT * FROM t1 WHERE id1=10 RETURNING val1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + } + } +} +INSERT IGNORE INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING *; +id2 val2 +8 n +INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *; +id2 val2 +5 e +26 Z +12 l +13 m +Warnings: +Warning 1062 Duplicate entry '1' for key 'PRIMARY' +Warning 1062 Duplicate entry '2' for key 'PRIMARY' +Warning 1062 Duplicate entry '3' for key 'PRIMARY' +Warning 1062 Duplicate entry '4' for key 'PRIMARY' +Warning 1062 Duplicate entry '6' for key 'PRIMARY' +Warning 1062 Duplicate entry '7' for key 'PRIMARY' +Warning 1062 Duplicate entry '8' for key 'PRIMARY' +ANALYZE INSERT INTO t2 SELECT * FROM t1 WHERE id1=11 RETURNING *; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM t2; +id2 val2 +1 a +2 b +3 c +4 d +6 f +7 g +8 n +5 e +26 Z +12 l +13 m +TRUNCATE TABLE t2; +INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*; +id2 val2 +1 a +INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*; +id2 val2 +2 b +SELECT * FROM t2; +id2 val2 +1 a +2 b +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE ins_duplicate; +# +# Error message test +# +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'); +# +# SIMLPE INSERT STATEMENT +# +INSERT INTO t2(id2,val2) VALUES(1,'a') RETURNING id1; +ERROR 42S22: Unknown column 'id1' in 'field list' +INSERT INTO t2(id2,val2) values(2,'b') RETURNING SUM(id2); +ERROR HY000: Invalid use of group function +INSERT INTO t2(id2,val2) VALUES(3,'c') RETURNING (SELECT id1 FROM t1); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t1); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t2); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2(id2,val2) VALUES(5,'e') RETURNING id2, (SELECT id1+id2 FROM +t1 WHERE id1=1); +id2 (SELECT id1+id2 FROM +t1 WHERE id1=1) +5 6 +INSERT INTO t2(id2,val2) VALUES(5,'f') RETURNING (SELECT id2 FROM t2); +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data +INSERT INTO t2 (id2, val2) VALUES (6,'f') RETURNING t1.*; +ERROR 42S02: Unknown table 'test.t1' +# +# Multiple rows in single insert statement +# +INSERT INTO t2 VALUES(1,'a'),(2,'b') RETURNING id1; +ERROR 42S22: Unknown column 'id1' in 'field list' +INSERT INTO t2 VALUES(3,'c'),(4,'d') RETURNING MAX(id2); +ERROR HY000: Invalid use of group function +INSERT INTO t2 VALUES(5,'c'),(6,'f') RETURNING (SELECT id1 FROM t1); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO t2 VALUES(7,'g'),(8,'h') RETURNING (SELECT * FROM t1); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2 VALUES(9,'g'),(10,'h') RETURNING (SELECT * FROM t2); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2 VALUES(11,'e'),(12,'f') RETURNING id2, (SELECT id1+id2 FROM +t1 WHERE id1=1); +id2 (SELECT id1+id2 FROM +t1 WHERE id1=1) +11 12 +12 13 +INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING (SELECT id2 FROM t2); +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data +INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING t1.*; +ERROR 42S02: Unknown table 'test.t1' +# +# INSERT ... SET +# +INSERT INTO t2 SET id2=1, val2='a' RETURNING id1; +ERROR 42S22: Unknown column 'id1' in 'field list' +INSERT INTO t2 SET id2=2, val2='b' RETURNING COUNT(id2); +ERROR HY000: Invalid use of group function +INSERT INTO t2 SET id2=3, val2='c' RETURNING (SELECT id1 FROM t1); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t1); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t2); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2 SET id2=5, val2='e' RETURNING id2, (SELECT id1+id2 FROM t1 +WHERE id1=1); +id2 (SELECT id1+id2 FROM t1 +WHERE id1=1) +5 6 +INSERT INTO t2 SET id2=5, val2='f' RETURNING (SELECT id2 FROM t2); +ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data +INSERT INTO t2 SET id2=5, val2='f' RETURNING t1.*; +ERROR 42S02: Unknown table 'test.t1' +# +# INSERT...ON DUPLICATE KEY UPDATE +# +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING id1; +ERROR 42S22: Unknown column 'id1' in 'field list' +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING MAX(id); +ERROR HY000: Invalid use of group function +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING (SELECT id1 FROM t1); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING (SELECT * FROM t1); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING (SELECT * FROM ins_duplicate); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING id2, (SELECT id1+id FROM t1 WHERE id1=1); +ERROR 42S22: Unknown column 'id2' in 'field list' +INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING (SELECT id FROM ins_duplicate); +ERROR HY000: Table 'ins_duplicate' is specified twice, both as a target for 'INSERT' and as a separate source for data +INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' +RETURNING t1.*; +ERROR 42S02: Unknown table 'test.t1' +# +# INSERT...SELECT +# +INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=1 RETURNING id1; +ERROR 42S22: Unknown column 'id1' in 'field list' +INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING MAX(id2); +ERROR HY000: Invalid use of group function +INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT +id1 FROM t1); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT +* FROM t1); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING(SELECT +* FROM t2); +ERROR 21000: Operand should contain 1 column(s) +INSERT INTO t2(id2,val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT +id2 FROM t2); +ERROR 21000: Subquery returns more than 1 row +INSERT INTO t2(id2,val2) SELECT t1.* FROM t1 WHERE id1=2 RETURNING t1.*; +ERROR 42S02: Unknown table 'test.t1' +# +# TRIGGER +# +CREATE TRIGGER bi1 before insert on t1 for each row set NEW.val1= 'z'; +INSERT INTO t1 VALUES (4, 'd'), (5, 'e') RETURNING *; +id1 val1 +4 z +5 z +CREATE TRIGGER bi2 before insert on t2 for each row +INSERT INTO t1 VALUES (NEW.id2, NEW.val2) RETURNING *; +ERROR 0A000: Not allowed to return a result set from a trigger +# +# SP +# +CREATE FUNCTION f1(arg INT) RETURNS TEXT +BEGIN +INSERT INTO t1 VALUES (arg, arg) RETURNING *; +RETURN arg; +END| +ERROR 0A000: Not allowed to return a result set from a function +CREATE PROCEDURE sp1(arg INT) +INSERT INTO t1 VALUES (arg, arg) RETURNING *; +CALL sp1(0); +id1 val1 +0 z +DROP PROCEDURE sp1; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE ins_duplicate; +DROP VIEW v1; +DROP VIEW v2; +DROP FUNCTION f; +# +# MDEV-25028: ASAN use-after-poison in base_list_iterator::next or +# Assertion `sl->join == 0' upon INSERT .. RETURNING via PS +# +CREATE TABLE t1 (a INT); +CREATE TABLE t2 (b INT); +PREPARE stmt1 FROM "INSERT INTO t1 SELECT * FROM t1 WHERE a IN (SELECT b FROM t2) RETURNING a"; +EXECUTE stmt1; +a +PREPARE stmt2 FROM "INSERT INTO t1 SELECT * FROM t1 WHERE a IN (SELECT b FROM t2) RETURNING (SELECT b FROM t2)"; +EXECUTE stmt2; +(SELECT b FROM t2) +DROP TABLE t1, t2; +# +# MDEV-25187: Assertion `inited == NONE || table->open_by_handler' +# failed or Direct leak in init_dynamic_array2 upon INSERT .. RETURNING +# and memory leak in init_dynamic_array2 +# +CREATE TABLE t (a INT, KEY (a)); +CREATE TABLE t1 (f INT); +INSERT INTO t VALUES (1),(2); +INSERT INTO t1 SELECT a FROM t WHERE 1 NOT IN (SELECT a FROM t) RETURNING f; +f +DROP TABLE t, t1; +# +# MDEV-28740: crash in INSERT RETURNING subquery in prepared statements +# +CREATE TABLE t1 ( +id INTEGER NOT NULL, +data VARCHAR(30), +PRIMARY KEY (id) +)ENGINE=MyISAM; +EXECUTE IMMEDIATE 'INSERT INTO t1 (id, data) VALUES ((SELECT CAST(1 AS SIGNED INTEGER) AS anon_1), ?) RETURNING t1.id' using 'hi'; +id +1 +DROP TABLE t1; +# +# MDEV-27165: crash in base_list_iterator::next +# +CREATE TABLE t1 ( id int, a int); +CREATE TABLE t2 ( id int); +INSERT INTO t1 VALUES (( SELECT 1 from t2),999999999999) RETURNING id; +ERROR 22003: Out of range value for column 'a' at row 1 +EXECUTE immediate "INSERT INTO t1 VALUES (( SELECT 1 from t2),999999999999) RETURNING id "; +ERROR 22003: Out of range value for column 'a' at row 1 +EXECUTE immediate "INSERT INTO t1 VALUES (( SELECT 1 from t2),9) RETURNING id "; +id +NULL +DROP TABLE t1, t2; +# +# MDEV-29686: Assertion `slave == 0' failed in +# st_select_lex_node::attach_single +# +CREATE TABLE t (a INT); +INSERT t WITH cte AS (SELECT 1) SELECT * FROM cte RETURNING *; +a +1 +DROP TABLE t; |