[TEIID-5153] RAND function gives same results in view

Type: Bug
Resolution: Done
Priority: Critical
Fix Version/s: 10.1, 10.0.1, 9.3.6
Affects Version/s: 9.3.4
Component/s: Query Engine
Labels:
None

Steps to Reproduce:
Hide

1. Change (or patch) PostgreSQLExecutionFactory adding the following lines:
a) add in start() method

Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$

b) add in getSupportedFunctions() method

Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
supportedFunctions.add(SourceSystemFunctions.RAND);

2. In postgresql create the test_a table by the following script:

CREATE TABLE public.test_a ( a integer, b integer ); INSERT INTO public.test_a(a, b) VALUES (1, 1); INSERT INTO public.test_a(a, b) VALUES (1, 2); INSERT INTO public.test_a(a, b) VALUES (2, 1); INSERT INTO public.test_a(a, b) VALUES (2, 2); INSERT INTO public.test_a(a, b) VALUES (3, 2); INSERT INTO public.test_a(a, b) VALUES (3, 10);

3. Add postgresql database configuration in standalone-teiid.xml

<datasource jndi-name="java:/test_pg" pool-name="test_pg" enabled="true" use-java-context="true"> <connection-url>jdbc:postgresql://localhost:5432/test?charSet=utf8</connection-url> <driver-class>org.postgresql.Driver</driver-class> <driver>org.postgresql</driver> <pool> <min-pool-size>2</min-pool-size> <max-pool-size>70</max-pool-size> <prefill>false</prefill> <use-strict-min>false</use-strict-min> <flush-strategy>FailingConnectionOnly</flush-strategy> </pool> <security> <user-name>postgres</user-name> <password>xxxxxx</password> </security> <validation> <check-valid-connection-sql>select 0</check-valid-connection-sql> </validation> <timeout> <blocking-timeout-millis>120000</blocking-timeout-millis> <idle-timeout-minutes>5</idle-timeout-minutes> </timeout> </datasource>

4. Add in test-vdb.xml java:/test_pg configured in previous step as datasource:

<model name="test_pg"> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.tableTypes" value="TABLE,VIEW"/> <property name="importer.importKeys" value="false"/> <source name="test_pg" translator-name="myPg" connection-jndi-name="java:/test_pg"/> </model>

5. Configure in the test-vdb.xml the following virtual view:

<model visible = "true" type = "VIRTUAL" name = "views"> <metadata type = "DDL"><![CDATA[ CREATE virtual view v1 as select rand() from test_pg.test_a ]]> </metadata> </model>

6. Run the following queries and compare them:

select rand() from test_pg.test_a;;

and

select * from views.v1;;

the first one will return 6 different values but the second one will return 6 absolutely identical values.
Show
1. Change (or patch) PostgreSQLExecutionFactory adding the following lines: a) add in start() method Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier( "random" )); //$NON-NLS-1$ b) add in getSupportedFunctions() method Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml supportedFunctions.add(SourceSystemFunctions.RAND); 2. In postgresql create the test_a table by the following script: CREATE TABLE public .test_a ( a integer , b integer ); INSERT INTO public .test_a( a , b) VALUES (1, 1); INSERT INTO public .test_a( a , b) VALUES (1, 2); INSERT INTO public .test_a( a , b) VALUES (2, 1); INSERT INTO public .test_a( a , b) VALUES (2, 2); INSERT INTO public .test_a( a , b) VALUES (3, 2); INSERT INTO public .test_a( a , b) VALUES (3, 10); 3. Add postgresql database configuration in standalone-teiid.xml <datasource jndi- name = "java:/test_pg" pool- name = "test_pg" enabled= " true " use -java-context= " true " > < connection -url>jdbc:postgresql://localhost:5432/test?charSet=utf8</ connection -url> <driver- class& gt;org.postgresql.Driver</driver- class& gt; <driver>org.postgresql</driver> <pool> < min -pool- size >2</ min -pool- size > < max -pool- size >70</ max -pool- size > <prefill> false </prefill> < use - strict - min > false </ use - strict - min > < flush -strategy>FailingConnectionOnly</ flush -strategy> </pool> < security > < user - name >postgres</ user - name > < password >xxxxxx</ password > </ security > <validation> < check - valid - connection - sql > select 0</ check - valid - connection - sql > </validation> <timeout> <blocking-timeout-millis>120000</blocking-timeout-millis> <idle-timeout-minutes>5</idle-timeout-minutes> </timeout> </datasource> 4. Add in test-vdb.xml java:/test_pg configured in previous step as datasource: <model name = "test_pg" > <property name = "importer.useFullSchemaName" value = " false " /> <property name = "importer.tableTypes" value = " TABLE , VIEW " /> <property name = "importer.importKeys" value = " false " /> < source name = "test_pg" translator- name = "myPg" connection -jndi- name = "java:/test_pg" /> </model> 5. Configure in the test-vdb.xml the following virtual view: <model visible = "true" type = "VIRTUAL" name = "views" > <metadata type = "DDL" > <![CDATA[ CREATE virtual view v1 as select rand() from test_pg.test_a ]]> </metadata> </model> 6. Run the following queries and compare them: select rand() from test_pg.test_a;; and select * from views.v1;; the first one will return 6 different values but the second one will return 6 absolutely identical values.

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.

