Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5341

Cannot extract time components from TIME column with Oracle

    Details

    • Type: Bug
    • Status: Resolved (View Workflow)
    • Priority: Major
    • Resolution: Done
    • Affects Version/s: 8.12.12.6_4
    • Fix Version/s: 10.3
    • Component/s: Misc. Connectors
    • Labels:
      None

      Description

      This query against an Oracle-backed VDB:

      SELECT hour(timevalue) FROM BQT1.SmallA
      

      will fail with this error:

      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
      

      this seems to affect TIME columns which are converted from timestamps in the view model:

      CREATE VIEW smalla (...)
      TimeValue time, (...) AS SELECT (...)
      convert(TimeValue, time) AS TimeValue
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jmartisk Jan Martiska
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: