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

[dalex dalex (JIRA)]

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

dalex dalex commented on TEIID-4423:
------------------------------------

[~shawkins] I got it, so if we have a query with update command and we try to use a non-deterministic function on a field which is not unique (primary key) we will get the TEIID30253 error message because having not unique fields means deterministic behavior. Correct? Another way to treat it is to use, for this case, pushdown support for the uuid function like moving the treatment on MySQL level.

> In general each database has different rules for handling non-deterministic expressions. We are similar to H2, but it looks like you are expecting Postgres style handling such that uncorrelated subqueries are always treated as deterministic.

Didn't know about it. Interesting.

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