Dmitrii Pogorelov added a comment - 2017/12/03 6:43 AM

rhn-engineering-shawkins np at all, thanks to you for the quick fix, the last changes worked for me.
> I'm surprised no other test ran into that situation.
me too

Dmitrii Pogorelov added a comment - 2017/12/03 6:43 AM rhn-engineering-shawkins np at all, thanks to you for the quick fix, the last changes worked for me. > I'm surprised no other test ran into that situation. me too

Steven Hawkins added a comment - 2017/12/01 3:25 PM

Thanks for highlighting this. It has been addressed as well. I'm surprised no other test ran into that situation.

Steven Hawkins added a comment - 2017/12/01 3:25 PM Thanks for highlighting this. It has been addressed as well. I'm surprised no other test ran into that situation.

Dmitrii Pogorelov added a comment - 2017/12/01 3:07 PM

> I should have realized there were potential issues with re-purposing that code from the view removal logic.
np at all
> I'll provide a fix shortly.
thx!

Dmitrii Pogorelov added a comment - 2017/12/01 3:07 PM > I should have realized there were potential issues with re-purposing that code from the view removal logic. np at all > I'll provide a fix shortly. thx!

Steven Hawkins added a comment - 2017/12/01 2:52 PM

Yes, that is a problem. I should have realized there were potential issues with re-purposing that code from the view removal logic. I'll provide a fix shortly.

Steven Hawkins added a comment - 2017/12/01 2:52 PM Yes, that is a problem. I should have realized there were potential issues with re-purposing that code from the view removal logic. I'll provide a fix shortly.

Dmitrii Pogorelov added a comment - 2017/12/01 2:03 PM - edited

Hi rhn-engineering-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:

<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>

the view1 uses the table_a table which I described above in "Steps to Reproduce" section.
1. When running the following query:

SELECT CustomerType FROM views.view2

and setting again the RequestMessage.rowLimit field to 0 you will get the following error message:

Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml

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

2. When running the following query:

SELECT CustomerType FROM views.view2 where CustomerType = 'a'

and setting again the RequestMessage.rowLimit field to 0 you will get the following error message:

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

Dmitrii Pogorelov added a comment - 2017/12/01 2:03 PM - edited Hi rhn-engineering-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: <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> the view1 uses the table_a table which I described above in "Steps to Reproduce" section. 1. When running the following query: SELECT CustomerType FROM views.view2 and setting again the RequestMessage.rowLimit field to 0 you will get the following error message: Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml 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 2. When running the following query: SELECT CustomerType FROM views.view2 where CustomerType = ' a ' and setting again the RequestMessage.rowLimit field to 0 you will get the following error message: Unable to find source-code formatter for language: noformat. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml 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

