The comprehensive SQL bitwise operations compatibility list

lukaseder

14 years ago

One of the rather nasty, off-the-beaten-track battlefields in SQL non-standards are bitwise operations. To my knowledge, they’re not in any SQL standard (I’ve checked SQL:2008 drafts), but almost all databases support some form of bitwise operations. Here’s the relevant MySQL documentation page, as an example: https://dev.mysql.com/doc/refman/5.5/en/bit-functions.html So we’re talking about these operations: bit_count(), and (&), or (|), xor (^), not (~), left shift (<<), right shift (>>). Agreed, that’s not the most important functionality in a database. But these operations may come in handy every now and then.

The Ranking

So here’s the ranking of bitwise support as far as the upcoming jOOQ support is concerned:

1. Making the world a better place

These databases are bit-friends. This is probably because some C / Assembler guys are on the development teams. I don’t want to have a look at the source code… The winners are:

MySQL (Max score! The only one to support bit_count())
Postgres

2. Attending their duty

Nothing wrong with these databases. They support the important operations: and (&), or (|), xor (^), not (~). The left / right shift isn’t so important anyway…

DB2 (Even has support for BITANDNOT! Operations are called BITAND, BITOR, BITXOR, BITNOT)
SQLite (Misses out on the xor (^) operator, though)
SQL Server
Sybase Adaptive Server
Sybase SQL Anywhere

3. Wait a minute

Where did the not (~) operation go on these ones? Hmm… But we still have and (&), or (|), xor (^)

H2 (Operations are called BITAND, BITOR, BITXOR)
HSQLDB (Operations are called BITAND, BITOR, BITXOR)

4. A bit disappointing, for once

Usually the winner in these comparisons, here’s now a loser. It only supports the and (&) operation:

Oracle (Operation is called BITAND)

5. Out of the game

And finally, the usual suspects that lack bitwise functionality entirely

Ingres (Has hard-to-use BIT_AND and BIT_OR support. Input and output types have to be converted several times, so it doesn’t count)
Derby (No bitwise operations whatsoever)

Simulation in jOOQ

As usual, jOOQ hides such incompatibility facts from the developers where possible. The API is simple. Call upon any Field<?>:


    Field<Integer> bitCount();
    Field<T> bitNot();

    // The below methods are overloaded to also support field parameters
    Field<T> bitAnd(Number value);
    Field<T> bitNand(Number value);
    Field<T> bitOr(Number value);
    Field<T> bitNor(Number value);
    Field<T> bitXor(Number value);
    Field<T> bitXNor(Number value);
    Field<T> shl(Number value);
    Field<T> shr(Number value);

Here’s how these methods are simulated:

bit_count()

So MySQL’s bit_count() function can be simulated in most databases using this algorithm. In this case, for TINYINT data types. It’ll get quite mad for BIGINT’s:


SELECT (my_field &  1 )      +
       (my_field &  2 ) >> 1 +
       (my_field &  4 ) >> 2 +
       (my_field &  8 ) >> 3 +
       (my_field &  16) >> 4 +
        ...
       (my_field & 128) >> 7
FROM my_table

There’s also quite a freaky method in Java’s Integer.bitCount(int) and Long.bitCount(long) methods. It’s too freaky for me to understand, so I didn’t check if that’s the same as what MySQL does:


public static int bitCount(int i) {
    // HD, Figure 5-2
    i = i - ((i >>> 1) & 0x55555555);
    i = (i & 0x33333333) + ((i >>> 2) & 0x33333333);
    i = (i + (i >>> 4)) & 0x0f0f0f0f;
    i = i + (i >>> 8);
    i = i + (i >>> 16);
    return i & 0x3f;
}

Should you have a better idea, here’s where you could place it and score some reputation: https://stackoverflow.com/questions/7946349/how-to-simulate-the-mysql-bit-count-function-in-sybase-sql-anywhere

Left / Right Shifts

Simulated in DB2, H2, HSQLDB, Ingres, Oracle, SQL Server, Sybase ASE, Sybase SQL Anywhere. This can obviously be done by multiplication / division (running the risk of overflows). Here’s how


-- a << b
a * power(2, b)

-- a >> b
a / power(2, b)

If unavailable (Derby, SQLite), the power function is already simulated by jOOQ using the following formula:


-- power(a, b)
exp(ln(a) * b)

Not / Bitwise inverse

Simulated in H2, HSQLDB, Ingres, Oracle. This can be done arithmetically, by calculating


-- ~a, or bitnot(a)
-a - 1

All the rest

Simulated in Oracle. I’ve found something for that case, too:


-- a | b or bitor(a, b)
a - (a & b) + b

-- a ^ b or bitxor(a, b)
(a | b) - (a & b)

-- or also
~(a & b) & (a | b)

Conclusion

Once more, a slim layer of SQL abstraction proves to be very powerful to keep you from doing highly repetitive tasks. So download jOOQ right now, if you haven’t already! :-) https://www.jooq.org