[
https://issues.apache.org/jira/browse/CALCITE-7109?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18010109#comment-18010109
]
krooswu commented on CALCITE-7109:
----------------------------------
Hi @[~julianhyde] ,
Thank you for the feedback about negative shift handling. I did some testing
across databases and found quite surprising inconsistencies:
h2. Cross-Database Testing Results
*PostgreSQL* (uses modular arithmetic):
{{SELECT 1 << -1; -- Returns -2147483648 (= 1 << 31, using -1 & 0x1F = 31)
SELECT 1 << -2; -- Returns 1073741824 (= 1 << 30, using -2 & 0x1F = 30)
SELECT 1 << -3; -- Returns 536870912 (= 1 << 29, using -3 & 0x1F = 29)}}
*SQL Server* (treats as right shift):
{{SELECT 8 << -1; -- Returns 4 (equivalent to 8 >> 1)}}
{*}MySQL{*}: Still need to test, but likely different from both above.
h2. My Proposal: Strict Validation
Given these {*}significant inconsistencies{*}, I'm leaning toward having
Calcite throw an {{IllegalArgumentException}} for negative shift counts, with a
clear error message explaining the cross-database inconsistency.
*Rationale:*
* {*}Clarity{*}: Users get explicit feedback rather than potentially confusing
results
* {*}Predictability{*}: Same behavior across all Calcite deployments
* {*}Safety{*}: Avoids unexpected results from modular arithmetic or implicit
conversions
* {*}Future-proof{*}: We can always relax this later if a clear standard
emerges
h2. Question for Review
*Do you agree with the strict validation approach for negative shifts?*
Alternative approaches:
# *Follow PostgreSQL* (modular arithmetic) - but this seems unintuitive
# *Follow SQL Server* (treat as right shift) - more intuitive but diverges
from PostgreSQL
# *Strict validation* (throw exception) - my current preference
I've updated the proposal to reflect the strict approach, but I'm happy to
adjust based on your guidance. What's your preference for handling this
cross-database inconsistency?
Thanks for your thoughts!
> 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)
