incorrect pushdown in WITH clause for curdate() - Teiid 8.9.0-Beta1
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
