Bug #105526 | Query result is incompatible with the SQL standard | ||
---|---|---|---|
Submitted: | 11 Nov 2021 9:00 | Modified: | 15 Nov 2021 15:14 |
Reporter: | Yukun Liang | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 Nov 2021 9:00]
Yukun Liang
[11 Nov 2021 13:44]
MySQL Verification Team
Hi Mr. Liang, Thank you for your bug report. However, this is not a bug. First of all, your quoting of the SQL standard is correct, but you did not apply fully SQL standards to your examples. What you have , in both cases, is HAVING clause that does not conform to SQL standard, since it is referencing an expression that is not one of the aggregating columns / expressions. Next, one of the queries has a nested query, while the other one does not. Last, but not least, applying 'DISTINCT` reserved word on the query, changes the order of the evaluation. It also slows down, totally unnecessary, the entire query since the aggregated queries are, by definition and as per SQL standard, distinct queries. Not a bug.
[11 Nov 2021 21:08]
Roy Lyseng
The reporter's interpretation of the SQL standard is correct. The HAVING clause contains an aggregation of columns from the source tables, which is allowed. The bug verifies on 5.7 up to 8.0.27.
[15 Nov 2021 15:14]
MySQL Verification Team
Hi Mr. Liang, We had some long internal discussions, after which we concluded that your report should be verified as a bug. There are still some discussions on how to fix this problem, but there is no doubt that this is a bug. Verified as reported.