1# Test for INSERT...RETURNING 2CREATE TABLE t1(id1 INT PRIMARY KEY AUTO_INCREMENT, val1 VARCHAR(1)); 3CREATE TABLE t2(id2 INT PRIMARY KEY AUTO_INCREMENT, val2 VARCHAR(1)); 4CREATE VIEW v1 AS SELECT id1, val1 FROM t1; 5CREATE VIEW v2 AS SELECT id2,val2 FROM t2; 6INSERT INTO t2 VALUES (1,'a'),(2,'b'),(3,'c'); 7CREATE FUNCTION f(arg INT) RETURNS TEXT 8BEGIN 9RETURN (SELECT arg+arg); 10END| 11# 12# Simple insert statement...RETURNING 13# 14INSERT INTO t1 (id1, val1) VALUES (1, 'a'); 15INSERT INTO t1 (id1, val1) VALUES (2, 'b') RETURNING *; 16id1 val1 172 b 18INSERT INTO t1 (id1, val1) VALUES (3, 'c') RETURNING id1+id1 as total,val1, 19id1 && id1, id1 | id1, UPPER(val1),f(id1); 20total val1 id1 && id1 id1 | id1 UPPER(val1) f(id1) 216 c 1 3 C 6 22INSERT INTO t1(id1,val1) VALUES (4,'d') RETURNING(SELECT GROUP_CONCAT(val2) 23FROM t2 WHERE id2=1); 24(SELECT GROUP_CONCAT(val2) 25FROM t2 WHERE id2=1) 26a 27INSERT INTO t1(id1,val1) VALUES(5,'e') RETURNING(SELECT GROUP_CONCAT(val2) 28FROM t2 GROUP BY id2 HAVING id2=id1-2); 29(SELECT GROUP_CONCAT(val2) 30FROM t2 GROUP BY id2 HAVING id2=id1-2) 31c 32PREPARE stmt FROM "INSERT INTO t1 (id1,val1) VALUES (6,'f') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 33EXECUTE stmt; 34(SELECT id2 FROM t2 WHERE val2='b') 352 36DELETE FROM t1 WHERE id1=6; 37SELECT * FROM t1; 38id1 val1 391 a 402 b 413 c 424 d 435 e 44EXECUTE stmt; 45(SELECT id2 FROM t2 WHERE val2='b') 462 47DEALLOCATE PREPARE stmt; 48INSERT IGNORE INTO t1(id1,val1) VALUES (7,'h') RETURNING *; 49id1 val1 507 h 51SELECT * FROM t1; 52id1 val1 531 a 542 b 553 c 564 d 575 e 586 f 597 h 60EXPLAIN INSERT INTO t1(id1,val1) VALUES (8,'i') RETURNING *; 61id select_type table type possible_keys key key_len ref rows Extra 621 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 63EXPLAIN EXTENDED INSERT INTO t1(id1,val1) VALUES(9,'j') RETURNING id1; 64id select_type table type possible_keys key key_len ref rows filtered Extra 651 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL 66EXPLAIN FORMAT="json" INSERT INTO t1(id1,val1) VALUES (10,'k') RETURNING id1; 67EXPLAIN 68{ 69 "query_block": { 70 "select_id": 1, 71 "table": { 72 "table_name": "t1" 73 } 74 } 75} 76SELECT * FROM t1; 77id1 val1 781 a 792 b 803 c 814 d 825 e 836 f 847 h 85INSERT INTO v1(id1, val1) VALUES (12, 'a') RETURNING id1+id1 as total,val1, 86id1 && id1, id1 id1, UPPER(val1),f(id1); 87total val1 id1 && id1 id1 UPPER(val1) f(id1) 8824 a 1 12 A 24 89ANALYZE INSERT INTO t1(id1,val1) VALUES(13,'l') RETURNING *; 90id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 911 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 92INSERT INTO t1(id1,val1) VALUES(14,'m') RETURNING t1.*; 93id1 val1 9414 m 95TRUNCATE TABLE t1; 96# 97# Multiple values in one insert statement...RETURNING 98# 99INSERT INTO t1 VALUES (1,'a'),(2,'b'); 100INSERT INTO t1 VALUES (3,'c'),(4,'d') RETURNING *; 101id1 val1 1023 c 1034 d 104INSERT INTO t1 VALUES (5,'e'),(6,'f') RETURNING id1 as id,val1, 105id1 && id1, id1|id1, UPPER(val1),f(id1); 106id val1 id1 && id1 id1|id1 UPPER(val1) f(id1) 1075 e 1 5 E 10 1086 f 1 6 F 12 109INSERT INTO t1 VALUES (7,'g'),(8,'h') RETURNING(SELECT GROUP_CONCAT(val2) 110FROM t2 WHERE id2=1); 111(SELECT GROUP_CONCAT(val2) 112FROM t2 WHERE id2=1) 113a 114a 115INSERT INTO t1 VALUES (9,'i'),(10,'j') RETURNING(SELECT GROUP_CONCAT(val2) 116FROM t2 GROUP BY id2 HAVING id2=id1-8); 117(SELECT GROUP_CONCAT(val2) 118FROM t2 GROUP BY id2 HAVING id2=id1-8) 119a 120b 121PREPARE stmt FROM "INSERT INTO t1 VALUES (11,'k'),(12,'l') RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 122EXECUTE stmt; 123(SELECT id2 FROM t2 WHERE val2='b') 1242 1252 126DELETE FROM t1 WHERE val1 IN ('k','l'); 127SELECT * FROM t1; 128id1 val1 1291 a 1302 b 1313 c 1324 d 1335 e 1346 f 1357 g 1368 h 1379 i 13810 j 139EXECUTE stmt; 140(SELECT id2 FROM t2 WHERE val2='b') 1412 1422 143DEALLOCATE PREPARE stmt; 144INSERT IGNORE INTO t1 VALUES(13,'o'),(14,'p') RETURNING *; 145id1 val1 14613 o 14714 p 148EXPLAIN INSERT INTO t1 VALUES(15,'q'),(16,'r') RETURNING *; 149id select_type table type possible_keys key key_len ref rows Extra 1501 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 151EXPLAIN EXTENDED INSERT INTO t1 VALUES (17,'s'),(18,'t') RETURNING *; 152id select_type table type possible_keys key key_len ref rows filtered Extra 1531 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL 154EXPLAIN FORMAT="json" INSERT INTO t1 VALUES(19,'u'),(20,'v') RETURNING id1; 155EXPLAIN 156{ 157 "query_block": { 158 "select_id": 1, 159 "table": { 160 "table_name": "t1" 161 } 162 } 163} 164SELECT * FROM t1; 165id1 val1 1661 a 1672 b 1683 c 1694 d 1705 e 1716 f 1727 g 1738 h 1749 i 17510 j 17612 l 17711 k 17813 o 17914 p 180INSERT INTO v1 VALUES(23,'y'),(24,'z') RETURNING id1 as id,val1, 181id1 && id1, id1|id1, UPPER(val1),f(id1); 182id val1 id1 && id1 id1|id1 UPPER(val1) f(id1) 18323 y 1 23 Y 46 18424 z 1 24 Z 48 185ANALYZE INSERT INTO t1 VALUES(25,'a'),(26,'b') RETURNING *; 186id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1871 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 188ANALYZE INSERT INTO t1 VALUES(27,'c'),(28,'d') RETURNING t1.*; 189id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 1901 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 191# 192# INSERT...ON DUPLICATE KEY UPDATE...RETURNING 193# 194CREATE TABLE ins_duplicate (id INT PRIMARY KEY AUTO_INCREMENT, val VARCHAR(1)); 195INSERT INTO ins_duplicate VALUES (1,'a'); 196INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 197RETURNING *; 198id val 1992 b 200INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='c' 201RETURNING id+id AS total, val, id && id, id|id, UPPER(val),f(id); 202total val id && id id|id UPPER(val) f(id) 2034 c 1 2 C 4 204INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='d' 205RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1); 206(SELECT GROUP_CONCAT(val2) FROM t2 WHERE id2=1) 207a 208INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='e' 209RETURNING (SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id); 210(SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id) 211b 212PREPARE stmt FROM "INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE 213KEY UPDATE val='f' RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 214EXECUTE stmt; 215(SELECT id2 FROM t2 WHERE val2='b') 2162 217SELECT * FROM t2; 218id2 val2 2191 a 2202 b 2213 c 222EXECUTE stmt; 223(SELECT id2 FROM t2 WHERE val2='b') 2242 225DEALLOCATE PREPARE stmt; 226INSERT IGNORE INTO ins_duplicate(id,val) VALUES (3,'c'),(4,'d') ON DUPLICATE 227KEY UPDATE val='g' RETURNING id; 228id 2293 2304 231EXPLAIN INSERT INTO ins_duplicate(id,val) VALUES (2,'b') ON DUPLICATE KEY 232UPDATE val='h' RETURNING val; 233id select_type table type possible_keys key key_len ref rows Extra 2341 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL NULL 235EXPLAIN EXTENDED INSERT INTO ins_duplicate(id,val) VALUES (2,'b') 236ON DUPLICATE KEY UPDATE val='i' RETURNING val; 237id select_type table type possible_keys key key_len ref rows filtered Extra 2381 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL 100.00 NULL 239EXPLAIN FORMAT="json" INSERT INTO ins_duplicate(id,val) VALUES (2,'b') 240ON DUPLICATE KEY UPDATE val='j' RETURNING val; 241EXPLAIN 242{ 243 "query_block": { 244 "select_id": 1, 245 "table": { 246 "table_name": "ins_duplicate" 247 } 248 } 249} 250INSERT INTO v1(id1, val1) VALUES (2,'d') ON DUPLICATE KEY UPDATE 251val1='d' RETURNING id1+id1 AS total, val1, id1 && id1, id1|id1, UPPER(val1), 252f(id1); 253total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) 2544 d 1 2 D 4 255ANALYZE INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE 256val='k' RETURNING *; 257id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 2581 INSERT ins_duplicate ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 259INSERT INTO ins_duplicate(id,val) VALUES(2,'b') ON DUPLICATE KEY UPDATE 260val='l' RETURNING ins_duplicate.*; 261id val 2622 l 263SELECT * FROM ins_duplicate; 264id val 2651 a 2662 l 2673 c 2684 d 269# 270# INSERT...SET...RETURNING 271# 272TRUNCATE TABLE t1; 273INSERT INTO t1 SET id1= 1, val1= 'a'; 274INSERT INTO t1 SET id1= 2, val1= 'b' RETURNING *; 275id1 val1 2762 b 277INSERT INTO t1 SET id1= 3, val1= 'c' RETURNING id1+id1 AS total,val1, 278id1 && id1, id1|id1, UPPER(val1),f(id1); 279total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) 2806 c 1 3 C 6 281INSERT INTO t1 SET id1= 4, val1= 'd' RETURNING (SELECT GROUP_CONCAT(val2) 282FROM t2 WHERE id2=1); 283(SELECT GROUP_CONCAT(val2) 284FROM t2 WHERE id2=1) 285a 286INSERT INTO t1 SET id1= 5, val1='e' RETURNING (SELECT GROUP_CONCAT(val2) 287FROM t2 GROUP BY id2 HAVING id2=id1-3); 288(SELECT GROUP_CONCAT(val2) 289FROM t2 GROUP BY id2 HAVING id2=id1-3) 290b 291PREPARE stmt FROM "INSERT INTO t1 SET id1= 6, val1='f' RETURNING (SELECT id2 FROM t2 WHERE val2='b')"; 292EXECUTE stmt; 293(SELECT id2 FROM t2 WHERE val2='b') 2942 295DELETE FROM t1 WHERE val1='f'; 296SELECT * FROM t1; 297id1 val1 2981 a 2992 b 3003 c 3014 d 3025 e 303EXECUTE stmt; 304(SELECT id2 FROM t2 WHERE val2='b') 3052 306DEALLOCATE PREPARE stmt; 307INSERT INTO t1 SET id1= 7, val1= 'g' RETURNING f(id1); 308f(id1) 30914 310INSERT INTO t1 SET val1= 'n' RETURNING *; 311id1 val1 3128 n 313INSERT IGNORE INTO t1 SET id1= 8, val1= 'h' RETURNING *; 314id1 val1 315Warnings: 316Warning 1062 Duplicate entry '8' for key 'PRIMARY' 317EXPLAIN INSERT INTO t1 SET id1=9, val1='i' RETURNING id1; 318id select_type table type possible_keys key key_len ref rows Extra 3191 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 320EXPLAIN EXTENDED INSERT INTO t1 SET id1=10, val1='j' RETURNING val1; 321id select_type table type possible_keys key key_len ref rows filtered Extra 3221 INSERT t1 ALL NULL NULL NULL NULL NULL 100.00 NULL 323EXPLAIN FORMAT="json" INSERT INTO t1 SET id1=11, val1='k' RETURNING val1; 324EXPLAIN 325{ 326 "query_block": { 327 "select_id": 1, 328 "table": { 329 "table_name": "t1" 330 } 331 } 332} 333INSERT INTO v1 SET id1=26, val1='Z' RETURNING id1+id1 AS total,val1, 334id1 && id1, id1|id1, UPPER(val1),f(id1); 335total val1 id1 && id1 id1|id1 UPPER(val1) f(id1) 33652 Z 1 26 Z 52 337ANALYZE INSERT INTO t1 SET id1=12, val1='l' RETURNING *; 338id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 3391 INSERT t1 ALL NULL NULL NULL NULL NULL NULL 100.00 100.00 NULL 340INSERT INTO t1 SET id1= 13, val1= 'm' RETURNING t1.*; 341id1 val1 34213 m 343SELECT * FROM t1; 344id1 val1 3451 a 3462 b 3473 c 3484 d 3495 e 3506 f 3517 g 3528 n 35326 Z 35412 l 35513 m 356# 357# INSERT...SELECT...RETURNING 358# 359TRUNCATE TABLE t2; 360INSERT INTO t2(id2,val2) SELECT * FROM t1; 361TRUNCATE TABLE t2; 362INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING *; 363id2 val2 3641 a 365INSERT INTO t2 SELECT * FROM t1 WHERE id1=2 RETURNING id2+id2 AS total, 366val2,id2 && id2, id2|id2, UPPER(val2),f(id2); 367total val2 id2 && id2 id2|id2 UPPER(val2) f(id2) 3684 b 1 2 B 4 369INSERT INTO t2 SELECT * FROM t1 WHERE id1=3 RETURNING (SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1); 370(SELECT GROUP_CONCAT(val1) FROM t1 GROUP BY id1 HAVING id1=id1+1) 371NULL 372PREPARE stmt FROM "INSERT INTO t2 SELECT * FROM t1 WHERE id1=4 RETURNING (SELECT id1 FROM t1 WHERE val1='b')"; 373EXECUTE stmt; 374(SELECT id1 FROM t1 WHERE val1='b') 3752 376DELETE FROM t2 WHERE id2=4; 377SELECT * FROM t1; 378id1 val1 3791 a 3802 b 3813 c 3824 d 3835 e 3846 f 3857 g 3868 n 38726 Z 38812 l 38913 m 390EXECUTE stmt; 391(SELECT id1 FROM t1 WHERE val1='b') 3922 393DEALLOCATE PREPARE stmt; 394INSERT INTO t2 SELECT * FROM t1 WHERE id1=6 RETURNING 395(SELECT id1+id2 FROM t1 WHERE id1=1); 396(SELECT id1+id2 FROM t1 WHERE id1=1) 3977 398INSERT INTO t2 SELECT * FROM t1 WHERE id1=7 RETURNING f(id2); 399f(id2) 40014 401EXPLAIN INSERT INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING id2; 402id select_type table type possible_keys key key_len ref rows Extra 4031 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 404EXPLAIN EXTENDED INSERT INTO t1 SELECT * FROM t1 WHERE id1=9 RETURNING val1; 405id select_type table type possible_keys key key_len ref rows filtered Extra 4061 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 407EXPLAIN FORMAT="json" INSERT INTO t1 SELECT * FROM t1 WHERE id1=10 RETURNING val1; 408EXPLAIN 409{ 410 "query_block": { 411 "select_id": 1, 412 "table": { 413 "message": "Impossible WHERE noticed after reading const tables" 414 } 415 } 416} 417INSERT IGNORE INTO t2 SELECT * FROM t1 WHERE id1=8 RETURNING *; 418id2 val2 4198 n 420INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *; 421id2 val2 4225 e 42326 Z 42412 l 42513 m 426Warnings: 427Warning 1062 Duplicate entry '1' for key 'PRIMARY' 428Warning 1062 Duplicate entry '2' for key 'PRIMARY' 429Warning 1062 Duplicate entry '3' for key 'PRIMARY' 430Warning 1062 Duplicate entry '4' for key 'PRIMARY' 431Warning 1062 Duplicate entry '6' for key 'PRIMARY' 432Warning 1062 Duplicate entry '7' for key 'PRIMARY' 433Warning 1062 Duplicate entry '8' for key 'PRIMARY' 434ANALYZE INSERT INTO t2 SELECT * FROM t1 WHERE id1=11 RETURNING *; 435id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra 4361 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table 437SELECT * FROM t2; 438id2 val2 4391 a 4402 b 4413 c 4424 d 4436 f 4447 g 4458 n 4465 e 44726 Z 44812 l 44913 m 450TRUNCATE TABLE t2; 451INSERT INTO t2 SELECT * FROM t1 WHERE id1=1 RETURNING t2.*; 452id2 val2 4531 a 454INSERT INTO t2 SELECT t1.* FROM t1 WHERE id1=2 RETURNING t2.*; 455id2 val2 4562 b 457SELECT * FROM t2; 458id2 val2 4591 a 4602 b 461DROP TABLE t1; 462DROP TABLE t2; 463DROP TABLE ins_duplicate; 464# 465# Error message test 466# 467CREATE TABLE t1(id1 INT,val1 VARCHAR(1)); 468CREATE TABLE t2(id2 INT,val2 VARCHAR(1)); 469CREATE TABLE ins_duplicate (id INT PRIMARY KEY, val VARCHAR(1)); 470INSERT INTO t1 VALUES(1,'a'),(2,'b'),(3,'c'); 471# 472# SIMLPE INSERT STATEMENT 473# 474INSERT INTO t2(id2,val2) VALUES(1,'a') RETURNING id1; 475ERROR 42S22: Unknown column 'id1' in 'field list' 476INSERT INTO t2(id2,val2) values(2,'b') RETURNING SUM(id2); 477ERROR HY000: Invalid use of group function 478INSERT INTO t2(id2,val2) VALUES(3,'c') RETURNING (SELECT id1 FROM t1); 479ERROR 21000: Subquery returns more than 1 row 480INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t1); 481ERROR 21000: Operand should contain 1 column(s) 482INSERT INTO t2(id2,val2) VALUES(4,'d') RETURNING (SELECT * FROM t2); 483ERROR 21000: Operand should contain 1 column(s) 484INSERT INTO t2(id2,val2) VALUES(5,'e') RETURNING id2, (SELECT id1+id2 FROM 485t1 WHERE id1=1); 486id2 (SELECT id1+id2 FROM 487t1 WHERE id1=1) 4885 6 489INSERT INTO t2(id2,val2) VALUES(5,'f') RETURNING (SELECT id2 FROM t2); 490ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data 491INSERT INTO t2 (id2, val2) VALUES (6,'f') RETURNING t1.*; 492ERROR 42S02: Unknown table 'test.t1' 493# 494# Multiple rows in single insert statement 495# 496INSERT INTO t2 VALUES(1,'a'),(2,'b') RETURNING id1; 497ERROR 42S22: Unknown column 'id1' in 'field list' 498INSERT INTO t2 VALUES(3,'c'),(4,'d') RETURNING MAX(id2); 499ERROR HY000: Invalid use of group function 500INSERT INTO t2 VALUES(5,'c'),(6,'f') RETURNING (SELECT id1 FROM t1); 501ERROR 21000: Subquery returns more than 1 row 502INSERT INTO t2 VALUES(7,'g'),(8,'h') RETURNING (SELECT * FROM t1); 503ERROR 21000: Operand should contain 1 column(s) 504INSERT INTO t2 VALUES(9,'g'),(10,'h') RETURNING (SELECT * FROM t2); 505ERROR 21000: Operand should contain 1 column(s) 506INSERT INTO t2 VALUES(11,'e'),(12,'f') RETURNING id2, (SELECT id1+id2 FROM 507t1 WHERE id1=1); 508id2 (SELECT id1+id2 FROM 509t1 WHERE id1=1) 51011 12 51112 13 512INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING (SELECT id2 FROM t2); 513ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data 514INSERT INTO t2 VALUES(13,'f'),(14,'g') RETURNING t1.*; 515ERROR 42S02: Unknown table 'test.t1' 516# 517# INSERT ... SET 518# 519INSERT INTO t2 SET id2=1, val2='a' RETURNING id1; 520ERROR 42S22: Unknown column 'id1' in 'field list' 521INSERT INTO t2 SET id2=2, val2='b' RETURNING COUNT(id2); 522ERROR HY000: Invalid use of group function 523INSERT INTO t2 SET id2=3, val2='c' RETURNING (SELECT id1 FROM t1); 524ERROR 21000: Subquery returns more than 1 row 525INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t1); 526ERROR 21000: Operand should contain 1 column(s) 527INSERT INTO t2 SET id2=4, val2='d' RETURNING (SELECT * FROM t2); 528ERROR 21000: Operand should contain 1 column(s) 529INSERT INTO t2 SET id2=5, val2='e' RETURNING id2, (SELECT id1+id2 FROM t1 530WHERE id1=1); 531id2 (SELECT id1+id2 FROM t1 532WHERE id1=1) 5335 6 534INSERT INTO t2 SET id2=5, val2='f' RETURNING (SELECT id2 FROM t2); 535ERROR HY000: Table 't2' is specified twice, both as a target for 'INSERT' and as a separate source for data 536INSERT INTO t2 SET id2=5, val2='f' RETURNING t1.*; 537ERROR 42S02: Unknown table 'test.t1' 538# 539# INSERT...ON DUPLICATE KEY UPDATE 540# 541INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 542RETURNING id1; 543ERROR 42S22: Unknown column 'id1' in 'field list' 544INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 545RETURNING MAX(id); 546ERROR HY000: Invalid use of group function 547INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 548RETURNING (SELECT id1 FROM t1); 549ERROR 21000: Subquery returns more than 1 row 550INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 551RETURNING (SELECT * FROM t1); 552ERROR 21000: Operand should contain 1 column(s) 553INSERT INTO ins_duplicate VALUES (2,'b') ON DUPLICATE KEY UPDATE val='b' 554RETURNING (SELECT * FROM ins_duplicate); 555ERROR 21000: Operand should contain 1 column(s) 556INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 557RETURNING id2, (SELECT id1+id FROM t1 WHERE id1=1); 558ERROR 42S22: Unknown column 'id2' in 'field list' 559INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 560RETURNING (SELECT id FROM ins_duplicate); 561ERROR HY000: Table 'ins_duplicate' is specified twice, both as a target for 'INSERT' and as a separate source for data 562INSERT INTO ins_duplicate VALUES(2,'b') ON DUPLICATE KEY UPDATE val='b' 563RETURNING t1.*; 564ERROR 42S02: Unknown table 'test.t1' 565# 566# INSERT...SELECT 567# 568INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=1 RETURNING id1; 569ERROR 42S22: Unknown column 'id1' in 'field list' 570INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING MAX(id2); 571ERROR HY000: Invalid use of group function 572INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 573id1 FROM t1); 574ERROR 21000: Subquery returns more than 1 row 575INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 576* FROM t1); 577ERROR 21000: Operand should contain 1 column(s) 578INSERT INTO t2(id2, val2) SELECT * FROM t1 WHERE id1=2 RETURNING(SELECT 579* FROM t2); 580ERROR 21000: Operand should contain 1 column(s) 581INSERT INTO t2(id2,val2) SELECT * FROM t1 WHERE id1=2 RETURNING (SELECT 582id2 FROM t2); 583ERROR 21000: Subquery returns more than 1 row 584INSERT INTO t2(id2,val2) SELECT t1.* FROM t1 WHERE id1=2 RETURNING t1.*; 585ERROR 42S02: Unknown table 'test.t1' 586# 587# TRIGGER 588# 589CREATE TRIGGER bi1 before insert on t1 for each row set NEW.val1= 'z'; 590INSERT INTO t1 VALUES (4, 'd'), (5, 'e') RETURNING *; 591id1 val1 5924 z 5935 z 594CREATE TRIGGER bi2 before insert on t2 for each row 595INSERT INTO t1 VALUES (NEW.id2, NEW.val2) RETURNING *; 596ERROR 0A000: Not allowed to return a result set from a trigger 597# 598# SP 599# 600CREATE FUNCTION f1(arg INT) RETURNS TEXT 601BEGIN 602INSERT INTO t1 VALUES (arg, arg) RETURNING *; 603RETURN arg; 604END| 605ERROR 0A000: Not allowed to return a result set from a function 606CREATE PROCEDURE sp1(arg INT) 607INSERT INTO t1 VALUES (arg, arg) RETURNING *; 608CALL sp1(0); 609id1 val1 6100 z 611DROP PROCEDURE sp1; 612DROP TABLE t1; 613DROP TABLE t2; 614DROP TABLE ins_duplicate; 615DROP VIEW v1; 616DROP VIEW v2; 617DROP FUNCTION f; 618