Can someone explain this SQL query to me? I am not understanding what the buckets are doing or what even is a bucket or bitmask?

user_13847908 0
Viewed 98

Basically the task is to query data to isolate which users have a certain scope of permissions for a project.

select  ssm.user_id,s.scope
      from  user_available_scope_sets uass
      join  scope_set_masks ssm
        on  ssm.user_id = uass.user_id
       and  ssm.scope_set_id = uass.scope_set_id
      join  scopes s
        on  case floor(s.bitmask_index / 64)
            when 0 then bucket_0
            when 1 then bucket_1
            when 2 then bucket_2
            when 3 then bucket_3
            when 4 then bucket_4
            when 5 then bucket_5
            else 0
            end & (1 << (s.bitmask_index % 64)) != 0
     where  uass.user_id in (1, 2, 3)
postgresql sql
1 Answers

Here is the bits manipulations.

Assuming that the bitmask_index contains two significant bytes, for example:

┌ bit 15 bit 0 ┐
 byte 1  byte 0
┌──────┐┌──────┐
0000000011000101
└────────┘└────┘
    y       x

bitmask_index / 64 is same to bitmask_index >> 6 (shift bits right to 6 positions) - it is y in the example, binary 00000011, decimal 3

bitmask_index % 64 is the masking, same to bitmask_index & 63 (zero all bits higher then 5) - it is x in the example, binary 00000101, decimal 5

So, actually, bitmask_index field holds two values.

Next, 1 << (s.bitmask_index % 64) (shift 1 left to the specified value) means "value where x-th and only x-th bit is 1", for our example it is

    bit 5 ┐
0000000000100000

Finally value1 & value2 != 0 means "value1 and value2" have common bits.

Obviously that the bucket_0 - bucket_5 is the fields in the table (scope_set_masks probably?)

And all together:

Depending on the y value get bucket_<n> field and check is the x-th bit in it = 1

You could to play with it using PostgreSQL bit string types:

select
    '0000000011000101'::bit(16) as "Binary value",
    '0000000011000101'::bit(16)::int as "Decimal value",
    (197 / 64)::bit(16) as "197 / 64",
    (197 >> 6)::bit(16) as "197 >> 6",
    (197 % 64)::bit(16) as "197 % 64",
    (197 & 63)::bit(16) as "197 & 63",
    (1 << 5)::bit(16) as "1 << 5",
    (1 << (197 % 64))::bit(16) as "1 << (197 % 64)";

┌─[ RECORD 1 ]────┬──────────────────┐
│ Binary value    │ 0000000011000101 │
│ Decimal value   │ 197              │
│ 197 / 64        │ 0000000000000011 │
│ 197 >> 6        │ 0000000000000011 │
│ 197 % 64        │ 0000000000000101 │
│ 197 & 63        │ 0000000000000101 │
│ 1 << 5          │ 0000000000100000 │
│ 1 << (197 % 64) │ 0000000000100000 │
└─────────────────┴──────────────────┘
user_5931440
Related