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}