diff options
author | Sergei Golubchik <serg@mariadb.org> | 2019-10-11 14:39:05 +0200 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2019-10-13 20:03:08 +0200 |
commit | 3593d4f935e1adc7b762bba8a7066309bb6ce016 (patch) | |
tree | c14bcae00f221a69c13bdbbab733b0d1ca7e5d70 | |
parent | a9331dd08c31af161c87cedc27a0b46445efb8bf (diff) | |
download | mariadb-git-bb-10.5-10014.tar.gz |
MDEV-10014 Add RETURNING to INSERTbb-10.5-10014
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
-rw-r--r-- | mysql-test/main/insert_returning.result | 60 | ||||
-rw-r--r-- | mysql-test/main/insert_returning.test | 36 | ||||
-rw-r--r-- | sql/sp_head.cc | 11 | ||||
-rw-r--r-- | sql/sql_insert.cc | 56 | ||||
-rw-r--r-- | sql/sql_insert.h | 3 |
5 files changed, 101 insertions, 65 deletions
diff --git a/mysql-test/main/insert_returning.result b/mysql-test/main/insert_returning.result index 8928cdfcf41..e664e02bedc 100644 --- a/mysql-test/main/insert_returning.result +++ b/mysql-test/main/insert_returning.result @@ -25,10 +25,10 @@ FROM t2 WHERE id2=1); 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=id2+1); +FROM t2 GROUP BY id2 HAVING id2=id1-2); (SELECT GROUP_CONCAT(val2) -FROM t2 GROUP BY id2 HAVING id2=id2+1) -NULL +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') @@ -110,11 +110,11 @@ 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=id2+1); +FROM t2 GROUP BY id2 HAVING id2=id1-8); (SELECT GROUP_CONCAT(val2) -FROM t2 GROUP BY id2 HAVING id2=id2+1) -NULL -NULL +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') @@ -200,9 +200,9 @@ 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=id2+1); -(SELECT GROUP_CONCAT(val2) FROM t2 GROUP BY id2 HAVING id2=id2+1) -NULL +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; @@ -274,10 +274,10 @@ FROM t2 WHERE id2=1); 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=id2+1); +FROM t2 GROUP BY id2 HAVING id2=id1-3); (SELECT GROUP_CONCAT(val2) -FROM t2 GROUP BY id2 HAVING id2=id2+1) -NULL +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') @@ -302,7 +302,6 @@ id1 val1 8 n INSERT IGNORE INTO t1 SET id1= 8, val1= 'h' RETURNING *; id1 val1 -8 h Warnings: Warning 1062 Duplicate entry '8' for key 'PRIMARY' EXPLAIN INSERT INTO t1 SET id1=9, val1='i' RETURNING id1; @@ -405,14 +404,7 @@ id2 val2 8 n INSERT IGNORE INTO v2 SELECT * FROM v1 RETURNING *; id2 val2 -1 a -2 b -3 c -4 d 5 e -6 f -7 g -8 n 26 Z 12 l Warnings: @@ -552,6 +544,32 @@ 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 +# +# 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; 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; diff --git a/sql/sp_head.cc b/sql/sp_head.cc index 1968ba83101..8c07a060f30 100644 --- a/sql/sp_head.cc +++ b/sql/sp_head.cc @@ -290,6 +290,10 @@ sp_get_flags_for_command(LEX *lex) break; case SQLCOM_DELETE: case SQLCOM_DELETE_MULTI: + case SQLCOM_INSERT: + case SQLCOM_REPLACE: + case SQLCOM_REPLACE_SELECT: + case SQLCOM_INSERT_SELECT: { /* DELETE normally doesn't return resultset, but there are 3 exceptions: @@ -297,8 +301,7 @@ sp_get_flags_for_command(LEX *lex) - EXPLAIN DELETE ... - ANALYZE DELETE ... */ - if (lex->first_select_lex()->item_list.is_empty() && - !lex->describe && !lex->analyze_stmt) + if (!lex->has_returning() && !lex->describe && !lex->analyze_stmt) flags= 0; else flags= sp_head::MULTI_RESULTS; @@ -306,10 +309,6 @@ sp_get_flags_for_command(LEX *lex) } case SQLCOM_UPDATE: case SQLCOM_UPDATE_MULTI: - case SQLCOM_INSERT: - case SQLCOM_REPLACE: - case SQLCOM_REPLACE_SELECT: - case SQLCOM_INSERT_SELECT: { if (!lex->describe && !lex->analyze_stmt) flags= 0; diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index ba7bf4c44c7..7be005bd79b 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -1076,21 +1076,9 @@ bool mysql_insert(THD *thd, TABLE_LIST *table_list, } else #endif - error=write_record(thd, table ,&info); + error= write_record(thd, table, &info, result); if (unlikely(error)) break; - /* - We send the row after writing it to the table so that the - correct values are sent to the client. Otherwise it won't show - autoinc values (generated inside the handler::ha_write()) and - values updated in ON DUPLICATE KEY UPDATE (handled inside - write_record()). - */ - if (returning && result->send_data(returning->item_list) < 0) - { - error= 1; - break; - } thd->get_stmt_da()->inc_current_row_for_warning(); } its.rewind(); @@ -1688,6 +1676,7 @@ int vers_insert_history_row(TABLE *table) info - COPY_INFO structure describing handling of duplicates and which is used for counting number of records inserted and deleted. + sink - result sink for the RETURNING clause NOTE Once this record will be written to table after insert trigger will @@ -1704,7 +1693,7 @@ int vers_insert_history_row(TABLE *table) */ -int write_record(THD *thd, TABLE *table,COPY_INFO *info) +int write_record(THD *thd, TABLE *table, COPY_INFO *info, select_result *sink) { int error, trg_error= 0; char *key=0; @@ -1750,7 +1739,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (info->ignore) { table->file->print_error(error, MYF(ME_WARNING)); - goto ok_or_after_trg_err; /* Ignoring a not fatal error, return 0 */ + goto after_trg_or_ignored_err; /* Ignoring a not fatal error */ } goto err; } @@ -1856,7 +1845,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) /* CHECK OPTION for VIEW ... ON DUPLICATE KEY UPDATE ... */ res= info->table_list->view_check_option(table->in_use, info->ignore); if (res == VIEW_CHECK_SKIP) - goto ok_or_after_trg_err; + goto after_trg_or_ignored_err; if (res == VIEW_CHECK_ERROR) goto before_trg_err; @@ -1874,7 +1863,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) if (!(thd->variables.old_behavior & OLD_MODE_NO_DUP_KEY_WARNINGS_WITH_IGNORE)) table->file->print_error(error, MYF(ME_WARNING)); - goto ok_or_after_trg_err; + goto after_trg_or_ignored_err; } goto err; } @@ -1893,7 +1882,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) table->file->print_error(error, MYF(0)); trg_error= 1; restore_record(table, record[2]); - goto ok_or_after_trg_err; + goto after_trg_or_ignored_err; } restore_record(table, record[2]); } @@ -1934,7 +1923,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) { table->file->restore_auto_increment(prev_insert_id_for_cur_row); } - goto ok_or_after_trg_err; + goto ok; } else /* DUP_REPLACE */ { @@ -2019,7 +2008,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) TRG_ACTION_AFTER, TRUE)) { trg_error= 1; - goto ok_or_after_trg_err; + goto after_trg_or_ignored_err; } /* Let us attempt do write_row() once more */ } @@ -2055,7 +2044,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) OLD_MODE_NO_DUP_KEY_WARNINGS_WITH_IGNORE)) table->file->print_error(error, MYF(ME_WARNING)); table->file->restore_auto_increment(); - goto ok_or_after_trg_err; + goto after_trg_or_ignored_err; } after_trg_n_copied_inc: @@ -2065,7 +2054,17 @@ after_trg_n_copied_inc: table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_AFTER, TRUE)); -ok_or_after_trg_err: +ok: + /* + We send the row after writing it to the table so that the + correct values are sent to the client. Otherwise it won't show + autoinc values (generated inside the handler::ha_write()) and + values updated in ON DUPLICATE KEY UPDATE. + */ + if (sink && sink->send_data(thd->lex->returning()->item_list) < 0) + trg_error= 1; + +after_trg_or_ignored_err: if (key) my_safe_afree(key,table->s->max_unique_length); if (!table->file->has_transactions()) @@ -3413,7 +3412,7 @@ bool Delayed_insert::handle_inserts(void) VCOL_UPDATE_FOR_WRITE); } - if (unlikely(tmp_error) || unlikely(write_record(&thd, table, &info))) + if (unlikely(tmp_error || write_record(&thd, table, &info, NULL))) { info.error_count++; // Ignore errors thread_safe_increment(delayed_insert_errors,&LOCK_delayed_status); @@ -3908,16 +3907,7 @@ int select_insert::send_data(List<Item> &values) } } - error= write_record(thd, table, &info); - /* - Sending the result set to the cliet after writing record. The reason is - same as other variants of insert. - */ - if (sel_result && sel_result->send_data(thd->lex->returning()->item_list) < 0) - { - error= 1; - DBUG_RETURN(1); - } + error= write_record(thd, table, &info, sel_result); table->vers_write= table->versioned(); table->auto_increment_field_not_null= FALSE; diff --git a/sql/sql_insert.h b/sql/sql_insert.h index 5b86f09d13b..977a0eb23c8 100644 --- a/sql/sql_insert.h +++ b/sql/sql_insert.h @@ -38,7 +38,8 @@ void upgrade_lock_type_for_insert(THD *thd, thr_lock_type *lock_type, int check_that_all_fields_are_given_values(THD *thd, TABLE *entry, TABLE_LIST *table_list); int vers_insert_history_row(TABLE *table); -int write_record(THD *thd, TABLE *table, COPY_INFO *info); +int write_record(THD *thd, TABLE *table, COPY_INFO *info, + select_result *returning= NULL); void kill_delayed_threads(void); #ifdef EMBEDDED_LIBRARY |