[CALCITE-2638] Constant reducer must not duplicate calls to non-deterministic functions

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.17.0
• Fix Version/s: 1.18.0
• Component/s: core
• Assignee: Julian Hyde
• Reporter: Danny Chen
• Created: 24/Oct/18 05:51
• Updated: 13/Oct/21 09:52
• Resolved: 29/Oct/18 04:01

Description

When we have a dynamic function like

current_timestamp

in the query, calcite will skip constant reduction, for example, for query1: 

select sal, current_timestamp as t from emp

The plan after rule `ReduceExpressionsRule.PROJECT_INSTANCE` is

LogicalProject(SAL=[$5], T=[CURRENT_TIMESTAMP])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

This is as expect cause there is such code snippet in `ReducibleExprLocator#visitCall`:

// Even if all operands are constant, the call itself may
// be non-deterministic.
if (!call.getOperator().isDeterministic()) {   
  callConstancy = Constancy.NON_CONSTANT;
} else if (call.getOperator().isDynamicFunction()){   
  // We can reduce the call to a constant, but we can't   
  // cache the plan if the function is dynamic.   
  // For now, treat it same as non-deterministic.   
  callConstancy = Constancy.NON_CONSTANT; 
}

But for query2:

select sal, sal + 5, t from (select sal, current_timestamp as t from emp) 

we will get a plan with rule `ReduceExpressionsRule.PROJECT_INSTANCE` as:

+ plan before

LogicalProject(SAL=[$0], EXPR$1=[+($0, 5)], T=[$1])
  LogicalProject(SAL=[$5], T=[CURRENT_TIMESTAMP])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

+ plan after

LogicalProject(SAL=[$0], EXPR$1=[+($0, 5)], T=[CURRENT_TIMESTAMP])
  LogicalProject(SAL=[$5], T=[CURRENT_TIMESTAMP])
  LogicalTableScan(table=[[CATALOG, SALES, EMP]])

This is actually wrong cause `current_timestamp` is dynamic and we do not want to compute it again in the outer project.

The reason we did constant reduction is that: for query2, we will get a pulled up predicates with tool function `RexUtil.isConstant`, this function decide if the call is constant by invoking `SqlOperator#isDeterministic` which is default true here. It did not do the `isDynamicFunction()` decision just like `ReducibleExprLocator` and the reduction finally happened which handle by `RexExecutor`.

Personally i think we should keep sync in reduction logic for `inputRef` and `RexCall` and i reuse the `analyzeCall` and apply a patch here.

[pull-request](https://github.com/apache/calcite/pull/892)

 

Issue Links

• is related to: CALCITE-2683 ProjectMergeRule should not be performed when Nondeterministic udf has been referenced more than once
• is related to: CALCITE-4848 Adding a HAVING condition to a query with a dynamic parameter makes the result always empty