Hacktoberfest 2026: những issue maintainer đã đánh dấu cho tháng Mười, đang mở và phù hợp người mới. Xem issue Hacktoberfest

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

Đang mở
  • Có gì không ổn?
#538 0 bình luận 0 reaction 0 người được giao Xem trên GitHub

Maintainer thường phản hồi trong vòng 1 ngày

Chưa có ai nhận issue này.

Đánh giá

Độ khó
5/5
Thời gian dự kiến
Hơn một tuần
Mức phù hợp với người mới
35/100
Loại issue
Lỗi
Độ rõ ràng
Khá rõ ràng
Mức độ hoạt động
Sôi nổi
Công nghệ
php, sqlite
Lĩnh vực
backend, database

Hướng nghiên cứu

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.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Mô tả

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
Ngôn ngữ chính
PHP
Star
363
Fork
72
Merge trung bình
3 ngày 7 giờ
Pull request đã merge (30 ngày)
13

Chuẩn bị môi trường

Mở trong Codespaces

Khởi chạy dev container của dự án ngay trên trình duyệt, bằng tài khoản GitHub của bạn.

  • Không có Dockerfile hay tệp Docker Compose
  • Không có mẫu pull request
  • Không có hướng dẫn đóng góp

Bắt đầu từ đâu

  1. Đọc hết issue, rồi đọc hướng dẫn đóng góp của dự án.
  2. Bình luận trên issue rằng bạn sẽ nhận — tránh hai người làm cùng một việc.
  3. Fork repository và làm thay đổi trên một nhánh.
  4. Mở pull request có tham chiếu số hiệu của issue.

Issue khác của WordPress/sqlite-database-integration

Tất cả issue của WordPress/sqlite-database-integration

Issue tương tự

Thêm issue về PHP

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.