BIT_OR y BIT_AND en PostgreSQL: agregados bit a bit sobre mascaras de flags

BIT_OR y BIT_AND son funciones de agregacion que recorren una columna entera de un grupo y aplican OR o AND bit a bit a todos los valores a la vez. Brillan cuando permisos, funciones o atributos van empaquetados como bits dentro de un solo numero: una consulta te dice que bits aparecen en algun sitio y cuales estan en todas las filas.

Que calculan BIT_OR, BIT_AND y BIT_XOR

Imagina los permisos de un usuario empaquetados en un numero: el bit 1 es lectura, el bit 2 es escritura, el bit 4 es borrado. Los tres agregados responden preguntas distintas sobre un grupo de filas.

SELECT
    user_id,
    BIT_OR(flags)  AS any_bit_set,
    BIT_AND(flags) AS bits_in_every_row,
    BIT_XOR(flags) AS parity
FROM permissions
GROUP BY user_id;

• BIT_OR es una union: un bit queda activo si aparecio en al menos una fila. Asi se construye la mascara combinada de todos los permisos.
• BIT_AND es una interseccion: un bit sobrevive solo si esta presente en todas las filas del grupo. Es el comun denominador.
• BIT_XOR es la paridad: un bit vale 1 si aparecio un numero impar de veces. Util para sumas de control y para detectar valores sin pareja.

El resultado es un entero normal, que luego se lee con el operador &.

Combinar mascaras de permisos con BIT_OR

El caso mas comun es reunir cada permiso que tiene un usuario a partir de muchas filas (por roles, equipos o recursos):

SELECT
    user_id,
    BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id;

Con la mascara, comprueba un bit concreto con el mismo AND bit a bit. Por ejemplo, quien puede borrar (bit 4):

SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) = 4;

Trampa: escribe (mask & 4) = 4, no mask & 4 = 4. En PostgreSQL = tiene mas prioridad que &, asi que sin parentesis la expresion se interpreta como mask & (4 = 4) y falla por tipos o devuelve algo sin sentido. Pon siempre parentesis alrededor de la operacion de bits.

El mismo truco escala a paises o departamentos, por ejemplo que funciones estan activas para al menos un usuario de un pais:

SELECT
    u.country,
    BIT_OR(p.flags) AS country_features
FROM users u
JOIN permissions p ON p.user_id = u.id
GROUP BY u.country;

Encontrar bits comunes con BIT_AND

BIT_AND responde a "que tiene todo el mundo". Supon que necesitas los permisos compartidos por cada empleado de un departamento, el acceso minimo garantizado:

SELECT
    e.dept,
    BIT_AND(p.flags) AS common_to_all
FROM employees e
JOIN permissions p ON p.user_id = e.id
GROUP BY e.dept;

Un resultado de 0 significa que no hay ningun bit compartido. Es ideal para comprobar invariantes: por ejemplo, el bit "activo" (valor 1) deberia estar en todas las filas de un usuario.

SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_AND(flags) & 1) = 0;  -- somebody is missing the active bit

Modelar flags: habitos practicos

Para que los bits no se vuelvan numeros magicos, etiquetalos y calcula la mascara de forma explicita:

-- bit layout: 1 = read, 2 = write, 4 = delete, 8 = export
SELECT
    user_id,
    BIT_OR(flags) AS mask,
    (BIT_OR(flags) & 1) <> 0 AS can_read,
    (BIT_OR(flags) & 2) <> 0 AS can_write,
    (BIT_OR(flags) & 4) <> 0 AS can_delete
FROM permissions
GROUP BY user_id;

Algunas pautas de modelado:

• Un bit es un atributo independiente. No codifiques estados mutuamente excluyentes como bits; usa una columna de estado aparte.
• Un integer de 32 bits guarda 31 flags seguros (el bit mas alto es el signo); usa bigint para mas.
• Los agregados ignoran los NULL, igual que SUM o COUNT. Un grupo vacio produce NULL.
• Si los flags viven en un tipo bit(n), los mismos BIT_OR y BIT_AND funcionan sobre el y devuelven una cadena de bits.

Trampa: los agregados de bits no sustituyen la normalizacion. Si necesitas "encontrar a todos con el permiso X", un indice sobre el numero empaquetado no ayuda, porque el planificador no puede buscar un solo bit. Para esas consultas, manten un indice de expresion sobre ((flags & 4)) o mueve los permisos a su propia tabla.

MySQL y ClickHouse

MySQL tambien tiene BIT_OR, BIT_AND y BIT_XOR con la misma sintaxis, aunque una comparacion distinta de cero se lee mejor al probar un bit:

-- MySQL: users that can delete (bit 4)
SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) <> 0;

Diferencias que conviene recordar:

• En versiones antiguas de MySQL, BIT_AND sobre un grupo vacio devolvia "todos unos" (por ejemplo, 18446744073709551615) en vez de NULL. Verifica el comportamiento de tu version.
• En ClickHouse las funciones son groupBitOr, groupBitAnd y groupBitXor: nombres distintos, mismo significado.
• En todas partes el resultado sigue siendo un entero, asi que lee los bits con & y parentesis, atento a la prioridad de operadores.

Cuando el estado esta empaquetado en bits, estos tres agregados dan respuestas exactas a "alguien / todos / impar" en una sola pasada por el grupo, sin desplegar los flags en filas ni logica del lado de la aplicacion.