GSoC 2019 with MariaDB
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.
- Extended parser to parse RETURNING option in UPDATE clause.
- Maked necessary preparations, passed
mysql_updatefunction 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
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
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
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_KEYas 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 in
- 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
note: last GSoC 2019 commit is Implemented UPDATE .. RETURNING for multiple tables
Jira task for single-table UPDATE .. RETURNING
Jira task for multiple tables UPDATE .. RETURNING