[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17217129#comment-17217129 ] Julian Hyde commented on CALCITE-3732: -- [~hailong wang], Is this still in progress? Is anything ready to be reviewed to go into release 1.27? > Implement bit functions and operators > - > > Key: CALCITE-3732 > URL: https://issues.apache.org/jira/browse/CALCITE-3732 > Project: Calcite > Issue Type: Task > Components: core >Affects Versions: 1.21.0 >Reporter: hailong wang >Assignee: hailong wang >Priority: Major > > Bit function is non-standard operators, but all db has implemented, such as > mysql, postgresql. > Calcite has implemented BIT_AND, BIT_OR in CALCITE-2770, BIT_XOR in > CALCITE-3591. BIT_COUNT is in progress CALCITE-3697, BIT_NOT(~) is in > progress 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17032440#comment-17032440 ] hailong wang commented on CALCITE-3732: --- Thanks [~julianhyde]. I have updated, and this ticket is in in progress. > Implement bit functions and operators > - > > Key: CALCITE-3732 > URL: https://issues.apache.org/jira/browse/CALCITE-3732 > Project: Calcite > Issue Type: Task > 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 CALCITE-2770, BIT_XOR in > CALCITE-3591. BIT_COUNT is in progress CALCITE-3697, BIT_NOT(~) is in > progress 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17021473#comment-17021473 ] Julian Hyde commented on CALCITE-3732: -- Yes, let's go with that. Thus: * Scalar functions: BITAND, BITNOT, BITANDNOT, BITOR, BITSHIFTLEFT, BITSHIFTRIGHT, BITXOR, BITCOUNT; * Aggregate functions: BIT_AND, BIT_OR, BIT_XOR. Note that this is a change in plan for BITCOUNT (CALCITE-3697 would call it BIT_COUNT, but now we'll call it BITCOUNT) and BIT_NOT (CALCITE-3592 would call it BIT_NOT, but now we'll call it BITNOT). We can expose these under various synonyms in particular libaries, e.g.: * In the MySQL variant we can expose BITCOUNT as BIT_COUNT. * In the Snowflake variant we can expose BIT_AND as BITAND_AGG, similarly BIT_OR and BIT_XOR. * Et cetera. [~hailong wang] If you agree please update CALCITE-3697 and CALCITE-3592. > 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17017856#comment-17017856 ] hailong wang commented on CALCITE-3732: --- [~julianhyde] Maybe we can call BITAND, BITOR etc. like snowflake and DB2 which are no underscore compared with aggregate functions ? > 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17017640#comment-17017640 ] Julian Hyde commented on CALCITE-3732: -- I'm not sure Oracle has all of those functions in SQL. The ones you list are in PL/SQL. I would not implement those. BIT_AND, BIT_OR etc. are aggregate functions. What do you propose to call the binary non-aggregate functions that perform bitwise and, or, etc.? > 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[
https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17016310#comment-17016310
]
Rui Wang commented on CALCITE-3732:
---
Julian had already have a great summary for some functions.
Regarding to other functions from the spreadsheet that hailong shared:
{code:java}
// functions from sql-server
bit and assignment function
bit or assignment function
bit xor assignment function
{code}
assignment operator seems a special kind operator for a specific purpose (under
sql-server transact-sql section). Calcite does not need it for now.
{code:java}
// functions from Oracle
BitClear// clearing the specified bit in a value
BitRotate //bit shift-and-rotate operation
BitSet//setting the specified bit on in a value
//functions from postgresql
bit concat function
{code}
These functions are not common (a few engines implement them) but the
definition seems easy to understand and likely be consistent. We could support
them.
{code:java}
// functions from Oracle
DashCode
Dec2Hex
Hex2Dec
{code}
I don't have impressions to see functions here even implemented in other
languages. Calcite might not need such functions for now.
> 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[
https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17015559#comment-17015559 ] hailong wang commented on CALCITE-3732: --- Thanks [~zabetak] [~amaliujia]. Yes, I am planning to work on this. I will send to dev@ to have a discussion. > 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17015349#comment-17015349 ] Rui Wang commented on CALCITE-3732: --- It could useful to have a spreadsheet to list functions that you want to support, with other major DBs/SQL standard's investigation in it (if they support it, etc.) There is a thread in dev@ about math functions that did it. I found it was particularly for people to inspect and discuss. > 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)
[jira] [Commented] (CALCITE-3732) Implement bit functions and operators
[ https://issues.apache.org/jira/browse/CALCITE-3732?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17015117#comment-17015117 ] Stamatis Zampetakis commented on CALCITE-3732: -- As you said bitwise and, xor, and or are already implemented so what exactly are you proposing? The syntactic sugar for them (&, ^, |)? Are you planning to work on this? > 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)
