[
https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17016235#comment-17016235
]
Julian Hyde commented on CALCITE-3732:
--------------------------------------
I see that MySQL allows BIT_COUNT applied to binary strings:
{{BIT_COUNT(b'101010')}} returns 3. Bit strings, like b'0101', are deprecated
in modern SQL but the modern equivalent are BINARY and VARBINARY data types.
We should make these operators work on all integer types, BINARY and VARBINARY.
What dialects are these operators in? I did a quick survey:
* MySQL has all of the above
* [PostgreSQL|https://www.postgresql.org/docs/9.3/functions-math.html] has
operators &, |, #, ~, <<, >> on integers, bit strings and binary, but no
bit-count; note that it uses '#' rather than '^' for XOR; it also has bit_and,
bit_or aggregate functions
*
[BigQuery|https://cloud.google.com/bigquery/docs/reference/standard-sql/bit_functions]
has BIT_COUNT (applies to INTEGER and BYTES, i.e. VARBINARY)
* [Presto|https://prestosql.io/docs/current/functions/bitwise.html] has a
2-argument variant of BIT_COUNT, also bitwise_and, bitwise_or, bitwise_not,
bitwise_and_agg, bitwise_or_agg. They don't have infix operators ~ etc.
*
[MemSQL|https://docs.memsql.com/v7.0/reference/sql-reference/numeric-functions/bitcount/]
has BIT_COUNT, &, ~, |, ^, >>, >>; no aggregate functions
*
[Oracle|https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions014.htm#SQLRF00612]
has BITAND; no aggregate functions
* [IBM
DB2|https://www.ibm.com/support/knowledgecenter/SSFMBX/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0052628.html]
has BITAND, BITANDNOT, BITOR, BITXOR, BITNOT; no aggregate functions
*
[Vertica|https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/BITCOUNT.htm]
has BITCOUNT that applies to VARBINARY but not integer; it has BIT_AND,
BIT_OR, BIT_XOR aggregate functions.
I am against introducing lexical changes to the default parser to add infix
binary operators (~, &, |, ^, #, <<, >>). In the default parser (with
appropriate library) we could expose those operations as functions (named, say,
BITWISE_NOT, BITWISE_AND, BITWISE_OR, BITWISE_LEFT_SHIFT, BITWISE_RIGHT_SHIFT).
A variant of the parser (say Babel) could support the infix binary operators.
> 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
> 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)
