[teiid-issues] [JBoss JIRA] (TEIID-2965) wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1

wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1 -------------------------------------------------------------------------- Key: TEIID-2965 URL: https://issues.jboss.org/browse/TEIID-2965 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.4 Reporter: Alex K. Assignee: Steven Hawkins Priority: Critical Fix For: 8.7.1, 8.8 Having created a PostgreSQL Table like: {code:sql} CREATE TABLE Test_Insert ( status character varying(4000) ) {code} And then running this statement {code:sql} INSERT INTO test_tables_pg.Test_Insert SELECT CASE WHEN (status = '0') AND (cnt > 0) THEN '4' ELSE status END AS status FROM (SELECT (SELECT COUNT(*) FROM test_tables_pg.test_a AS smh2) AS cnt, a AS status FROM test_tables_pg.test_a AS smh ) AS a {code} I get the following exception: {code} 16:18:34,427 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue16) ii4bbeYlV1WF TEIID30020 Processing exception for request ii4bbeYlV1WF.4 'TEIID30504 test_tables_pg: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh]'. Originally TeiidProcessingException 'ERROR: INSERT has more expressions than target columns Position: 74' QueryExecutorImpl.java:2157. Enable more detailed logging to see the entire stacktrace. 16:20:12,498 WARN [org.teiid.CONNECTOR] (Worker5_QueryProcessorQueue22) ii4bbeYlV1WF Connector worker process failed for atomic-request=ii4bbeYlV1WF.6.2.6: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: INSERT INTO "public"."test_insert" ("status") SELECT smh."a" AS status, (SELECT COUNT(*) FROM "public"."test_a" AS g_0) AS cnt FROM "public"."test_a" AS smh] at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:247) at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:79) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:359) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:326) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] 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.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.8.0.Alpha1.jar:8.8.0.Alpha1] 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: ERROR: INSERT has more expressions than target columns Position: 74 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.executeUpdate(AbstractJdbc2Statement.java:363) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493) at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:218) ... 14 more {code} It looks like the rewriting is completely wrong, since Teiid is trying to insert 2 fields into a single one. In order to reproduce the bug, it is necessary that test_a and the Test_Insert tables are both in the same schema, so that the full command can be pushed down. Moreover, the bug can be reproduced in PostgreSQL, Oracle, MS SQL Server, but it correctly works in MySQL.