4 Replies Latest reply on Sep 25, 2014 10:36 AM by Alex K.

    wrong rewriting on "insert into ... select" statement - Teiid 8.8.0.Alpha1

    Alex K. Newbie

      Having created a PostgreSQL Table like:

       

      CREATE TABLE Test_Insert
      (
        status character varying(4000)
      )
      
      
      

       

      And then running this statement

      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
      

       

      I get the following exception:

       

      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
      

       

      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.