Google Summer of Code 2019: Add RETURNING to INSERT statement
Hi! My name is Rucha Deodhar. This year, I got my proposal selected for MariaDB Foundation. I spent the 12 weeks not only coding but also learning many new things.
This blog describes all the work that I accomplished during GSoC 2019.
Read on to find out!
Introduction
This task is about adding RETURNING clause to INSERT and REPLACE (which is a variant of INSERT) statement which returns the listed columns for all the rows that are inserted, or alternatively, the specified SELECT expression. Any SQL expressions which can be calculated can be used in the select expression for RETURNING clause. So virtual columns and alias, expressions which use various operators like bitwise, logical and arithmetic operators, functions like string function, date-time functions, numeric functions, control flow function, secondary functions and stored functions can be used. Along with this, statements which have subquery and prepared statements can also be used.
In addition to this, a system variable for feedback plugin is also added which keeps a count of the number of times INSERT…RETURNING and REPLACE…RETURNING feature is used.
Syntax
1. INSERT STATEMENT:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),…
[ ON DUPLICATE KEY UPDATE
col=expr [, col=expr] … ][RETURNING select_expr[, select_expr …]]
Or
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, …
[ ON DUPLICATE KEY UPDATE
col=expr[, col=expr] … ][RETURNING select_expr[, select_expr …]]
Or
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [PARTITION (partition_list)] [(col,…)]
SELECT …[ ON DUPLICATE KEY UPDATE
col=expr [, col=expr] … ][RETURNING select_expr[, select_expr …]]
2. REPLACE STATEMENT:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,…)]
{VALUES | VALUE} ({expr | DEFAULT},…),(…),… [RETURNING select_expr[, select_expr …]]
Or
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)]
SET col={expr | DEFAULT}, … [RETURNING select_expr[, select_expr …]]
Or
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [PARTITION (partition_list)] [(col,…)]
SELECT … [RETURNING select_expr[, select_expr …]]
Goals of the Project
One of the goals here is to make it library first and integratable into existing applications. It also eliminates the need for writing additional query. INSERT…RETURNING is already supported by PostgreSQL. If a query is more generic and can be used with any RDBMS, then naturally it is preferable over the other queries. Hence, adding this feature makes MariaDB more compatible with Postgresql and other RDBMS like Oracle RDBMS.
System variable for feedback plugin can be useful for someone who is looking to optimize things.
This feature is very useful when the INSERT statement will create data in addition to the one that is explicitly inserted, for example auto increment and default values. INSERT…RETURNING and REPLACE…RETURNING is useful as it eliminates the need to write an additional query. Example:
CREATE TABLE tb1(id1 INT PRIMARY KEY AUTO_INCREMENT, val1 VARCHAR(1));
INSERT INTO tb1(val1) VALUES (‘A’),(‘B’),(‘C’);
SELECT * from tb1 WHERE id1 IN (1,2,3);
The above INSERT and SELECT can be replaced with a single query:
INSERT INTO tb1(val1) VALUES (’A’),(‘B’),(‘C’) RETURNING *;
To replace a value and to return it in the same query, REPLACE…RETURNING can be used:
REPLACE INTO tb1(id1,val1) VALUES (3,’D’) RETURNING *;
Examples
Work Done During Google Summer of Code 2019
This section contains commits I made during GSoC and weekly status of the project. I used to write weekly reports on Sunday evening and send it on the mailing list every Monday.
Click on the links below to view Weekly Report of the project and commits made during coding period. Feel free to ask questions ☺
Click Here To View Weekly Report
Click Here To View Commits Made During The Coding Period
Learning Outcomes
This summer I learned a lot, to begin with being introduced to the great community of MariaDB and so much about open source contribution. I did run into problems but it improved my debugging skills and introduced me to many new git commands. I also learned that it is important to think about how the code can and will evolve in the future. Just getting things to work is okay up to a certain point but the code should be implemented in as decoupled fashion as possible. I developed skills like how to write better and clean code, better test cases, things to remember while writing documentation and other important skills to have to become a really good software developer. All in all, GSoC’19 was an amazing experience.
