KNOWLEDGE BASE

"ERROR: AnalysisException: ORDER BY expression not produced by aggregation output" when Connected to Cloudera Impala


Published: 23 Oct 2017
Last Modified Date: 13 Jun 2019

Issue

When using a calculated field used as a filter for a view with a Cloudera Impala data source, the following error occurs: 

ERROR: AnalysisException: ORDER BY expression not produced by aggregation output (missing from GROUP BY clause?)

Environment

  • Tableau Desktop
  • Cloudera Hadoop Impala 2.9 (CDH 5.12)

Resolution

Please try the following workarounds:

Option 1

If the calculation uses LEFT or CONTAINS, use FIND instead.

For example, instead of:
ELSEIF LEFT([Level3 Desc],3) = 'SE ' THEN 'State Example' 

Use: 
ELSEIF FIND([Level3 Desc],'SE ') = 1 THEN 'State Example' 

Option 2

Add the setting "SET ENABLE_EXPR_REWRITES=0" as an initial SQL command:
  1. Open the affected workbook in Tableau Desktop.
  2. Select the Data Source tab.
  3. Right-click the Impala connection on the left panel and select Initial SQL...
  4. In the Initial SQL dialog box, enter: SET ENABLE_EXPR_REWRITES=0
  5. Click OK.

Cause

This error may be caused by an Impala bug affecting Impala 2.9 (CDH 5.12).

Or,  the ‘mid’ function is used in one of the calculated fields, such fonction is not supported by Impala.
Did this article resolve the issue?