MySQL Bugs: #105130: HAVING clause with sub queries on FROM clause fetches wrong results

Bug #105130	HAVING clause with sub queries on FROM clause fetches wrong results
Submitted:	5 Oct 2021 10:24	Modified:	7 Oct 2021 12:39
Reporter:	Simone Di Tullio	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have 2 queries that are logically identical but one of those is fetching a wrong result set.

In the attachments you can find
- the DB dump (dump-kxtest-202110051151.sql) with tables and data
- the query fetching the correct result set (working_query.sql)
- the query fetching the wrong result set (not_working_query.sql) which is logically identical to the query above (working_query.sql). This query joins sub selects which correspond to the tables, in fact they are SELECT statements without WHERE clause.

The wrong query (not_working_query.sql) 
- is fetching the correct result set on MySQL Server versions 5.x
- is fetching a wrong result set on MySQL Server versions 8.x

I've ran these tests on these Docker Images
https://hub.docker.com/_/mysql

How to repeat:
1. Create the DB using file dump dump-kxtest-202110051151.sql on a MySQL Server 8.x
2. Execute not_working_query.sql and check that the result set does not satisfy the having clause
3. (optional) Use working_query.sql to display the expected (correct) result set

Zip file containing SQL files mentioned in the bug report

Attachment: having_subqueries_issue.zip (application/x-zip-compressed, text), 56.21 KiB.

Hi Mr. Di Tullio,

Thank you for your bug report.

However, it does not look like a bug.

MySQL is following SQL standards regarding query composition. According to the standard, HAVING clause can refer only to the items in the GROUP BY clause, in the same query. Regardless if there are nested queries above or below that level.

Otherwise, the results are unpredictable. It might work or it might not work, but it is not a bug in either case.

Hence, rewrite your query and let us know if it works or not.

We are waiting on your feedback.

Hello,

the HAVING clause it is actually referring to the items in the GROUP BY clause, except quantityordered field which is aggreated through sum operator.
Maybe I didn't interpret the explaination correctly, but this behavior does look wrong.

I'll add this simpler example that can be executed on the sql dump file I've attached.

This query extracts the right results.
SELECT c.contactlastname,c.salesrepemployeenumber,o.customernumber,
       sum(od.quantityordered),max(od.priceeach)
FROM customers c
         join orders o on o.customernumber=c.customernumber
         join orderdetails od on od.ordernumber=o.ordernumber
group by c.contactlastname, c.salesrepemployeenumber,o.customernumber
having sum(od.quantityordered) between 1000 and 1500 or c.salesrepemployeenumber > 1700
order by 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC;

I get the query above and I replace the table refs on FROM clause with explicit selects. This query does not extract correct results.

SELECT c.contactlastname,c.salesrepemployeenumber,o.customernumber,
       sum(od.quantityordered),max(od.priceeach)
FROM (select * from customers) c
         join (select * from orders) o on o.customernumber=c.customernumber
         join (select * from orderdetails) od on od.ordernumber=o.ordernumber
group by c.contactlastname, c.salesrepemployeenumber,o.customernumber
having sum(od.quantityordered) between 1000 and 1500 or c.salesrepemployeenumber > 1700
order by 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC;

Hi Mr. Di Tullio,

HAVING should only contain conditions on names or aliases of the aggregated columns.

To make things even worse, your non-working query has an OR expression, which must work in the WHERE clause, but yields unpredictable results in the HAVING clause.

HAVING clause is not a replacement for the WHERE clause.

Not a bug.