MySql Query Table of Masks

Question

I have a table that is filled with a variety of "masks" such at this:

Type    Mask1    Mask2    Mask3
0       fff      fff      ff
1       aff      fff      ff
2       aff      fff      92 
3       001      fff      00   

And basically I want to query the database and see if a particular query matches, say a00-111-12.

Anywhere there is an f (this is all in hex) I want to say there is a match.

So I take the value a00-111-12 and it should match with rows 0 and 1 but not 2 and 3 because in row 0, all f's appear and thus a value AND'd with them would result in that same value. BUT, AND-ing does not work since if testing with row 2, Mask3 column value 92 AND'd with 12 results in 12, however I don't want that row to be a match.

I find this a difficult question to ask, it may not be possible with a few MySQL Queries but I want to avoid importing the entire table into PHP and then finding the correct rows from there.

An idea of a query would be:

    SELECT * FROM TABLE WHERE Mask1 = a00 AND Mask2 = 111 AND ...
    

However some operation would need to be done on either Mask1, 2, 3 or the value being sent to the query.

The end goal is to get the Type from the matching rows. If you need more information please ask.

Answer 1

Create a submasks table to make your job easier, add one row

 z1 :  z2  : z3
0xf : 0xf0 : 0xf00

Then use the following query

   Select 
           t.*
   from Table t 
   inner join submasks s
     on (
         ((t.Mask1 & s.z1) = s.z1 || (t.Mask1 & s.z1) = (a00 & s.z1)) &&
         ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) &&
         ((t.Mask1 & s.z2) = s.z2 || (t.Mask1 & s.z2) = (a00 & s.z2)) &&
         ((t.Mask2 & s.z1) = s.z1 || (t.Mask2 & s.z1) = (111 & s.z1)) &&
         ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) &&
         ((t.Mask2 & s.z2) = s.z2 || (t.Mask2 & s.z2) = (111 & s.z2)) &&
         ((t.Mask3 & s.z1) = s.z1 || (t.Mask3 & s.z1) = (12 & s.z1)) &&
         ((t.Mask3 & s.z2) = s.z2 || (t.Mask3 & s.z2) = (12 & s.z2))
   )

The way this works is by comparing individual hex digits by performing a bitwise AND with z1,z2 and z2 to get each of the 3 digits respectively.

so

• <any value> & z1 sets all hex digits except the last to 0, ie 0x123 becomes 0x003
• <any value> & z2 sets all hex digits except the second from last to 0, ie 0x123 becomes 0x020
• <any value> & z3 sets all hex digits except the third from last to 0, ie 0x123 becomes 0x100

Using this filter the test for each digit can be built as

((mask & filter) = filter)          // is the digit f
||                                  // OR
((mask & filter) = (test & filter)) // is the digit the same.

Repeat the test for each of z1,z2 and z3 (ie 0x00f, 0x0f0, and 0xf00) combine the results with an and condition and you can check all 3 hex digits of the mask are either f or exactly the test value.

This is then repeated for Mask2 and Mask3 (but only z1,z2 as Mask3 is 2 digits).

By using inner join with the submasks table the result will only include the values from Table where the mask conditions are true.

UPDATE - you may want to perform select distinct instead of just select as if two masks match a single row in Table then 2 results will be returned.

Answer 2

Don't know if I explained my question well enough but I ended up coming to the conlusion that this works best:

val1 = 0xa00
val2 = 0x111
val3 = 0x12

SELECT * FROM TABLE WHERE 
((Mask1 | val1)=val1 OR (Mask1 | val1)=0xfff) AND
((Mask1 | val2)=val2 OR (Mask1 | val2)=0xfff) AND
((Mask1 | val3)=val3 OR (Mask1 | val2)=0xfff);

The only problem is that val1=a00 will not match with Mask1=aff although I would like it to. Still working on it...