BIT_OR(), BIT_AND() and BIT_XOR() aggregates are not supported #526

Reproduction

CREATE TABLE b (x INT);
INSERT INTO b (x) VALUES (1), (4), (NULL);
SELECT BIT_OR(x), BIT_AND(x), BIT_XOR(x) FROM b;
-- SQLSTATE[HY000]: General error: 1 no such function: BIT_OR

Expected (MySQL 8): 5, 0, 5. Over an empty set, MySQL returns 0 for BIT_OR and BIT_XOR, and 18446744073709551615 for BIT_AND. NULLs are ignored, and the result type is BIGINT UNSIGNED.

Reproduced on trunk (3.1.0) with PHP 8.3 and SQLite 3.45.1.

Implementation question

Registering PHP aggregates through createAggregate() / sqliteCreateAggregate() looks natural next to the existing UDFs, but one constraint makes the design a maintainers' call: pdo_sqlite passes integers into and out of PHP callbacks as 32 bits (verified on PHP 8.3: an argument of 4294967297 arrives as 1, and a returned 4294967297 comes back as 1). Correct 64-bit results would need either:

• a translation that passes the argument as text (CAST(expr AS TEXT)) and returns the result as text, plus a decision on how values above 2^63 (for example the empty-set BIT_AND) should surface; or
• a pure-SQL translation per bit (for example COALESCE(MAX((expr >> n) & 1), 0) << n summed over the 64 bits for BIT_OR), which is exact but large.

A PHP aggregate limited to 32-bit values would cover common flag columns, but would silently return wrong results for wider ones, so I haven't proposed one. Happy to contribute a PR for whichever direction you prefer.

Found while running WooCommerce on WordPress Playground.

Use of AI

This report was prepared with Claude Code.