Support for BitwiseAnd &, BitOr | binary operators #1619
Description
My parquet file contains a bitpacked flags (i32, but pretend it's u32). The 2nd bit in flags indicates whether the record is a potato or not. I would like to be able to access this from SQL.
Describe the solution you'd like
I think, in SQL, this would look like:
select sum(value) from pq where ((flags & 0b10) = 0b10);
I'd also be happy with it having a boolean output:
select sum(value) from pq where (flags & 0b10);
These operators (but not the binary literals) parse, but cannot be planned:
NotImplemented("Unsupported SQL binary operator BitwiseAnd")
This makes & a regular binary operator, like % or even +, although I wouldn't implement it for Decimal, or even signed integers, to start?
In my application I am going to be checking multiple flags, so any syntax which could be optimised would be great for me.
Postgres produces the integer output, instead of boolean.
Describe alternatives you've considered
You can fake this, to some extent, with (integer) / and %2.
You can (outside of the CLI, my favourite place to run SQL) register custom functions, like is_potato(flags).
I have also considered a custom function like bits_set(flags, "1001") -> bool? This is valuable to readability as there's no support for the 0b1001 binary literal syntax in the SQL at the moment, but its performance is bad, 'cos you end up with a long list of strings passed to your function.