3 Replies Latest reply on Sep 25, 2014 10:39 AM by Alex K.

    incorrect pushdown in WITH clause for curdate() - Teiid 8.9.0-Beta1

    Alex K. Newbie

      The following SQL code

      with test as (
            select CURDATE() as startDate
              from schema.table)
      select startDate from test
      

      correctly works if the source schema is MySQL, but it fails on other sources like PostgreSQL, Microsoft SQL Server or Oracle.

       

      For example, when the source is PostgreSQL, the following exception is thrown:

       

      WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue47) 76zDcCmNQQgL TEIID30020 Processing exception for request 76zDcCmNQQgL.17 'TEIID30504 test_tables_ms: 195 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH test (startDate) AS (SELECT curdate() FROM "test_tables"."dbo"."test_a" g_0) SELECT g_0.startDate FROM test g_0]'. Originally TeiidProcessingException ''curdate' wird nicht als Name einer integrierten Funktion erkannt.' SQLServerException.java:216. Enable more detailed logging to see the entire stacktrace.
      16:28:11,090 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue49) 76zDcCmNQQgL Connector worker process failed for atomic-request=76zDcCmNQQgL.18.1.10: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: WITH test (startDate) AS (SELECT curdate() FROM "public"."test_a" AS g_0) SELECT g_0.startDate FROM test AS g_0]
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131)
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_51]
              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.9.0.Beta1.jar:8.9.0.Beta1]
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
              at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
      Caused by: org.postgresql.util.PSQLException: FEHLER: Funktion curdate() existiert nicht
        Hinweis: Keine Funktion stimmt mit dem angegebenen Namen und den Argumenttypen ++berein. Sie m++ssen m+Âglicherweise ausdr++ckliche Typumwandlungen hinzuf++gen.
        Position: 34
              at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)
              at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1886)
              at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:555)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
              at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:302)
              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123)
              ... 12 more
      

       

      The same query written without "WITH" clause works correctly on all datasources:

      select CURDATE() as startDate
        from schema.table