[
https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17016235#comment-17016235
]
Julian Hyde edited comment on CALCITE-3732 at 1/15/20 7:12 PM:
---------------------------------------------------------------
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, and
[also|https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Operators/BitwiseOperators.htm]
&, |, #, ~, <<, >> operators that apply to integer and binary types; it has
BIT_AND, BIT_OR, BIT_XOR aggregate functions.
*
[Snowflake|https://docs.snowflake.net/manuals/sql-reference/expressions-byte-bit.html]
has BITAND, BITNOT, BITOR, BITSHIFTLEFT, BITSHIFTRIGHT, BITXOR; also
BITAND_AGG, BITOR_AGG, BITXOR_AGG 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,
BITNOT, BITAND, BITOR, BITSHIFTLEFT, BITSHIFTRIGHT, consistent with Oracle,
DB2, Snowflake). A variant of the parser (say Babel) could support the infix
binary operators.
was (Author: julianhyde):
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, and
[also|https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/LanguageElements/Operators/BitwiseOperators.htm]
&, |, #, ~, <<, >> operators that apply to integer and binary types; 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)
