| Bug #99149 | BETWEEN query malfunctions for special character and TEXT index | ||
|---|---|---|---|
| Submitted: | 1 Apr 2020 11:07 | Modified: | 1 Apr 2020 11:25 |
| Reporter: | Manuel Rigger | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[1 Apr 2020 11:25]
MySQL Verification Team
Hello Manuel Rigger, Thank you for the report. regards, Umesh
Description: Consider the following statements: How to repeat: CREATE TABLE t0(c0 TEXT) ; INSERT INTO t0(c0) VALUES(0); CREATE INDEX i0 ON t0(c0(1)); SELECT * FROM t0 WHERE t0.c0 NOT BETWEEN '֣a' AND NULL; -- expected: {0}, actual: {} Unexpectedly, the SELECT does not fetch a row. When removing the INDEX, the row is correctly fetched. The negated predicate works as expected, and does not fetch any rows: SELECT * FROM t0 WHERE t0.c0 BETWEEN '֣a' AND NULL -- {} Note that '֣a' is a special character. In case the character is printed incorrectly in the bugtracker, you can reproduce it with the following, alternative statement: SELECT * FROM t0 WHERE t0.c0 NOT BETWEEN (CAST(UNHEX('d6a361') as CHAR(100))) AND NULL; -- expected: {0}, actual: {}