2 Replies Latest reply on Nov 19, 2016 1:56 PM by omarbkhan

    ANSI / UPDATE generated from fails in teiid without limit 1

    omarbkhan
    omarbkhan Nov 18, 2016 1:38 AM

      Hi,


      I made a copy of the status table to test an update with, and I was able to get an update in postgres for teiid if I included limit 1.

       

      Does teiid support UPDATE with JOINs in SQL?  

       

      In postgresql it probably was just coinidental that it joined to all of the rows okay.

       

      I did try the same approach with a large Oracle table, and it just cherry picked the top result and updated all of the records to a single instead of joining on them a postgres did. I'm not sure why?

       

      UPDATE source.statusbackup SET targetname =

      (

         SELECT

           source.status.targetname

         FROM source.status

         WHERE

           source.status.name = source.statusbackup.name

          limit 1

      )

      WHERE EXISTS

      (

         SELECT *

         FROM source.status

         WHERE

         source.status.name = source.statusbackup.name

      )

       

      If I don't include the limit 1 in postgres what teiid generates succeeds in postgresql but fails in teiid?

       

      UPDATE "public"."statusbackup" SET "targetname" = (SELECT g_0."targetname" AS c_0 FROM "public"."status" AS g_0 WHERE g_0."name" = "public"."statusbackup"."name") WHERE EXISTS (SELECT 1 AS c_0 FROM "public"."status" AS g_0 WHERE g_0."name" = "public"."statusbackup"."name")

       

      details of error without limit 1:

      00:08:55,263 WARN  [org.teiid.CONNECTOR] (Worker6_QueryProcessorQueue73) YFRvhkzv/xHm Connector worker process failed for atomic-request=YFRvhkzv/xHm.17.0.30: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11013:TEIID11004 Error executing statement(s): [SQL: UPDATE "public"."statusbackup" SET "targetname" = (SELECT g_0."targetname" AS c_0 FROM "public"."status" AS g_0 WHERE g_0."name" = "public"."statusbackup"."name") WHERE EXISTS (SELECT 1 AS c_0 FROM "public"."status" AS g_0 WHERE g_0."name" = "public"."statusbackup"."name")]

        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:293)

        at org.teiid.translator.jdbc.JDBCUpdateExecution.execute(JDBCUpdateExecution.java:84)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem$1.execute(ConnectorWorkItem.java:403)

        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:365)

        at sun.reflect.GeneratedMethodAccessor93.invoke(Unknown Source)

        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

        at java.lang.reflect.Method.invoke(Method.java:498)

        at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

        at com.sun.proxy.$Proxy57.execute(Unknown Source)

        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)

        at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:394)

        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)

        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)

        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

        at java.lang.Thread.run(Thread.java:745)

      Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2458)

        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2158)

        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:291)

        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:432)

        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:358)

        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:305)

        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:291)

        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:269)

        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:249)

        at org.jboss.jca.adapters.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:414)

        at org.teiid.translator.jdbc.JDBCUpdateExecution.executeTranslatedCommand(JDBCUpdateExecution.java:207)

        ... 27 more

      Caused by: org.postgresql.util.PSQLException: ERROR: more than one row returned by a subquery used as an expression

        ... 38 more

      • 232 Views
      • Tags:
        • 1. Re: ANSI / UPDATE generated from fails in teiid without limit 1
          shawkins
          shawkins Nov 18, 2016 11:18 AM (in response to omarbkhan)

          > Does teiid support UPDATE with JOINs in SQL? 

           

          Unfortunately no. A Teiid update does not directly support a from clause / join, just the basic SET / WHERE clause.

           

          > If I don't include the limit 1 in postgres what teiid generates succeeds in postgresql but fails in teiid?

           

          As entered you are setting a value to the result of a scalar subquery, then the WHERE clause applies to the UPDATE.  If that scalar subquery (SELECT source.status.targetname ...) returns more than 1 row, then you would expect it to fail - which is exactly what posgresql is complaining about "ERROR: more than one row returned by a subquery used as an expression"

           

          I'm not sure what you mean about seeing different behavior, as you would have to enter the query different for postgresql to interpret it differently.

           

          You can log an issue to support so form of update with from/join or a common table to address this.

            • Actions
          • 2. Re: ANSI / UPDATE generated from fails in teiid without limit 1
            omarbkhan
            omarbkhan Nov 19, 2016 1:56 PM (in response to shawkins)

            Thanks Steven!


            I created a JIRA issue for this [TEIID-4591] support UPDATE SQL JOIN / FROM - JBoss Issue Tracker

             

            I also do have an inefficient workaround by simply generating the SQL UPDATE text from a select. It's not ideal as there are multiple steps that would need to be done for the update. My logic is as follows:

             

            SELECT

            DISTINCT 'UPDATE table SET TARGETCOLUMN=''' ||  value1 || ''' WHERE FILTERCOLUMN=''' || value2 || ''';' AS SQL_UPDATE

            FROM TABLE1  as a

            INNER JOIN  TABLE2 ON

            (

               a.id = b.id

            )

             

            Then just grab the output and run the individual updates.

              • Actions