[teiid-issues] [JBoss JIRA] (TEIID-5153) RAND function gives same results in view
dalex dalex (JIRA)
issues at jboss.org
Fri Dec 1 14:14:00 EST 2017
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13496465#comment-13496465 ]
dalex dalex edited comment on TEIID-5153 at 12/1/17 2:13 PM:
-------------------------------------------------------------
Hi [~shawkins], I found another problem which is related to the aggressive removal mechanism added within the issue. The bug can be reproduced even with your the latest changes from the issue.
To reproduce the problem, please, add the following views:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
CREATE VIEW view1 AS SELECT * FROM test_pg.test_a;
create view view2 as select 'a' as CustomerType, view1.*
from (select min(a) as FirstOrderDate, count(b), b
from view1 as pixiGetOrderHeader group by b) as NewCustomerTable, view1
where view1.a = NewCustomerTable.b and view1.b = NewCustomerTable.FirstOrderDate
UNION select 'Existing Customer' as CustomerType, view1.*
from (select min(a) as FirstOrderDate, count(b), b
from view1 group by b) as NewCustomerTable, view1
where view1.a = NewCustomerTable.b and view1.b != NewCustomerTable.FirstOrderDate
]]>
</metadata>
</model>
{code}
the view1 uses the table_a table which I described above in "Steps to Reproduce" section.
1. When running the following query:
{code:sql}
SELECT CustomerType FROM views.view2
{code}
and setting again the RequestMessage.rowLimit field to 0 you will get the following error message:
{code:noformat}
2017-12-01 19:56:36,917 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue44) Myo54LyNkJt/ Connector worker process failed for atomic-request=Myo54LyNkJt/.14.2.5: org.teiid.tra
nslator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_2.CustomerType FROM (SELECT cast('a' AS bpchar) AS c_0,
g_3."a" AS c_1, g_3."b" AS c_2 FROM (SELECT g_2."b" AS c_0, MIN(g_2."a") AS c_1 FROM "public"."test_a" AS g_2 GROUP BY g_2."b") AS v_1, "public"."test_a" AS g_3 WHERE g_3."a" = v_1.c
_0 AND g_3."b" = v_1.c_1 UNION SELECT cast('Existing Customer' AS bpchar) AS c_0, g_1."a" AS c_1, g_1."b" AS c_2 FROM (SELECT g_0."b" AS c_0, MIN(g_0."a") AS c_1 FROM "public"."test_
a" AS g_0 GROUP BY g_0."b") AS v_0, "public"."test_a" AS g_1 WHERE g_1."a" = v_0.c_0 AND g_1."b" <> v_0.c_1) AS v_2]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)
at com.sun.proxy.$Proxy28.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: field v_2.customertype doesn't exist
Position: 8
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:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
2. When running the following query:
{code:sql}
SELECT CustomerType FROM views.view2 where CustomerType = 'a'
{code}
and setting again the RequestMessage.rowLimit field to 0 you will get the following error message:
{code:noformat}
2017-12-01 20:10:50,570 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue47) Myo54LyNkJt/ Connector worker process failed for atomic-request=Myo54LyNkJt/.15.2.6: org.teiid.tra
nslator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_1.CustomerType FROM (SELECT DISTINCT cast('a' AS bpchar)
AS c_0, g_1."a" AS c_1, g_1."b" AS c_2 FROM (SELECT g_0."b" AS c_0, MIN(g_0."a") AS c_1 FROM "public"."test_a" AS g_0 GROUP BY g_0."b") AS v_0, "public"."test_a" AS g_1 WHERE g_1."a
" = v_0.c_0 AND g_1."b" = v_0.c_1) AS v_1]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)
at com.sun.proxy.$Proxy28.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: field v_1.customertype doesn't exist
Position: 8
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:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
was (Author: dalex005):
Hi [~shawkins], I found another problem which is related to the aggressive removal mechanism added within the issue. The bug can be reproduced even with your the latest changes from the issue.
To reproduce the problem, please, add the following views:
{code:xml}
<model visible = "true" type = "VIRTUAL" name = "views">
<metadata type = "DDL"><![CDATA[
CREATE VIEW view1 AS SELECT * FROM test_pg.test_a;
create view view2 as select 'a' as CustomerType, view1.*
from (select min(a) as FirstOrderDate, count(b), b
from view1 as pixiGetOrderHeader group by b) as NewCustomerTable, view1
where view1.a = NewCustomerTable.b and view1.b = NewCustomerTable.FirstOrderDate
UNION select 'Existing Customer' as CustomerType, view1.*
from (select min(a) as FirstOrderDate, count(b), b
from view1 group by b) as NewCustomerTable, view1
where view1.a = NewCustomerTable.b and view1.b != NewCustomerTable.FirstOrderDate
]]>
</metadata>
</model>
{code}
the view1 uses the table_a table which I described above in "Steps to Reproduce" section.
When running the following query:
{code:sql}
SELECT CustomerType FROM views.view2
{code}
and setting again the RequestMessage.rowLimit field to 0 you will get the following error message:
{code:noformat}
2017-12-01 19:56:36,917 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue44) Myo54LyNkJt/ Connector worker process failed for atomic-request=Myo54LyNkJt/.14.2.5: org.teiid.tra
nslator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT v_2.CustomerType FROM (SELECT cast('a' AS bpchar) AS c_0,
g_3."a" AS c_1, g_3."b" AS c_2 FROM (SELECT g_2."b" AS c_0, MIN(g_2."a") AS c_1 FROM "public"."test_a" AS g_2 GROUP BY g_2."b") AS v_1, "public"."test_a" AS g_3 WHERE g_3."a" = v_1.c
_0 AND g_3."b" = v_1.c_1 UNION SELECT cast('Existing Customer' AS bpchar) AS c_0, g_1."a" AS c_1, g_1."b" AS c_2 FROM (SELECT g_0."b" AS c_0, MIN(g_0."a") AS c_1 FROM "public"."test_
a" AS g_0 GROUP BY g_0."b") AS v_0, "public"."test_a" AS g_1 WHERE g_1."a" = v_0.c_0 AND g_1."b" <> v_0.c_1) AS v_2]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:220)
at com.sun.proxy.$Proxy28.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: field v_2.customertype doesn't exist
Position: 8
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:504)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
... 18 more
{code}
> RAND function gives same results in view
> ----------------------------------------
>
> Key: TEIID-5153
> URL: https://issues.jboss.org/browse/TEIID-5153
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 9.3.4
> Environment: teiid-9.3.4 on WildFly Full 10.0.0.Final (WildFly Core 2.0.10.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 10.1, 10.0.1, 9.3.6
>
>
> When using patched PostgreSQLExecutionFactory class to push down RAND function it starts to give the same values in a view. I wanted to push down RAND function in PostgreSQL for a while and noticed the bug related to identical values when using RAND function through a select in a view.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list