Yu Xu created CALCITE-7694:
------------------------------
Summary: RexSimplify should simplify comparisons involving RAND()
using its [0, 1) range
Key: CALCITE-7694
URL: https://issues.apache.org/jira/browse/CALCITE-7694
Project: Calcite
Issue Type: Improvement
Components: core
Affects Versions: 1.42.0
Reporter: Yu Xu
Assignee: Yu Xu
Fix For: 1.43.0
RAND() is a non-deterministic function that returns a DOUBLE value in the
half-open range [0, 1). Because it is non-deterministic, its result cannot be
constant-folded by the usual reduction rules, so predicates involving RAND()
survive into the physical plan and are evaluated row by row at runtime.
However, some of these predicates are decidable at planning time purely from
the known [0, 1) range of RAND(), regardless of the actual random value. For
example:
{code:java}
SELECT * FROM emp WHERE RAND() > 1.0 -- always false
SELECT * FROM emp WHERE RAND() >= 0 -- always true
SELECT * FROM emp WHERE RAND() = 5 -- always false {code}
Today RexSimplify leaves all of these untouched. An always-false predicate
should collapse the relation to empty, and an always-true predicate should let
the filter be removed entirely, avoiding a full scan and a per-row evaluation
of the random function. The same reasoning extends to linear arithmetic on a
single RAND() call, which appears after other rewrites or in generated
sampling SQL:
{code:java}
RAND() * 3 < 3 -- normalizes to RAND() < 1 -> always true
RAND() - 1 > 0 -- normalizes to RAND() > 1 -> always false
1 - RAND() > 1 -- normalizes to RAND() < 0 -> always false {code}
We can simplify these expressions.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
