Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
11.8.1, 10.5, 10.6, 10.11, 11.4
Description
I run the following statements, in which the query returns an incorrect result.
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} |
I got the following query plan.
mysql> EXPLAIN SELECT c1 FROM t1 WHERE 'a' BETWEEN 0 AND (c1);
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | range | c1 | c1 | 5 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
The problem is also repeatable the other way around, when args[1] is a field:
CREATE OR REPLACE TABLE t1 (c1 TINYBLOB, UNIQUE (c1(2))); |
INSERT INTO t1 (c1) VALUES ('-1'),('-2'); |
SELECT c1 FROM t1 WHERE '#' BETWEEN c1 AND 0; -- 0 rows - wrong |
SELECT c1 FROM t1 IGNORE KEY(c1) WHERE '#' BETWEEN c1 AND 0; -- 2 rows - correct |
|
|
CREATE OR REPLACE TABLE t1 (c1 VARBINARY(10), UNIQUE (c1)); |
INSERT INTO t1 (c1) VALUES ('-1'),('-2'); |
SELECT c1 FROM t1 WHERE '#' BETWEEN c1 AND 0; -- 0 rows - wrong |
SELECT c1 FROM t1 IGNORE KEY(c1) WHERE '#' BETWEEN c1 AND 0; -- 2 rows - correct |
|
|
CREATE OR REPLACE TABLE t1 (c1 BINARY(10), UNIQUE (c1)); |
INSERT INTO t1 (c1) VALUES ('-1'),('-2'); |
SELECT c1 FROM t1 WHERE '#' BETWEEN c1 AND 0; -- 0 rows - wrong |
SELECT c1 FROM t1 IGNORE KEY(c1) WHERE '#' BETWEEN c1 AND 0; -- 2 rows - correct |
Hi Gosselin. Can you please also add tests when args[1] is a field. See above. Thanks.
Hi Gosselin,
https://github.com/MariaDB/server/pull/3964/commits/2d48eac80cd868452bbac41462687e09e82f06b4
is OK to push.
Thanks!
For the release notes:
BETWEEN no longer produces an incorrect result when one of the boundaries requires a cast from a STRING to a numeric type and the other boundary is a binary type (e.g. BLOB) with a UNIQUE prefix.
Gosselin, please find review comments on github.