The comprehensive SQL bitwise operations compatibility list

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:

http://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:

http://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! 🙂

http://www.jooq.org

2 thoughts on “The comprehensive SQL bitwise operations compatibility list

  1. Great article.
    I think you’d be a great contributor for DZone’s MVB program (dzone.com/aboutmvb).

    If you’re interested, send me and email and we can discuss further.

    Regards
    James
    (Editor @ JavaLobby)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s