[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18029199#comment-18029199
]
krooswu commented on CALCITE-7184:
----------------------------------
yeah, you’re are right — the introduction of unsigned integer types has
introduced some divergence in type inference among the bitwise operators.
Specifically, cases such as {{INTEGER & ULONG}} now infer a {{ULONG}} result
type, whereas {{INTEGER & BIGINT}} continues to return {{{}BIGINT{}}}. This
change is intentional: unsigned operands follow a different promotion rule to
prevent sign-extension issues and to align with the behavior of systems like
MySQL and C/C++ when mixing signed and unsigned integers.
As a result, the current type inference and operand checking rules between
operators like {{{}BITAND{}}}, {{{}BITOR{}}}, and {{BITXOR}} may temporarily
differ. The inconsistency does not indicate an error in the current PR but
rather reflects an intermediate stage as we extend Calcite’s type system to
support unsigned semantics correctly.
Once the unsigned handling logic is fully validated and stable, I plan to
refactor the bitwise operator family to follow a unified type inference
strategy that correctly handles both signed and unsigned operands. This should
restore full consistency across all bitwise operators.
> 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)
