[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18029220#comment-18029220
]
Ruben Q L commented on CALCITE-7184:
------------------------------------
IIUC the existing functions were not modified, we have added some new
(equivalent) operators, and the function (BIT_AND) vs operator (&) differ due
to unsigned logic. The idea proposed by [~krooswu] is to continue working
incrementally, and in the long term the "old" functions shall be adjusted as
well to deal with unsigned parameters, and everything will be aligned them.
If I'm not missing anything, this may sound reasonable, there's no breaking
change (so far), and we can inform explicitly on the Release Note that the new
bitwise operator(s) and their equivalent existing functions can differ on
return type inference / operand checker.
What's your proposal [~mbudiu]? Revert/disable all the bitwise operators work
done in 1.41 (I guess CALCITE-7184, CALCITE-7109, CALCITE-6731), and then do
what? Carry out everything in one single release?
> Support for bitwise AND (&) operator in SQL
> -------------------------------------------
>
> Key: CALCITE-7184
> URL: https://issues.apache.org/jira/browse/CALCITE-7184
> Project: Calcite
> Issue Type: Improvement
> Reporter: krooswu
> Assignee: krooswu
> Priority: Minor
> Labels: pull-request-available
> Fix For: 1.41.0
>
>
> h3. *Overview*
>
> This task is to add comprehensive support for the bitwise AND operator
> ({{{}&{}}}) in SQL. This is part of a larger initiative to support a suite of
> bitwise operators (^, {{{}<<{}}}, {{{}&{}}}, {{{}>>{}}}). The ^ and {{<<}}
> operators are already implemented. The right-shift operator ({{{}>>{}}}) is
> currently blocked by a syntax conflict and will be addressed separately.
> ----
>
> h3. *Design Decision: Align with PostgreSQL*
>
> To ensure consistent and predictable behavior, the implementation of the
> {{&}} operator {*}will align with the behavior of PostgreSQL{*}. This
> decision applies to all aspects of the operator, including:
> * {*}Type Promotion Rules{*}: For {{INTEGER}} family types.
> * {*}Negative Number Handling{*}: Using two's complement.
> * {*}{{VARBINARY}} Type Semantics{*}: Mimicking PostgreSQL's behavior for
> bit/byte strings.
> This approach follows the precedent set by previous bitwise operator
> implementations in Calcite.
> ----
>
> h3. *Implementation Plan*
>
> * [ ] {*}Parser{*}: Extend {{SqlParser.jj}} to recognize the {{&}} operator
> with a precedence of 32.
> * [ ] {*}Operator Table{*}: Add a {{SqlBinaryOperator}} named
> {{BITAND_OPERATOR}} to {{{}SqlStdOperatorTable{}}}.
> * [ ] {*}Type System{*}: Implement return type inference that mirrors
> PostgreSQL's promotion rules for all supported types ({{{}TINYINT{}}},
> {{{}SMALLINT{}}}, {{{}INTEGER{}}}, {{{}BIGINT{}}}, {{{}VARBINARY{}}}).
> * [ ] {*}Relational Algebra{*}: Update {{SqlToRelConverter}} and
> {{RexBuilder}} to correctly translate the {{&}} operator into a
> {{{}RexCall{}}}.
> * [ ] {*}Code Generation{*}: Implement the final code generation logic.
> * [ ] {*}Unit Tests{*}: Add comprehensive tests covering:
> *
> ** Positive and negative integer operands.
> *
> ** Mixed-type operations (e.g., {{{}SMALLINT & BIGINT{}}}).
> *
> ** {{VARBINARY}} operands.
> *
> ** Edge cases (e.g., operations with {{{}0{}}}, {{{}-1{}}}).
> ----
> h3. *Target Behavior (Based on PostgreSQL)*
> * {*}Integer Examples{*}:
> *
> ** {{SELECT 5 & 3;}} → {{1}}
> *
> ** {{SELECT -5 & 3;}} → {{3}}
> *
> ** {{SELECT -5 & -3;}} → {{-7}}
> * {*}{{VARBINARY}} Types{*}: The operation on {{VARBINARY}} operands will
> follow PostgreSQL's bitwise AND semantics for binary strings.
> * UNSINGED TYPES: will support too
> ----
>
> h3. *Appendix: Cross-Database Survey* 📚
>
> _(This section serves as a reference for the original analysis.)_
> * {*}PostgreSQL{*}: *(Target Behavior)* Fully supports {{&}} for integer and
> bit string types. Uses two's complement.
> * {*}MySQL{*}: Supports {{{}&{}}}. Behavior for negative numbers can vary
> based on context (e.g., {{SELECT -5 & -3}} can return {{-7}} or a large
> unsigned integer).
> * {*}BigQuery{*}: Fully supports {{&}} with two's complement for integers
> and {{BYTES}} types.
> * {*}SQL Server{*}: Fully supports {{&}} with two's complement.
> * {*}Snowflake{*}: Fully supports {{&}} for numeric and binary types.
> * {*}Databricks{*}: Fully supports {{&}} with two's complement.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
