[
https://issues.apache.org/jira/browse/CALCITE-7716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18106103#comment-18106103
]
Sean Broeder commented on CALCITE-7716:
---------------------------------------
I have tested Postgres and it treats UUID as unsigned, so
{code:java}
SELECT
CAST('8ba7b810-9dad-11d1-80b4-00c04fd430c8' AS UUID)
BETWEEN CAST('00000000-0000-0000-0000-000000000000' AS UUID)
AND CAST('ffffffff-ffff-ffff-ffff-ffffffffffff' AS UUID) AS
mid_between_min_and_max;
{code}
and
{code:java}
SELECT
CAST('ffffffff-ffff-ffff-ffff-ffffffffffff' AS UUID)
> CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS
max_gt_min;
{code}
both return true.
Whereas
{code:java}
SELECT
CAST('ffffffff-ffff-ffff-ffff-ffffffffffff' AS UUID)
< CAST('00000000-0000-0000-0000-000000000000' AS UUID) AS
max_lt_min;
{code}
returns false.
> BETWEEN/range predicates on UUID literals give wrong results because
> RexSimplify orders bounds using java.util.UUID#compareTo (signed comparison)
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7716
> URL: https://issues.apache.org/jira/browse/CALCITE-7716
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.39.0
> Reporter: Sean Broeder
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
>
> A BETWEEN predicate (or any conjunction of two comparisons on the same
> operand that RexSimplify merges into a range) is evaluated incorrectly when
> the operand is of type UUID and the literal bounds straddle a UUID whose
> most-significant 64 bits have the sign bit set.
> Can be reproduced by:
> SELECT u,
> u BETWEEN UUID '00000000-0000-0000-0000-000000000000'
> AND UUID 'ffffffff-ffff-ffff-ffff-ffffffffffff' AS in_range
> FROM (VALUES (UUID '8ba7b810-9dad-11d1-80b4-00c04fd430c8')) AS t(u)
> Expected: IN_RANGE = true — every UUID lies within
> [00000000-...-000000000000, ffffffff-...-ffffffffffff], the full range of
> representable UUIDs.
> Actual: IN_RANGE = false. The plan shows the whole predicate folded to a
> constant:
> EnumerableCalc(expr#0=[{inputs}], expr#1=[false], proj#0..1=[{exprs}])
> EnumerableValues(tuples=[[{ 8ba7b810-9dad-11d1-80b4-00c04fd430c8 }]])
> The root cause is that RexLiteral stores a UUID value's literal directly as a
> java.util.UUID object. RexSimplify#processRange (which merges x >= lo AND x
> <= hi-shaped terms into a Range) determines whether the resulting range is
> empty via v0.compareTo(r.lowerEndpoint()) / v0.compareTo(r.upperEndpoint()),
> relying on the literal type's natural Comparable ordering.
> java.util.UUID#compareTo compares mostSigBits/leastSigBits as signed longs (a
> long-documented JDK quirk — see JDK-7025832
> (https://bugs.openjdk.org/browse/JDK-7025832)), not as unsigned values. For
> 'ffffffff-ffff-ffff-ffff-ffffffffffff', mostSigBits == -1L (signed), so it
> compares as less than '00000000-0000-0000-0000-000000000000' (mostSigBits ==
> 0L) — the reverse of the ordering SQL comparison operators (and every other
> part of the UUID feature) are expected to use. processRange concludes the
> range [lo, hi] is empty and short-circuits the whole conjunction to literal
> false, regardless of the actual runtime value of x.
> This is a general problem, not specific to BETWEEN's two-term case — anywhere
> Calcite's simplification/range machinery (RexSimplify, Sarg/RangeSets) relies
> on a UUID literal's natural ordering, results will be wrong for any pair of
> UUID literals whose most-significant-bit sign disagrees under
> signed-vs-unsigned interpretation (i.e., whenever one bound's first hex digit
> is in 0-7 and the other's is in 8-f).
> Test case demonstrating the bug (schema-free, drops into
> core/src/test/java/org/apache/calcite/test/, e.g. alongside JdbcTest):
> class UuidBetweenBugTest {
> @Test
> void uuidWithHighBitSetIsWronglyExcludedFromFullRangeBetween() {
> CalciteAssert.that()
> .query(
> "select u,\n"
> + " u between UUID
> '00000000-0000-0000-0000-000000000000'\n"
> + " and UUID 'ffffffff-ffff-ffff-ffff-ffffffffffff'
> as in_range\n"
> + "from (values (UUID
> '8ba7b810-9dad-11d1-80b4-00c04fd430c8')) as t(u)")
> // Expected: IN_RANGE=true (every UUID is within [min, max]).
> // Actual: IN_RANGE=false, because Calcite's constant-folding
> decided the range
> // [00000000-...-000000000000, ffffffff-...-ffffffffffff]
> is empty.
> .returns("U=8ba7b810-9dad-11d1-80b4-00c04fd430c8; IN_RANGE=true\n");
> }
> @Test
> void plansToAConstantFalseInsteadOfEvaluatingTheComparison() {
> CalciteAssert.that()
> .query(
> "select u,\n"
> + " u between UUID
> '00000000-0000-0000-0000-000000000000'\n"
> + " and UUID 'ffffffff-ffff-ffff-ffff-ffffffffffff'
> as in_range\n"
> + "from (values (UUID
> '8ba7b810-9dad-11d1-80b4-00c04fd430c8')) as t(u)")
> // The plan should contain a real comparison (or at worst a
> folded-true constant);
> // instead the whole predicate is folded to a bare `false` literal.
> .explainContains("expr#1=[false]");
> }
> }
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
