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:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.19 OS:Any
Assigned to: CPU Architecture:Any

[1 Apr 2020 11:07] Manuel Rigger
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: {}
[1 Apr 2020 11:25] MySQL Verification Team
Hello Manuel Rigger,

Thank you for the report.

regards,
Umesh