[MDEV-19289] Unknown column error with UNION and INTERSECT Created: 2019-04-19  Updated: 2023-10-03  Resolved: 2023-09-25

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 10.3, 10.4
Fix Version/s: 10.4.32

Type: Bug Priority: Critical
Reporter: Sergei Petrunia Assignee: Oleksandr Byelkin
Resolution: Cannot Reproduce Votes: 0
Labels: None


 Description   

Reproducible on current 10.3 (323e6cd74ce76c7811835bed640a2934e1d77f1b) but not on 10.4

create table t10 (a int , b int);
 
insert into t10 values (1,1),(1,1);
 
create table t11 (a int , b int);
 
insert into t11 values (2,2),(2,2);
 
create table ten(a int);
 
insert into ten values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);


explain 
select * from ten A, 
 (select * from t10 union select * from t11 intersect 
  select * from t11) T 
where T.a=A.a;


ERROR 1054 (42S22): Unknown column 'T.a' in 'where clause'



 Comments   
Comment by Varun Gupta (Inactive) [ 2019-04-21 ]

Also affect 10.4

The test case is

create table t1(a int, b int);
 
insert into t1 values (1,1),(2,2),(3,3);
 
create table t2(a int, b int,c int);
 
insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5);
 
create table t3(a int, b int);
 
insert into t3(a,b) values (7,1),(8,2), (1,3);
 
explain
 
select * from ( (select t1.a from t1 order by b) union  (select t2.a from t2 order by c) intersect(select t3.a from t3 order by b))q;
 
where q.a = 1;
 
drop table t1,t2,t3;

Comment by Oleksandr Byelkin [ 2023-09-25 ]

I can not reproduce it on 10.4

Generated at Thu Feb 08 08:50:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.