[teiid-issues] [JBoss JIRA] (TEIID-4423) UPDATE command with non-pushdown functions leads to TEIID30253 error

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-4423?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-4423.
-----------------------------------
    Resolution: Done


Changed the logic to consistently check for non-deterministic expressions - we'll now only deeply check in uncorrelated subqueries.  Also add a release note and a doc note.

So now:

update test.textagg_test_2 set id =(select uuid()) where id = 'xxxx';

will evaluate to a single id value.

update test.textagg_test_2 set id =uuid() where id = 'xxxx';

will set an id per row, which in this case without a key will result in an error.

> UPDATE command with non-pushdown functions leads to TEIID30253 error
> --------------------------------------------------------------------
>
>                 Key: TEIID-4423
>                 URL: https://issues.jboss.org/browse/TEIID-4423
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.x
>         Environment: teiid-8.12-Beta1 on Red Hat JBoss Enterprise Application Platform - Version 6.3.0.GA
> teiid-9.0.3 on WildFly Full 9.0.2.Final
>            Reporter: dalex dalex
>            Assignee: Steven Hawkins
>             Fix For: 9.1
>
>
> Running the following queries:
> {code:sql}
> update test.textagg_test_2 set id =(select uuid());;
> {code}
> or
> {code:sql}
> update test.textagg_test_2 set id =(select rand());;
> {code}
> leads to the following errors:
> {code}
> TEIID30253 Source UPDATE or DELETE command "UPDATE test.textagg_test_2 SET id = (SELECT uuid() 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. 
> {code}
> and
> {code}
> TEIID30253 Source UPDATE or DELETE command "UPDATE test.textagg_test_2 SET id = convert((SELECT rand() LIMIT 2), string)" 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. 
> {code}
> though the following query:
> {code:sql}
> update test.textagg_test_2 set id =(select now());;
> {code}
> works without errors.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)