Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Aperta
  • Qualcosa non va?
#538 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
35/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
php, sqlite
Ambito
backend, database

Direzione di ricerca

Read translate_function_call() in packages/mysql-on-sqlite/src/sqlite/class-wp-mysql-on-sqlite.php and WP_SQLite_PDO_User_Defined_Functions::unhex() in packages/mysql-on-sqlite/src/sqlite/class-wp-sqlite-pdo-user-defined-functions.php. Reproduce the UUID equality and IN cases through the AST driver, then inspect existing tests for comparison coercion and CAST behavior. Done means MySQL-compatible UNHEX comparisons pass across binary and character operands, IN/NOT IN, joins, and numeric contexts while preserving existing CAST behavior.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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:

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
Lingua principale
PHP
Stelle
363
Fork
72
Merge medio
3g 7h
PR unite (30g)
13

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

  • Nessun Dockerfile né file Docker Compose
  • Nessun modello di pull request
  • Nessuna guida per i contributori

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di WordPress/sqlite-database-integration

Tutte le issue di WordPress/sqlite-database-integration

Issue simili

Altre issue su PHP

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.