Bitwise XOR in sqlite - bitwise NOT not working as I expect

I try to update flag in sqlite database.

I need to remove bit from my flag. Current flag vallue is 11.

In Java I simple do:

newflag = flag  ^ 2;
//newflag = 9

In SQL I try:

... SET flag = flag | ~2 ...

Now flag in db = -1. Why?

I try this in SQLite maganer for Firefox.

SELECT 9 | 2

returns 11, but

SELECT 11 | ~2

returns -1

Solution

To answer your question (why does 11 | ~2 evaluate to -1), let's break it up. First the bitwise complement:

> SELECT ~2;
-3

Using binary notation, this is what's happening:

 2 : 00000000000000000000000000000010
~2 : 11111111111111111111111111111101
-3 : 11111111111111111111111111111101

SQLite takes your 2, applies a bitwise complement, and interprets it as a signed integer. In SQLite, ~n (where n is an integer value) is mathematically equivalent to -(n)-1, because it flips the sign bit along with all the other bits. To understand this better, read up on Two's complement:

http://en.wikipedia.org/wiki/Two's_complement

Next the bitwise or:

> SELECT 11 | ~2;
-1

Again in binary:

     11 : 00000000000000000000000000001011
     ~2 : 11111111111111111111111111111101
11 | ~2 : 11111111111111111111111111111111
     -1 : 11111111111111111111111111111111

As for emulating your Java: your Java uses the bitwise XOR operator (^). To emulate a bitwise XOR in sqlite, you can use this general form:

(~(a&b))&(a|b)

http://www.mail-archive.com/sqlite-users@sqlite.org/msg02250.html