[
https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
krooswu updated CALCITE-7184:
-----------------------------
Description:
h3. *Overview*
This task is to add comprehensive support for the bitwise AND operator
({{{}&{}}}) in SQL. This is part of a larger initiative to support a suite of
bitwise operators ({{{}^{}}}, {{{}<<{}}}, {{{}&{}}}, {{{}>>{}}}). The {{^}} and
{{<<}} operators are already implemented. The right-shift operator ({{{}>>{}}})
is currently blocked by a syntax conflict and will be addressed separately.
----
h3. *Design Decision: Align with PostgreSQL* 👑
To ensure consistent and predictable behavior, the implementation of the {{&}}
operator {*}will align with the behavior of PostgreSQL{*}. This decision
applies to all aspects of the operator, including:
* {*}Type Promotion Rules{*}: For {{INTEGER}} family types.
* {*}Negative Number Handling{*}: Using two's complement.
* {*}{{VARBINARY}} Type Semantics{*}: Mimicking PostgreSQL's behavior for
bit/byte strings.
This approach follows the precedent set by previous bitwise operator
implementations in Calcite.
----
h3. *Implementation Plan* ✅
* [ ] {*}Parser{*}: Extend {{SqlParser.jj}} to recognize the {{&}} operator
with a precedence of 32.
* [ ] {*}Operator Table{*}: Add a {{SqlBinaryOperator}} named
{{BITAND_OPERATOR}} to {{{}SqlStdOperatorTable{}}}.
* [ ] {*}Type System{*}: Implement return type inference that mirrors
PostgreSQL's promotion rules for all supported types ({{{}TINYINT{}}},
{{{}SMALLINT{}}}, {{{}INTEGER{}}}, {{{}BIGINT{}}}, {{{}VARBINARY{}}}).
* [ ] {*}Relational Algebra{*}: Update {{SqlToRelConverter}} and
{{RexBuilder}} to correctly translate the {{&}} operator into a {{{}RexCall{}}}.
* [ ] {*}Code Generation{*}: Implement the final code generation logic.
* [ ] {*}Unit Tests{*}: Add comprehensive tests covering:
** Positive and negative integer operands.
** Mixed-type operations (e.g., {{{}SMALLINT & BIGINT{}}}).
** {{VARBINARY}} operands.
** Edge cases (e.g., operations with {{{}0{}}}, {{{}-1{}}}).
----
h3. *Target Behavior (Based on PostgreSQL)*
* {*}Integer Examples{*}:
** {{SELECT 5 & 3;}} → {{1}}
** {{SELECT -5 & 3;}} → {{3}}
** {{SELECT -5 & -3;}} → {{-7}}
* {*}{{VARBINARY}} Types{*}: The operation on {{VARBINARY}} operands will
follow PostgreSQL's bitwise AND semantics for binary strings.
----
h3. *Appendix: Cross-Database Survey* 📚
_(This section serves as a reference for the original analysis.)_
* {*}PostgreSQL{*}: *(Target Behavior)* Fully supports {{&}} for integer and
bit string types. Uses two's complement.
* {*}MySQL{*}: Supports {{{}&{}}}. Behavior for negative numbers can vary
based on context (e.g., {{SELECT -5 & -3}} can return {{-7}} or a large
unsigned integer).
* {*}BigQuery{*}: Fully supports {{&}} with two's complement for integers and
{{BYTES}} types.
* {*}SQL Server{*}: Fully supports {{&}} with two's complement.
* {*}Snowflake{*}: Fully supports {{&}} for numeric and binary types.
* {*}Databricks{*}: Fully supports {{&}} with two's complement.
was:
h3. *Overview*
Add comprehensive support for the bitwise AND operator ({{{}&{}}}) in SQL,
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. {*}Design Decision: Align with PostgreSQL{*}{*}{*}
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.
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.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3. SELECT -5 & -3 return -7
*
** {*}PostgreSQL{*}: Supports {{&}} with integer and binary types, uses
modular arithmetic for large shifts.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3. SELECT -5 & -3 return -7.
*
** {*}SQL Server{*}: Supports bitwise AND and requires casting for non-integer
types.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3.
*
** {*}BigQuery{*}: Supports {{&}} with {{BYTES}} types and handles negative
values with two's complement.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3. SELECT -5 & -3 return -7.
*
** {*}Snowflake{*}: Supports {{&}} with {{BINARY}} and {{VARBINARY}} types.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3.
*
** {*}Databricks{*}: Supports {{&}} and requires casting for non-integer types.
** {*}Example{*}: {{SELECT 5 & 3;}} returns {{{}1{}}}, and {{SELECT -5 & 3;}}
returns 3.
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.
> Support for bitwise AND (&) operator 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*
>
> This task is to add comprehensive support for the bitwise AND operator
> ({{{}&{}}}) in SQL. This is part of a larger initiative to support a suite of
> bitwise operators ({{{}^{}}}, {{{}<<{}}}, {{{}&{}}}, {{{}>>{}}}). The {{^}}
> and {{<<}} operators are already implemented. The right-shift operator
> ({{{}>>{}}}) is currently blocked by a syntax conflict and will be addressed
> separately.
> ----
>
> h3. *Design Decision: Align with PostgreSQL* 👑
>
> To ensure consistent and predictable behavior, the implementation of the
> {{&}} operator {*}will align with the behavior of PostgreSQL{*}. This
> decision applies to all aspects of the operator, including:
> * {*}Type Promotion Rules{*}: For {{INTEGER}} family types.
> * {*}Negative Number Handling{*}: Using two's complement.
> * {*}{{VARBINARY}} Type Semantics{*}: Mimicking PostgreSQL's behavior for
> bit/byte strings.
> This approach follows the precedent set by previous bitwise operator
> implementations in Calcite.
> ----
>
> h3. *Implementation Plan* ✅
>
> * [ ] {*}Parser{*}: Extend {{SqlParser.jj}} to recognize the {{&}} operator
> with a precedence of 32.
> * [ ] {*}Operator Table{*}: Add a {{SqlBinaryOperator}} named
> {{BITAND_OPERATOR}} to {{{}SqlStdOperatorTable{}}}.
> * [ ] {*}Type System{*}: Implement return type inference that mirrors
> PostgreSQL's promotion rules for all supported types ({{{}TINYINT{}}},
> {{{}SMALLINT{}}}, {{{}INTEGER{}}}, {{{}BIGINT{}}}, {{{}VARBINARY{}}}).
> * [ ] {*}Relational Algebra{*}: Update {{SqlToRelConverter}} and
> {{RexBuilder}} to correctly translate the {{&}} operator into a
> {{{}RexCall{}}}.
> * [ ] {*}Code Generation{*}: Implement the final code generation logic.
> * [ ] {*}Unit Tests{*}: Add comprehensive tests covering:
> ** Positive and negative integer operands.
> ** Mixed-type operations (e.g., {{{}SMALLINT & BIGINT{}}}).
> ** {{VARBINARY}} operands.
> ** Edge cases (e.g., operations with {{{}0{}}}, {{{}-1{}}}).
> ----
>
> h3. *Target Behavior (Based on PostgreSQL)*
>
> * {*}Integer Examples{*}:
> ** {{SELECT 5 & 3;}} → {{1}}
> ** {{SELECT -5 & 3;}} → {{3}}
> ** {{SELECT -5 & -3;}} → {{-7}}
> * {*}{{VARBINARY}} Types{*}: The operation on {{VARBINARY}} operands will
> follow PostgreSQL's bitwise AND semantics for binary strings.
> ----
>
> h3. *Appendix: Cross-Database Survey* 📚
>
> _(This section serves as a reference for the original analysis.)_
> * {*}PostgreSQL{*}: *(Target Behavior)* Fully supports {{&}} for integer and
> bit string types. Uses two's complement.
> * {*}MySQL{*}: Supports {{{}&{}}}. Behavior for negative numbers can vary
> based on context (e.g., {{SELECT -5 & -3}} can return {{-7}} or a large
> unsigned integer).
> * {*}BigQuery{*}: Fully supports {{&}} with two's complement for integers
> and {{BYTES}} types.
> * {*}SQL Server{*}: Fully supports {{&}} with two's complement.
> * {*}Snowflake{*}: Fully supports {{&}} for numeric and binary types.
> * {*}Databricks{*}: Fully supports {{&}} with two's complement.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
