About work done on implementing UPDATE .. RETURNING in MariaDB during GSoC 2019

Work product.md

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 to mysql_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 in ret_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