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

[dalex dalex (JIRA)]

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

dalex dalex commented on TEIID-4740:
------------------------------------

[~shawkins] Then such question: the first query works correctly (when all tables are within one data source). Can the behavior be spread when we have tables from another data sources? You say that If there is no key, then we can't do row by row updates but how does it work in the first query? I'm trying just to understand the logic.

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