Cloud

Bitwise Shift Right

Bitwise shift operators in Redpanda SQL manipulate the bits of integer value by shifting them left or right. These operations are fundamental in low-level data processing and optimization.

The bitwise right shift (>>) operator shifts the bits of an integer to the right by the specified number of positions. For integers, this operation is equivalent to dividing the integer value by 2 raised to the power of the shift amount, discarding any remainder. Unlike a logical shift, the right shift in Redpanda SQL is an arithmetic shift, meaning that the vacant leftmost bits are filled with the original sign bits (the most significant bit) rather than zeros. This preserves the sign of the integer after the shift, ensuring correct behavior for signed values. During the shift, low-order bits that move beyond the size limit are permanently lost, but the overall bit width remains constant.

Syntax

The syntax for this function is:

value >> shift_amount

Parameters

  • value: Integer expression.

  • shift_amount: A non-negative integer specifying how many bit positions to shift.

Restrictions

Bitwise shift operators in Redpanda SQL require the shift amount to be a non-negative integer. Redpanda SQL treats negative shift counts as valid by applying modulo arithmetic based on the bit width, so shifting 1 >> -3 in a 32-bit integer is equivalent to shifting 1 >> 29, producing predictable results without errors or undefined behavior.

When performing bitwise right shift operations (>>) on 32-bit integer values in Redpanda SQL, the shift count is taken modulo 32, just as with left shifts. This means: * Shifting by a number of bits greater than or equal to 32 wraps around * For example, 1 >> 35 is equivalent to 1 >> 3 because 35\(modulo\)32 = 3

If you shift by a value larger than or equal to 32, the actual shift is the remainder after dividing by 32, which may lead to unexpected results if not carefully considered.

Examples

This section uses a simplified version of the film table from the Pagila database, containing only the title, rating and privileges columns. The complete schema for the film table can be found on the Pagila database website.

DROP TABLE IF EXISTS film;
CREATE TABLE film (
  title TEXT NOT NULL,
  rating TEXT,
  privileges INT NOT NULL
);
INSERT INTO film(title, rating, privileges) VALUES
  ('ATTRACTION NEWTON', 'PG-13', 1),     -- Free users
  ('CHRISTMAS MOONSHINE', 'NC-17', 2),   -- Premium users
  ('DANGEROUS UPTOWN', 'PG', 3),         -- Free + Premium users (bits 0 and 1)
  ('KILL BROTHERHOOD', 'G', 4),          -- Admin-only content
  ('HALLOWEEN NUTS', 'PG-13', 1),
  ('HOURS RAGE', 'NC-17', 2),
  ('PIANIST OUTFIELD', 'NC-17', 3),
  ('PICKUP DRIVING', 'G', 4),
  ('INDEPENDENCE HOTEL', 'NC-17', 1),
  ('PRIVATE DROP', 'PG', 2),
  ('SAINTS BRIDE', 'G', 3),
  ('FOREVER CANDIDATE', 'NC-17', 4),
  ('MILLION ACE', 'PG-13', 1),
  ('SLEEPY JAPANESE', 'PG', 2),
  ('WRATH MILE', 'NC-17', 3),
  ('YOUTH KICK', 'NC-17', 4),
  ('CLOCKWORK PARADISE', 'PG-13', 1);

  • Privilege 1 (binary 0001): Free users can watch.

  • Privilege 2 (binary 0010): Premium users can watch.

  • Privilege 3 (binary 0011): Both free and premium users can watch.

  • Privilege 4 (binary 0100): Admin-only content.

The following query uses the integer right shift (>>) operation, shifting the privileges value right by 1 for the movie ‘DANGEROUS UPTOWN’:

UPDATE film
SET privileges = privileges >> 1
WHERE title = 'DANGEROUS UPTOWN';

After running the update, you can verify the change with:

SELECT title, privileges FROM film WHERE title = 'DANGEROUS UPTOWN';

Expected output:

       title       | privileges
-------------------+------------
 DANGEROUS UPTOWN |          1
(1 row)