Hex literals return wrong results in numeric context and text comparisons #575

Problem

The driver translates every hexadecimal literal to an SQLite blob (x'…'). MySQL treats a hex literal as a binary string by default, but as a number in numeric context. MySQL also compares a hex literal with a text value byte by byte. SQLite does neither: a blob converts to 0 in arithmetic, and a blob never equals a text value.

Reproduction

SELECT 0x1 + 0, 5 & 0x1, CAST(0x711 AS UNSIGNED);

CREATE TABLE t (id INT PRIMARY KEY, title TEXT, n INT);
INSERT INTO t VALUES (1, 'AB', 1);
SELECT COUNT(*) FROM t WHERE title = 0x4142;
INSERT INTO t VALUES (2, NULL, 0x711);

MySQL results are from MySQL 8.4. Driver results are from #573 at cd4384bc, with PHP 8.5.12 and SQLite 3.54.1. Even-length literals, such as 0x01 + 0, behave the same way on trunk.

This is uncommon in WordPress. Hex literals there mostly come from SQL dumps of binary columns, such as mysqldump --hex-blob, and those already work. Only the INSERT fails with an error. The other cases return wrong results silently.

Related

#538 is a similar binary-versus-text comparison problem with UNHEX().