[teiid-issues] [JBoss JIRA] (TEIID-3748) Impala translator - SELECT and HAVING statements are translating differently for Case statements

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13117909#comment-13117909 ] 

Steven Hawkins commented on TEIID-3748:
---------------------------------------

To reproduce I'm running a similar query against a simple source model and the impala translator, and not seeing the else being added nor the cast.  Ideally I'll at least need the query plan (showing the pushdown query) which would clarify if the translator or engine is adding the else.  The query planning debug log would clarify if this is happening in the engine.  And just to make sure, it would be good to see the incoming query to the server to make sure there isn't something unexpected from you client side.

> Impala translator - SELECT and HAVING statements are translating differently for Case statements
> ------------------------------------------------------------------------------------------------
>
>                 Key: TEIID-3748
>                 URL: https://issues.jboss.org/browse/TEIID-3748
>             Project: Teiid
>          Issue Type: Bug
>          Components: JDBC Connector
>    Affects Versions: 8.11.4
>         Environment: Ubuntu Trusty
>            Reporter: Don Krapohl
>            Assignee: Steven Hawkins
>              Labels: Impala_Translator, Translators
>
> Error from Impala-
> all DISTINCT aggregate functions need to have the same set of parameters as count(DISTINCT (CASE WHEN (secondcol >= 0) THEN 1 ELSE CAST(NULL AS STRING) END))
> deviating function: count(DISTINCT (CASE WHEN (secondcol >= 0) THEN 1 ELSE NULL END))
> Query:
> SELECT user_key, sum(firstcol),count(distinct case when secondcol >= 0 then 1 end) 
> FROM sometable 
> WHERE customer_key=6
> GROUP BY user_key 
> HAVING sum(firstcol)>100 
> 	AND count(distinct case when secondcol >= 0 then 1 end)=0
> 	
> Query explanation:
> For all users
> Add up values in the firstcol column (integer column)
> count distinct values in secondcol where secondcol value zero or more
> 	otherwise return null (output is string)
> Translated Teiid query:
> SELECT user_key, SUM(firstcol) as `EXPR_0`, COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' ELSE CAST(NULL AS STRING) END)) as `EXPR_1`
> FROM sometable 
> WHERE customer_key` = 6
> HAVING (EXPR_0 > 100) AND (COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' ELSE NULL END)) = 0))
> Note the difference between the select and having for EXPR_1:
> Select - THEN '1' ELSE CAST(NULL AS STRING) END
> Having - THEN '1' ELSE NULL END
> Impala doesn't accept that these are the same aggregate function.  Aliases aren't accepted in the HAVING.
> One further observation- if we swap the translation and write the statement in the select as 
> COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' *ELSE NULL END*))
> Teiid translates the SELECT to
> COUNT(DISTINCT (CASE WHEN (secondcol >= 0) THEN '1' *ELSE CAST(NULL AS STRING) END*))
> So it always makes these mismatched.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)