Dmitrii Pogorelov added a comment - 2017/11/30 12:34 PM

Ok rhn-engineering-shawkins, I will wait for your fix, thx!

Dmitrii Pogorelov added a comment - 2017/11/30 12:34 PM Ok rhn-engineering-shawkins , I will wait for your fix, thx!

Steven Hawkins added a comment - 2017/11/30 12:26 PM

Yes that introduced an issue. That code was very similar to what is used when removing a view layer, but in this case the source node is retained and needs to have the output symbols set. I'll check that fix in under this issue.

Steven Hawkins added a comment - 2017/11/30 12:26 PM Yes that introduced an issue. That code was very similar to what is used when removing a view layer, but in this case the source node is retained and needs to have the output symbols set. I'll check that fix in under this issue.

Dmitrii Pogorelov added a comment - 2017/11/30 11:21 AM - edited

Hi rhn-engineering-shawkins it seems that I found a potential problem related to the new removal algorithm introduced by you
in the RuleCollapseSource.removeUnnecessaryInlineView method. If I set in debug mode RequestMessage.rowLimit field as 0 then
Teiid won't add a LIMIT clause in the end of original query (Request.generatePlan method, a check on requestMsg.getRowLimit() > 0 expression) and
we get the same error which was described in ~~TEIID-3846~~ issue. Could you please check in additional?
To explain more precisely what is happening when we set rowLimit to 0: Teiid won't add TupleLimit node with particular limit value over all nodes and the RuleCollapseSource.removeUnnecessaryInlineView will remove some select symbols from a project node.

Dmitrii Pogorelov added a comment - 2017/11/30 11:21 AM - edited Hi rhn-engineering-shawkins it seems that I found a potential problem related to the new removal algorithm introduced by you in the RuleCollapseSource.removeUnnecessaryInlineView method. If I set in debug mode RequestMessage.rowLimit field as 0 then Teiid won't add a LIMIT clause in the end of original query (Request.generatePlan method, a check on requestMsg.getRowLimit() > 0 expression) and we get the same error which was described in TEIID-3846 issue. Could you please check in additional? To explain more precisely what is happening when we set rowLimit to 0: Teiid won't add TupleLimit node with particular limit value over all nodes and the RuleCollapseSource.removeUnnecessaryInlineView will remove some select symbols from a project node.

Steven Hawkins added a comment - 2017/11/17 2:09 PM

Yes it can be added where ever it seems to be missing.

Steven Hawkins added a comment - 2017/11/17 2:09 PM Yes it can be added where ever it seems to be missing.

Dmitrii Pogorelov added a comment - 2017/11/17 12:51 PM - edited

rhn-engineering-shawkins yes, I want, but I would offer to add also the support for MySQL, MSSQL and Oracle. What do you think? For MySQL and MSSQL it would be rand() function, for Oracle accordingly DBMS_RANDOM.VALUE.

Dmitrii Pogorelov added a comment - 2017/11/17 12:51 PM - edited rhn-engineering-shawkins yes, I want, but I would offer to add also the support for MySQL, MSSQL and Oracle. What do you think? For MySQL and MSSQL it would be rand() function, for Oracle accordingly DBMS_RANDOM.VALUE.

Steven Hawkins added a comment - 2017/11/17 12:34 PM

Yes, if you want to have the random pushdown support built-in.

Steven Hawkins added a comment - 2017/11/17 12:34 PM Yes, if you want to have the random pushdown support built-in.

Steven Hawkins added a comment - 2017/11/17 12:29 PM

Expanded the logic that sets the eval flag to false to also consider functions appearing in subquery from clause and with clause locations.

Steven Hawkins added a comment - 2017/11/17 12:29 PM Expanded the logic that sets the eval flag to false to also consider functions appearing in subquery from clause and with clause locations.

Dmitrii Pogorelov added a comment - 2017/11/17 12:28 PM

rhn-engineering-shawkins do you mean to create a corresponding issue to add the random support for postgresql?

