Bug #120174 Incorrect empty result for BETWEEN on binary string columns when a UNIQUE index is used

• Submitted: 28 Mar 9:25
• Modified: 29 Mar 20:21
• Reporter: mu mu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[28 Mar 9:25] mu mu

Description:
For predicates of the form '#' BETWEEN c1 AND 0 on several binary string types with a UNIQUE index on c1, the optimizer uses a range/ref access on that index and returns 0 rows. The same predicate with the index ignored (IGNORE INDEX) performs a full table scan and returns the expected non-empty result (2 rows for the sample data).

The same SQL must not change row count depending only on whether an index is used.

How to repeat:
DROP DATABASE IF EXISTS bug_between_index;
CREATE DATABASE bug_between_index;
USE bug_between_index;

-- Case 1: TINYBLOB with prefix UNIQUE index
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1(2)));
INSERT INTO t1 (c1) VALUES ('-1'), ('-2');

SELECT COUNT(*) AS cnt_indexed
FROM t1
WHERE '#' BETWEEN c1 AND 0;

SELECT COUNT(*) AS cnt_ignore_index
FROM t1 IGNORE INDEX (c1)
WHERE '#' BETWEEN c1 AND 0;
-- Observed: cnt_indexed = 0, cnt_ignore_index = 2

-- Case 2: VARBINARY with UNIQUE on full column
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 VARBINARY(10), UNIQUE (c1));
INSERT INTO t1 (c1) VALUES ('-1'), ('-2');

SELECT COUNT(*) FROM t1 WHERE '#' BETWEEN c1 AND 0;
SELECT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE '#' BETWEEN c1 AND 0;
-- Same discrepancy: 0 vs 2

-- Case 3: BINARY(10) with UNIQUE
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 BINARY(10), UNIQUE (c1));
INSERT INTO t1 (c1) VALUES ('-1'), ('-2');

SELECT COUNT(*) FROM t1 WHERE '#' BETWEEN c1 AND 0;
SELECT COUNT(*) FROM t1 IGNORE INDEX (c1) WHERE '#' BETWEEN c1 AND 0;
-- Same discrepancy: 0 vs 2

[29 Mar 20:21] Roy Lyseng

Thank you for the bug report.
Verified as described.