[IMPALA-6322] Group by expression fails when expression includes a CAST

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: Impala 2.9.0
• Fix Version/s: Impala 3.0
• Component/s: Frontend
• Epic Color: ghx-label-2
• Assignee: Zoltán Borók-Nagy
• Reporter: N Campbell
• Created: 14/Dec/17 15:22
• Updated: 26/Jan/18 13:32
• Resolved: 26/Jan/18 13:32

Description

Impala 2.5 thru 2.9 will fail to execute a Group by when it includes an expression which also includes a includes CAST.

Fails
SELECT
`sno` AS `SNO`,
upper( cast(`pno` as varchar(32)) ) AS `PNO`
FROM
`cert`.`tsupply`
GROUP BY
`sno`,
upper ( cast(`pno` as varchar(32)) )

Error: [Simba][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: select list expression not produced by aggregation output (missing from GROUP BY clause?): upper(CAST(pno AS VARCHAR(32)))
), Query: SELECT
`sno` AS `SNO`,
upper( cast(`pno` as varchar(32)) ) AS `PNO`
FROM
`cert`.`tsupply`
GROUP BY
`sno`,
upper ( cast(`pno` as varchar(32)) ).
SQLState: HY000
ErrorCode: 500051

Works

SELECT
`sno` AS `SNO`,
upper(pno) AS `PNO`
FROM
`cert`.`tsupply`
GROUP BY
`sno`,
upper(pno)

SELECT distinct
`sno` AS `SNO`,
upper( cast(`pno` as varchar(32)) ) AS `PNO`
FROM
`cert`.`tsupply`

select `SNO`, `PNO` from (
SELECT
`sno` AS `SNO`,
upper( cast(`pno` as varchar(32)) ) AS `PNO`
FROM `cert`.`tsupply`
) T
GROUP BY
`SNO`,
`PNO`

Issue Links

• relates to: IMPALA-5191 Behavior of GROUP BY, HAVING, ORDER BY with column aliases should be more standard conforming