[teiid-issues] [JBoss JIRA] (TEIID-4740) Update statement doesn't work correctly for data sources in some cases

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4740?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13358537#comment-13358537 ] 

Steven Hawkins commented on TEIID-4740:
---------------------------------------

The entire update is pushed to the source for processing, when it is to the same source.  A compensating action is not required.

Spanning two sources however we need to compensate for the lack of being pushed down completely.  The default compensation logic uses row by row processing, but that is only applicable when there is a key.  This is different case.

> Update statement doesn't work correctly for data sources in some cases
> ----------------------------------------------------------------------
>
>                 Key: TEIID-4740
>                 URL: https://issues.jboss.org/browse/TEIID-4740
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 9.0.3
>         Environment: teiid-9.0.3 on WildFly Full 9.0.2.Final (WildFly Core 1.0.2.Final)
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>            Priority: Blocker
>
> Running the following query:
> {code:sql}
> UPDATE test_int.contacttest
> SET test_int.contacttest.salutation = ( 
>  select 
>  test_int.updatetest.prefix
>  from test_int.updatetest
>  where test_int.updatetest.id = test_int.contacttest.id
>  );
> {code}
> works correctly changing salutation field value according to where condition (note that contacttest and updatetest table are in the same test_int data source). But the following query:
> {code:sql}
> UPDATE test.contacttest
> SET test.contacttest.salutation = ( 
>  select 
>  test_int.updatetest.prefix
>  from test_int.updatetest
>  where test_int.updatetest.id = test.contacttest.id
>  );
> {code}
>  will fail as we try to update "contacttest" table of "test" data source from another "test_int" data source using "updatetest" table in where condition. The query fails showing the following error message:
> {code}
> Error: TEIID30253 Remote org.teiid.api.exception.query.QueryPlannerException: TEIID30253 Source UPDATE or DELETE command "UPDATE test.contacttest SET salutation = (SELECT test_int.updatetest.prefix FROM test_int.updatetest WHERE test_int.updatetest.id = test.contacttest.id LIMIT 2)" contains non-pushdown constructs and no compensating action can be taken as the table lacks a unique key or the source does not support equality predicates. 
> SQLState:  50000
> ErrorCode: 30253
> {code}



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)