MySQL Bugs: #107393: A Tree table use left join and some condition cannot query result

Bug #107393	A Tree table use left join and some condition cannot query result
Submitted:	25 May 2022 6:04	Modified:	25 May 2022 12:05
Reporter:	ss sssd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.35，5.7.32-log	OS:	Windows (Windows 10 专业版)
Assigned to:		CPU Architecture:	x86 (Intel(R) Core(TM) i5-10210U CPU @ 1.60GHz 2.11 GHz)
Tags:	left join

Description:
1. i have been created an tree table (id, pid ,level,deleted_flat...etc), when i try to query current id and if have child。then I want to use left join to solve this problem.but I have no problem querying level 1-4, but the data is not available at level 5, and I can confirm that the data is filtered by the filter criteria.

SQL :SELECT
	t1.id,
	t1.parent_id,
	t1.LEVEL,
	t1.block_grade,
	t1.CODE,
	t1.NAME,
	t1.area,
	t1.color,
	t1.anchor_point,
	t1.border_number,
	t1.director_id,
	t1.sort,
	count(t2.id)
FROM
	luban_block t1
	LEFT JOIN luban_block t2 ON t1.id = t2.parent_id 
WHERE
	t1.tenant_id = 1484363138399756297 
	AND t1.LEVEL = 5 
	AND t1.parent_id = '1525832039616483328' 
	AND t1.deleted_flag = 0 
 	and t2.deleted_flag = 0
GROUP BY
	t1.id,
	t1.parent_id,
	t1.LEVEL,
	t1.block_grade,
	t1.CODE,
	t1.NAME,
	t1.area,
	t1.color,
	t1.anchor_point,
	t1.border_number,
	t1.director_id,
	t1.sort 
ORDER BY
	t1.sort;

but when i delete the `t2.deleted_flag = 0` then i get the result . but the deleted_flag is actrually `0`. i have no idea with this. please help check.

How to repeat:
1. TABLE AND DATAS:
files

2. execute:
SELECT
	t1.id,
	t1.parent_id,
	t1.LEVEL,
	t1.block_grade,
	t1.CODE,
	t1.NAME,
	t1.area,
	t1.color,
	t1.anchor_point,
	t1.border_number,
	t1.director_id,
	t1.sort,
	count(t2.id)
FROM
	luban_block t1
	LEFT JOIN luban_block t2 ON t1.id = t2.parent_id 
WHERE
	t1.tenant_id = 1484363138399756297 
	AND t1.LEVEL = 5 
	AND t1.parent_id = '1525832039616483328' 
	AND t1.deleted_flag = 0 
-- 	and t2.deleted_flag = 0
GROUP BY
	t1.id,
	t1.parent_id,
	t1.LEVEL,
	t1.block_grade,
	t1.CODE,
	t1.NAME,
	t1.area,
	t1.color,
	t1.anchor_point,
	t1.border_number,
	t1.director_id,
	t1.sort 
ORDER BY
	t1.sort;
3. level 1-4 can query data,but level 5 not work. but if remove `and t2.deleted_flag = 0` it works.

Suggested fix:
-

test Structure and data files

Attachment: luban_block.sql (application/octet-stream, text), 175.50 KiB.

solved

Hi Mr. sssd,

Thank you for your bug report.

If we have understood your report, you have solved a problem with your query.

If that is a case, there is no need for you to confirm it.