[
https://issues.apache.org/jira/browse/CALCITE-7651?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18096243#comment-18096243
]
Julian Hyde commented on CALCITE-7651:
--------------------------------------
Good catch, [~zrlpar]! Let's do the right thing, even if it means breaking our
previous behavior. I agree that little-endian seems right, and Postgres almost
always makes the right choice when specifying things.
Also check Snowflake and Oracle, but don't bother with MySQL (they do the right
thing only 50% of the time).
Assuming we're going with Postgres, please include a few SQL examples from
Postgres (for both shift and cast) in the description. We should be absolutely
clear what our behavior is, and that we are identical to Postgres.
> Unify endianness semantics for bitwise shift (<<, >>) on BINARY/VARBINARY
> -------------------------------------------------------------------------
>
> Key: CALCITE-7651
> URL: https://issues.apache.org/jira/browse/CALCITE-7651
> Project: Calcite
> Issue Type: Task
> Reporter: Niels Pardon
> Priority: Major
>
> CALCITE-7109 added bitwise left shift ({{<<}} / {{LEFTSHIFT}}) over
> {{BINARY}}/{{VARBINARY}}, and CALCITE-7639 adds the right-shift counterparts.
> The binary implementation in {{SqlFunctions.leftShift(byte[], long)}} treats
> the byte array as a *little-endian* bit string (index 0 = least-significant
> byte) and normalizes the shift amount modulo {{8 × length}}.
> This is inconsistent with CALCITE-7368, which made {{CAST(<integer> AS
> BINARY)}} *big-endian*. As a result {{CAST(x AS BINARY(N)) << k}} !=
> {{CAST((x << k) AS BINARY(N))}}, and e.g. {{RIGHTSHIFT(X'1234', 4)}} yields
> {{4103}} (little-endian) rather than the big-endian {{0123}}.
> The "PostgreSQL behavior" cited in the current code comments is inaccurate:
> PostgreSQL does not define {{<<}}/{{>>}} on {{bytea}} — only on bit strings
> ({{bit}}/{{bit varying}}), where the semantics are big-endian,
> length-preserving, zero-filled, and do *not* modulo-wrap the shift amount.
> MySQL's {{<<}}/{{>>}} operate on the integer value of their operands, not
> element-wise on bytes.
> *Proposal:* decide a single documented semantics for binary shifts and apply
> it to both {{<<}} and {{>>}}. Big-endian (matching CALCITE-7368 and
> PostgreSQL bit strings) is the recommended choice, ideally so that {{CAST(x
> AS BINARY(N)) << k = CAST((x << k) AS BINARY(N))}} holds for in-range shift
> amounts. Because binary {{<<}} shipped in 1.41.0/1.42.0, changing its
> endianness is a behavioral change that needs a compatibility note.
> *Scope note:* binary {{>>}}/{{RIGHTSHIFT}} was descoped from CALCITE-7639
> pending this decision — that PR ships only the integer/unsigned right shift
> and rejects binary operands at validation. Binary left shift remains as
> released.
> Related: CALCITE-7368, CALCITE-7109, CALCITE-7639, CALCITE-5087.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
