GSoC 2019 with MariaDB
Student: Miroslav Koberskii
GitHub: @Mup0c
Organization: MariaDB
Project Title: Implementing UPDATE with result set
About
The task is about adding RETURNING option to UPDATE clause in MariaDB server, that returns a result set of the changed rows to the client.
UPDATE table SET table.item = table.item + 100 RETURNING table.item;
It contains two parts: for single-table UPDATE and for multiple tables UPDATE. My main task was a single-table variant. And extra goal was doing the multiple table variant.
What's done:
- Extended parser to parse RETURNING option in UPDATE clause.
- Maked necessary preparations, passed
select_send
tomysql_update
function and called it's functions where needed. - Wrote tests for single-table UPDATE .. RETURNING.
for multiple table variant:
Multiple table UPDATE is resolved by passing custom select_result
(multi_update
) to mysql_select
.
It makes joins and calls multi_update
functions. These functions contain all the logic of multiple table
UPDATE, and intercept sending rows to the client.
So I've added select_send
to multi_update
to be able to actually send result set rows to the client.
Updates are made table by table with help of temporary tables, that store rowid (unique identificator for row in table) and values that will be inserted to the original table in the position that rowid points.
So we can not just call send_data
when updates are done. Only one table will be in right position. Other tables'
positions will not change during the update of each table.
So I've added creating a new temporary table (ret_tmp_table
) that contains rowids of tables used in RETURNING item
list (columns referenced in RETURNING).
The table is filled with one row of current rowids of tables per one call of multi_update::send_data()
. They're in
right positions for RETURNING because mysql_select
set their positions as they would be when select should send
rows to the client.
After the update tables are set to positions that are stored in ret_tmp_table
and send_data()
is called. That
outputs result set to the client
What's to be done:
- Solve an issue with InnoDB storage engine, that uses
PRIMARY_KEY
as rowid. So if we change it in update, later we can not set table in that position by stored rowid to send it to the client because the rowid is now changed. (Just replace old rowids with new inret_tmp_table
) - Now it's returning all potentially updated rows, no matter if it was updated to the same value or duplicate key error occured and we skipped updating of that row (when using IGNORE, for example). It's better not to output rows that were not actually updated.
- Write tests for multiple table variant of UPDATE .. RETURNING
Links
note: last GSoC 2019 commit is Implemented UPDATE .. RETURNING for multiple tables
GitHub compare
GitHub branch
Proposal
Jira task for single-table UPDATE .. RETURNING
Jira task for multiple tables UPDATE .. RETURNING