[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=13358309#comment-13358309 ] 

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

This is not a correctness issue.  If you issue an update with a predicate that cannot be pushed we can compensate for that by creating a "for each row" plan to perform the modification off of select.  However that is only possible if the rows can be uniquely identified.  This will need to be rejected unless there is something else you see going on here.

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