Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2965

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

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Critical
    • 8.8, 8.7.1
    • 8.4
    • Query Engine
    • None

    Description

      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.

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            blaxell Alex K. (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: