[JBoss JIRA] (TEIID-4135) Invalid query when using recursive CTE with translators that do not suport RCTE pushdown
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4135?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4135:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1326842|https://bugzilla.redhat.com/show_bug.cgi?id=1326842] from VERIFIED to CLOSED
> Invalid query when using recursive CTE with translators that do not suport RCTE pushdown
> ----------------------------------------------------------------------------------------
>
> Key: TEIID-4135
> URL: https://issues.jboss.org/browse/TEIID-4135
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x
> Reporter: Andrej Šmigala
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5
>
>
> When running a recursive common table expression query against a source that does not support recursive cte pushdown, only the recursive part of the query is pushed, which fails, since it references a non-existent table (the cte).
> E. g. the query
> {code:sql}
> WITH tmp_cte(id, fk, lvl) AS (
> SELECT id, fk, 0 as lvl FROM SourceModel.cte_source WHERE fk IS NULL
> UNION ALL
> SELECT e.id, e.fk, lvl + 1 as lvl FROM SourceModel.cte_source AS e INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk
> )
> SELECT * FROM tmp_cte
> {code}
> against a PostreSQL source results in {noformat}Remote org.postgresql.util.PSQLException: ERROR: relation "tmp_cte" does not exist{noformat}
> because this query is pushed down:
> {code:sql}SELECT g_0.id, g_0.fk, g_0.lvl FROM tmp_cte AS g_0{code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3983) External Materialization MATVIEW_ONERROR_ACTION WAIT problem
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3983?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3983:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1309582|https://bugzilla.redhat.com/show_bug.cgi?id=1309582] from VERIFIED to CLOSED
> External Materialization MATVIEW_ONERROR_ACTION WAIT problem
> ------------------------------------------------------------
>
> Key: TEIID-3983
> URL: https://issues.jboss.org/browse/TEIID-3983
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
>
> When using "teiid_rel:MATVIEW_ONERROR_ACTION" 'WAIT' option in materialized view definition, there's problem with blocked request.
> The blocked request is the one, that triggered the materialized view's loading (first query on the defined materialized view). After such request, one can observe, that the query execution doesn't end, but hangs.
> Meanwhile while examining the contents of 'status' table in the underlying database, the LoadNumber column's value increases regularly according to the defined ttl. Moreover the 'LOADSTATE' column changes from LOADING to LOADED in similar manner. The materialized table is populated with correct data. From this I assume, materialization is set up correctly.
> During the wait, when logging set to DEBUG, there appears regularly, apart from the logs about refreshing the mat view, this entry:
> {code:plain}
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 with state PROCESSING
> [32m09:14:52,151 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue2695) Request Thread 87XzBpSzkyk4.0 - processor blocked
> {code}
> The log appears in 'ttl' determined intervals, each time after materialized table loading related entries.
> The thread mentioned is the one created after the original query execution was initiated.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3994) AliasSymbol wrapping AliasSymbol error
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3994?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3994:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1314909|https://bugzilla.redhat.com/show_bug.cgi?id=1314909] from VERIFIED to CLOSED
> AliasSymbol wrapping AliasSymbol error
> --------------------------------------
>
> Key: TEIID-3994
> URL: https://issues.jboss.org/browse/TEIID-3994
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 8.7.1.6_2
> Environment: Red Hat JBoss Data Virtualization 6.2 on EAP6.4.0 patched to version 6.4.3,
> JBoss Developer Studio 8.1.0GA with Teiid Designer plugin 9.0.3.Final.v20150810-1438-B1157
> 64-bit Windows 7 environment
> Reporter: Steve Tran
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5, 8.13.2
>
> Attachments: Oracle.PNG, SQLServer.PNG
>
>
> I've defined a view and overrode the default triggers for Select, Insert, Update, and Delete. I overrode the triggers because the select is returning "hardcoded" values for a few fields (calling them virtual columns). The Update and Insert triggers will ignore these hardcoded values just in case the caller tries to update a virtual column.
> I'm trying to run a insert into <tableA> select * from <tableB> query. When I run this, I get an error org.teiid.jdbc.TeiidSQLException: Cannot create AliasSymbol wrapping AliasSymbol.
> Facts:
> -- I can insert a record if I only select one column from tableB.
> -- If I select two or more columns, it throws the error every time.
> -- If I remove the overridden triggers, the insert into <tableA> select * from <tableB> works fine.
> -- I can insert one or more columns into <tableA> when *not* using a insert into <tableA> select * from <tableB> query
> -- Table A is Oracle
> -- Table B is SQL Server
> My hunch is the generated query (hibernate, or whatever SQL generator) is choking with the overridden trigger. Perhaps this use-case slipped through testing, or even more probable, I made a mistake in the trigger creation.
> Here's a snip of the insert trigger, with a lot of fields removed for simplicity.
> {code}
> FOR EACH ROW
> BEGIN ATOMIC
> INSERT INTO <schema>.<tableA> (SourceSystemID, RecordTypeID, BatchID ...)
> VALUES (
> convert("NEW".SourceSystemID, short),
> convert("NEW".RecordTypeID, short),
> convert("NEW".BatchID, integer)
> ...
> )
> END
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4094) Move the packaging of teiid-jdbc.jar into is own project
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4094?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4094:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1318684|https://bugzilla.redhat.com/show_bug.cgi?id=1318684] from VERIFIED to CLOSED
> Move the packaging of teiid-jdbc.jar into is own project
> --------------------------------------------------------
>
> Key: TEIID-4094
> URL: https://issues.jboss.org/browse/TEIID-4094
> Project: Teiid
> Issue Type: Enhancement
> Components: Build/Kits
> Affects Versions: 8.12.x
> Reporter: Van Halbert
> Assignee: Van Halbert
> Fix For: 8.12.5
>
>
> The issue with the way the teiid-jdbc.jar is packaged now, is that the teiid-web-console.zip gets included as a dependency.
> {code}
> [INFO] +- org.jboss.teiid:teiid:jar:jdbc:8.12.5.redhat-2:compile
> [INFO] | \- org.jboss.teiid.web-console:teiid-console-dist:zip:jboss-as7:2.5.6.Final-redhat-63-4:compile
> {code}
> And that becomes an issue when other external projects to Teiid include the jdbc driver as a dependency, in that they also end up requiring the web-console.zip.
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3815) Oracle translator - OFFSET function does not work
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3815?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3815:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1279460|https://bugzilla.redhat.com/show_bug.cgi?id=1279460] from VERIFIED to CLOSED
> Oracle translator - OFFSET function does not work
> -------------------------------------------------
>
> Key: TEIID-3815
> URL: https://issues.jboss.org/browse/TEIID-3815
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.7.2.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Priority: Blocker
> Fix For: 9.0, 8.12.5, 8.13.6
>
>
> Oracle translator translates OFFSET function as:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (***Query***) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
> This always returns an empty result because of *ROWNUM <= -2147483604*.
> Example:
> Query:
> {code:sql}
> SELECT INTKEY FROM bqt1.smalla ORDER BY INTKEY OFFSET 45 ROWS
> {code}
> Source-specific command:
> {code:sql}
> SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT trunc(g_0."INTKEY") AS c_0 FROM "DV"."SMALLA" g_0 ORDER BY c_0) VIEW_FOR_LIMIT WHERE ROWNUM <= -2147483604) WHERE ROWNUM_ > 45
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4194) Informix translator - constant time values are translated as timestamp
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4194?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4194:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1334201|https://bugzilla.redhat.com/show_bug.cgi?id=1334201] from VERIFIED to CLOSED
> Informix translator - constant time values are translated as timestamp
> ----------------------------------------------------------------------
>
> Key: TEIID-4194
> URL: https://issues.jboss.org/browse/TEIID-4194
> Project: Teiid
> Issue Type: Bug
> Components: JDBC Connector
> Affects Versions: 8.12.x
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
> Fix For: 9.0, 8.12.5
>
>
> Teiid translates constant time value (e.g. '02:00:00') as a _timestamp_ (\{ts '1970-01-01 02:00:00'\}) even if column is of type _time_.
> *DDL:*
> {code:xml}
> <model name="Source" type="PHYSICAL">
> ...
> <metadata type="DDL">
> <![CDATA[
> CREATE FOREIGN TABLE SmallA (
> ...
> TimeValue time OPTIONS (NATIVE_TYPE 'datetime hour to fraction(1)', NAMEINSOURCE 'timevalue'),
> ...
> {code}
> *Example query:*
> {code:sql}SELECT TimeValue FROM Source.SmallA WHERE TimeValue > '21:00:00' OR TimeValue < '02:00:00'{code}
> *Source-specific command:*
> {code:sql}SELECT g_0.timevalue FROM smalla AS g_0 WHERE g_0.timevalue > {ts '1970-01-01 21:00:00.0'} OR g_0.timevalue < {ts '1970-01-01 02:00:00.0'}{code}
> *Exception:*
> {code:plain}
> 09:08:35,320 WARN [org.teiid.CONNECTOR] (Worker7_QueryProcessorQueue65) Connector worker process failed for atomic-request=c/y8mqFekM4p.9.1.4: org.teiid.translator.jdbc.JDBCExecutionException: -1261 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.timevalue FROM smalla AS g_0 WHERE g_0.timevalue > {ts '1970-01-01 21:00:00.0'} OR g_0.timevalue < {ts '1970-01-01 02:00:00.0'}]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_40]
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_40]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_40]
> at java.lang.reflect.Method.invoke(Method.java:497) [rt.jar:1.8.0_40]
> 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: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) [rt.jar:1.8.0_40]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> 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:1142) [rt.jar:1.8.0_40]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_40]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_40]
> Caused by: java.sql.SQLException: Too many digits in the first field of datetime or interval.
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:3178)
> at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3458)
> at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2369)
> at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2294)
> at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1264)
> at com.informix.jdbc.IfxPreparedStatement.setupExecutePrepare(IfxPreparedStatement.java:308)
> at com.informix.jdbc.IfxPreparedStatement.processSQL(IfxPreparedStatement.java:289)
> at com.informix.jdbc.IfxPreparedStatement.<init>(IfxPreparedStatement.java:182)
> at com.informix.jdbc.IfxSqliConnect.createPreparedStmt(IfxSqliConnect.java:6108)
> at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:2509)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:744)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:730)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:405)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:196) [translator-jdbc-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:121) [translator-jdbc-8.12.5.redhat-4.jar:8.12.5.redhat-4]
> ... 18 more
> Caused by: java.sql.SQLException
> at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:408)
> at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3463)
> ... 31 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-3808) Informix translator - date/time/timestamp values are adjusted differently according to timezone
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-3808?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-3808:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1278724|https://bugzilla.redhat.com/show_bug.cgi?id=1278724] from VERIFIED to CLOSED
> Informix translator - date/time/timestamp values are adjusted differently according to timezone
> -----------------------------------------------------------------------------------------------
>
> Key: TEIID-3808
> URL: https://issues.jboss.org/browse/TEIID-3808
> Project: Teiid
> Issue Type: Quality Risk
> Affects Versions: 8.7.1.6_2
> Reporter: Juraj Duráni
> Assignee: Steven Hawkins
>
> The Informix translator adjusts only time value according to server's timezone. Timestamp values are adjusted only if 'DatabaseTimeZone' property is set. Date values are not adjusted at all.
> I am not sure what is Teiid's logic in this case. Is the difference between server's timezone and database's timezone taken into account? Or only the timezone of the database?
> -------------------
> Server's timezone - GMT+1
> DatabaseTimeZone property - not overridden
> Result:
> |time|date|timestamp|
> |01:00:00|2000-01-01|2000-01-01 00:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - not overridden
> Result:
> |time|date|timestamp|
> |05:00:00|2000-01-01|2000-01-01 00:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - GMT+1
> Result:
> |time|date|timestamp|
> |01:00:00|2000-01-01|2000-01-01 04:00:00.0|
> Server's timezone - GMT+5
> DatabaseTimeZone property - GMT-1
> Result (int this case
> |time|date|timestamp|
> |23:00:00|2000-01-01|2000-01-01 06:00:00.0|
> Informix:
> {code:sql}
> ...
> datevalue date,
> timevalue datetime hour to fraction(1),
> timestampvalue datetime year to fraction(1),
> ...
> {code}
> VDB - foreign table:
> {code:sql}
> ...
> DateValue date OPTIONS (NATIVE_TYPE 'date', NAMEINSOURCE 'datevalue'),
> TimeValue timestamp OPTIONS (NATIVE_TYPE 'datetime hour to fraction(1)', NAMEINSOURCE 'timevalue'),
> TimestampValue timestamp OPTIONS (NATIVE_TYPE 'datetime year to fraction(1)', NAMEINSOURCE 'timestampvalue'),
> ...
> {code}
> VDB - view:
> {code:sql}
> ...
> DateValue date,
> TimeValue time,
> TimestampValue timestamp,
> ...
> AS ... DateValue, convert(TimeValue, time), TimestampValue, ...
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months
[JBoss JIRA] (TEIID-4246) HANA translator: boolean column is not valid boolean expression in HANA
by RH Bugzilla Integration (JIRA)
[ https://issues.jboss.org/browse/TEIID-4246?page=com.atlassian.jira.plugin... ]
RH Bugzilla Integration commented on TEIID-4246:
------------------------------------------------
Filip Elias <felias(a)redhat.com> changed the Status of [bug 1343573|https://bugzilla.redhat.com/show_bug.cgi?id=1343573] from VERIFIED to CLOSED
> HANA translator: boolean column is not valid boolean expression in HANA
> -----------------------------------------------------------------------
>
> Key: TEIID-4246
> URL: https://issues.jboss.org/browse/TEIID-4246
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.5
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Priority: Critical
> Fix For: 9.0, 8.12.5
>
>
> SAP HANA does not take boolean column or literal as boolean expressions.
> Query like this won't pass (invoked on HANA directly):
> {code:sql}
> SELECT (CASE WHEN BooleanValue THEN 'a' ELSE 'b' END) FROM BQT1.smalla;
> {code}
> The following could serve as workaround (invoked on HANA directly):
> {code:sql}
> SELECT (CASE WHEN BooleanValue=true THEN 'a' ELSE 'b' END) FROM BQT1.smalla;
> {code}
> The problem is observable when using boolean column in string function (invoked on Teiid):
> {code:sql}
> SELECT INTKEY, ('test+' || (BOOLEANVALUE || '+test')) FROM BQT1.SmallA ORDER BY IntKey
> {code}
> Which results in following error:
> {code:sql}
> TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 HanaSource: 257 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0."INTKEY" AS c_0, concat('test+', concat(CASE WHEN g_0."BOOLEANVALUE" THEN 'true' WHEN not(g_0."BOOLEANVALUE") THEN 'false' END, '+test')) AS c_1 FROM "BQT1"."SMALLA" AS g_0 ORDER BY c_0]
> {code}
--
This message was sent by Atlassian JIRA
(v6.4.11#64026)
9 years, 7 months