[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 updated TEIID-5341:
----------------------------------
    Fix Version/s: 10.3


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