[
https://issues.apache.org/jira/browse/CALCITE-7694?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18102501#comment-18102501
]
Julian Hyde commented on CALCITE-7694:
--------------------------------------
Does anyone need this? It adds a lot of code so will inevitably make
RexSimplify slower and harder to maintain.
Most people who write use {{RAND()}} in a query do not want/expect their
expression to be optimized.
If you had left a gap between logging this issue and providing a PR I would
have saved you the effort.
> 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
> Priority: Major
> Labels: pull-request-available
> 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)
