You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Xurenhe (Jira)" <ji...@apache.org> on 2022/04/07 13:15:00 UTC
[jira] [Updated] (CALCITE-5087) Support bitwise operators

     [ https://issues.apache.org/jira/browse/CALCITE-5087?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Xurenhe updated CALCITE-5087:
-----------------------------
    Description: 
As [email|https://lists.apache.org/thread/01vxw7rlzwzh82kk1p1srxhfhg5not30] discussed, the most sql engine support bitwise's operators, and CALCITE could support it.

I did some research

*1. BIT_AND*

 
||SQL-Engine||Example||
|Oracle|a & b|
|MySQL|a & b|
|SQL Server|a & b|
|PostgreSQL|a & b|
|BigQuery|a & b|
|Spark SQL|a & b|

 

*2. BIT_OR*
||SQL-Engine||Example||
|Oracle|a \| b|
|MySQL|a \| b|
|SQL Server|a \| b|
|PostgreSQL|a \| b|
|BigQuery|a \| b|
|Spark SQL|a \| b|

*BIT_INVERSION*
||SQL-Engine||Example||
|Oracle|~a|
|MySQL|~a|
|SQL Server|~a|
|PostgreSQL|~a|
|BigQuery|~a|
|Spark SQL|~a|

*BIT_XOR*
||SQL-Engine||Example||
|Oracle|a ^ b|
|MySQL|a ^ b|
|SQL Server|a ^ b|
|PostgreSQL|a # b|
|BigQuery|a ^ b|
|Spark SQL|a ^ b|

*BIT_LEFT_SHIFT*
||SQL-Engine||Example||
|Oracle|a << b|
|MySQL|a << b|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a << b|
|BigQuery|a << b|
|Spark SQL|shiftleft(a, b)|

*BIT_RIGHT_SHIFT*
||SQL-Engine||Example||
|Oracle|a >> b|
|MySQL|a >> b|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a >> b|
|BigQuery|a >> b|
|Spark SQL|shiftright(a, b)|

*BIT_RIGHT_SHIFT_FILL_ZERO*
||SQL-Engine||Example||
|Oracle|a >>> b|
|MySQL|NOT_SUPPORT|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|NOT_SUPPORT|
|BigQuery|NOT_SUPPORT|
|Spark SQL|NOT_SUPPORT|

*BIT_CONCATENATION*
||SQL-Engine||Example||
|Oracle|NOT_SUPPORT|
|MySQL|NOT_SUPPORT(run as c-style)|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a \|\| b|
|BigQuery|NOT_SUPPORT|
|Spark SQL|NOT_SUPPORT|

 

  was:
As [email|https://lists.apache.org/thread/01vxw7rlzwzh82kk1p1srxhfhg5not30] discussed, the most sql engine support bitwise's operators, and CALCITE could support it.

I did some research

*1. BIT_AND*

 
||SQL-Engine||Example||
|Oracle|a & b|
|MySQL|a & b|
|SQL Server|a & b|
|PostgreSQL|a & b|
|BigQuery|a & b|
|Spark SQL|a & b|
|Presto|bitwise_and(a, b)|

 

*2. BIT_OR*
||SQL-Engine||Example||
|Oracle|a \| b|
|MySQL|a \| b|
|SQL Server|a \| b|
|PostgreSQL|a \| b|
|BigQuery|a \| b|
|Spark SQL|a \| b|
|Presto|bitwise_or(a, b)|

*BIT_INVERSION*
||SQL-Engine||Example||
|Oracle|~a|
|MySQL|~a|
|SQL Server|~a|
|PostgreSQL|~a|
|BigQuery|~a|
|Spark SQL|~a|
|Presto|bitwise_not(a, b)|

*BIT_XOR*
||SQL-Engine||Example||
|Oracle|a ^ b|
|MySQL|a ^ b|
|SQL Server|a ^ b|
|PostgreSQL|a # b|
|BigQuery|a ^ b|
|Spark SQL|a ^ b|

*BIT_LEFT_SHIFT*
||SQL-Engine||Example||
|Oracle|a << b|
|MySQL|a << b|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a << b|
|BigQuery|a << b|
|Spark SQL|shiftleft(a, b)|

*BIT_RIGHT_SHIFT*
||SQL-Engine||Example||
|Oracle|a >> b|
|MySQL|a >> b|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a >> b|
|BigQuery|a >> b|
|Spark SQL|shiftright(a, b)|

*BIT_RIGHT_SHIFT_FILL_ZERO*
||SQL-Engine||Example||
|Oracle|a >>> b|
|MySQL|NOT_SUPPORT|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|NOT_SUPPORT|
|BigQuery|NOT_SUPPORT|
|Spark SQL|NOT_SUPPORT|

*BIT_CONCATENATION*
||SQL-Engine||Example||
|Oracle|NOT_SUPPORT|
|MySQL|NOT_SUPPORT(run as c-style)|
|SQL Server|NOT_SUPPORT|
|PostgreSQL|a \|\| b|
|BigQuery|NOT_SUPPORT|
|Spark SQL|NOT_SUPPORT|

 


> Support bitwise operators
> -------------------------
>
>                 Key: CALCITE-5087
>                 URL: https://issues.apache.org/jira/browse/CALCITE-5087
>             Project: Calcite
>          Issue Type: New Feature
>          Components: core
>            Reporter: Xurenhe
>            Assignee: Xurenhe
>            Priority: Major
>
> As [email|https://lists.apache.org/thread/01vxw7rlzwzh82kk1p1srxhfhg5not30] discussed, the most sql engine support bitwise's operators, and CALCITE could support it.
> I did some research
> *1. BIT_AND*
>  
> ||SQL-Engine||Example||
> |Oracle|a & b|
> |MySQL|a & b|
> |SQL Server|a & b|
> |PostgreSQL|a & b|
> |BigQuery|a & b|
> |Spark SQL|a & b|
>  
> *2. BIT_OR*
> ||SQL-Engine||Example||
> |Oracle|a \| b|
> |MySQL|a \| b|
> |SQL Server|a \| b|
> |PostgreSQL|a \| b|
> |BigQuery|a \| b|
> |Spark SQL|a \| b|
> *BIT_INVERSION*
> ||SQL-Engine||Example||
> |Oracle|~a|
> |MySQL|~a|
> |SQL Server|~a|
> |PostgreSQL|~a|
> |BigQuery|~a|
> |Spark SQL|~a|
> *BIT_XOR*
> ||SQL-Engine||Example||
> |Oracle|a ^ b|
> |MySQL|a ^ b|
> |SQL Server|a ^ b|
> |PostgreSQL|a # b|
> |BigQuery|a ^ b|
> |Spark SQL|a ^ b|
> *BIT_LEFT_SHIFT*
> ||SQL-Engine||Example||
> |Oracle|a << b|
> |MySQL|a << b|
> |SQL Server|NOT_SUPPORT|
> |PostgreSQL|a << b|
> |BigQuery|a << b|
> |Spark SQL|shiftleft(a, b)|
> *BIT_RIGHT_SHIFT*
> ||SQL-Engine||Example||
> |Oracle|a >> b|
> |MySQL|a >> b|
> |SQL Server|NOT_SUPPORT|
> |PostgreSQL|a >> b|
> |BigQuery|a >> b|
> |Spark SQL|shiftright(a, b)|
> *BIT_RIGHT_SHIFT_FILL_ZERO*
> ||SQL-Engine||Example||
> |Oracle|a >>> b|
> |MySQL|NOT_SUPPORT|
> |SQL Server|NOT_SUPPORT|
> |PostgreSQL|NOT_SUPPORT|
> |BigQuery|NOT_SUPPORT|
> |Spark SQL|NOT_SUPPORT|
> *BIT_CONCATENATION*
> ||SQL-Engine||Example||
> |Oracle|NOT_SUPPORT|
> |MySQL|NOT_SUPPORT(run as c-style)|
> |SQL Server|NOT_SUPPORT|
> |PostgreSQL|a \|\| b|
> |BigQuery|NOT_SUPPORT|
> |Spark SQL|NOT_SUPPORT|
>  



--
This message was sent by Atlassian Jira
(v8.20.1#820001)