[CALCITE-516] GROUP BY on a CASE expression containing IN predicate fails

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.9.1-incubating
• Fix Version/s: 1.0.0-incubating
• Labels: newbie
• Assignee: Julian Hyde
• Reporter: Aman Sinha
• Created: 10/Dec/14 07:58
• Updated: 13/Mar/15 23:24
• Resolved: 06/Jan/15 02:25

Description

The following query which does a Group-By on a CASE expression that contains IN predicate fails. If I replace the IN with an equality or <, > the query succeeds. I am not on latest Calcite master but I suspect the same problem exists there.

select (case when emp.empno in (3) then 0 else 1 end) 
  from emp 
group by (case when emp.empno in (3) then 0 else 1 end);

java.lang.AssertionError: Internal error: while converting CASE WHEN `EMP`.`EMPNO` IN (3) THEN 0 ELSE 1 END
	at org.eigenbase.util.Util.newInternal(Util.java:750)
	at org.eigenbase.sql2rel.ReflectiveConvertletTable$1.convertCall(ReflectiveConvertletTable.java:93)
	at org.eigenbase.sql2rel.SqlNodeToRexConverterImpl.convertCall(SqlNodeToRexConverterImpl.java:52)
	at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:4093)
	at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.visit(SqlToRelConverter.java:1)
	at org.eigenbase.sql.SqlCall.accept(SqlCall.java:125)
	at org.eigenbase.sql2rel.SqlToRelConverter$Blackboard.convertExpression(SqlToRelConverter.java:3988)
	at org.eigenbase.sql2rel.SqlToRelConverter$AggConverter.addGroupExpr(SqlToRelConverter.java:4314)
	at org.eigenbase.sql2rel.SqlToRelConverter.createAggImpl(SqlToRelConverter.java:2240)
	at org.eigenbase.sql2rel.SqlToRelConverter.convertAgg(SqlToRelConverter.java:2191)
	at org.eigenbase.sql2rel.SqlToRelConverter.convertSelectImpl(SqlToRelConverter.java:514)
	at org.eigenbase.sql2rel.SqlToRelConverter.convertSelect(SqlToRelConverter.java:474)

Issue Links

• is duplicated by: CALCITE-614 IN clause in CASE in GROUP BY gives AssertionError
• relates to: CALCITE-551 Sub-query inside aggregate function