[IGNITE-18931] Sql. Invalid expression code generation for comparing the results of a non-deterministic function.

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: calcite2-required calcite3-required ignite-3
• Assignee: Unassigned
• Reporter: Pavel Pereslegin
• Created: 01/Mar/23 12:00
• Updated: 01/Mar/23 15:37

Description

Currently expressions like "expr() = expr()" and "expr() != expr()" for non-deterministic functions (for example rand_uuid()) do not work correctly.

The problem seems to be related to the optimization made in RexProgramBuilder#registerInternal, ignoring the fact that the function might be non-deterministic.

final RexSimplify simplify =
        new RexSimplify(rexBuilder, RelOptPredicateList.EMPTY, RexUtil.EXECUTOR);
    expr = simplify.simplifyPreservingType(expr);

This optimization translates comparison
<>($t1, $t1)
to 
CAST(AND(null, IS NULL($t1))):BOOLEAN NOT NULL

The first expression can be translated into two function calls, but the second expression unambiguously makes only one call.

And it looks like a naive fix would be to apply this optimization only if RexUtil.isDeterministic(rexNode) returns true.

RexProgramBuilder currently is not a part of Ignite-3 codebase and it seems that it will be better to do such fix in Apache Calcite.

However, before doing this, we need to learn the discussions that have been made on this topic previously.

CALCITE-2823
CALCITE-3760
CALCITE-2142
CALCITE-2638
CALCITE-3531
CALCITE-4256

Issue Links

• relates to: IGNITE-18801 Sql. UUID. Add an SQL function that generates a random UUID.