[CALCITE-3760] Rewriting non-deterministic function can break query semantics

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: core
• Labels: pull-request-available
• Assignee: Jin Xing
• Reporter: Jin Xing
• Created: 30/Jan/20 13:50
• Updated: 13/May/22 01:42

Description

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

select coalesce(udf(col0), 100) from foo;
select nullif(udaf(col0), 1024) from foo;

They will be rewritten as

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

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.

Issue Links

• is related to: CALCITE-2348 Handling non-deterministic operator in rules
• links to: GitHub Pull Request #1777