Hello All,

I'm having a bit of an issue with the RETURNING feature of DELETEhttps://mariadb.com/kb/en/mariadb/delete/. This was introduced in MariaDB 10.0.5. Specifically there seems to be an issue when using aliases with the RETURNING keyword.

It involves a query like this. A user came to me with queries of this form that were running slowly. Here is the explain plan...

EXPLAIN DELETE from t1 where id in (select id from t1 where acc_id = 9999) \G

*************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4675 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t2 type: unique_subquery possible_keys: XXXXXXXXXX key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using where 2 rows in set (0.00 sec)

You can see it's being forced to run a table scan and run the subquery for each row. This was fixed by properly aliasing the query...

EXPLAIN EXTENDED DELETE uc from t1 AS uc where uc. id in (select p. id from t2 p where p.acc_id = 9999) \G

*************************** 1. row *************************** id: 1 select_type: PRIMARY table: p type: ref possible_keys: XXXXXXXXXXXXXXXXXXXXXXXX key: uk_XXXXXXXXXXXXXXXXXXXX key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 1 select_type: PRIMARY table: uc type: ref possible_keys: XXXXXXXXXXXXXXXXXXX key: XXXXXXXXXXXXXX key_len: 4 ref: XXXXXXXXXXXXXX rows: 1 filtered: 100.00 Extra: 2 rows in set (0.00 sec)

There now seems to be an issue when attempting to use the RETURNING keyword with this aliases version of the query. I have tried numerous versions of this query...

EXPLAIN DELETE uc FROM t1 AS uc WHERE uc.id IN (SELECT p.id FROM t2 AS p WHERE p.account_id = 9999) RETURNING uc.some_id;

EXPLAIN DELETE uc FROM t1 uc INNER JOIN t2 p ON uc.id = p.id WHERE p.acc_id = 9999 RETURNING some_id;

All of these produce a SQL syntax error. I've tried a range of ways of writing this, using different joins, subqueries, with and without the AS keyword. The issues seems to be with aliases.

The only version I can seem to get to work is this (note the removal of the aliases)...

EXPLAIN DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE acc_id = 9999) RETURNING some_id;

But then that creates the slow table scan problem again.

Anyone have any experience of this?

Cheers,

Rhys