[JBoss JIRA] (TEIID-5042) Couchbase supported data types restriction issues
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-5042?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-5042:
------------------------------------------------
Jan Stastny <jstastny(a)redhat.com> changed the Status of [bug 1486332|https://bugzilla.redhat.com/show_bug.cgi?id=1486332] from ON_QA to VERIFIED
> Couchbase supported data types restriction issues
> -------------------------------------------------
>
> Key: TEIID-5042
> URL: https://issues.jboss.org/browse/TEIID-5042
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 10.0, 8.12.x-6.4, 9.3.3
>
>
> # Couchbase translator supports only subset of supported datatypes in Teiid:
> ??All supported type in a Couchbase JSON item: null, String, Integer, Long, Double, BigInteger, BigDecimal, JsonObject, JsonArray??
> #* this is too restrictive, why not float? time/date/timestamp? And let the translator to handle conversion in both directions.
> # Couchbase leaves out silently the columns with unsupported data types in INSERTs.
> #* See the example in bottom of description.
> # documentID is (in code) considered String. This is too restrictive. In the code there might be String.valueOf() instead of (String) cast (N1QLUpdateVisitor.java:139). It would help users to use their primary keys (from existing datasets) as document ids in couchbase.
> Example:
> For a source model DDL:
> {code:sql|title=DDL}
> CREATE FOREIGN TABLE SmallA (
> documentID string PRIMARY KEY,
> type string OPTIONS (NAMEINSOURCE '`type`'),
> FloatNum float OPTIONS (NAMEINSOURCE '`FloatNum`'),
> BigIntegerValue biginteger OPTIONS (NAMEINSOURCE '`BigIntegerValue`'),
> StringKey string OPTIONS (NAMEINSOURCE '`StringKey`'),
> CharValue string OPTIONS (NAMEINSOURCE '`CharValue`'),
> LongNum long OPTIONS (NAMEINSOURCE '`LongNum`'),
> DoubleNum double OPTIONS (NAMEINSOURCE '`DoubleNum`'),
> ObjectValue string OPTIONS (NAMEINSOURCE '`ObjectValue`'),
> ShortValue integer OPTIONS (NAMEINSOURCE '`ShortValue`'),
> BigDecimalValue bigdecimal OPTIONS (NAMEINSOURCE '`BigDecimalValue`'),
> DateValue string OPTIONS (NAMEINSOURCE '`DateValue`'),
> BooleanValue boolean OPTIONS (NAMEINSOURCE '`BooleanValue`'),
> TimestampValue string OPTIONS (NAMEINSOURCE '`TimestampValue`'),
> ByteNum integer OPTIONS (NAMEINSOURCE '`ByteNum`'),
> StringNum string OPTIONS (NAMEINSOURCE '`StringNum`'),
> TimeValue string OPTIONS (NAMEINSOURCE '`TimeValue`'),
> IntNum integer OPTIONS (NAMEINSOURCE '`IntNum`')
> ) OPTIONS (NAMEINSOURCE '`dvqe_crud`', UPDATABLE TRUE, "teiid_couchbase:ISARRAYTABLE" 'false', "teiid_couchbase:NAMEDTYPEPAIR" '`type`:''nullSmallA''');
> {code}
> and query
> {code:sql|title=INSERT query}
> INSERT INTO Source.SmallA (documentID, StringKey, IntNum, StringNum,DoubleNum, FloatNum,LongNum) VALUES (4, '1', 1, '1',CAST(5.00 AS double),CAST(5.00 AS float), 5);
> {code}
> there is incorrect source command being pushed to couchbase. The float column is not being pushed at all, no warning appears.
> {code:title=teiid-command.log}
> 14:49:37,061 INFO [org.teiid.COMMAND_LOG] (New I/O worker #2) ReyS5USI/FcT START USER COMMAND: startTime=2017-08-29 14:49:37.061 requestID=ReyS5USI/FcT.9 txID=null sessionID=ReyS5USI/FcT applicationName=JDBC principal=user@teiid-security vdbName=couchbase_crud vdbVersion=1 sql=INSERT INTO Source.SmallA (documentID, StringKey, IntNum, StringNum,DoubleNum, FloatNum,LongNum) VALUES (4, '1', 1, '1',CAST(5.00 AS double),CAST(5.00 AS float), 5)
> 14:49:38,375 DEBUG [org.teiid.COMMAND_LOG] (Worker32_QueryProcessorQueue150) ReyS5USI/FcT START DATA SRC COMMAND: startTime=2017-08-29 14:49:38.375 requestID=ReyS5USI/FcT.9 sourceCommandID=0 executionID=27 txID=null modelName=Source translatorName=couchbase sessionID=ReyS5USI/FcT principal=user@teiid-security sql=INSERT INTO Source.SmallA (documentID, StringKey, IntNum, StringNum, DoubleNum, FloatNum, LongNum) VALUES ('4', '1', 1, '1', 5.0, 5.0, 5)
> 14:49:38,380 DEBUG [org.teiid.COMMAND_LOG] (Worker32_QueryProcessorQueue150) ReyS5USI/FcT SOURCE SRC COMMAND: endTime=2017-08-29 14:49:38.38 requestID=ReyS5USI/FcT.9 sourceCommandID=0 executionID=27 txID=null modelName=Source translatorName=couchbase sessionID=ReyS5USI/FcT principal=user@teiid-security sourceCommand=[INSERT INTO `dvqe_crud` (KEY, VALUE) VALUES ('4', {"DoubleNum":5.0,"IntNum":1,"LongNum":5,"StringNum":"1","StringKey":"1"}) RETURNING META(`dvqe_crud`).id AS PK]
> 14:49:38,962 DEBUG [org.teiid.COMMAND_LOG] (Worker31_QueryProcessorQueue151) ReyS5USI/FcT END SRC COMMAND: endTime=2017-08-29 14:49:38.962 requestID=ReyS5USI/FcT.9 sourceCommandID=0 executionID=27 txID=null modelName=Source translatorName=couchbase sessionID=ReyS5USI/FcT principal=user@teiid-security finalRowCount=1 cpuTime(ns)=12306369
> 14:49:38,966 INFO [org.teiid.COMMAND_LOG] (Worker31_QueryProcessorQueue152) ReyS5USI/FcT END USER COMMAND: endTime=2017-08-29 14:49:38.966 requestID=ReyS5USI/FcT.9 txID=null sessionID=ReyS5USI/FcT principal=user@teiid-security vdbName=couchbase_crud vdbVersion=1 finalRowCount=1
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 9 months
[JBoss JIRA] (TEIID-5153) RAND function gives same results in view
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-5153:
------------------------------------
[~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
> 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)
7 years, 10 months
[JBoss JIRA] (TEIID-5153) RAND function gives same results in view
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5153:
---------------------------------------
Thanks for highlighting this. It has been addressed as well. I'm surprised no other test ran into that situation.
> 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)
7 years, 10 months
[JBoss JIRA] (TEIID-5175) Binding of Hive date types should not use the calendar
by Steven Hawkins (JIRA)
Steven Hawkins created TEIID-5175:
-------------------------------------
Summary: Binding of Hive date types should not use the calendar
Key: TEIID-5175
URL: https://issues.jboss.org/browse/TEIID-5175
Project: Teiid
Issue Type: Bug
Components: JDBC Connector
Reporter: Steven Hawkins
Assignee: Steven Hawkins
Fix For: 10.1, 10.0.1
TEIID-3847 addressed timestamp retrieval by the translator and added code for binding, but the binding code did not add the necessary return statements to prevent the binding from taking place afterwards.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 10 months
[JBoss JIRA] (TEIID-5131) Create ansible playbooks for the ansible service broker
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5131?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5131:
---------------------------------------
Updated the repo to back out most ha considerations and updated the salesforce script to be inline with new openshift services - importantly the environment variables are specified by the secret, not by the template parameters.
[~rareddy] where is the icon you have developed?
After looking at the health / readiness checks some more, we do need to modify that. We need the readiness to check for vdb status failed / failed metadata load - in the latter case we may want institute a reload attempt as part of the readiness check.
> Create ansible playbooks for the ansible service broker
> -------------------------------------------------------
>
> Key: TEIID-5131
> URL: https://issues.jboss.org/browse/TEIID-5131
> Project: Teiid
> Issue Type: Enhancement
> Components: Build/Kits
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Fix For: 10.1
>
>
> For some of the most common usage patterns, we should create playbooks that contribute service brokers via the ansible service broker. This could be:
> salesforce as db
> couchbase as db/odata
> mongodb as db/odata
> other openshift databases as odata
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
7 years, 10 months
[JBoss JIRA] (TEIID-5153) RAND function gives same results in view
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-5153:
------------------------------------
> 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!
> 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)
7 years, 10 months
[JBoss JIRA] (TEIID-5153) RAND function gives same results in view
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5153:
---------------------------------------
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.
> 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)
7 years, 10 months
[JBoss JIRA] (TEIID-5153) RAND function gives same results in view
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5153?page=com.atlassian.jira.plugin... ]
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)
7 years, 10 months