[JBoss JIRA] (TEIID-3748) Impala translator - SELECT and HAVING statements are translating differently for Case statements
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3748?page=com.atlassian.jira.plugin... ]
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)
9 years, 3 months
[JBoss JIRA] (TEIID-3748) Impala translator - SELECT and HAVING statements are translating differently for Case statements
by Don Krapohl (JIRA)
[ https://issues.jboss.org/browse/TEIID-3748?page=com.atlassian.jira.plugin... ]
Don Krapohl commented on TEIID-3748:
------------------------------------
Agree on the cast(null as string). We're not doing that. We're doing:
count(distinct case when secondcol >= 0 then 1 end)
Since there's no ELSE in the statement the translator adds one (the cast(null as string)).
Will get logs and upload.
> 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)
9 years, 3 months
[JBoss JIRA] (TEIID-3751) NPE in GroupingNode on running a query with CTEs and GROUP BY clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3751?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-3751.
-----------------------------------
Resolution: Done
The initial issue here is that we have a missing validation. sum(p.b) should not be allowed to reference p.b as the windowing is computed after the aggregation and p.b does not appear in the group by clause - which is essentially the error from the full pushdown.
I also addressed that we were explicitly not allowing nested aggregates in window functions, for example sum(max(p.b)) - which should be allowable.
> NPE in GroupingNode on running a query with CTEs and GROUP BY clause
> --------------------------------------------------------------------
>
> Key: TEIID-3751
> URL: https://issues.jboss.org/browse/TEIID-3751
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> Running the following query:
> {code:sql}
> WITH x as (
> SELECT a FROM my.test_a
> )
> SELECT
> p.a,
> SUM(p.b) OVER (partition by p.a) as y
> FROM
> my.test_a p JOIN x ON x.a = p.a
> GROUP BY p.a
> {code}
> I get two different exceptions, depending if the query is fully pushed down (like in PostgreSQL) or not (like in MySQL).
> In the first case (not fully pushed down - MySQL), this NPE is thrown:
> {code:sql}
> 16:23:22,638 ERROR [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue5) NOliW/x7idDC TEIID30019 Unexpected exception for request NOliW/x7idDC.3: java.lang.NullPointerException
> at org.teiid.query.processor.relational.GroupingNode.initialize(GroupingNode.java:218) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:96) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.initialize(RelationalPlan.java:91) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:226) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:138) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:462) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:344) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:271) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> {code}
> If the query is pushed down (PostgreSQL), the following error comes directly from the underlying DBMS:
> {code:sql}
> 16:25:26,306 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue7) NOliW/x7idDC Connector worker process failed for atomic-request=NOliW/x7idDC.4.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH x (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_0."a" AS c_0, SUM(g_0."b") OVER (PARTITION BY g_0."a") AS c_1 FROM "public"."test_a" AS g_0, x AS g_1 WHERE g_1.a = g_0."a" GROUP BY g_0."a" LIMIT 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column "g_0.b" must appear in the GROUP BY clause or be used in an aggregate function
> Position: 89
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3751) NPE in GroupingNode on running a query with CTEs and GROUP BY clause
by Salvatore R (JIRA)
[ https://issues.jboss.org/browse/TEIID-3751?page=com.atlassian.jira.plugin... ]
Salvatore R commented on TEIID-3751:
------------------------------------
Thanks!
> NPE in GroupingNode on running a query with CTEs and GROUP BY clause
> --------------------------------------------------------------------
>
> Key: TEIID-3751
> URL: https://issues.jboss.org/browse/TEIID-3751
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> Running the following query:
> {code:sql}
> WITH x as (
> SELECT a FROM my.test_a
> )
> SELECT
> p.a,
> SUM(p.b) OVER (partition by p.a) as y
> FROM
> my.test_a p JOIN x ON x.a = p.a
> GROUP BY p.a
> {code}
> I get two different exceptions, depending if the query is fully pushed down (like in PostgreSQL) or not (like in MySQL).
> In the first case (not fully pushed down - MySQL), this NPE is thrown:
> {code:sql}
> 16:23:22,638 ERROR [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue5) NOliW/x7idDC TEIID30019 Unexpected exception for request NOliW/x7idDC.3: java.lang.NullPointerException
> at org.teiid.query.processor.relational.GroupingNode.initialize(GroupingNode.java:218) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:96) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.initialize(RelationalPlan.java:91) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:226) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:138) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:462) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:344) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:271) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> {code}
> If the query is pushed down (PostgreSQL), the following error comes directly from the underlying DBMS:
> {code:sql}
> 16:25:26,306 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue7) NOliW/x7idDC Connector worker process failed for atomic-request=NOliW/x7idDC.4.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH x (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_0."a" AS c_0, SUM(g_0."b") OVER (PARTITION BY g_0."a") AS c_1 FROM "public"."test_a" AS g_0, x AS g_1 WHERE g_1.a = g_0."a" GROUP BY g_0."a" LIMIT 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column "g_0.b" must appear in the GROUP BY clause or be used in an aggregate function
> Position: 89
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3751) NPE in GroupingNode on running a query with CTEs and GROUP BY clause
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3751?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-3751:
----------------------------------
Component/s: Query Engine
Fix Version/s: 8.12
Yes, I can reproduce this. The fix should get into 8.12 Final.
> NPE in GroupingNode on running a query with CTEs and GROUP BY clause
> --------------------------------------------------------------------
>
> Key: TEIID-3751
> URL: https://issues.jboss.org/browse/TEIID-3751
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Reporter: Salvatore R
> Assignee: Steven Hawkins
> Fix For: 8.12
>
>
> Running the following query:
> {code:sql}
> WITH x as (
> SELECT a FROM my.test_a
> )
> SELECT
> p.a,
> SUM(p.b) OVER (partition by p.a) as y
> FROM
> my.test_a p JOIN x ON x.a = p.a
> GROUP BY p.a
> {code}
> I get two different exceptions, depending if the query is fully pushed down (like in PostgreSQL) or not (like in MySQL).
> In the first case (not fully pushed down - MySQL), this NPE is thrown:
> {code:sql}
> 16:23:22,638 ERROR [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue5) NOliW/x7idDC TEIID30019 Unexpected exception for request NOliW/x7idDC.3: java.lang.NullPointerException
> at org.teiid.query.processor.relational.GroupingNode.initialize(GroupingNode.java:218) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:96) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.relational.RelationalPlan.initialize(RelationalPlan.java:91) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:226) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:138) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:462) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:344) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:271) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> {code}
> If the query is pushed down (PostgreSQL), the following error comes directly from the underlying DBMS:
> {code:sql}
> 16:25:26,306 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue7) NOliW/x7idDC Connector worker process failed for atomic-request=NOliW/x7idDC.4.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH x (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_0."a" AS c_0, SUM(g_0."b") OVER (PARTITION BY g_0."a") AS c_1 FROM "public"."test_a" AS g_0, x AS g_1 WHERE g_1.a = g_0."a" GROUP BY g_0."a" LIMIT 100]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
> at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy47.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
> at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
> Caused by: org.postgresql.util.PSQLException: ERROR: column "g_0.b" must appear in the GROUP BY clause or be used in an aggregate function
> Position: 89
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
> at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
> at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
> ... 18 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3751) NPE in GroupingNode on running a query with CTEs and GROUP BY clause
by Salvatore R (JIRA)
Salvatore R created TEIID-3751:
----------------------------------
Summary: NPE in GroupingNode on running a query with CTEs and GROUP BY clause
Key: TEIID-3751
URL: https://issues.jboss.org/browse/TEIID-3751
Project: Teiid
Issue Type: Bug
Reporter: Salvatore R
Assignee: Steven Hawkins
Running the following query:
{code:sql}
WITH x as (
SELECT a FROM my.test_a
)
SELECT
p.a,
SUM(p.b) OVER (partition by p.a) as y
FROM
my.test_a p JOIN x ON x.a = p.a
GROUP BY p.a
{code}
I get two different exceptions, depending if the query is fully pushed down (like in PostgreSQL) or not (like in MySQL).
In the first case (not fully pushed down - MySQL), this NPE is thrown:
{code:sql}
16:23:22,638 ERROR [org.teiid.PROCESSOR] (Worker3_QueryProcessorQueue5) NOliW/x7idDC TEIID30019 Unexpected exception for request NOliW/x7idDC.3: java.lang.NullPointerException
at org.teiid.query.processor.relational.GroupingNode.initialize(GroupingNode.java:218) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:96) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.relational.RelationalPlan.connectExternal(RelationalPlan.java:102) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.relational.RelationalPlan.initialize(RelationalPlan.java:91) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.QueryProcessor.init(QueryProcessor.java:226) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:138) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:462) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:344) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:271) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.0.CR1.jar:8.12.0.CR1]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
{code}
If the query is pushed down (PostgreSQL), the following error comes directly from the underlying DBMS:
{code:sql}
16:25:26,306 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue7) NOliW/x7idDC Connector worker process failed for atomic-request=NOliW/x7idDC.4.2.1: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH x (a) AS (SELECT g_0."a" FROM "public"."test_a" AS g_0) SELECT g_0."a" AS c_0, SUM(g_0."b") OVER (PARTITION BY g_0."a") AS c_1 FROM "public"."test_a" AS g_0, x AS g_1 WHERE g_1.a = g_0."a" GROUP BY g_0."a" LIMIT 100]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:349)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_67]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_67]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_67]
at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_67]
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
at com.sun.proxy.$Proxy47.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_67]
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_67]
at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_67]
Caused by: org.postgresql.util.PSQLException: ERROR: column "g_0.b" must appear in the GROUP BY clause or be used in an aggregate function
Position: 89
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.0.CR1.jar:8.12.0.CR1]
... 18 more
{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months
[JBoss JIRA] (TEIID-3750) Allow the parsing of an expression
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-3750:
-------------------------------------
Summary: Allow the parsing of an expression
Key: TEIID-3750
URL: https://issues.jboss.org/browse/TEIID-3750
Project: Teiid
Issue Type: Sub-task
Components: Query Engine
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 8.12.1
Now that we have an extension property, we could allow an expression to be used in parsing rather than just a string.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 3 months