[
https://issues.apache.org/jira/browse/CALCITE-7826?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-7826:
---------------------------------
Description:
{{IS NOT DISTINCT FROM}} compares {{DECIMAL}} values using {{equals}}, so
values with different scales are distinct.
In the Enumerable convention, {{IS NOT DISTINCT FROM}} and {{IS DISTINCT FROM}}
are implemented by {{RexImpTable.DistinctFromImplementor}}, which compares
non-null operands using {{{}Objects.equals{}}}, and does not harmonize the
operand types (\{{harmonize}} is {{false}}). For {{java.math.BigDecimal}},
{{equals}} is sensitive to scale, so 1.10 and 1.1 are distinct values. SQL's
{{=}} operator compares the same values numerically, and finds them equal. So
for non-null {{DECIMAL}} operands, {{x IS NOT DISTINCT FROM y}} and {{x = y}}
can disagree.
A query parsed from SQL is not affected.
{{StandardConvertletTable.convertIsDistinctFrom}} expands {{IS [NOT] DISTINCT
FROM}} using {{RelOptUtil.isDistinctFrom}}, so the plan uses {{IS NULL}} and
{{=}} on operands that have been cast to a common type; for example, {{a IS NOT
DISTINCT FROM b}} where {{a}} is {{DECIMAL(3, 2)}} and {{b}} is {{DECIMAL(2,
1)}} becomes {{OR(AND(IS NULL(a), IS NULL(b)), IS TRUE(=(a, CAST(b):DECIMAL(3,
2))))}}. The {{IS_NOT_DISTINCT_FROM}} operator reaches
{{DistinctFromImplementor}} only if a plan calls it directly, for example using
{{RelBuilder.call(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM, a, b)}}.
was:
IS NOT DISTINCT FROM compares DECIMAL values using equals, so values with
different scales are distinct.
In the Enumerable convention, {{IS NOT DISTINCT FROM}} and {{IS DISTINCT FROM}}
are implemented by {{{}RexImpTable.DistinctFromImplementor{}}}, which compares
non-null operands using {{{}Objects.equals{}}}, and does not harmonize the
operand types (`harmonize` is `false`). For {{{}java.math.BigDecimal{}}},
{{equals}} is sensitive to scale, so 1.10 and 1.1 are distinct values. SQL's
{{=}} operator compares the same values numerically, and finds them equal. So
for non-null {{DECIMAL}} operands, {{x IS NOT DISTINCT FROM y}} and {{x = y}}
can disagree.
A query parsed from SQL is not affected.
{{StandardConvertletTable.convertIsDistinctFrom}} expands {{IS [NOT] DISTINCT
FROM}} using {{{}RelOptUtil.isDistinctFrom{}}}, so the plan uses {{IS NULL}}
and {{=}} on operands that have been cast to a common type; for example, {{a IS
NOT DISTINCT FROM b}} where {{a}} is {{DECIMAL(3, 2)}} and {{b}} is
{{DECIMAL(2, 1)}} becomes {{OR(AND(IS NULL(a), IS NULL(b)), IS TRUE(=(a,
CAST(b):DECIMAL(3, 2))))}}The {{IS_NOT_DISTINCT_FROM}} operator reaches
{{DistinctFromImplementor}} only if a plan calls it directly, for example using
{{{}RelBuilder.call(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM, a, b){}}}.
> IS NOT DISTINCT FROM gives wrong result in Enumerable convention when applied
> to DECIMAL values of different scales
> -------------------------------------------------------------------------------------------------------------------
>
> Key: CALCITE-7826
> URL: https://issues.apache.org/jira/browse/CALCITE-7826
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Priority: Major
>
> {{IS NOT DISTINCT FROM}} compares {{DECIMAL}} values using {{equals}}, so
> values with different scales are distinct.
> In the Enumerable convention, {{IS NOT DISTINCT FROM}} and {{IS DISTINCT
> FROM}} are implemented by {{RexImpTable.DistinctFromImplementor}}, which
> compares non-null operands using {{{}Objects.equals{}}}, and does not
> harmonize the operand types (\{{harmonize}} is {{false}}). For
> {{java.math.BigDecimal}}, {{equals}} is sensitive to scale, so 1.10 and 1.1
> are distinct values. SQL's {{=}} operator compares the same values
> numerically, and finds them equal. So for non-null {{DECIMAL}} operands, {{x
> IS NOT DISTINCT FROM y}} and {{x = y}} can disagree.
> A query parsed from SQL is not affected.
> {{StandardConvertletTable.convertIsDistinctFrom}} expands {{IS [NOT] DISTINCT
> FROM}} using {{RelOptUtil.isDistinctFrom}}, so the plan uses {{IS NULL}} and
> {{=}} on operands that have been cast to a common type; for example, {{a IS
> NOT DISTINCT FROM b}} where {{a}} is {{DECIMAL(3, 2)}} and {{b}} is
> {{DECIMAL(2, 1)}} becomes {{OR(AND(IS NULL(a), IS NULL(b)), IS TRUE(=(a,
> CAST(b):DECIMAL(3, 2))))}}. The {{IS_NOT_DISTINCT_FROM}} operator reaches
> {{DistinctFromImplementor}} only if a plan calls it directly, for example
> using {{RelBuilder.call(SqlStdOperatorTable.IS_NOT_DISTINCT_FROM, a, b)}}.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
