MySQL Bugs: #113005: Aggregate function with inner join gives incorrect result

Bug #113005	Aggregate function with inner join gives incorrect result
Submitted:	8 Nov 2023 11:09	Modified:	10 Nov 2023 8:14
Reporter:	keerthika K	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.1	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	Aggregate Function, INNER JOIN

Description:
Consider a query with two tables Table 1 and Table 2. Table 1 has one row and Table 2 has 0 rows. 

SELECT Table1.CurrencyID, Table1.OutStanding, (Table1.OutStanding + IFNULL(SUM(Table2.ARUnUsedCredit), (0))) AS UnusedCreditAmount FROM Table1 INNER JOIN Table2 ON Table1.CustomerID=Table2.CustomerID AND Table1.CurrencyID=Table2.CurrencyID WHERE ((Table1.CustomerID = 4154000000041029) AND (((Table1.CustomerID >= 4154000000000000) AND (Table1.CustomerID <= 4154999999999999)) OR ((Table1.CustomerID >= 0) AND (Table1.CustomerID <= 999999999999))))

Here inner join should not give any result. But we are getting the value of Table1.OutStanding in UnusedCreditAmount

How to repeat:
Create two table with FK index. Add one row in table 1 and zero rows in table 2. 

Suggested fix:
Inner join on empty table should not give any result

Thank you for taking the time to report a problem.  Unfortunately you are not using a current version of the product you reported a problem with -- the problem might already be fixed. Please download a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions, please change the version on this bug report to the version you tested and change the status back to "Open".  Again, thank you for your continued support of MySQL.

Version 5.7 is no longer supported.

If you can repeat the problem in 8.0 or 8.2, please file a new bug report, with fully repeatable test case, not just with a description.

Unsupported.

This bug is fixed in Mysql 8.. I want to reason for this bug in 5.7.18 and in which version it was fixed. Just want to know about the bug details

Please share some details about this bug and in which version it was fixed

can u please share the details in which version it was fixed

Seems it is fixed in this version but could not find anything related to this in release notes

Can u reply

Please check