[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
krooswu updated CALCITE-7184:
-----------------------------
Description:
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;}}
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.
* {*}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 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 {{{}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 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:*
* {*}^ 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.
was:
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.
> 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;}}
> 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.
> * {*}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 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 {{{}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 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:*
> * {*}^ 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)
