ANSI / UPDATE generated from fails in teiid without limit 1
omarbkhan Nov 18, 2016 1:38 AMHi,
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