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

["Julian Hyde (Jira)" <ji...@apache.org>]

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

Julian Hyde edited comment on CALCITE-3760 at 1/30/20 7:22 PM:
---------------------------------------------------------------

SQL is a [strict language|https://en.wikipedia.org/wiki/Strict_programming_language] (with the exception of a few constructs such as CASE) but becomes non-strict when you add non-deterministic UDFs. As you point out, some of our rewrites assume strictness. It would be helpful if we had a 'let' construct, e.g. {{coalesce(e1, e2)}} becomes {{let v = e1 in case when x is not null then x else e2 end}}. It would ensure that expressions are evaluated the correct number of times. Without {{let}} or something similar I don't know how we could do these rewrites.

Related issues are re-ordering of the branches of AND and OR conditions, and behavior when an expression throws.


was (Author: julianhyde):
SQL is a [strict language|https://en.wikipedia.org/wiki/Strict_programming_language] (with the exception of a few constructs such as CASE) but becomes non-strict when you add non-deterministic UDFs. As you point out, some of our rewrites assume strictness. It would be helpful if we had a 'let' construct, e.g. {{coalesce(e1, e2)}} becomes {{let v = e1 in case when x is not null then x else e2 end}}. It would ensure that expressions are evaluated the correct number of times. Without {{let}} or something similar I don't know how we could do these rewrites.

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