Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to perform a bitwise group function?

I have a field in a table which contains bitwise flags. Let's say for the sake of example there are three flags: 4 => read, 2 => write, 1 => execute and the table looks like this*:

  user_id  |  file  |  permissions
-----------+--------+---------------
        1  |  a.txt |  6    ( <-- 6 = 4 + 2 = read + write)
        1  |  b.txt |  4    ( <-- 4 = 4 = read)
        2  |  a.txt |  4
        2  |  c.exe |  1    ( <-- 1 = execute)

I'm interested to find all users who have a particular flag set (eg: write) on ANY record. To do this in one query, I figured that if you OR'd all the user's permissions together you'd get a single value which is the "sum total" of their permissions:

  user_id  |  all_perms
-----------+-------------
        1  |  6        (<-- 6 | 4 = 6)
        2  |  5        (<-- 4 | 1 = 5)

*My actual table isn't to do with files or file permissions, 'tis but an example

Is there a way I could perform this in one statement? The way I see it, it's very similar to a normal aggregate function with GROUP BY:

SELECT user_id, SUM(permissions) as all_perms
FROM permissions
GROUP BY user_id

...but obviously, some magical "bitwise-or" function instead of SUM. Anyone know of anything like that?

(And for bonus points, does it work in oracle?)

like image 844
nickf Avatar asked Sep 09 '09 06:09

nickf


People also ask

How do you perform Bitwise AND operations?

The bitwise AND operator ( & ) compares each bit of the first operand to the corresponding bit of the second operand. If both bits are 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0. Both operands to the bitwise AND operator must have integral types.

What is Bitwise operation example?

A bitwise operation operates on two-bit patterns of equal lengths by positionally matching their individual bits. For example, a logical AND (&) of each bit pair results in a 1 if both the first AND second bits are 1. If only one bit is a 1, the result is 0.

What is the output of Bitwise operation?

The output of bitwise AND is 1 if the corresponding bits of two operands is 1. If either bit of an operand is 0, the result of corresponding bit is evaluated to 0.

What do Bitwise Operators do?

Bitwise operators are used to change individual bits in an operand. A single byte of computer memory-when viewed as 8 bits-can signify the true/false status of 8 flags because each bit can be used as a boolean variable that can hold one of two values: true or false.


2 Answers

MySQL:

SELECT user_id, BIT_OR(permissions) as all_perms
FROM permissions
GROUP BY user_id
like image 140
manji Avatar answered Oct 19 '22 17:10

manji


Ah, another one of those questions where I find the answer 5 minutes after asking... Accepted answer will go to the MySQL implementation though...

Here's how to do it with Oracle, as I discovered on Radino's blog

You create an object...

CREATE OR REPLACE TYPE bitor_impl AS OBJECT
(
  bitor NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT bitor_impl,
                                       VALUE IN NUMBER) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl,
                                     ctx2 IN bitor_impl) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT bitor_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER
)
/

CREATE OR REPLACE TYPE BODY bitor_impl IS
  STATIC FUNCTION ODCIAggregateInitialize(ctx IN OUT bitor_impl) RETURN NUMBER IS
  BEGIN
    ctx := bitor_impl(0);
    RETURN ODCIConst.Success;
  END ODCIAggregateInitialize;

  MEMBER FUNCTION ODCIAggregateIterate(SELF  IN OUT bitor_impl,
                                       VALUE IN NUMBER) RETURN NUMBER IS
  BEGIN
    SELF.bitor := SELF.bitor + VALUE - bitand(SELF.bitor, VALUE);
    RETURN ODCIConst.Success;
  END ODCIAggregateIterate;

  MEMBER FUNCTION ODCIAggregateMerge(SELF IN OUT bitor_impl,
                                     ctx2 IN bitor_impl) RETURN NUMBER IS
  BEGIN
    SELF.bitor := SELF.bitor + ctx2.bitor - bitand(SELF.bitor, ctx2.bitor);
    RETURN ODCIConst.Success;
  END ODCIAggregateMerge;

  MEMBER FUNCTION ODCIAggregateTerminate(SELF        IN OUT bitor_impl,
                                         returnvalue OUT NUMBER,
                                         flags       IN NUMBER) RETURN NUMBER IS
  BEGIN
    returnvalue := SELF.bitor;
    RETURN ODCIConst.Success;
  END ODCIAggregateTerminate;
END;
/

...and then define your own aggregate function

CREATE OR REPLACE FUNCTION bitoragg(x IN NUMBER) RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING bitor_impl;
/

Usage:

SELECT user_id, bitoragg(permissions) FROM perms GROUP BY user_id
like image 40
nickf Avatar answered Oct 19 '22 18:10

nickf