[
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}}
BITAND_OPERATORin {{{}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.
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 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.
> 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}}
> BITAND_OPERATORin {{{}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)
