[teiid-issues] [JBoss JIRA] (TEIID-5153) RAND function gives same results in view

[dalex dalex (JIRA)]

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

dalex dalex commented on TEIID-5153:
------------------------------------

Hi [~shawkins], I found another problem which is related to the aggressive removal mechanism added within 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)