1--echo # Test for INSERT...RETURNING 2 3CREATE TABLE t1(id1 INT PRIMARY KEY AUTO_INCREMENT, val1 VARCHAR(1)); 4CREATE TABLE t2(id2 INT PRIMARY KEY AUTO_INCREMENT, val2 VARCHAR(1)); 5CREATE VIEW v1 AS SELECT id1, val1 FROM t1; 6CREATE VIEW v2 AS SELECT id2,val2 FROM t2; 7INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 8 9DELIMITER |; 10 11CREATE FUNCTION f(arg INT) RETURNS TEXT 12BEGIN 13 RETURN (SELECT arg+arg); 14END| 15 16DELIMITER ;| 17 18--echo # 19--echo # Simple insert statement...RETURNING 20--echo # 21INSERT INTO t1 (id1, val1) VALUES (1, 'a'); 22INSERT INTO t1 (id1, val1) VALUES (2, 'b') RETURNING *; 23INSERT INTO t1 (id1, val1) VALUES (3, 'c') RETURNING id1+id1 as total,val1, 24id1 && id1, id1 | id1, UPPER(val1),f(id1); 25INSERT INTO t1(id1,val1) VALUES (4,'d') RETURNING(SELECT GROUP_CONCAT(val2) 26FROM t2 WHERE id2=1); 27INSERT INTO t1(id1,val1) VALUES(5,'e') RETURNING(SELECT GROUP_CONCAT(val2) 28FROM t2 GROUP BY id2 HAVING id2=id1-2); 29PREPARE stmt FROM "INSERT INTO t1 (id1,val1) VALUES (6,'f') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 30EXECUTE stmt; 31DELETE FROM t1 WHERE id1=6; 32SELECT * FROM t1; 33EXECUTE stmt; 34DEALLOCATE PREPARE stmt; 35INSERT IGNORE INTO t1(id1,val1) VALUES (7,'h') RETURNING *; 36SELECT * FROM t1; 37EXPLAIN INSERT INTO t1(id1,val1) VALUES (8,'i') RETURNING *; 38EXPLAIN EXTENDED INSERT INTO t1(id1,val1) VALUES(9,'j') RETURNING id1; 39EXPLAIN FORMAT="json" INSERT INTO t1(id1,val1) VALUES (10,'k') RETURNING id1; 40SELECT * FROM t1; 41INSERT INTO v1(id1, val1) VALUES (12, 'a') RETURNING id1+id1 as total,val1, 42id1 && id1, id1 id1, UPPER(val1),f(id1); 43ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *; 44INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*; 45TRUNCATE TABLE t1; 46 47--echo # 48--echo # Multiple values in one insert statement...RETURNING 49--echo # 50INSERT INTO t1 VALUES (1,'a'),(2,'b'); 51INSERT INTO t1 VALUES (3,'c'),(4,'d') RETURNING *; 52INSERT INTO t1 VALUES (5,'e'),(6,'f') RETURNING id1 as id,val1, 53id1 && id1, id1|id1, UPPER(val1),f(id1); 54INSERT INTO t1 VALUES (7,'g'),(8,'h') RETURNING(SELECT GROUP_CONCAT(val2) 55FROM t2 WHERE id2=1); 56INSERT INTO t1 VALUES (9,'i'),(10,'j') RETURNING(SELECT GROUP_CONCAT(val2) 57FROM t2 GROUP BY id2 HAVING id2=id1-8); 58PREPARE stmt FROM "INSERT INTO t1 VALUES (11,'k'),(12,'l') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 59EXECUTE stmt; 60DELETE FROM t1 WHERE val1 IN ('k','l'); 61SELECT * FROM t1; 62EXECUTE stmt; 63DEALLOCATE PREPARE stmt; 64INSERT IGNORE INTO t1 VALUES(13,'o'),(14,'p') RETURNING *; 65EXPLAIN INSERT INTO t1 VALUES(15,'q'),(16,'r') RETURNING *; 66EXPLAIN EXTENDED INSERT INTO t1 VALUES (17,'s'),(18,'t') RETURNING *; 67EXPLAIN FORMAT="json" INSERT INTO t1 VALUES(19,'u'),(20,'v') RETURNING id1; 68SELECT * FROM t1; 69INSERT INTO v1 VALUES(23,'y'),(24,'z') RETURNING id1 as id,val1, 70id1 && id1, id1|id1, UPPER(val1),f(id1); 71ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *; 72ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*; 73 74--echo # 75--echo # INSERT...ON DUPLICATE KEY UPDATE...RETURNING 76--echo # 77CREATE TABLE ins_duplicate (id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(1)); 78INSERT INTO ins_duplicate VALUES (1,'a'); 79INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 80RETURNING *; 81INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='c' 82RETURNING id+id AS total, val, id && id, id|id, UPPER(val),f(id); 83INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='d' 84RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1); 85INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='e' 86RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id); 87PREPARE stmt FROM "INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE 88KEY UPDATE val='f' RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 89EXECUTE stmt; 90SELECT * FROM t2; 91EXECUTE stmt; 92DEALLOCATE PREPARE stmt; 93INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATE 94KEY UPDATE val='g' RETURNING id; 95EXPLAIN INSERT INTO ins_duplicate(id,val) VALUES (2,'b') ON DUPLICATE KEY 96UPDATE val='h' RETURNING val; 97EXPLAIN EXTENDED INSERT INTO ins_duplicate(id,val) VALUES (2,'b') 98ON DUPLICATE KEY UPDATE val='i' RETURNING val; 99EXPLAIN FORMAT="json" INSERT INTO ins_duplicate(id,val) VALUES (2,'b') 100ON DUPLICATE KEY UPDATE val='j' RETURNING val; 101INSERT INTO v1(id1, val1) VALUES (2,'d') ON DUPLICATE KEY UPDATE 102val1='d' RETURNING id1+id1 AS total, val1, id1 && id1, id1|id1, UPPER(val1), 103f(id1); 104ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE 105val='k' RETURNING *; 106INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE 107val='l' RETURNING ins_duplicate.*; 108SELECT * FROM ins_duplicate; 109 110--echo # 111--echo # INSERT...SET...RETURNING 112--echo # 113TRUNCATE TABLE t1; 114INSERT INTO t1 SET id1= 1, val1= 'a'; 115INSERT INTO t1 SET id1= 2, val1= 'b' RETURNING *; 116INSERT INTO t1 SET id1= 3, val1= 'c' RETURNING id1+id1 AS total,val1, 117id1 && id1, id1|id1, UPPER(val1),f(id1); 118INSERT INTO t1 SET id1= 4, val1= 'd' RETURNING (SELECT GROUP_CONCAT(val2) 119FROM t2 WHERE id2=1); 120INSERT INTO t1 SET id1= 5, val1='e' RETURNING (SELECT GROUP_CONCAT(val2) 121FROM t2 GROUP BY id2 HAVING id2=id1-3); 122PREPARE stmt FROM "INSERT INTO t1 SET id1= 6, val1='f' RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 123EXECUTE stmt; 124DELETE FROM t1 WHERE val1='f'; 125SELECT * FROM t1; 126EXECUTE stmt; 127DEALLOCATE PREPARE stmt; 128INSERT INTO t1 SET id1= 7, val1= 'g' RETURNING f(id1); 129INSERT INTO t1 SET val1= 'n' RETURNING *; 130INSERT IGNORE INTO t1 SET id1= 8, val1= 'h' RETURNING *; 131EXPLAIN INSERT INTO t1 SET id1=9, val1='i' RETURNING id1; 132EXPLAIN EXTENDED INSERT INTO t1 SET id1=10, val1='j' RETURNING val1; 133EXPLAIN FORMAT="json" INSERT INTO t1 SET id1=11, val1='k' RETURNING val1; 134INSERT INTO v1 SET id1=26, val1='Z' RETURNING id1+id1 AS total,val1, 135id1 && id1, id1|id1, UPPER(val1),f(id1); 136ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *; 137INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*; 138SELECT * FROM t1; 139 140--echo # 141--echo # INSERT...SELECT...RETURNING 142--echo # 143TRUNCATE TABLE t2; 144INSERT INTO t2(id2,val2) SELECT * FROM t1; 145TRUNCATE TABLE t2; 146INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING *; 147INSERT INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING id2+id2 AS total, 148val2,id2 && id2, id2|id2, UPPER(val2),f(id2); 149INSERT INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1); 150PREPARE stmt FROM "INSERT INTO t2 SELECT * FROM t1 WHERE id1=4 RETURNING (SELECT id1 FROM t1 WHERE val1='b')"; 151EXECUTE stmt; 152DELETE FROM t2 WHERE id2=4; 153SELECT * FROM t1; 154EXECUTE stmt; 155DEALLOCATE PREPARE stmt; 156INSERT INTO t2 SELECT * FROM t1 WHERE id1=6 RETURNING 157(SELECT id1+id2 FROM t1 WHERE id1=1); 158INSERT INTO t2 SELECT * FROM t1 WHERE id1=7 RETURNING f(id2); 159EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING id2; 160EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t1 WHERE id1=9 RETURNING val1; 161EXPLAIN FORMAT="json" INSERT INTO t1 SELECT * FROM t1 WHERE id1=10 RETURNING val1; 162INSERT IGNORE INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING *; 163INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *; 164ANALYZE INSERT INTO t2 SELECT * FROM t1 WHERE id1=11 RETURNING *; 165SELECT * FROM t2; 166TRUNCATE TABLE t2; 167INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*; 168INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*; 169SELECT * FROM t2; 170 171DROP TABLE t1; 172DROP TABLE t2; 173DROP TABLE ins_duplicate; 174 175--echo # 176--echo # Error message test 177--echo # 178 179CREATE TABLE t1(id1 INT,val1 VARCHAR(1)); 180CREATE TABLE t2(id2 INT,val2 VARCHAR(1)); 181CREATE TABLE ins_duplicate (id INT PRIMARY KEY, val VARCHAR(1)); 182 183INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); 184 185--echo # 186--echo # SIMLPE INSERT STATEMENT 187--echo # 188--error ER_BAD_FIELD_ERROR 189INSERT INTO t2(id2,val2) VALUES(1,'a') RETURNING id1; 190--error ER_INVALID_GROUP_FUNC_USE 191INSERT INTO t2(id2,val2) values(2,'b') RETURNING SUM(id2); 192--error ER_SUBQUERY_NO_1_ROW 193INSERT INTO t2(id2,val2) VALUES(3,'c') RETURNING (SELECT id1 FROM t1); 194--error ER_OPERAND_COLUMNS 195INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t1); 196--error ER_OPERAND_COLUMNS 197INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t2); 198INSERT INTO t2(id2,val2) VALUES(5,'e') RETURNING id2, (SELECT id1+id2 FROM 199t1 WHERE id1=1); 200--error ER_UPDATE_TABLE_USED 201INSERT INTO t2(id2,val2) VALUES(5,'f') RETURNING (SELECT id2 FROM t2); 202--error ER_BAD_TABLE_ERROR 203INSERT INTO t2 (id2, val2) VALUES (6,'f') RETURNING t1.*; 204 205--echo # 206--echo # Multiple rows in single insert statement 207--echo # 208--error ER_BAD_FIELD_ERROR 209INSERT INTO t2 VALUES(1,'a'),(2,'b') RETURNING id1; 210--error ER_INVALID_GROUP_FUNC_USE 211INSERT INTO t2 VALUES(3,'c'),(4,'d') RETURNING MAX(id2); 212--error ER_SUBQUERY_NO_1_ROW 213INSERT INTO t2 VALUES(5,'c'),(6,'f') RETURNING (SELECT id1 FROM t1); 214--error ER_OPERAND_COLUMNS 215INSERT INTO t2 VALUES(7,'g'),(8,'h') RETURNING (SELECT * FROM t1); 216--error ER_OPERAND_COLUMNS 217INSERT INTO t2 VALUES(9,'g'),(10,'h') RETURNING (SELECT * FROM t2); 218INSERT INTO t2 VALUES(11,'e'),(12,'f') RETURNING id2, (SELECT id1+id2 FROM 219t1 WHERE id1=1); 220--error ER_UPDATE_TABLE_USED 221INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING (SELECT id2 FROM t2); 222--error ER_BAD_TABLE_ERROR 223INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING t1.*; 224 225--echo # 226--echo # INSERT ... SET 227--echo # 228--error ER_BAD_FIELD_ERROR 229INSERT INTO t2 SET id2=1, val2='a' RETURNING id1; 230--error ER_INVALID_GROUP_FUNC_USE 231INSERT INTO t2 SET id2=2, val2='b' RETURNING COUNT(id2); 232--error ER_SUBQUERY_NO_1_ROW 233INSERT INTO t2 SET id2=3, val2='c' RETURNING (SELECT id1 FROM t1); 234--error ER_OPERAND_COLUMNS 235INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t1); 236--error ER_OPERAND_COLUMNS 237INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t2); 238INSERT INTO t2 SET id2=5, val2='e' RETURNING id2, (SELECT id1+id2 FROM t1 239WHERE id1=1); 240--error ER_UPDATE_TABLE_USED 241INSERT INTO t2 SET id2=5, val2='f' RETURNING (SELECT id2 FROM t2); 242--error ER_BAD_TABLE_ERROR 243INSERT INTO t2 SET id2=5, val2='f' RETURNING t1.*; 244 245--echo # 246--echo # INSERT...ON DUPLICATE KEY UPDATE 247--echo # 248--error ER_BAD_FIELD_ERROR 249INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 250RETURNING id1; 251--error ER_INVALID_GROUP_FUNC_USE 252INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 253RETURNING MAX(id); 254--error ER_SUBQUERY_NO_1_ROW 255INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 256RETURNING (SELECT id1 FROM t1); 257--error ER_OPERAND_COLUMNS 258INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 259RETURNING (SELECT * FROM t1); 260--error ER_OPERAND_COLUMNS 261INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 262RETURNING (SELECT * FROM ins_duplicate); 263--error ER_BAD_FIELD_ERROR 264INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 265RETURNING id2, (SELECT id1+id FROM t1 WHERE id1=1); 266--error ER_UPDATE_TABLE_USED 267INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 268RETURNING (SELECT id FROM ins_duplicate); 269--error ER_BAD_TABLE_ERROR 270INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 271RETURNING t1.*; 272 273--echo # 274--echo # INSERT...SELECT 275--echo # 276--error ER_BAD_FIELD_ERROR 277INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=1 RETURNING id1; 278--error ER_INVALID_GROUP_FUNC_USE 279INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING MAX(id2); 280--error ER_SUBQUERY_NO_1_ROW 281INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 282id1 FROM t1); 283--error ER_OPERAND_COLUMNS 284INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 285* FROM t1); 286--error ER_OPERAND_COLUMNS 287INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING(SELECT 288* FROM t2); 289--error ER_SUBQUERY_NO_1_ROW 290INSERT INTO t2(id2,val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 291id2 FROM t2); 292--error ER_BAD_TABLE_ERROR 293INSERT INTO t2(id2,val2) SELECT t1.* FROM t1 WHERE id1=2 RETURNING t1.*; 294 295--echo # 296--echo # TRIGGER 297--echo # 298CREATE TRIGGER bi1 before insert on t1 for each row set NEW.val1= 'z'; 299INSERT INTO t1 VALUES (4, 'd'), (5, 'e') RETURNING *; 300 301--error ER_SP_NO_RETSET 302CREATE TRIGGER bi2 before insert on t2 for each row 303 INSERT INTO t1 VALUES (NEW.id2, NEW.val2) RETURNING *; 304 305--echo # 306--echo # SP 307--echo # 308delimiter |; 309--error ER_SP_NO_RETSET 310CREATE FUNCTION f1(arg INT) RETURNS TEXT 311BEGIN 312 INSERT INTO t1 VALUES (arg, arg) RETURNING *; 313 RETURN arg; 314END| 315delimiter ;| 316 317CREATE PROCEDURE sp1(arg INT) 318 INSERT INTO t1 VALUES (arg, arg) RETURNING *; 319 320CALL sp1(0); 321 322DROP PROCEDURE sp1; 323DROP TABLE t1; 324DROP TABLE t2; 325DROP TABLE ins_duplicate; 326DROP VIEW v1; 327DROP VIEW v2; 328DROP FUNCTION f; 329