[jira] [Commented] (DERBY-5313) Assert failure with CASE expression in GROUP BY clause

["Mamta A. Satoor (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-5313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13468603#comment-13468603 ] 

Mamta A. Satoor commented on DERBY-5313:
----------------------------------------

I debugged this jira further to see why following query will work 
  select  x1+x2
    from t1 join t2 on x1=x2 
    group by x1, x2; 
but following query won't work
  select  1
    from t1 join t2 on x1=x2 
    group by x1+x2; 
Both of the queries above use the same binary arithmetic expression, one uses it in select clause and other one uses it in group by clause.

What I found is that both the queries go through the same code during the bind time. But during optimization, while flattening the join node, in sane mode we check if group by columns are all instances of ColumnReference(this check happens in GroupByList.remapColumnReferencesToExpression code) and throw an exception if any of the group by column is found to be not an instance of ColumnReference.

As mentioned in the earlier comments in this jira, this behavior has been in the code for many releases.


                
> Assert failure with CASE expression in GROUP BY clause
> ------------------------------------------------------
>
>                 Key: DERBY-5313
>                 URL: https://issues.apache.org/jira/browse/DERBY-5313
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2
>            Reporter: Knut Anders Hatlen
>              Labels: derby_triage10_9
>
> I see the following assert failure with debug jars on 10.5.3.0 and later (doesn't fail on 10.5.1.1 and earlier):
> ij> create table t(x int);
> 0 rows inserted/updated/deleted
> ij> insert into t values 1;
> 1 row inserted/updated/deleted
> ij> select case when a=1 then 1 else 2 end
>   from t t1(a) join t t2(b) on a=b
>   group by case when a=1 then 1 else 2 end;
> ERROR XJ001: Java exception: 'ASSERT FAILED retVN expected to be instanceof ColumnReference, not org.apache.derby.impl.sql.compile.ConditionalNode: org.apache.derby.shared.common.sanity.AssertFailure'.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira