Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL)
-
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
|
Attachments
Activity
... 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)
The testcase works in PostgreSQL, for example.