Dmitrii Pogorelov added a comment - 2017/11/17 12:28 PM rhn-engineering-shawkins do you mean to create a corresponding issue to add the random support for postgresql?

Steven Hawkins added a comment - 2017/11/17 12:24 PM

Can you log an issue to add random support to postgresql as well?

Steven Hawkins added a comment - 2017/11/17 12:24 PM Can you log an issue to add random support to postgresql as well?

Steven Hawkins added a comment - 2017/11/16 8:06 PM

The logic isn't appropriately differentiating between scalar subqueries and inline views. In the former you do want to preevaluate "select (select random()) from test_a", but not the latter.

Steven Hawkins added a comment - 2017/11/16 8:06 PM The logic isn't appropriately differentiating between scalar subqueries and inline views. In the former you do want to preevaluate "select (select random()) from test_a", but not the latter.

Assignee:: Steven Hawkins

Reporter:: Dmitrii Pogorelov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Created:: 2017/11/16 4:46 PM

Updated:: 2021/10/24 5:59 AM

Resolved:: 2017/11/17 12:29 PM

Details

Description

Attachments

Easy Agile Planning Poker

Activity

Collapse comment: Dmitrii Pogorelov added a comment - 2017/12/03 6:43 AM

Expand comment: Dmitrii Pogorelov added a comment - 2017/12/03 6:43 AM

Collapse comment: Steven Hawkins added a comment - 2017/12/01 3:25 PM

Expand comment: Steven Hawkins added a comment - 2017/12/01 3:25 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/12/01 3:07 PM

Expand comment: Dmitrii Pogorelov added a comment - 2017/12/01 3:07 PM

Collapse comment: Steven Hawkins added a comment - 2017/12/01 2:52 PM

Expand comment: Steven Hawkins added a comment - 2017/12/01 2:52 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/12/01 2:03 PM, Edited by Dmitrii Pogorelov - 2017/12/01 2:13 PM

Expand comment: Dmitrii Pogorelov added a comment - 2017/12/01 2:03 PM, Edited by Dmitrii Pogorelov - 2017/12/01 2:13 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/11/30 12:34 PM

Expand comment: Dmitrii Pogorelov added a comment - 2017/11/30 12:34 PM

Collapse comment: Steven Hawkins added a comment - 2017/11/30 12:26 PM

Expand comment: Steven Hawkins added a comment - 2017/11/30 12:26 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/11/30 11:21 AM, Edited by Dmitrii Pogorelov - 2017/11/30 11:28 AM

Expand comment: Dmitrii Pogorelov added a comment - 2017/11/30 11:21 AM, Edited by Dmitrii Pogorelov - 2017/11/30 11:28 AM

Collapse comment: Steven Hawkins added a comment - 2017/11/17 2:09 PM

Expand comment: Steven Hawkins added a comment - 2017/11/17 2:09 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/11/17 12:51 PM, Edited by Dmitrii Pogorelov - 2017/11/17 1:01 PM

Expand comment: Dmitrii Pogorelov added a comment - 2017/11/17 12:51 PM, Edited by Dmitrii Pogorelov - 2017/11/17 1:01 PM

Collapse comment: Steven Hawkins added a comment - 2017/11/17 12:34 PM

Expand comment: Steven Hawkins added a comment - 2017/11/17 12:34 PM

Collapse comment: Steven Hawkins added a comment - 2017/11/17 12:29 PM

Expand comment: Steven Hawkins added a comment - 2017/11/17 12:29 PM

Collapse comment: Dmitrii Pogorelov added a comment - 2017/11/17 12:28 PM

Expand comment: Dmitrii Pogorelov added a comment - 2017/11/17 12:28 PM

Collapse comment: Steven Hawkins added a comment - 2017/11/17 12:24 PM

Expand comment: Steven Hawkins added a comment - 2017/11/17 12:24 PM

Collapse comment: Steven Hawkins added a comment - 2017/11/16 8:06 PM

Expand comment: Steven Hawkins added a comment - 2017/11/16 8:06 PM

People

Dates