Alexander Barkov
ece174d55d
MDEV-39563 Implement UPDATE ... RETURNING ... INTO
...
Adding support for UPDATE .. RETURNING .. INTO queries.
For example:
UPDATE t1 SET a=10,b=20 RETURNING a,b INTO va,vb;
UPDATE t1 SET a=10,b=20 RETURNING a,b INTO @a,@b;
Limitations:
1. These types of queries:
- REPLACE .. RETURNING .. INTO
- DELETE .. RETURNING .. INTO
- INSERT .. RETURNING .. INTO
do not work - they return an error.
They will be implemented separately, when needed.
2. UPDATE..RETURNING..INTO with --binlog_format=statement is not allowed
and an error is raised.
3. Using OLD_VALUE(col) inside UPDATE..RETURNING..INTO is not allowed
and an error is raised.
4. Multi-table updates, as well as single table updates with a subquery
to the same table in WHERE (which get converted to multi-table) do
not work and an error is raised.
Notes:
1. ANALYZE and EXPLAIN
Both
ANALYZE UPDATE .. RETURNING .. INTO ..
EXPLAIN UPDATE .. RETURNING .. INTO ..
return this error:
'RETURNING..INTO' is not allowed in this context
2. Behavior on no data
a. In case of degenerated plans (WHERE 1=0, LIMIT 0),
no errors are raised.
b. If the updated table contains no rows, the behavior depends on the engine,
for example:
- MyISAM returns no errors
- InnoDB raises
No data - zero rows fetched, selected, or processed
This behavior is engine dependent because some engines (e.g. MyISAM)
quickly know that the table has no records and execute the statement
using a degenerated plan.
c. If there are some rows, but non of them match the WHERE condition,
then this error is raised:
No data - zero rows fetched, selected, or processed
d. If some rows where found but none of them actually
got changed by the SET, still this error is raised:
No data - zero rows fetched, selected, or processed
The error message might be misleading. However, if we read
it as "zero rows [that required updates] fetched", it looks OK.
Let's not introduce a new error message for now.
3. Behavior on VIEW with CHECK OPTION
The "too many rows" check is done after the view's CHECK OPTION check.
With UPDATE IGNORE, a row rejected by WITH CHECK OPTION is skipped
(with a warning) and does not count as an updated row, so
UPDATE IGNORE .. RETURNING .. INTO does not raise ER_TOO_MANY_ROWS
if only one row is actually updated.
Helper changes:
1. The grammar in analyze_stmt_command was changed to have
LEX::analyze_stmt set to true earlier, so
LEX::set_returning_into_result() already knows if this
is an ANALYZE statement.
2. The Sql_cmd_update constructor is now called earlier in the grammar,
to be able to call Sql_cmd_update::set_with_old_value_items()
in the SET and RETURNING clauses.
3. Sql_cmd_dml::lex is now set during the constructor time.
It makes things easier:
- Sql_cmd_update::returns_result_set() needs the lex.
- Sql_cmd_delete::orig_multitable and Sql_cmd_update::orig_multitable
are not needed any more.
They were used only in Sql_cmd_delete::sql_command_code() and
Sql_cmd_update::sql_command_code().
Sql_cmd_dml::sql_command_code() now returns lex->sql_command.
The overrides Sql_cmd_delete::sql_command_code() and
Sql_cmd_update::sql_command_code() were removed.
This commit also fixes bugs found during testing:
- MDEV-41262 ER_TOO_MANY_ROWS should be checked after CHECK OPTION in view
while Update Returning Into
- MDEV-41265 SELECT/ UPDATE RETURNING INTO a single row-field fails with
ER_WRONG_NUMBER_OF_COLUMNS_IN_SELECT, even though the column count matches
This bug was also fixed in 12.3.4.
- MDEV-41260 Misleading message text "uses a LIMIT clause" after Update
Returning Into
2026-10-08 20:28:08 +04:00
..
2026-10-08 20:28:08 +04:00
2026-06-15 16:53:03 +02:00
2026-05-26 20:00:43 +04:00
2025-06-13 08:52:54 +10:00
2026-08-13 23:15:53 +04:00
2026-06-17 16:46:39 +02:00
2026-08-12 14:31:53 +03:00
2026-05-15 13:30:21 +10:00
2026-08-03 13:42:39 +02:00
2026-02-06 17:46:52 +01:00
2024-11-05 14:00:47 -08:00
2025-07-28 21:29:29 +02:00
2024-11-05 14:00:48 -08:00
2026-08-03 13:42:39 +02:00
2025-04-28 12:59:39 +03:00
2025-09-29 18:25:09 +03:00
2024-11-05 14:00:47 -08:00
2025-03-14 12:08:56 +04:00
2026-02-16 21:55:18 +01:00
2025-03-14 12:08:56 +04:00
2025-03-14 12:08:56 +04:00
2025-06-18 16:46:21 +10:00
2024-12-02 11:35:34 +02:00
2026-07-20 13:23:00 +03:00
2026-07-24 17:06:40 +02:00
2024-12-02 17:51:17 +02:00
2026-01-31 14:08:54 +01:00
2026-07-20 13:23:00 +03:00
2026-07-20 13:23:00 +03:00
2025-08-01 18:03:20 +02:00
2026-01-31 14:08:54 +01:00
2026-07-30 16:15:40 +02:00
2026-07-30 16:15:40 +02:00
2024-11-05 14:00:48 -08:00
2024-12-02 11:35:34 +02:00
2026-08-13 13:00:12 +03:00
2026-08-12 14:31:52 +03:00
2026-07-29 12:27:46 +02:00
2026-03-24 09:49:17 +02:00
2026-03-24 09:49:17 +02:00
2025-04-22 16:26:36 -04:00
2025-04-29 16:53:02 +02:00
2024-10-15 15:36:12 +11:00
2025-04-22 16:26:36 -04:00
2026-06-14 23:40:25 +05:30
2026-08-12 14:31:52 +03:00
2026-05-26 21:15:58 +02:00
2026-06-15 16:53:03 +02:00
2026-05-26 21:15:58 +02:00
2025-10-08 09:05:38 +02:00
2026-08-12 14:31:56 +03:00
2026-08-12 14:31:52 +03:00
2024-11-05 14:00:47 -08:00
2026-07-29 12:27:46 +02:00
2024-11-29 13:43:17 +02:00
2026-07-02 11:08:22 +10:00
2026-07-30 11:41:55 +02:00
2026-05-26 21:15:58 +02:00
2025-08-01 18:03:20 +02:00
2025-08-01 18:03:20 +02:00
2026-06-18 11:01:05 +03:00
2024-12-05 10:11:58 +01:00
2026-08-03 13:42:39 +02:00
2026-07-30 16:15:40 +02:00
2026-07-06 09:40:58 -04:00
2026-02-03 14:53:15 +01:00
2026-07-30 11:41:55 +02:00
2026-05-26 21:15:58 +02:00
2026-02-03 14:53:15 +01:00
2025-10-15 11:32:39 +04:00
2026-02-03 14:53:15 +01:00
2026-02-03 14:53:15 +01:00
2026-08-03 13:42:39 +02:00
2026-08-03 13:42:39 +02:00
2026-08-12 14:31:55 +03:00
2026-02-03 14:53:15 +01:00
2026-07-30 11:41:55 +02:00
2026-02-04 17:35:21 +01:00
2026-06-29 07:41:06 +03:00
2026-03-13 16:17:11 +01:00
2025-09-16 10:10:08 +05:30
2026-03-24 09:49:17 +02:00
2024-10-08 11:58:46 +02:00
2026-01-31 14:08:54 +01:00
2026-04-01 15:03:59 +03:00
2026-07-30 11:41:55 +02:00
2026-07-30 11:41:55 +02:00
2026-05-15 13:46:27 +04:00
2026-08-03 13:42:39 +02:00
2026-08-03 13:42:39 +02:00
2026-07-30 11:41:55 +02:00
2025-09-16 10:10:08 +05:30
2026-07-15 18:22:18 +10:00
2026-01-08 12:02:45 +01:00
2026-08-03 13:42:39 +02:00
2026-06-11 11:40:20 +10:00
2026-02-05 11:55:12 +11:00
2024-11-23 08:14:22 -07:00
2025-03-14 12:08:56 +04:00
2025-03-14 12:08:56 +04:00
2025-05-05 12:02:47 +07:00
2026-08-13 23:15:53 +04:00
2026-05-26 21:15:58 +02:00
2025-04-27 19:40:00 +02:00
2024-11-05 14:00:47 -08:00
2026-08-04 15:04:06 +10:00
2026-05-26 20:00:43 +04:00
2024-10-17 07:53:59 -06:00
2026-01-23 03:21:03 +01:00
2026-08-17 13:46:32 +03:00
2026-08-17 13:46:32 +03:00
2026-07-30 11:41:55 +02:00
2026-08-17 13:46:32 +03:00
2026-06-16 15:03:05 +02:00
2026-05-26 21:15:58 +02:00
2025-03-14 12:08:56 +04:00
2026-05-26 20:00:43 +04:00
2026-06-05 13:15:49 +04:00
2025-08-04 20:24:01 +02:00
2024-10-03 15:59:20 +10:00
2025-03-14 12:08:56 +04:00
2026-06-24 12:27:13 +02:00
2025-03-14 12:08:56 +04:00
2025-03-14 12:08:56 +04:00
2026-05-26 21:15:58 +02:00
2026-07-07 15:56:47 +02:00
2026-06-25 13:03:39 +05:30
2026-06-25 13:03:39 +05:30
2025-04-15 18:58:32 +02:00
2026-05-08 08:00:01 +02:00
2026-08-03 13:42:39 +02:00
2026-05-26 21:15:58 +02:00
2026-07-29 12:27:46 +02:00
2026-01-23 03:21:03 +01:00
2026-08-13 13:00:12 +03:00
2026-08-12 14:31:57 +03:00
2025-10-16 09:31:56 +12:00
2025-10-16 09:31:56 +12:00
2026-05-22 09:52:05 +02:00
2026-05-22 09:52:05 +02:00
2026-04-30 16:58:47 +07:00
2026-04-30 17:28:18 +07:00
2026-04-30 16:58:47 +07:00
2026-06-25 13:03:39 +05:30
2025-09-16 10:10:08 +05:30
2025-10-10 20:32:56 +07:00
2026-08-12 14:31:52 +03:00
2026-08-12 14:31:56 +03:00
2026-08-12 14:31:53 +03:00
2024-11-05 14:00:47 -08:00
2026-07-29 12:27:46 +02:00
2025-03-14 12:08:56 +04:00
2026-07-03 15:08:53 +03:00
2026-07-30 11:41:55 +02:00
2026-07-01 09:36:53 +05:30
2026-08-12 14:31:57 +03:00
2025-10-08 09:05:38 +02:00
2025-03-14 12:08:56 +04:00
2026-08-12 14:31:55 +03:00
2026-08-12 14:31:55 +03:00
2026-05-06 11:35:09 +10:00
2025-07-22 10:44:12 +10:00
2025-03-14 12:08:56 +04:00
2025-03-14 12:08:56 +04:00
2026-07-30 11:41:55 +02:00
2026-07-22 14:36:17 +02:00
2026-08-04 15:04:06 +10:00
2026-08-04 15:04:06 +10:00
2026-08-04 15:04:06 +10:00
2026-05-08 08:00:01 +02:00
2026-07-20 13:23:00 +03:00
2024-11-05 14:00:47 -08:00
2026-01-31 14:08:54 +01:00
2026-05-26 21:15:58 +02:00
2025-04-27 19:40:00 +02:00
2026-06-21 15:32:20 +02:00
2026-01-25 19:10:53 +01:00
2024-11-05 14:00:47 -08:00
2026-04-28 13:05:50 +03:00
2026-04-28 13:05:50 +03:00
2025-10-10 20:32:56 +07:00
2026-02-03 14:53:15 +01:00
2025-09-15 18:57:04 -06:00
2025-09-29 18:25:09 +03:00
2026-05-26 21:15:58 +02:00
2026-05-26 21:15:58 +02:00
2026-08-14 17:30:08 +02:00
2025-10-08 09:05:38 +02:00
2025-04-11 13:42:12 +02:00
2026-08-14 17:30:08 +02:00
2026-05-26 21:15:58 +02:00
2026-06-16 12:24:32 +02:00
2026-07-30 11:41:55 +02:00
2025-07-07 14:03:49 -06:00
2026-07-30 11:41:55 +02:00
2026-01-27 12:59:30 -07:00
2026-07-28 13:29:55 -06:00
2026-06-16 12:24:32 +02:00
2026-02-07 22:06:40 +01:00
2026-01-27 12:59:23 -07:00
2026-01-27 12:59:23 -07:00
2026-04-27 18:26:17 -06:00
2026-01-27 12:59:30 -07:00
2026-01-27 12:59:30 -07:00
2025-05-05 12:02:43 +07:00
2024-11-05 14:00:47 -08:00
2025-03-14 12:08:56 +04:00
2026-10-08 20:28:08 +04:00
2025-03-14 12:08:56 +04:00
2026-06-15 16:53:03 +02:00
2026-06-15 16:53:03 +02:00
2025-04-23 17:20:47 -06:00
2025-04-23 17:20:47 -06:00
2025-03-14 12:08:56 +04:00
2026-04-02 09:15:17 +03:00
2026-05-26 21:15:58 +02:00
2026-05-26 21:15:58 +02:00
2026-05-26 21:15:58 +02:00
2026-07-20 13:23:00 +03:00
2026-05-15 13:30:21 +10:00
2026-04-30 17:22:36 +07:00
2025-10-15 11:32:39 +04:00
2026-08-14 17:30:08 +02:00
2026-04-27 18:26:17 -06:00
2025-03-14 12:08:56 +04:00
2026-02-06 17:46:50 +01:00
2025-11-04 12:39:27 +01:00
2026-04-06 15:16:38 +04:00
2026-04-06 15:16:38 +04:00
2026-10-08 20:28:08 +04:00
2026-08-13 23:15:53 +04:00
2026-07-14 14:22:31 +04:00
2026-08-13 23:15:53 +04:00
2026-08-13 23:15:53 +04:00
2026-08-13 23:15:53 +04:00
2025-08-01 18:03:20 +02:00
2026-08-20 11:12:17 +04:00
2026-08-20 11:12:17 +04:00
2026-04-06 15:16:38 +04:00
2026-08-13 23:15:53 +04:00
2026-08-13 23:15:53 +04:00
2026-07-30 11:41:55 +02:00
2026-07-19 16:35:20 +10:00
2025-09-04 18:08:39 +03:00
2026-08-14 17:30:08 +02:00
2026-07-20 13:23:00 +03:00
2026-05-26 21:15:58 +02:00
2026-08-03 13:42:39 +02:00
2026-07-30 11:41:55 +02:00
2026-06-15 15:15:10 +02:00
2026-06-15 15:15:10 +02:00
2024-11-05 14:00:47 -08:00
2026-08-12 14:31:57 +03:00
2025-03-14 12:08:56 +04:00
2026-01-08 21:41:40 +01:00
2026-08-04 15:04:06 +10:00
2026-08-04 15:04:06 +10:00
2025-10-08 09:05:38 +02:00
2026-02-24 14:48:11 +02:00
2025-10-08 09:05:38 +02:00
2024-11-05 14:00:50 -08:00
2026-04-02 09:15:17 +03:00
2025-03-14 12:08:56 +04:00
2026-10-08 20:28:08 +04:00
2026-10-08 20:28:08 +04:00
2026-10-08 20:28:08 +04:00
2026-01-05 20:34:44 +01:00
2026-07-30 11:41:55 +02:00
2025-05-03 12:06:36 +02:00
2026-03-24 09:49:17 +02:00
2024-11-05 14:00:47 -08:00
2024-11-05 14:00:47 -08:00
2026-06-29 07:40:04 +03:00
2026-01-08 12:02:45 +01:00
2026-07-30 16:15:40 +02:00
2026-07-30 16:15:40 +02:00
2026-08-03 13:42:39 +02:00
2026-02-06 17:46:51 +01:00
2026-03-17 18:57:42 +01:00
2026-08-12 14:31:54 +03:00
2026-10-08 20:28:08 +04:00
2026-07-20 13:23:00 +03:00
2026-07-30 16:15:40 +02:00
2026-04-15 21:49:26 +02:00
2025-04-19 10:59:58 +04:00
2026-02-06 17:46:51 +01:00
2026-02-06 17:46:51 +01:00
2026-08-12 14:31:52 +03:00
2026-08-12 14:31:52 +03:00
2025-09-29 18:25:09 +03:00
2025-08-04 12:05:53 +02:00
2024-11-05 14:00:47 -08:00
2025-05-05 12:02:48 +07:00
2024-12-02 17:51:17 +02:00
2026-05-28 17:27:57 +03:00
2026-08-03 13:42:39 +02:00
2025-09-19 12:31:14 +03:00
2025-10-08 09:05:38 +02:00
2025-10-08 09:05:38 +02:00
2026-08-12 14:31:55 +03:00
2026-08-12 14:31:57 +03:00
2026-10-08 20:28:08 +04:00
2026-10-08 20:28:08 +04:00
2026-06-24 12:27:13 +02:00
2025-03-14 12:08:56 +04:00
2024-11-05 14:00:47 -08:00
2026-04-06 15:00:16 +04:00
2026-05-26 21:15:58 +02:00
2026-04-09 17:25:40 +05:30
2026-04-09 17:25:40 +05:30
2025-02-12 10:17:44 +01:00
2025-03-14 12:08:56 +04:00
2025-08-04 12:05:53 +02:00
2026-08-14 17:30:08 +02:00
2026-07-20 13:23:00 +03:00
2026-07-30 11:41:55 +02:00
2026-08-04 15:04:06 +10:00
2026-08-04 15:04:06 +10:00
2026-08-04 15:04:06 +10:00
2026-08-13 23:15:53 +04:00
2026-08-13 23:15:53 +04:00
2025-07-29 13:15:02 +10:00
2026-07-06 13:44:56 +02:00
2026-08-12 14:31:52 +03:00
2025-01-09 09:41:38 +02:00
2026-08-03 13:42:39 +02:00
2026-02-09 15:28:24 +04:00
2026-04-16 09:26:39 +10:00
2026-03-11 08:05:08 +02:00
2025-03-14 12:08:56 +04:00
2024-12-02 17:51:17 +02:00
2026-06-26 07:32:21 +03:00
2026-07-30 11:41:55 +02:00
2026-08-03 13:42:39 +02:00
2026-01-23 03:21:03 +01:00
2026-10-08 20:28:08 +04:00
2026-06-22 09:21:14 -04:00
2026-05-27 12:49:36 +02:00
2025-10-08 09:05:38 +02:00
2026-05-26 21:15:58 +02:00
2024-10-15 10:50:22 +11:00
2026-08-12 14:31:56 +03:00
2026-02-06 17:46:52 +01:00
2025-10-08 09:05:38 +02:00
2026-08-12 14:31:54 +03:00
2026-08-12 14:31:52 +03:00
2026-08-12 14:31:57 +03:00
2026-08-12 14:31:57 +03:00
2026-08-03 13:42:39 +02:00
2026-07-30 16:15:40 +02:00
2026-04-01 10:49:05 +03:00
2026-03-17 18:57:42 +01:00
2025-10-08 09:05:38 +02:00
2026-07-28 13:14:38 +02:00
2026-08-13 23:15:53 +04:00
2026-02-07 22:12:32 +01:00
2025-04-29 11:18:00 +10:00
2026-05-26 21:15:58 +02:00
2026-02-09 15:28:24 +04:00
2026-05-22 09:52:05 +02:00
2025-08-01 18:03:20 +02:00
2026-05-22 09:52:05 +02:00
2026-05-22 09:52:05 +02:00
2026-05-22 09:52:05 +02:00
2025-04-19 10:59:58 +04:00
2025-03-14 12:08:56 +04:00
2025-04-19 10:59:58 +04:00
2026-10-08 20:28:08 +04:00
2026-04-06 15:16:38 +04:00
2025-08-01 18:03:20 +02:00
2025-08-01 18:03:20 +02:00
2026-07-30 16:15:40 +02:00
2026-04-06 15:00:16 +04:00
2026-08-03 13:42:39 +02:00
2026-08-03 13:42:39 +02:00
2025-03-14 12:08:56 +04:00
2024-12-02 17:51:17 +02:00
2026-05-26 21:15:58 +02:00
2026-10-08 20:28:08 +04:00
2026-10-08 20:28:08 +04:00
2026-07-30 16:15:40 +02:00
2026-06-15 16:53:03 +02:00
2025-03-14 12:08:56 +04:00
2026-10-08 20:28:08 +04:00
2025-04-19 10:59:58 +04:00
2026-03-13 16:17:11 +01:00
2026-03-13 16:17:11 +01:00
2026-08-13 23:15:53 +04:00
2026-08-14 17:30:08 +02:00
2026-05-26 21:15:58 +02:00
2026-06-24 12:27:13 +02:00
2026-06-18 16:14:40 +07:00
2026-08-03 13:42:39 +02:00
2026-08-04 15:04:06 +10:00
2026-07-20 13:23:00 +03:00
2025-02-12 10:17:44 +01:00
2025-04-29 11:18:00 +10:00
2026-03-24 09:49:17 +02:00
2025-09-04 18:08:39 +03:00
2025-03-14 12:08:56 +04:00
2026-01-23 03:23:20 +01:00
2025-08-04 14:58:10 +02:00
2025-09-30 17:37:12 +03:00
2025-10-08 09:05:38 +02:00
2025-03-14 12:08:56 +04:00
2026-05-22 09:52:05 +02:00
2024-12-02 17:51:17 +02:00
2024-12-02 11:35:34 +02:00
2026-04-06 15:00:16 +04:00
2025-09-15 18:57:04 -06:00
2026-05-05 13:57:50 +03:00
2026-04-13 21:25:09 +02:00
2025-01-21 12:18:56 +01:00
2025-03-14 12:08:56 +04:00
2025-03-14 12:08:56 +04:00
2025-09-10 15:03:59 +03:00
2026-06-16 12:24:32 +02:00
2025-08-04 14:58:10 +02:00
2025-11-28 12:56:27 +02:00
2025-11-25 13:01:26 +02:00
2026-01-08 12:02:45 +01:00
2026-07-30 11:41:55 +02:00
2025-08-04 14:58:18 +02:00
2026-06-24 17:31:40 +02:00
2026-07-20 13:23:00 +03:00
2026-06-04 09:23:29 +03:00
2026-06-21 15:32:20 +02:00
2025-10-14 14:47:35 +02:00
2026-06-15 16:53:03 +02:00
2026-04-02 09:15:17 +03:00
2025-12-15 14:47:15 +02:00
2024-09-01 02:48:19 +02:00
2024-10-03 13:24:43 +03:00
2024-09-09 13:50:38 +03:00
2026-08-03 13:42:39 +02:00
2026-06-04 09:23:29 +03:00
2026-01-08 12:02:45 +01:00
2026-02-07 22:12:32 +01:00
2026-01-20 10:23:44 +02:00
2025-07-14 08:21:50 +02:00
2026-08-03 13:42:39 +02:00
2026-07-29 12:27:46 +02:00
2026-06-25 14:49:32 +02:00
2026-06-04 09:23:29 +03:00
2025-10-08 14:15:04 +03:00
2026-04-21 13:00:39 +03:00
2026-06-05 13:15:49 +04:00
2024-10-16 13:23:47 +02:00