[
https://issues.apache.org/jira/browse/CALCITE-7109?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
krooswu updated CALCITE-7109:
-----------------------------
Description:
h1. Proposal: Add support for << (bitwise left shift) operator
h2. Overview
Add comprehensive support for the bitwise left shift operator ({{{}<<{}}}) in
Apache Calcite, following mainstream database semantics.
h2. Implementation Details
h3. Core Components
* {*}Parser Extension{*}: Extend parser to recognize {{<<}} operator with
appropriate precedence (32, matching standard shift operators)
* {*}Operator Definition{*}: Add {{SqlBinaryOperator LEFTSHIFT}} in
{{SqlStdOperatorTable}}
* {*}Integration{*}: Support in {{{}SqlToRelConverter{}}}, {{{}RexBuilder{}}},
and code generation
* {*}Type System{*}: Add type inference for all INTEGER family types (TINYINT,
SMALLINT, INTEGER, BIGINT)
h3. Type Handling Strategy
* {*}Return Type{*}: Result type matches left operand type
({{{}ReturnTypes.ARG0_NULLABLE{}}})
* {*}Mixed Type Promotion{*}: When operand types differ, promote to
accommodate both operands
* {*}Supported Types{*}:
** INTEGER family types (TINYINT, SMALLINT, INTEGER, BIGINT)
** VARBINARY types (following BigQuery semantics)
h3. Behavioral Semantics
h4. Shift Count Handling
* {*}Negative Shift{*}: Following SQL Server semantics, treat negative shift
as right shift
{{SELECT 8 << -1; -- Equivalent to 8 >> 1, returns 4}}
* {*}Large Shift{*}: Shift counts >= type bit width return 0
{{SELECT 1 << 40; -- Returns 0 for INTEGER types}}
h4. Overflow Behavior
* {*}Silent Truncation{*}: Overflow bits are silently discarded (no exceptions
thrown)
* {*}Natural Bit Operations{*}: Follow Java's natural bit truncation behavior
* {*}Alignment{*}: Matches PostgreSQL, MySQL, and SQL Server overflow handling
h4. Null Handling
* {*}Standard SQL Semantics{*}: NULL operands result in NULL output
* {*}Consistent{*}: Follows existing Calcite null propagation patterns
h3. Database Compatibility Matrix
||Database||Negative Shift||Overflow||Implementation Status||
|*PostgreSQL*|Error/Undefined|Silent truncation|✅ Aligned|
|*MySQL*|Error/Undefined|Silent truncation|✅ Aligned|
|*SQL Server*|Treated as right shift|Silent truncation|✅ Aligned|
|*BigQuery*|Limited support|Silent truncation|✅ Aligned (BYTES support)|
h3. Test Coverage
* {*}Parsing Tests{*}: {{SqlParserTest}} - operator recognition and precedence
* {*}Validation Tests{*}: {{SqlValidatorTest}} - type checking and error
conditions
* {*}Execution Tests{*}: {{SqlOperatorTest}} - runtime behavior verification
* {*}Rex Tests{*}: {{{}RexImpTableTest{}}}, {{RexProgramTest}} - expression
tree handling
* {*}Integration Tests{*}: {{operator.iq}} - end-to-end SQL execution scenarios
* {*}Edge Cases{*}: Boundary conditions, type promotions, null handling
h3. Design Decisions & Rationales
h4. Why Silent Truncation?
* {*}Industry Standard{*}: All major databases handle overflow this way
* {*}Performance{*}: Avoids runtime exception overhead
* {*}Predictability{*}: Consistent bit-level behavior across platforms
h4. Why SQL Server's Negative Shift Semantics?
* {*}Intuitive{*}: Negative left shift logically becomes right shift
* {*}Practical{*}: Useful for bidirectional bit manipulation
* {*}Clear{*}: Unambiguous behavior specification
h4. Why ARG0 Return Type?
* {*}Consistency{*}: Matches other bitwise operators in Calcite
* {*}Control{*}: Users can explicitly cast for type promotion when needed
* {*}Performance{*}: Avoids unnecessary type conversions
h3. VARBINARY Implementation Details
* {*}Byte-level Shifting{*}: Shifts entire bytes within the binary array
* {*}Length Preservation{*}: Result maintains same byte length as input
* {*}Overflow Handling{*}: Bytes shifted beyond array boundaries are discarded
* {*}BigQuery Semantics{*}: Follows BigQuery's BYTES shifting behavior
h3. Future Extensions
* {*}Configuration Options{*}: Optional strict mode for overflow checking
* {*}Right Shift Operator{*}: Companion {{>>}} operator implementation
h3. Breaking Changes
* {*}None{*}: This is a new feature addition with no existing functionality
changes
This proposal ensures Calcite's left shift operator behaves consistently with
mainstream SQL databases while maintaining performance and providing clear,
predictable semantics.
was:
*Proposal: Add support for {{<<}} (bitwise left shift) operator*
* Extend parser to recognize {{{}<<{}}}.
* Add {{SqlBinaryOperator}} in {{{}SqlStdOperatorTable{}}}.
* Support in {{{}SqlToRelConverter{}}}, {{{}RexBuilder{}}}.
* Add type inference and validation for {{INTEGER}} and {{BIGINT}} (promote
mixed types).
* Negative shift count throws exception.
* Overflow shift silently truncates bits.
* Follow standard SQL null handling.
* Tests to cover parsing, validation, execution ({{{}SqlOperatorTest{}}},
{{{}SqlValidatorTest{}}}, {{{}RexImpTableTest{}}}, {{{}RexProgramTest{}}}) and
integration tests in {{{}operator.iq{}}}.
* No initial support for {{VARBINARY}} (future extension).
This aligns behavior with PostgreSQL and MySQL. SQL Server’s negative shift
handling is not adopted.
> Add support for << operator in Calcite
> --------------------------------------
>
> Key: CALCITE-7109
> URL: https://issues.apache.org/jira/browse/CALCITE-7109
> Project: Calcite
> Issue Type: New Feature
> Reporter: krooswu
> Assignee: krooswu
> Priority: Major
> Labels: pull-request-available
>
> h1. Proposal: Add support for << (bitwise left shift) operator
> h2. Overview
> Add comprehensive support for the bitwise left shift operator ({{{}<<{}}}) in
> Apache Calcite, following mainstream database semantics.
> h2. Implementation Details
> h3. Core Components
> * {*}Parser Extension{*}: Extend parser to recognize {{<<}} operator with
> appropriate precedence (32, matching standard shift operators)
> * {*}Operator Definition{*}: Add {{SqlBinaryOperator LEFTSHIFT}} in
> {{SqlStdOperatorTable}}
> * {*}Integration{*}: Support in {{{}SqlToRelConverter{}}},
> {{{}RexBuilder{}}}, and code generation
> * {*}Type System{*}: Add type inference for all INTEGER family types
> (TINYINT, SMALLINT, INTEGER, BIGINT)
> h3. Type Handling Strategy
> * {*}Return Type{*}: Result type matches left operand type
> ({{{}ReturnTypes.ARG0_NULLABLE{}}})
> * {*}Mixed Type Promotion{*}: When operand types differ, promote to
> accommodate both operands
> * {*}Supported Types{*}:
> ** INTEGER family types (TINYINT, SMALLINT, INTEGER, BIGINT)
> ** VARBINARY types (following BigQuery semantics)
> h3. Behavioral Semantics
> h4. Shift Count Handling
> * {*}Negative Shift{*}: Following SQL Server semantics, treat negative shift
> as right shift
> {{SELECT 8 << -1; -- Equivalent to 8 >> 1, returns 4}}
> * {*}Large Shift{*}: Shift counts >= type bit width return 0
> {{SELECT 1 << 40; -- Returns 0 for INTEGER types}}
> h4. Overflow Behavior
> * {*}Silent Truncation{*}: Overflow bits are silently discarded (no
> exceptions thrown)
> * {*}Natural Bit Operations{*}: Follow Java's natural bit truncation behavior
> * {*}Alignment{*}: Matches PostgreSQL, MySQL, and SQL Server overflow
> handling
> h4. Null Handling
> * {*}Standard SQL Semantics{*}: NULL operands result in NULL output
> * {*}Consistent{*}: Follows existing Calcite null propagation patterns
> h3. Database Compatibility Matrix
> ||Database||Negative Shift||Overflow||Implementation Status||
> |*PostgreSQL*|Error/Undefined|Silent truncation|✅ Aligned|
> |*MySQL*|Error/Undefined|Silent truncation|✅ Aligned|
> |*SQL Server*|Treated as right shift|Silent truncation|✅ Aligned|
> |*BigQuery*|Limited support|Silent truncation|✅ Aligned (BYTES support)|
> h3. Test Coverage
> * {*}Parsing Tests{*}: {{SqlParserTest}} - operator recognition and
> precedence
> * {*}Validation Tests{*}: {{SqlValidatorTest}} - type checking and error
> conditions
> * {*}Execution Tests{*}: {{SqlOperatorTest}} - runtime behavior verification
> * {*}Rex Tests{*}: {{{}RexImpTableTest{}}}, {{RexProgramTest}} - expression
> tree handling
> * {*}Integration Tests{*}: {{operator.iq}} - end-to-end SQL execution
> scenarios
> * {*}Edge Cases{*}: Boundary conditions, type promotions, null handling
> h3. Design Decisions & Rationales
> h4. Why Silent Truncation?
> * {*}Industry Standard{*}: All major databases handle overflow this way
> * {*}Performance{*}: Avoids runtime exception overhead
> * {*}Predictability{*}: Consistent bit-level behavior across platforms
> h4. Why SQL Server's Negative Shift Semantics?
> * {*}Intuitive{*}: Negative left shift logically becomes right shift
> * {*}Practical{*}: Useful for bidirectional bit manipulation
> * {*}Clear{*}: Unambiguous behavior specification
> h4. Why ARG0 Return Type?
> * {*}Consistency{*}: Matches other bitwise operators in Calcite
> * {*}Control{*}: Users can explicitly cast for type promotion when needed
> * {*}Performance{*}: Avoids unnecessary type conversions
> h3. VARBINARY Implementation Details
> * {*}Byte-level Shifting{*}: Shifts entire bytes within the binary array
> * {*}Length Preservation{*}: Result maintains same byte length as input
> * {*}Overflow Handling{*}: Bytes shifted beyond array boundaries are
> discarded
> * {*}BigQuery Semantics{*}: Follows BigQuery's BYTES shifting behavior
> h3. Future Extensions
> * {*}Configuration Options{*}: Optional strict mode for overflow checking
> * {*}Right Shift Operator{*}: Companion {{>>}} operator implementation
> h3. Breaking Changes
> * {*}None{*}: This is a new feature addition with no existing functionality
> changes
> This proposal ensures Calcite's left shift operator behaves consistently with
> mainstream SQL databases while maintaining performance and providing clear,
> predictable semantics.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
