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

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

How would you push this down across the databases?  

If there is no key, then you can't do row by row updates.  It seems that you would have to take some other approach like creating an analogous temporary table on the test source to process the update.  In any case that is a new feature.

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