Shape / columns · All operations

Operate on bits

A variant of Apply a maths function

Apply bitwise AND, OR, XOR, NOT or shifts to integers.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Apply bitwise AND, OR, XOR, NOT and shifts to integers

SQL
CREATE TEMP TABLE bits AS SELECT * FROM (VALUES (1, 12, 10), (2, 255, 15), (3, NULL, 10)) t(id, a, b);
CREATE TEMP TABLE result AS
SELECT id, a & b AS bit_and, a | b AS bit_or, xor(a, b) AS bit_xor, ~a AS bit_not, a << 2 AS shifted_left, a >> 2 AS shifted_right FROM bits;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT [bit_and, bit_or, bit_xor, bit_not, shifted_left, shifted_right] FROM result WHERE id = 1) = [8, 14, 6, -13, 48, 3] AND (SELECT [bit_and, bit_or, bit_xor, bit_not, shifted_left, shifted_right] FROM result WHERE id = 2) = [15, 255, 240, -256, 1020, 63] AND (SELECT count(*) FROM result WHERE id = 3 AND bit_and IS NULL AND bit_not IS NULL) = 1) THEN 'as expected' ELSE error('12 and 10 did not give and 8, or 14, xor 6, not -13 and the shifts 48 and 3') END AS verdict;
  • The statement builds its own data.
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “12 and 10 did not give and 8, or 14, xor 6, not -13 and the shifts 48 and 3”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (1)
Ibis (6)
Polars (1)

Background

In computer programming, a bitwise operation operates on a bit string, a bit array or a binary numeral at the level of its individual bits.[1] Bit shifts are sometimes considered bitwise operations, because they treat a value as a series of bits rather than as a numerical quantity.[1] In DuckDB, the bitwise and bitwise negation operators work only on integral data types.[2]

When to use it

ANDing a value with a pattern that has 1 only in a chosen bit shows whether that bit is set, a technique often called bit masking.[1] A bitwise AND can clear selected flags in a value whose bits each represent one Boolean state, an efficient way to store many Boolean values in little memory.[1] Reach for bitwise operations when a column packs several flags into one integer, and unpack each flag into a column of its own that a reader can filter on. (Meridian's view)

In DuckDB and PostgreSQL, ^ is an exponent operator, so 3 ^ 4 raises 3 to the fourth power.[2][3] DuckDB writes bitwise exclusive or as the function xor(x, y), and PostgreSQL writes it as the operator #, so both give 20 for 17 and 5.[2][3] Translate ^ with care when porting an expression from a language in which it means exclusive or. (Meridian's view)

The method

A bitwise AND takes two equal-length binary representations and performs the logical AND on each pair of corresponding bits.[1] The bitwise NOT, or bitwise complement, performs logical negation on each bit, so bits that are 0 become 1 and those that are 1 become 0.[1]

A left arithmetic shift by n is equivalent to multiplying by 2 to the power n, provided the value does not overflow.[1] A right arithmetic shift by n of a two's complement value is equivalent to taking the floor of division by 2 to the power n.[1] On simple low-cost processors, bitwise operations are typically substantially faster than division and several times faster than multiplication.[1]

Compared with the alternatives

DuckDB's BITSTRING type, alias BIT, holds variable-length strings of 1s and 0s, and casting 123 to it gives 00000000000000000000000001111011.[4] DuckDB's bit_count(x) returns the number of bits that are set, so bit_count(31) is 5.[2] Cast an integer to a bit string while working out a mask, then keep the integer operators in the query itself. (Meridian's view)

Sources

  1. [1]Bitwise operation - Wikipediasecondary source
  2. [2]Numeric Functions – DuckDBdocumentation
  3. [3]PostgreSQL: Documentation: 18: 9.3. Mathematical Functions and Operatorsdocumentation
  4. [4]Bitstring Type – DuckDBdocumentation