[jira] [Updated] (CALCITE-7184) Support for the bitwise AND(&) in sql

["krooswu (Jira)" <ji...@apache.org>]

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

krooswu updated CALCITE-7184:
-----------------------------
    Summary: Support for the bitwise AND(&) in sql  (was: Add support for the & (bitwise AND) operator in Apache Calcite)

> Support for the bitwise AND(&) 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
>
> h3. *Overview*
> Add comprehensive support for the bitwise AND operator ({{{}&{}}}) in Apache Calcite, ensuring consistent behavior across various query engines, and following mainstream database semantics. This is part of a larger plan to support the ^ , {{{}<<{}}}, {{{}&{}}}, and {{>>}} operators, where ^ and {{<<}} have already been implemented, and the right shift operator ({{{}>>{}}}) is currently blocked by a conflict.
> h3. *Implementation Details*
> h4. *Core Components*
>  * {*}Parser Extension{*}: Extend the parser to recognize the {{&}} operator with appropriate precedence (32, matching standard logical operators).
>  * {*}Operator Definition{*}: Add {{SqlBinaryOperator}}
> BITAND_OPERATOR in {{{}SqlStdOperatorTable{}}}.
>  * {*}Integration{*}: Ensure support in {{{}SqlToRelConverter{}}}, {{{}RexBuilder{}}}, and code generation to properly handle the {{{}&{}}}operator.
>  * {*}Type System{*}: Implement type inference for all INTEGER family types (TINYINT, SMALLINT, INTEGER, BIGINT).
> h4. *Type Handling Strategy*
>  * {*}Return Type{*}: The return type for the {{&}} operator should follow the standard type promotion rules, ensuring the result type is compatible with the operands involved. This prevents overflow and ensures correct type handling across different operand types.
>  * {*}Mixed Type Promotion{*}: Operands will be promoted according to standard SQL type promotion rules, which generally promote the operands to the least restrictive common type to ensure safe operation.
>  * {*}Supported Types{*}:
>  * 
>  ** *INTEGER family types* (TINYINT, SMALLINT, INTEGER, BIGINT): The result type will be determined based on the promotion rules, ensuring that the least restrictive type is used.
>  * 
>  ** {*}Unsigned integers{*}: The bitwise operations will also support unsigned integers, promoting them in accordance with the standard rules for unsigned types in SQL.
>  * 
>  ** {*}VARBINARY types{*}: These will follow the behavior of BigQuery semantics, maintaining the original type when performing the bitwise operation.
>  *  
> h4. *Behavioral Semantics*
>  * {*}Negative Operand Handling{*}:
>  * 
>  ** Follows two’s complement rules for negative numbers in line with most query engines.
>  * 
>  ** {*}Example{*}: {{SELECT -5 & 3;}}
> In MySQL, PostgreSQL, and other engines, {{-5}} is represented in two’s complement. For {{{}-5 & 3{}}}, the result is {{3}} because of how the two’s complement and bitwise AND work.
> h4. *Cross-Database Bitwise AND Behavior Analysis*
>  * {*}MySQL{*}: Fully supports the {{&}} operator for integer types and automatically casts other types like strings to integers. Uses two’s complement for negative numbers.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{{}SELECT }}\{{{}-5 & 3{}}}{{{};{}}} returns 3.
>  * {*}PostgreSQL{*}: Fully supports {{&}} and follows two’s complement for negative numbers. PostgreSQL also allows {{{}BIT{}}}and {{VARBIT}} types for bitwise operations.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}} returns 3. Shifts are handled using modular arithmetic.
>  * {*}SQL Server{*}: Fully supports {{&}} and follows two’s complement for negative numbers. Requires explicit casting for non-integer operands.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}} returns 3.
>  * {*}BigQuery{*}: Fully supports {{&}} and uses two’s complement for negative numbers. Supports {{BYTES}} types for bitwise operations.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}} returns 3.
>  * {*}Snowflake{*}: Fully supports {{&}} and follows two’s complement for negative numbers. Supports {{BINARY}} and {{{}VARBINARY{}}}types.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}} returns 3.
>  * {*}Databricks (Apache Spark SQL){*}: Fully supports {{&}} and follows two’s complement for negative numbers. Requires explicit casting for non-integer operands.
>  * 
>  ** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}} returns {{{}3{}}}.
> h3. *Related Information:*
>  * {*}Background{*}: The {{&}} operator is commonly used for bitwise manipulation in SQL queries. However, the implementation and behavior of the operator vary across different query engines, especially in handling negative numbers, large shift counts, and non-integer operands. This feature is part of a broader plan to support multiple bitwise operators in Apache Calcite.
>  * {*}Survey Content{*}: The implementation of the {{&}} operator should be consistent with the behavior found in widely used query engines. The following engines have been analyzed:
>  * 
>  ** {*}MySQL{*}: Supports bitwise AND with integer types and follows two’s complement for negative numbers.
>  * 
>  ** {*}PostgreSQL{*}: Supports {{&}} with integer and binary types, uses modular arithmetic for large shifts.
>  * 
>  ** {*}SQL Server{*}: Supports bitwise AND and requires casting for non-integer types.
>  * 
>  ** {*}BigQuery{*}: Supports {{&}} with {{BYTES}} types and handles negative values with two's complement.
>  * 
>  ** {*}Snowflake{*}: Supports {{&}} with {{BINARY}} and {{VARBINARY}} types.
>  * 
>  ** {*}Databricks{*}: Supports {{&}} and requires casting for non-integer types.
> h3. *Plan for Supporting Other Operators:*
>  * {*}Right Shift Operator (>>) Conflict{*}: The implementation of the right shift operator ({{{}>>{}}}) is currently blocked due to a syntax conflict with the {{map >>}} operator. This issue needs to be resolved before implementing the {{>>}} operator as part of the current work on bitwise operators.
> h3. *Expected Outcomes:*
>  * Extend Calcite's SQL parser and operator table to include the {{&}} operator, ensuring proper type handling and integration into Calcite's relational algebra.
>  * Implement consistent behavior for negative shifts, operand type promotion, and overflow prevention.
>  * Ensure Calcite's behavior aligns with industry-standard practices, as observed in MySQL, PostgreSQL, SQL Server, BigQuery, Snowflake, and Databricks.
>  * Address the conflict preventing the implementation of the right shift ({{{}>>{}}}) operator in a future phase.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)