[ 
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated CALCITE-7184:
------------------------------------
    Labels: pull-request-available  (was: )

> 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
>
> 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)

Reply via email to