[MDEV-10923] Subquery cannot see a CTE table Created: 2016-09-29  Updated: 2016-10-11  Resolved: 2016-10-11

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.2
Fix Version/s: 10.2.3

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: None


 Description   

I have a query that

  • defines two CTEs
  • refers to the second CTE from a query and a subquery

I believe the query should work, but it fails with a "Table doesn't exist" error.

Testcase:

create table employees (
 
  name varchar(32),
 
  dept varchar(32),
 
  country varchar(8)
 
);
 
 
 
insert into employees 
values 
('Sergei Golubchik', 'Development', 'DE'),
 
('Claudio Nanni', 'Support', 'ES'),
 
('Sergei Petrunia', 'Development', 'RU');


with eng as 
(
 
   select * from employees
 
   where dept in ('Development','Support')
 
),
 
eu_eng  as 
(
 
  select * from eng where country IN ('DE','ES','RU')
 
)
 
select * from eu_eng T1 
where 
  not exists (select 1 from eu_eng T2 
              where T2.country=T1.country
 
              and T2.name <> T1.name);
 
ERROR 1146 (42S02): Table 'j55.eng' doesn't exist



 Comments   
Comment by Sergei Petrunia [ 2016-09-29 ]

The testcase works in PostgreSQL, for example.

Comment by Sergei Petrunia [ 2016-09-29 ]

... and Oracle.

Also, I can see the problem is caused by this usage of eu_eng:

select * from eu_eng T1

If I replace "eu_eng" with "eng", it works.

(Hmm, got RU into eu while playing with the example.. didn't mean to make any statements here)

Comment by Igor Babaev [ 2016-10-11 ]

The fix for this bug was pushed into the 10.2 tree.

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