Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
Sprint 7 (07.04.2025), Q3/2026 Server Development
Description
Add ...RETURNING clause for UPDATE for multi table.
Basic idea:
Make parser accept ...RETURNING for multi table UPDATE (~1d)
Modify how temporary tables will be used to store old and new values and return the new value (~19d)
basic idea:
Let's take t1 (with two rows that need to be updated, with row ids t1_rid3 and t1_rid6), t2 (with two rows that need to be updated, with row ids t2_rid4 and t2_rid8), and t3 (with one row that needs to be updated, with row id t3_rid10).
Let's say the temporary tables corresponding to each table are temp1, temp2, and temp3:
• temp1 maps
t1_rid3 -> temp1_rid12 and
t1_rid6 -> temp1_rid13
• temp2 maps
t2_rid4 -> temp2_rid15 and
t2_rid8 -> temp2_rid16
• temp3 maps
t3_rid10 -> temp3_rid17
In a multi-table UPDATE, RETURNING can be implemented in one of the following four ways (open for discussion):
1. Create a separate temporary table during the prepare phase (let's call it temp_final1) that stores the row ID of the temporary table in the order in which rows are processed. Then, during inside send_data(), iterate over temp_final1, fetch old_values (before ha_update_rows()), update the base tables, and return the result to the client(send_result_set_row()). Drawback of this approach is that if a row is revisited during execution, may end up with result rows such as below because the same row was visited again after it had already been updated:
1. (old_value, new_value)
2. (new_value, new_value)
2. Extend the per-table temporary tables (temp1, temp2, temp3) to also store the old values (they currently store only the new values for the rows to be updated). Then insert both old and new values into a new temporary table made during the prepare phase( let's call it temp_final2). After all tables have been updated in send_data(), iterate over temp_final2 and send its rows one by one to the client using send_result_set_row().
3. This is a combination of the first two approaches. Additionally, store the old values (in another temporary table ?) and prevent redundant rows from being sent to the result set. This avoids duplicate (new_value, new_value) results, at the cost of additional memory usage.
4. iterate over each temporary table corresponding to temp1, temp2, and temp3. For each row, fetch the old value , update the base table, use the new value, and return rows for each table separately.
Attachments
Issue Links
- blocks
-
MDEV-12326 Implement multiple-table UPDATE/DELETE returning a result set
-
- Open
-
-
MDEV-37773 Use the syntax of UPDATE...RETURNING to return the old values in REPLACE and INSERT ODKU
-
- Open
-
- relates to
-
MDEV-5092 Implement UPDATE with result set (UPDATE ... RETURNING) for single table
-
- Closed
-
- split from
-
MDEV-5092 Implement UPDATE with result set (UPDATE ... RETURNING) for single table
-
- Closed
-