Bug #114775 Outer join condition filtering bug when using scalar subquery
| Submitted: | 25 Apr 2024 6:35 | Modified: | 25 Apr 2024 6:45 |
|---|---|---|---|
| Reporter: | jae hui lee | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.35, 8.0.36 | OS: | Ubuntu (AWS RDS) |
| Assigned to: | CPU Architecture: | x86 |
[25 Apr 2024 6:45]
MySQL Verification Team
Hello jae hui lee, Thank you for the report and test case. regards, Umesh
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: 버전 : 8.0.28 버전까지는 문제없음. 8.0.31, 35, 36 문제 발생. outer join 처리시 하드코딩된 컬럼에 대하여 최종 조건절에서 필터링 처리시 outer join과 같은 레벨에서 스칼라 서브쿼리가 존재하면 하드코딩된 결과값이 null더라도 하드코딩된 값으로 처리됨 How to repeat: /* 1) 테스트 케이스 데이터 생성 */ drop table main ; drop table dummy ; drop table seq ; create table main (id varchar(10)); insert into main values ('a'); insert into main values ('b'); create table dummy (id varchar(10)); insert into dummy values ('c'); insert into dummy values ('d'); create table seq (id varchar(10)); insert into seq values ('e'); insert into seq values ('f'); select 'main' tab, id from main union all select 'dummy' tab, id from dummy union all select 'seq' tab, id from seq ; /* 2) 오류 케이스 쿼리 */ select main_id , max_val , dummy_ID from ( select main.Id as main_id , ( select id from seq where seq.id = main.id ) as max_val -- 해당 구문 제외하면 정상 , dummy.ID as dummy_ID from main left outer join (select 'c' as ID from dummy ) dummy on dummy.ID = main.id where main.Id in ('a','b') ) test WHERE dummy_ID = 'c' ; --> 인라인 뷰(dummy)에서 하드코딩된 ID값으로 조회됨. 실제 결과는 null. 해당 조건절 인라인뷰 풀어서 처리하면 정상