[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020162#comment-18020162
]
krooswu commented on CALCITE-7184:
----------------------------------
sorry, just updated the desc.
> Add support for the & (bitwise AND) operator in Apache Calcite
> --------------------------------------------------------------
>
> 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 BITWISE_AND}} 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)
