Bug #120519 Wrong result for BETWEEN predicate with LONGBLOB prefix PRIMARY KEY and implicit type conversion

• Submitted: 21 May 15:49
• Reporter: ss w
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.43
• OS: Any
• CPU Architecture: Any

[21 May 15:49] ss w

Description:
A query using a BETWEEN predicate may evaluate inconsistently between WHERE filtering and direct SELECT expression evaluation when a LONGBLOB column with a prefix PRIMARY KEY participates in implicit type conversion.

In this case, the predicate compares a string literal against a LONGBLOB column and a numeric upper bound. The direct SELECT expression evaluation indicates that the predicate evaluates to TRUE, while the same predicate in the WHERE clause incorrectly filters out the row and returns an empty result set.

Removing the prefix PRIMARY KEY constraint from the LONGBLOB column makes the inconsistency disappear, suggesting that the issue is related to optimizer handling of BETWEEN predicates over prefix-indexed BLOB columns combined with implicit type conversion.

How to repeat:
CREATE TABLE IF NOT EXISTS t1054 (c1 LONGBLOB, c2 BIT (44), PRIMARY KEY (c1(4)));
INSERT INTO t1054 (c1,c2) VALUES ('v0fidE2FGfPZWhOTHzPEDPQtCJyRS',b'00000011001000110100011111010010110001011011');
SELECT t1054.c1 FROM t1054 WHERE ('d' BETWEEN (t1054.c1) AND 0);
-- return 0 rows
SELECT SUM(count) FROM (SELECT ('d' BETWEEN (t1054.c1) AND 0) IS TRUE AS count FROM t1054) AS ta_norec;
-- return 1