Bug #10995 Can not use function in HAVING clause

• Submitted: 31 May 2005 17:29
• Modified: 1 Jun 2005 17:13
• Reporter: Vladimir Loubenski
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.9, 4.1.12
• OS: Windows (Windows XP)
• CPU Architecture: Any

[31 May 2005 17:29] Vladimir Loubenski

Description:
Select statment:
SELECT  CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)
FROM  employee G_T0
GROUP BY CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)
HAVING CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)   = 'PeterHarvey'

return error message: Unknown column 'G_T0.LAST_NAME' in 'having clause' .

How to repeat:
1.Create table:
CREATE TABLE employee (EMPLOYEE_ID INTEGER NULL,
LAST_NAME VARCHAR(15) NULL,
FIRST_NAME VARCHAR(15) NULL )
2.Run the select  statment.

[31 May 2005 23:38] Hartmut Holzgraefe

From our manual:
"Before MySQL 5.0.2, a HAVING clause can refer to any column or alias named in a select_expr in the SELECT list or in outer subqueries, and to aggregate functions. Standard SQL requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions."

CONCAT() is not an aggregate functions and the columns used in the HAVING clause are not result columns.

Rewriting the statement as this works:

SELECT  CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME) AS name
FROM  employee G_T0
GROUP BY CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)
HAVING name  = 'PeterHarvey'

or even shorter:

SELECT  CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME) AS name
FROM  employee G_T0
GROUP BY name
HAVING name  = 'PeterHarvey'

quoting the result column name instead of using an alias works too:

SELECT  CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)
FROM  employee G_T0
GROUP BY CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)
HAVING `CONCAT(G_T0.LAST_NAME,G_T0.FIRST_NAME)`   = 'PeterHarvey'

but is not portable

I can only guess what the other databases are doing, maybe
they automaticly optimize away the attitional CONCAT calls
and use an alias internally insted as in my 2nd example,
maybe they automaticly interprete non-aggregate function calls
as column names like in my quoting example (#3).

But anyway, what mysql does here is conforming to the standard
so it is not a bug

[1 Jun 2005 16:33] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25471

[1 Jun 2005 17:13] Sergei Golubchik

never mind. it was a fix for bug#10932...