UNHEX() binary UUID comparisons silently return no rows (breaks Staatic) #538

Description

The AST driver silently returns no matches when a binary column is compared with UNHEX(...), even when the stored bytes match. Equality and IN lookups are affected.

We encountered this with Staatic 1.13.2, which stores UUIDs in binary columns. Its Publications page crashes while hydrating an existing publication because the associated build lookup returns no row:

PublicationRepository::rowToPublication():
Argument #2 ($build) must be of type Staatic\Framework\Build, null given

The build and deployment records exist. Publication setup also reports that an inserted publication does not exist.

Environment

• SQLite Database Integration 3.1.0; reproduced against commit 6546e4538b708a1d8a565a1d31688bd63b610fa3.
• AST driver / pure-PHP parser.
• Isolated reproduction and prototype tests: PHP 8.4.26, PDO SQLite 3.45.1, PHPUnit 8.5.56, in-memory databases.
• Application where the issue was discovered: Staatic 1.13.2.

Minimal reproduction

Run these MySQL queries through the integration's AST driver (rather than directly against a native SQLite connection):

CREATE TABLE uuid_lookup_test (uuid BINARY(16) PRIMARY KEY);
INSERT INTO uuid_lookup_test
  VALUES (X'00112233445566778899AABBCCDDEEFF');

SELECT COUNT(*) AS matches FROM uuid_lookup_test
  WHERE uuid = UNHEX(REPLACE(
    '00112233-4455-6677-8899-aabbccddeeff', '-', ''));

SELECT COUNT(*) AS matches FROM uuid_lookup_test
  WHERE uuid IN (UNHEX(REPLACE(
    '00112233-4455-6677-8899-aabbccddeeff', '-', '')));

Expected: both counts are 1, matching MySQL behavior.

Actual: both counts are 0 on the unpatched driver. Reversed equality and a join using binary_column = UNHEX(hex_column) also fail in regression tests.

Cause identified during investigation

WP_SQLite_PDO_User_Defined_Functions::unhex() returns a PHP string from pack(). PDO SQLite exposes that callback result as SQLite TEXT, while the driver stores binary columns as BLOBs. The different SQLite storage classes cause matching bytes to compare unequal.

Relevant code at the reproduced commit:

• AST function translation
• PHP UNHEX decoder

Workaround and possible fix direction

A scoped query filter replacing prepared, valid UUID expressions with equivalent X'...' binary literals restores the Staatic lookups without changing stored records. This is only an application workaround; the driver needs to handle arbitrary tables, nested expressions, and runtime column arguments.

A local proof of concept adds this to translate_function_call(), using the already translated AST arguments:

case 'UNHEX':
    return sprintf( 'CAST(UNHEX(%s) AS BLOB)', implode( ', ', $args ) );

This cast alone is not a complete global fix. These comparisons both need to return 1 under MySQL semantics:

SELECT UNHEX('4142') = X'4142';
SELECT UNHEX('4142') = 'AB';

The cast-only prototype returns 1 for the first and 0 for the second. A complete fix needs MySQL-compatible operand coercion, including binary/character comparisons, IN/NOT IN, joins, and numeric contexts. Type-aware handling in binary-column predicates may be another approach. The existing translation of MySQL CAST(... AS BINARY) to TEXT COLLATE BINARY should retain its established behavior.

The decoder also has boundary discrepancies: UNHEX(NULL) returns an empty string, invalid hex can raise PHP pack() warnings, and odd-length input is decoded incorrectly. The prototype returns NULL for NULL/invalid input, permits the empty string, and prepends a zero for odd-length valid input (e.g. UNHEX(123) should produce X'0123'). MySQL warning 1411 emulation is outside that prototype's scope.

Recorded prototype validation

The local prototype includes the BLOB cast, decoder corrections, and 14 regression cases covering UUID lookups and mutations, runtime-column joins, empty/mixed-case hex, NUL/non-UTF-8 bytes, NULL/invalid input, odd-length/numeric input, and result-name compatibility.

• Before: 10 failures and 4 errors across the 14 new cases.
• After: 14 tests, 58 assertions, all passing.
• Full package suite with the prototype: 1,057 tests, 1,429,652 assertions; no failures, 15 skipped, 2 incomplete.
• WordPress coding standards passed for the three changed files.

These are results from the isolated local investigation. They do not cover the unresolved mixed binary/character comparison above or the full supported PHP/SQLite CI matrix. The prototype has not been deployed to production.

MySQL references

• UNHEX binary-string and NULL/invalid-input behavior
• Comparison type-conversion rules
• MySQL regression results, including odd-length UNHEX input