maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

Hello All,

I'm having a bit of an issue with the RETURNING feature of DELETE<https://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