MySQL Bugs: #103961: Error: "Unknown column 'foo' in 'having clause'" when using function in HAVING

Bug #103961	Error: "Unknown column 'foo' in 'having clause'" when using function in HAVING
Submitted:	9 Jun 2021 14:01	Modified:	26 Jun 2021 10:23
Reporter:	Laurents Meyer (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If using a function in a HAVING clause, e.g.:

SELECT YEAR(`Foo`)
/* ... */
GROUP BY YEAR(`Foo`)
HAVING YEAR(`Foo`) = 2021

the HAVING clause does not recognize the YEAR(`Foo`) expression.

If the GROUP BY clause keeps using the function, but the HAVING clause does not, the HAVING clause works.

If the SELECT clause uses an alias, the HAVING clause will work by just using the alias.

How to repeat:
Run the following SQL:

DROP DATABASE IF EXISTS `having_issue_20210609`;
CREATE DATABASE `having_issue_20210609`;
USE `having_issue_20210609`;

CREATE TABLE `IceCreams` (
  `IceCreamId` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `Name` longtext,
  `BestServedBefore` datetime NOT NULL
) ENGINE=InnoDB;

-- Works:
SELECT `i`.`BestServedBefore`, COUNT(*) AS `ProductCount`
FROM `IceCreams` AS `i`
GROUP BY `i`.`BestServedBefore`
HAVING YEAR(`i`.`BestServedBefore`) = 2021;

-- Works:
SELECT YEAR(`i`.`BestServedBefore`) AS `Year`, COUNT(*) AS `ProductCount`
FROM `IceCreams` AS `i`
GROUP BY YEAR(`i`.`BestServedBefore`)
HAVING `Year` = 2021;

-- Does not work:
SELECT YEAR(`i`.`BestServedBefore`), COUNT(*) AS `ProductCount`
FROM `IceCreams` AS `i`
GROUP BY YEAR(`i`.`BestServedBefore`)
HAVING YEAR(`i`.`BestServedBefore`) = 2021;

Suggested fix:
Addressing a GROUP BY expression from the HAVING statement seems to be SQL standard, regardless of whether it uses a function or not.

It should just work.

Hi Mr. Meyer,

Thank you for your bug report.

However, this is not a bug.

Our manual explains that, in essence, any expressions that you are aggregating by, but have been used with aliases, in order for HAVING to be able to filter aggregated results further.

Not a bug.

Thanks for clarifying. Please point me to the paragraph in the docs, so that I can verify this.

I ask this, because I did read the the https://dev.mysql.com/doc/refman/8.0/en/select.html before reporting this as a bug, and was unable to find anything that stated, that the alias has to be used.

The only remotely relevant information I found is the following:

---

A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and --> CAN <-- be used in GROUP BY, ORDER BY, or HAVING clauses.

[...]

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

If the HAVING clause refers to a column that is ambiguous, a warning occurs. In the following statement, col2 is ambiguous because it is used as both an alias and a column name:

[...]

Preference is given to standard SQL behavior, so if a HAVING column name is used both in GROUP BY and as an aliased column in the select column list, preference is given to the column in the GROUP BY column.

---

It basically states, that an alias CAN (optionally) be used, but the SQL standard behavior always takes precedence.

So unless I missed a passage in the docs somewhere (which is absolutely possible), this is a bug and needs to be (re) categorized (and opened) as such.

In addition, the following SQL (as already stated in the OP) does not work, but does not use any alias at all (for the YEAR(...) expression):

SELECT YEAR(`i`.`BestServedBefore`), COUNT(*) AS `ProductCount`
FROM `IceCreams` AS `i`
GROUP BY YEAR(`i`.`BestServedBefore`)
HAVING YEAR(`i`.`BestServedBefore`) = 2021;

So even if reusing an alias would be a requirement (which it might not be, see previous post), this would still not apply to this query.

So unless I am missing something, this bug needs to be reopened.

Hi,

Regarding your first comment, second paragraph is the relevant one. However, we shall communicate your objection to our documentation team.

Regarding your last comment, that query is not supposed to work either ......

Thanks!

Please also forward my objection to this being just a documentation issue. While it should be documented (which it is currently not), it should also just work (which the documentation indirectly states).

It should also be mentioned, that this works in every other major database system.
If it does not for MySQL, it should be a violation of the SQL standard.

I therefore consider this a serious issue after all.

BTW, this is only an issue if the having expression does not contain an aggregate function. Otherwise, it just works.

Still needs to be reopened as a bug.