[teiid-issues] [JBoss JIRA] (TEIID-5341) Cannot extract time components from TIME column with Oracle

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5341?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5341.
-----------------------------------
    Resolution: Done


The was already a dataawareextract modifier, but it was too limited.  It has been updated to consider date and time types in general.

Obviously the intermediate cast to time isn't meaningful to the results in this case.  If needed we can add a rewrite rule to remove it in situations like this.

> Cannot extract time components from TIME column with Oracle
> -----------------------------------------------------------
>
>                 Key: TEIID-5341
>                 URL: https://issues.jboss.org/browse/TEIID-5341
>             Project: Teiid
>          Issue Type: Bug
>          Components: Misc. Connectors
>    Affects Versions: 8.12.12.6_4
>            Reporter: Jan Martiska
>            Assignee: Steven Hawkins
>             Fix For: 10.3
>
>
> This query against an Oracle-backed VDB:
> {noformat}
> SELECT hour(timevalue) FROM BQT1.SmallA
> {noformat}
> will fail with this error:
> {noformat}
> 13:16:42,493 WARN  [org.teiid.CONNECTOR] (Worker9_QueryProcessorQueue157) Connector worker process failed for atomic-request=eiMf6Yh5oppn.9.0.25: org.teiid.translator.jdbc.JDBCExecutionException: 30076 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT c_0 FROM (SELECT EXTRACT(HOUR FROM case when g_0.timevalue is null then null else to_date('1970-01-01 ' || to_char(g_0.timevalue, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end) AS c_0 FROM smalla g_0 WHERE case when g_0.timevalue is null then null else to_date('1970-01-01 ' || to_char(g_0.timevalue, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end IS NOT NULL) WHERE ROWNUM <= 100]
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.13.6_4-redhat-64-3.jar:8.12.13.6_4-redhat-64-3]
> 	at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> 	at sun.reflect.GeneratedMethodAccessor170.invoke(Unknown Source) [:1.8.0_151]
> 	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> 	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> 	at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> 	at com.sun.proxy.$Proxy79.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_151]
> 	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:1149) [rt.jar:1.8.0_151]
> 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> 	at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: java.sql.SQLException: ORA-30076: invalid extract field for extract source
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
> 	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
> 	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
> 	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
> 	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
> 	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
> 	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
> 	at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774)
> 	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925)
> 	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
> 	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845)
> 	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501)
> 	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
> 	at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.12.13.6_4-redhat-64-3.jar:8.12.13.6_4-redhat-64-3]
> 	... 17 more
> {noformat}
> this seems to affect TIME columns which are converted from timestamps in the view model:
> {noformat}
> CREATE VIEW smalla (...)
> TimeValue time, (...) AS SELECT (...)
> convert(TimeValue, time) AS TimeValue
> {noformat}



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)