[
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*
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.
> 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)
