Bug #106428 Unexpected aggregation result on lateral join
Submitted: 9 Feb 22:31 Modified: 10 Feb 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

[9 Feb 22:31] Leonardo Gasparini
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
[9 Feb 22:33] Leonardo Gasparini
DB and data to reproduce the problem

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

[9 Feb 22:33] Leonardo Gasparini
wrong query

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

[10 Feb 6:58] MySQL Verification Team
Hello Leonardo,

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

regards,
Umesh