maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

Hi Sergei,

Thanks for your response. Shouldn't this work?

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;

Or does that count in the definition of a multi-table delete? (I thought not). 

Cheers,

Rhys

-----Original Message-----
From: Sergei Golubchik [mailto:serg@xxxxxxxxxxx] 
Sent: Tuesday, July 26, 2016 5:38 PM
To: Campbell Rhys, INI-ON-FIT-TSO-QPM <Rhys.Campbell@xxxxxxxxxxxx>
Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-discuss] Finicky syntax with RETURNING?

Hi, Rhys.Campbell!

On Jul 25, Rhys.Campbell@xxxxxxxxxxxx wrote:
> 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
... 
> 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
...

The first query is, internally "single-table DELETE", the second is "multi-table DELETE".

I've reported different plans as a bug https://jira.mariadb.org/browse/MDEV-10447

DELETE ... RETURNING does not support multi-table delete, it's documented here: https://mariadb.com/kb/en/mariadb/delete/

I've created a task to fix it. https://jira.mariadb.org/browse/MDEV-10448

Regards,
Sergei
Chief Architect MariaDB
and security@xxxxxxxxxxx