MySQL Bugs: #106428: Unexpected aggregation result on lateral join

Bug #106428	Unexpected aggregation result on lateral join
Submitted:	9 Feb 2022 22:31	Modified:	10 Feb 2022 6:58
Reporter:	Leonardo Gasparini	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	regression

Description:
After updating the engine from version 8.0.19 to 8.0.28, I'm getting a wrong result from a query even if data is still coherent.

How to repeat:
Run this query:

select c.`id`,
	`notPendingOrders`.`quantity`
from `Customer` c
	left join lateral (
		select count(o.id) as `quantity`
		from `Order` o
			join `OrderDetail` od on od.`idOrder` = o.`id`
            join `OrderDetailStatus` ods on ods.`id` = od.`idOrderDetailStatus`
		where o.`idCustomer` = c.`id` 
			and ods.`name` != 'PENDING'
	) `notPendingOrders` on true
where coalesce(`notPendingOrders`.`quantity`, 0) > 0
;

and you should get:

id | quantity
 1 | 1
 2 | 1

BUT you get:

id | quantity
 1 | 1
 2 | 1
 3 | 2 <--- ???

which is not coherent with the stored data

select c.id as customerId,
	ods.name as orderStatus
		from `Order` o
			join `Customer` c on c.id = o.`idCustomer`
			join `OrderDetail` od on od.`idOrder` = o.`id`
            join `OrderDetailStatus` ods on ods.`id` = od.`idOrderDetailStatus`
		where o.`idCustomer` = c.`id` 
;

customerId | orderStatus
         1 | PENDING
         1 | COMPLETED
         2 | REFUNDED
         2 | PENDING
         2 | PENDING
         3 | PENDING 
         3 | PENDING

DB and data to reproduce the problem

Attachment: bug-report-db.sql (application/octet-stream, text), 4.76 KiB.

wrong query

Attachment: bug-report-query.sql (application/octet-stream, text), 415 bytes.

Hello Leonardo,

Thank you for the report and test case.
Verified as described.

regards,
Umesh