[ 
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)

Reply via email to