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

[Alex K. (JIRA)]

Alex K. created TEIID-2965:
------------------------------

             Summary: 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: Feature Request
    Affects Versions: 8.8
            Reporter: Alex K.
            Assignee: Steven Hawkins


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.



--
This message was sent by Atlassian JIRA
(v6.2.3#6260)