Hello All,

I’m having a bit of an issue with the RETURNING feature of 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