Sean Broeder created CALCITE-7716:
-------------------------------------
Summary: 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
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)
