Bug #117634 Incorrect query results

• Submitted: 6 Mar 14:00
• Modified: 7 Mar 12:57
• Reporter: John Jove
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.4.4, 8.0.41
• OS: Any
• CPU Architecture: Any

[6 Mar 14:00] John Jove

Description:
Run the following statements, in which the query result is incorrect.
I got the following query plan.
mysql> EXPLAIN SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | c1            | c1   | 5       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

How to repeat:
CREATE TABLE t1 (c1 TINYBLOB, UNIQUE (c1(2)));
INSERT INTO t1 (c1) VALUES (1);
SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); -- actual: {}, expected: {1}

[7 Mar 6:16] John Jove

I try the BLOB data type with NOT NULL, in which a correct result is returned.

CREATE TABLE t1 (c1 TINYBLOB NOT NULL);
INSERT INTO t1 VALUES (1);
SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1); -- {0x31}

It seems that the NOT NULL constraint causes a different calculation from the UNIQUE constraint against the BLOB data type.

[7 Mar 12:57] MySQL Verification Team

Hello John,

Thank you for the report and test case.
Verified as described.

regards,
Umesh