Bitwise operation support in omniscidb

Does omniScidB support bitwise operation? For example, if I want to extract the first 32 bits from a number using a SQL query, I will simply do:

“SELECT time_stamp, col_a, (col_a & 0x00000000ffffffff) as ‘bit-31-0’ FROM table order by time_stamp;”

Running the same query in OmniSci dB results in an error saying:
“Exception: Parse failed: Lexical error at line 1, column 50. Encountered: “&” (38), after : “”
unable to rollback”

1 Like

Hi @dipanwita2019,

As today omniscidb doesn’t support bitwise operators like AND, OR, SHIFT.

I think implements them with standard syntax; it’s not an easy task because of the calcite implementation.

Anyway, you can try to use this script that would define AND/OR/RIGHT SHIFT/LEFT SHIFT/NOT as UDF (User Defined Functions)

the operations defined are

  • bitwise_or
  • bitwise_and
  • bitwise_lshift
  • bitwise_rshift
  • bitwise_xor
  • bitwise_not

udf_bitwise.txt (2,4 KB)
to use them, you have to place the file into the server and rename into udf_bitwise.cpp where omnisci is running, then run omnisci server adding 2 udf parameters e.g.

/omnisci_server --data /mapd_storage/data_test/ --udf "/mapd_storage/udf_bitwise.cpp" --enable-runtime-udf true
if running omniscidb as a service you have to add the UDF and ENABLE-RUNTIME-UDF into omnisci.conf file

all functions except bitwise_not take 2 arguments.

select bitwise_and(depdelay,arrdelay),bitwise_or(depdelay,arrdelay),bitwise_xor(depdelay,arrdelay),bitwise_lshift(depdelay,2),bitwise_rshift(depdelay,2),bitwise_not(depdelay),depdelay from flights_sk;

everything looks to works on projections, aggregates when running the server in CPU mode, but there could be issues in GPU mode.

Anyway, take this solution as a temporary one.

I just changed the udf_bitwise.txt file, with corrections needed to run on a Gpu Build. I forgot to say, that to be able to use UDF on database clang has to be installed into the system