[ https://issues.apache.org/jira/browse/CALCITE-7184?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
ASF GitHub Bot updated CALCITE-7184: ------------------------------------ Labels: pull-request-available (was: ) > 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 > Labels: pull-request-available > > 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. > * UNSINGED TYPES: will support too > ---- > > 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)