KNOWLEDGE BASE

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


Published: 23 Oct 2017
Last Modified Date: 25 Jun 2018

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).
Did this article resolve the issue?