[jira] [Commented] (CALCITE-7716) BETWEEN/range predicates on UUID literals give wrong results because RexSimplify orders bounds using java.util.UUID#compareTo (signed comparison)

["Mihai Budiu (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-7716?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18106086#comment-18106086 ] 

Mihai Budiu commented on CALCITE-7716:
--------------------------------------

What do other SQL engines do about UUID comparisons?

> 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)