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