Update with result set implementation

[fengyi]

Oracle and PostgreSQL has the returning syntax for DML, which can return the result set immediately after the update, this feature can save network communications between client and mysql server, one SQL parse for the following SELECT statement.

MariaDB has now implementd the DELETE...RETURNING feature
http://falseisnotnull.wordpress.com/2013/11/16/mariadb-delete-returning/

and other RETURNINGs are in their plan:
https://mariadb.atlassian.net/browse/MDEV-5092

one year ago I have implemented this feature based on Percona server 5.5.18: combine UPDATE+SELECT into single statement to gain performance improvement.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

mysql> show create table tt\G
*************************** 1. row ***************************
       Table: tt
Create Table: CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
1 row in set (0.00 sec)

mysql> select * from tt;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 2 | 3 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 4 | 5 |
| 4 | 4 | 5 | 6 |
| 5 | 5 | 6 | 7 |
| 6 | 6 | 7 | 8 |
| 7 | 6 | 1 | 9 |
+----+------+------+------+
7 rows in set (0.00 sec)

mysql> select NOW() as time, a+5 as a5, b from update tt set a=7, b=b+7 where id=7;
+---------------------+------+------+
| time | a5 | b |
+---------------------+------+------+
| 2013-05-24 18:08:54 | 12 | 8 |
+---------------------+------+------+
1 row in set (0.01 sec)

mysql> select * from tt;
+----+------+------+------+
| id | a | b | c |
+----+------+------+------+
| 1 | 1 | 2 | 3 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 4 | 5 |
| 4 | 4 | 5 | 6 |
| 5 | 5 | 6 | 7 |
| 6 | 6 | 7 | 8 |
| 7 | 7 | 8 | 9 |
+----+------+------+------+
7 rows in set (0.00 sec)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

the syntax I added is not like Oracel/PostgreSQL's "UPDATE...RETURNING...", actually it is :

SELECT
  select_expr [, select_expr ...]
FROM UPDATE
[LOW_PRIORITY] [IGNORE]
[TARGET_AFFECT_ROW num]
  tbl_name
SET col_name1={expr1|DEFAULT} [, col_name2= {expr2|DEFAULT} ] ...
[WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]

the reason I implemented "the "SELECT ... FROM UPDATE..." is that returning result set from an update statement is incompatible with the mysql connector, which think statements that begin with DELETE/UPDATE/INSERT should be DML and should not return result set like SELECTs.

see the function checkForDml in StatementImpl::executeQuery

I will attached the patch, hope someone can review it for me.

Thanks!

[fengyi (fengyi)]

this patch is based on our MySQL branch(which is actually based on Percona Server 5.5.18)

[Valerii Kravchuk (valerii-kravchuk)]

Makes sense as a feature request/wishlist.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2445