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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-2965?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins updated TEIID-2965:
----------------------------------

           Issue Type: Bug  (was: Feature Request)
        Fix Version/s: 8.7.1
                       8.8
             Priority: Critical  (was: Major)
    Affects Version/s: 8.4
                           (was: 8.8)
          Component/s: Query Engine


> 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.



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