[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18020159#comment-18020159
]
Mihai Budiu commented on CALCITE-7184:
--------------------------------------
Why not use the standard type promotions for other arithmetic operations, where
the wider type is used?
> 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 always be
> {{BIGINT}} ({{{}ReturnTypes.BIGINT_NULLABLE{}}}) to prevent overflow.
> * {*}Mixed Type Promotion{*}: All operands from the INTEGER family (TINYINT,
> SMALLINT, INTEGER, BIGINT) should be promoted to {{BIGINT}} to ensure safe
> operation.
> * {*}Supported Types{*}:
> *
> ** INTEGER family types (TINYINT, SMALLINT, INTEGER, BIGINT) – all results
> should be returned as {{{}BIGINT{}}}.
> *
> ** {{VARBINARY}} types should follow the behavior of BigQuery semantics,
> maintaining the original type.
> h4. *Behavioral Semantics*
> * {*}Negative Operand Handling{*}:
> *
> ** Follows two’s complement rules for negative numbers in line with most
> query engines.
> *
> ** {*}Example{*}: {{SELECT -5 & 3;}} returns {{1}} (binary:
> {{{}11111111111111111111111111111011 & 00000000000000000000000000000011 =
> 00000000000000000000000000000001{}}}).
> * {*}Shift Count Handling{*}:
> *
> ** {*}Negative Shift{*}: Should throw an {{IllegalArgumentException}} to
> maintain clarity and prevent undefined behavior, as some engines do not
> handle negative shifts consistently.
> *
> **
> *** {*}Example{*}: {{SELECT 8 & -1;}} – Throws an exception for safety and
> clarity.
> *
> ** {*}Large Operand Handling{*}: When the shift count exceeds the bit width
> (e.g., shifting more than 32 bits), return {{0}} to avoid unexpected results.
> *
> **
> *** {*}Example{*}: {{SELECT 1 & 40;}} – Returns {{0}} for INTEGER types.
> 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 {{{}1{}}}.
> * {*}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 {{{}1{}}}. 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 {{{}1{}}}.
> * {*}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 {{{}1{}}}.
> * {*}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 {{{}1{}}}.
> * {*}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 {{{}1{}}}.
> 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:*
> * {*}^ Operator{*}: Supported and integrated into Calcite's parser and
> operator table.
> * {*}<< Operator{*}: Supported and integrated into Calcite, following the
> same principles as the {{^}} operator.
> * {*}& Operator{*}: Currently under implementation, with focus on
> integration into Calcite's relational algebra.
> * {*}>> Operator (Right Shift){*}: The implementation of the right shift
> operator ({{{}>>{}}}) is blocked due to a conflict with existing shift
> operator handling. We will address this conflict in a future iteration after
> the implementation of {{{}&{}}}.
> 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)
