[jira] [Commented] (CALCITE-3760) Rewriting non-deterministic function can break query semantics

["Rui Wang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17026927#comment-17026927 ] 

Rui Wang commented on CALCITE-3760:
-----------------------------------

It makes sense.

Regarding to the UDF/UDAF, as it is user defined, usually we cannot control what users really write as code. Sometimes even if users tell us the UDF is deterministic, it might be just not. In this case, adding a parameter for users do not solve the problem from root.

In production practice on my side, we usually just build a contract with users: say we expect your UDF satisfies A, B and C. If your UDF does not satisfy those properties, the query result will be unpredictable.

> Rewriting non-deterministic function can break query semantics
> --------------------------------------------------------------
>
>                 Key: CALCITE-3760
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3760
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>            Reporter: Jin Xing
>            Assignee: Jin Xing
>            Priority: Major
>
> Calcite rewrite some *SqlFunctions* during validation. But whether the function is deterministic is not considered. For a non-deterministic operator, the rewriting can break semantics. Additionally there's no interface for user to specify the determinism for a UDF/UDAF. 
> Say I have non-deterministic UDF & UDAF and run sql like below
> {code:java}
> select coalesce(udf(col0), 100) from foo;
> select nullif(udaf(col0), 1024) from foo;{code}
> They will be rewritten as
> {code:java}
> select case when udf(col0) is not null then udf(col0) else 100 end
> from foo;
> select case when udaf(col0)=1024 then null udaf(col0)
> from foo{code}
> As we can see that non-deterministic UDF & UDAF are called multiple times after written. Thus the condition in WHEN clause might NOT be held all the time.
> We need to provide an interface for user to specify the determinism in UDF/UDAF and consider whether a SqlNode is deterministic when rewriting.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)