[
https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17016880#comment-17016880
]
hailong wang commented on CALCITE-3732:
---------------------------------------
Thanks [~julianhyde] [~amaliujia]. I conclude as follows:
1. In the default parser, we can support:
||Function Name||
|BIT_COUNT|
|BIT_AND|
|BIT_OR|
|BIT_XOR|
These functions are in SqlStdOperatorTable. The input Type can be TINYINT,
SMALLINT, INT, BIGINT, BINARY and VARBINARY. The return type is same as input
type.
For Oracle, we can support:
||Function Name||
|BitAnd|
|BitOr|
|BitXor|
|BitNot|
|BitShift|
|BitClear|
|BitRotate|
|BitSet|
For Snowflake, we can support:
||Function Name||
|BITSHIFTLEFT|
|BITSHIFTRIGHT|
For DB2, we can support:
||Function Name||
|BITANDNOT|
These functions are in SqlLibraryOperators. The input Type only supports
numeric values, and will be converted to an BIGINT before the bitwise function.
The result type is BIGINT.
2. In the babel parser:
For Mysql, we can support:
||Function Name||
|&|
|\||
|~|
|^|
|<<|
|>>|
For PostgreSQL, we can support:
||Function Name||
|#|
These functions are in SqlLibraryOperators. The input type can The input Type
can be TINYINT, SMALLINT, INT, BIGINT, BINARY and VARBINARY, and numeric values
will be cast to BIGINT.
The result type depends on whether the bit argument is evaluated as a binary
string or number:
1.Binary-string evaluation occurs when the arguments have a binary string
type. Numeric evaluation occurs otherwise, with argument conversion to unsigned
64-bit integers as necessary.
2.Binary-string evaluation produces a binary string of the same length as the
arguments. If the arguments have unequal lengths, an
ER_INVALID_BITWISE_OPERANDS_SIZE error occurs. Numeric evaluation produces an
unsigned 64-bit integer.
What do you think of these above?
BTW, I found Oracle, Snowflake and DB2 only support number for input type,
should we need to support binary and varbinary?
> Implement bit functions and operators
> -------------------------------------
>
> Key: CALCITE-3732
> URL: https://issues.apache.org/jira/browse/CALCITE-3732
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.21.0
> Reporter: hailong wang
> Assignee: hailong wang
> Priority: Major
> Fix For: 1.22.0
>
>
> Bit function is non-standard operators, but all db has implemented, such as
> mysql, postgresql.
> Calcite has implemented BIT_AND, BIT_OR in
> https://issues.apache.org/jira/browse/CALCITE-2770, BIT_XOR in
> https://issues.apache.org/jira/browse/CALCITE-3591. BIT_COUNT is in progress
> https://issues.apache.org/jira/browse/CALCITE-3697, BIT_NOT(~) is in progress
> https://issues.apache.org/jira/browse/CALCITE-3592.
> So I think we should also implement Bitwise AND(&), Right shift(>>), Left
> shift(<<), Bitwise XOR(^), Bitwise OR(|). And data types support tinyint,
> smallint, int, bigint like before.
>
> Refence:
> [https://dev.mysql.com/doc/refman/8.0/en/bit-functions.html#operator_bitwise-invert]
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
