1
0
mirror of https://github.com/MariaDB/server.git synced 2026-10-23 18:49:29 +03:00
Files
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-06-15 16:53:03 +02:00
…
2026-08-12 14:31:53 +03: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-09-29 18:25:09 +03: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
…
2024-12-02 11:35:34 +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
…
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-12-02 11:35:34 +02:00
2026-03-24 09:49:17 +02:00
2026-03-24 09:49:17 +02: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
2026-07-29 12:27:46 +02:00
2024-11-29 13:43:17 +02:00
…
…
2026-05-26 21:15:58 +02: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-02-03 14:53:15 +01:00
2026-05-26 21:15:58 +02: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-02-03 14:53:15 +01:00
2026-07-30 11:41:55 +02:00
2026-06-29 07:41:06 +03:00
2026-03-24 09:49:17 +02:00
2026-01-31 14:08:54 +01:00
2026-04-01 15:03:59 +03:00
2026-08-03 13:42:39 +02:00
2026-08-03 13:42:39 +02:00
2026-01-08 12:02:45 +01:00
2026-08-03 13:42:39 +02:00
2026-05-26 21:15:58 +02:00
…
2026-07-30 11:41:55 +02: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
…
2024-10-03 15:59:20 +10:00
…
2025-03-14 12:08:56 +04:00
2026-05-26 21:15:58 +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-07-30 11:41:55 +02:00
2026-05-08 08:00:01 +02:00
2026-07-20 13:23:00 +03:00
2026-01-31 14:08:54 +01:00
2026-05-26 21:15:58 +02:00
2026-02-03 14:53:15 +01: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-05-26 21:15:58 +02:00
2026-06-16 12:24:32 +02:00
2026-07-30 11:41:55 +02:00
2026-06-16 12:24:32 +02:00
2026-06-15 16:53:03 +02:00
2026-06-15 16:53:03 +02: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-07-20 13:23:00 +03:00
2026-02-06 17:46:50 +01:00
2025-11-04 12:39:27 +01:00
2026-07-30 11:41:55 +02:00
2025-09-04 18:08:39 +03: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
2026-01-08 21:41:40 +01:00
2026-04-02 09:15:17 +03:00
2026-01-05 20:34:44 +01:00
2026-07-30 11:41:55 +02:00
2026-03-24 09:49:17 +02: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-07-20 13:23:00 +03:00
2026-07-30 16:15:40 +02:00
…
2026-02-06 17:46:51 +01:00
2026-02-06 17:46:51 +01:00
2025-09-29 18:25:09 +03:00
2025-08-04 12:05:53 +02:00
2024-12-02 17:51:17 +02:00
2026-08-03 13:42:39 +02:00
2025-09-19 12:31:14 +03:00
2026-05-26 21:15:58 +02:00
2025-03-14 12:08:56 +04:00
2026-07-20 13:23:00 +03:00
2025-01-09 09:41:38 +02:00
2026-08-03 13:42:39 +02:00
2024-12-02 17:51:17 +02:00
2026-07-30 11:41:55 +02:00
2026-08-03 13:42:39 +02:00
2026-05-27 12:49:36 +02:00
2026-05-26 21:15:58 +02: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-07-28 13:14:38 +02: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-05-22 09:52:05 +02:00
2026-05-22 09:52:05 +02: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-07-30 16:15:40 +02:00
2026-06-15 16:53:03 +02:00
2026-05-26 21:15:58 +02:00
2026-06-24 12:27:13 +02:00
2026-08-03 13:42:39 +02:00
2026-07-20 13:23:00 +03: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-05-22 09:52:05 +02:00
2024-12-02 17:51:17 +02:00
2024-12-02 11:35:34 +02:00
2025-03-14 12:08:56 +04:00
2026-06-16 12:24:32 +02:00
2025-11-28 12:56:27 +02:00
2026-07-20 13:23:00 +03:00
2026-06-21 15:32:20 +02:00
2026-06-15 16:53:03 +02:00
2026-04-02 09:15:17 +03: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-02-07 22:12:32 +01: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