BIT_OR e BIT_AND no PostgreSQL: agregados bit a bit sobre mascaras de flags

BIT_OR e BIT_AND sao funcoes de agregacao que percorrem uma coluna inteira de um grupo e aplicam OR ou AND bit a bit a todos os valores de uma vez. Elas brilham quando permissoes, recursos ou atributos vao empacotados como bits dentro de um unico numero: uma consulta diz quais bits aparecem em algum lugar e quais estao em todas as linhas.

O que BIT_OR, BIT_AND e BIT_XOR calculam

Imagine as permissoes de um usuario empacotadas em um numero: o bit 1 e leitura, o bit 2 e escrita, o bit 4 e exclusao. Os tres agregados respondem perguntas diferentes sobre um grupo de linhas.

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 e uma uniao: um bit fica ativo se apareceu em pelo menos uma linha. Assim se constroi a mascara combinada de todas as permissoes.
• BIT_AND e uma intersecao: um bit sobrevive apenas se estiver presente em todas as linhas do grupo. E o denominador comum.
• BIT_XOR e a paridade: um bit vale 1 se ocorreu um numero impar de vezes. Util para somas de verificacao e para achar valores sem par.

O resultado e um inteiro comum, que voce le depois com o operador &.

Combinar mascaras de permissoes com BIT_OR

O caso mais comum e reunir cada permissao que um usuario tem a partir de muitas linhas (por papeis, times ou recursos):

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

Com a mascara, teste um bit especifico com o mesmo AND bit a bit. Por exemplo, quem pode excluir (bit 4):

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

Pegadinha: escreva (mask & 4) = 4, nao mask & 4 = 4. No PostgreSQL = tem prioridade maior que &, entao sem parenteses a expressao e interpretada como mask & (4 = 4) e falha por tipos ou retorna algo sem sentido. Sempre coloque parenteses na operacao de bits.

O mesmo truque escala para paises ou departamentos, por exemplo quais recursos estao ativos para ao menos um usuario de um 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 comuns com BIT_AND

BIT_AND responde a "o que todo mundo tem". Suponha que voce precise das permissoes compartilhadas por cada funcionario de um departamento, o acesso minimo garantido:

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;

Um resultado 0 significa que nao ha nenhum bit compartilhado. E otimo para checar invariantes: por exemplo, o bit "ativo" (valor 1) deveria estar em todas as linhas de um 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 praticos

Para que os bits nao virem numeros magicos, rotule-os e calcule a 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;

Algumas diretrizes de modelagem:

• Um bit e um atributo independente. Nao codifique estados mutuamente exclusivos como bits; use uma coluna de status separada.
• Um integer de 32 bits guarda 31 flags seguros (o bit mais alto e o sinal); use bigint para mais.
• Os agregados ignoram os NULL, assim como SUM ou COUNT. Um grupo vazio produz NULL.
• Se os flags vivem em um tipo bit(n), os mesmos BIT_OR e BIT_AND funcionam sobre ele e retornam uma cadeia de bits.

Pegadinha: agregados de bits nao substituem a normalizacao. Se voce precisa "encontrar todos com a permissao X", um indice sobre o numero empacotado nao ajuda, porque o planejador nao consegue buscar um unico bit. Para essas consultas, mantenha um indice de expressao sobre ((flags & 4)) ou mova as permissoes para sua propria tabela.

MySQL e ClickHouse

O MySQL tambem tem BIT_OR, BIT_AND e BIT_XOR com a mesma sintaxe, embora uma comparacao diferente de zero leia melhor ao testar um bit:

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

Diferencas que vale lembrar:

• Em versoes antigas do MySQL, BIT_AND sobre um grupo vazio retornava "todos uns" (por exemplo, 18446744073709551615) em vez de NULL. Verifique o comportamento da sua versao.
• No ClickHouse as funcoes sao groupBitOr, groupBitAnd e groupBitXor: nomes diferentes, mesmo significado.
• Em todo lugar o resultado continua sendo um inteiro, entao leia os bits com & e parenteses, atento a prioridade dos operadores.

Quando o estado esta empacotado em bits, esses tres agregados dao respostas exatas a "alguem / todos / impar" em uma unica passagem pelo grupo, sem desdobrar os flags em linhas nem logica no lado da aplicacao.