[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18029067#comment-18029067
]
Mihai Budiu commented on CALCITE-7184:
--------------------------------------
The reason I didn't merge that PR is that there is still a question I was
hoping to get an answer to.
Here is the question:
{quote}Clearly, BITAND and BITAND_OPERATOR should have the same behavior.
But I was asking whether the type checking rules match the other bitwise
operators, such as BITXOR and BITOR.
Can you also please answer this question?
What do you think we should do about the fact that BITXOR_OPERATOR has a
different return type inference and a different operandTypeChecker?
One of the two must be wrong.
(I am not asking necessarily for a fix in this PR, I just want to understand
what you think should be the strategy to align them).
{quote}
> 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)
