Binary string conversion and bitwise AND operation

[dalvarez]

Hello there

I'm trying to do a bitwise operation over a text column that actually contains a string of zeros and ones without success. I hope someone could give me some help on this. Maybe i'm trying to do something impossible and i don't know...

Here the details:

I have a string with 1024 zeros and ones (a molecule fingerprint) stored in a table named 'identification' under the column 'value'. This column is TEXT type because some other identification formats (id_idformat) in the same table do not use this zeros-ones string.

Here the table description:

mysql> describe identification;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| id_state | int(11) | NO | MUL | NULL | |
| id_idformat | int(11) | NO | MUL | NULL | |
| value | text | NO | MUL | NULL | |
+-------------+---------+------+-----+---------+-------+
3 rows in set (0.04 sec)

My aim is to perform a bitwise AND comparison over that particular column for a specific id_idformat.

The query string is also another fingerprint of 1024 bits that in php i have under the variable $fp as a string.And this is the query that does not seem to work:

SELECT id_state, value FROM identification WHERE id_idformat = 5
AND BIT_COUNT(CONVERT(value,BINARY) & CONVERT($fp,BINARY)) = BIT_COUNT(CONVERT($fp,BINARY));

The result should be all the fingerprints in the table which share the same ONES that my query fingerprint. But this is not happening... and its returning all the table instead.

What am I doing wrong?? I guess the problem is the conversion from string to binary but i tried several types of conversion and it is still not working.

For example, with the b' ' conversion it works:

mysql> SELECT BIT_COUNT(b'0010100' & b'0011100') = BIT_COUNT(b'0011100');
+------------------------------------------------------------+
| BIT_COUNT(b'0010100' & b'0011100') = BIT_COUNT(b'0011100') |
+------------------------------------------------------------+
| 0 |
+------------------------------------------------------------+
1 row in set (0.08 sec)

mysql> SELECT BIT_COUNT(b'0010100' & b'0011100') = BIT_COUNT(b'0010100');
+------------------------------------------------------------+
| BIT_COUNT(b'0010100' & b'0011100') = BIT_COUNT(b'0010100') |
+------------------------------------------------------------+
| 1 |
+------------------------------------------------------------+
1 row in set (0.04 sec)

But i cannot do the b' ' command over the value field in the same query:

BIT_COUNT(b'value' & b'$fp') = BIT_COUNT(b'$fp')

This, of course, is wrong...

ARG i'm going crazy! I would really appreciate some help

Thank you very much!

DANIEL

 

[towerbase]

The MySQL documentation gives this

MySQL uses BIGINT (64-bit) arithmetic for bit operations, so these operators have a maximum range of 64 bits.

So I suspect trying to do bit operations on a 1024 bit values is not going to work.

Andrew
Hampshire, UK

 

[dalvarez]

Thank you Andrew,
I tried spliting the string first into 32 bits and then doing the operation and it is still not working.

I think it is something related with the conversion from string to binary. If you have one minute to take a look at the test i did?:

I built a test table:
+----+----------------------------------+
| i | v |
+----+----------------------------------+
| 1 | 00000000100000010000000000000000 |
| 2 | 00000000100000000000000000000000 |
| 3 | 00000000100000000000000000000000 |
| 4 | 00000000100000000000000000000000 |
| 5 | 00000000100000000000000000000000 |
| 6 | 00000000100000000000000000000000 |
| 7 | 00000000000000011000000000000010 |
| 8 | 00000000000000000000000000000010 |
| 9 | 00000000000000001100000000000100 |
| 10 | 00000000000000010000000001000000 |
| 11 | 00000000000010000010000000000010 |
| 12 | 00000000000000000000000000000010 |
| 13 | 00000100000000000000000000001100 |
| 14 | 00000000010000001010000000000000 |
| 15 | 00000000000000000000000000000001 |
+----+----------------------------------+

I took as 'query' the string in the 8th row. And testing (v & query) = query, as a result i would expect all the rows that have a 1 in the same position (7,8,11 and 12).

I tried several types of conversion and nothing works!

SET @q = '00000000000000000000000000000010';

SELECT i,v FROM test WHERE (v & @q) = @q;
SELECT i,v FROM test WHERE (binary(v) & binary(@q)) = binary(@q);
SELECT i,v FROM test WHERE BIT_COUNT(binary(v) & binary(@q)) = BIT_COUNT(binary(@q));

Any ideas?? I'm totally lost, don't know what else to try!

Funny thing is that this works:

mysql> select ('00000000000000000000000000000010' & '00000000000010000010000000000010')='00000000000000000000000000000010' as comp;
+------+
| comp |
+------+
| 1 |
+------+
1 row in set (0.04 sec)

mysql> select ('00000000000000000000000000000010' & '00000000000010000010000000000000')='00000000000000000000000000000010' as comp;
+------+
| comp |
+------+
| 0 |
+------+
1 row in set (0.04 sec)

But not with the variables??? Is this a bug?

 

[towerbase]

From my understanding of your requirements, I think you might consider using the SET type. Whilst there are disadvantages in the SET type it does seem to be a closer match to what you are trying to do. You are still going to be limited by 64 bits though. So your 1024 bit field would have to be represented by 16 SETS.

I think your current problems are caused by trying to do logical operations on a mixture of strings and numbers. For example '00000000000000000000000000000010' is a string but I think your test table contains integers.

Andrew
Hampshire, UK