[MDEV-26470] "No database selected" when using CTE in a subquery of DELETE statement Created: 2021-08-24  Updated: 2021-11-29  Resolved: 2021-11-23

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Delete, Optimizer - CTE
Affects Version/s: 10.6.4, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.42, 10.3.33, 10.4.23, 10.5.14, 10.6.6, 10.7.2

Type: Bug Priority: Major
Reporter: Lukas Eder Assignee: Igor Babaev
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
Relates
relates to MDEV-26469 "No database selected" error reported... Confirmed

 Description   

The problem can be reproduced as follows

create database x;
 
use x;
 
 
 
create table t (i int);
 
insert into t values (1);
 
 
 
 
 
delete from x.t
 
where x.t.i in (
 
  with u (i) as (
 
    select 1
 
  )
 
  select i
 
  from u
 
);

There is an error in the DELETE statement:

SQL Error [1046] [3D000]: (conn:77) No database selected

Using the syntax in a SELECT statement works as expected:

select *
 
from x.t
 
where x.t.i in (
 
  with u (i) as (
 
    select 1
 
  )
 
  select i
 
  from u
 
);

And so does replacing the CTE by a derived table:

delete from x.t
 
where x.t.i in (
 
  select i
 
  from (select 3 i) u
 
);



 Comments   
Comment by Oleksandr Byelkin [ 2021-11-22 ]

OK to push

Comment by Igor Babaev [ 2021-11-23 ]

A fix for this bug was pushed into 10.2. It must be applied to all higher version.

Generated at Thu Feb 08 09:45:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.