Mup0c/Work product.md

## Work product.md

      
        

            Raw
  

        
        
          
            
          
          
            
              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