[
https://issues.apache.org/jira/browse/CALCITE-3760?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17027214#comment-17027214
]
Julian Hyde commented on CALCITE-3760:
--------------------------------------
I wasn't aware of Couchbase and N1QL's {{LET}}. Thanks for sharing.
That said, for these purposes we don't need to add {{LET}} to SQL or even to
the {{SqlNode}} language. It would be sufficient to add it to the {{RexNode}}
language.
And in fact we already have {{RexProgram}}, which allows you to define several
expressions based on temporary expressions. We don't use {{RexProgram}} very
much these days, because it is just a little harder to write transformation
rules against a {{RexProgram}} than against a list of {{RexNode}}. On
reflection, I think history would repeat itself, and adding variables would
complicate too many places.
So, maybe the best way is to use a Project on a Project:
{noformat}
select coalesce(udf(c), 100)
from foo
{noformat}
becomes
{noformat}
select case when x is not null then x else 100 end
from (
select udf(c)
from foo)
{noformat}
As we discussed recently, it would be illegal to merge those Projects because
of the UDF. So the udf would be called exactly once per row.
> 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
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> 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)
