Bug #114534 Inconsistent result with index and symbol '̏
Submitted: 3 Apr 2024 4:22 Modified: 3 Apr 2024 8:18
Reporter: Ming Wei Tan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[3 Apr 2024 4:22] Ming Wei Tan
Description:
Test case generated with SQLancer

Given a table with an index on a text column. 

CREATE TABLE t0(c0 MEDIUMTEXT);
CREATE INDEX i0 USING BTREE ON t0(c0(1) DESC);
INSERT INTO t0(c0) VALUES (1); 

The result size for a simple select query is 1. 
SELECT t0.c0 AS ref0 FROM t0; -- size 1

With the predicate '̏D' > (t0.c0) where '̏ (a quote and a combining double grave symbol (U+030F)) is used. Selects with the predicate and negated predicates both do not return any records.

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE ('̏D') > (t0.c0); -- size 0
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (! (('̏D') > (t0.c0))); -- size 0

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;
CREATE TABLE t0(c0 MEDIUMTEXT);
CREATE INDEX i0 USING BTREE ON t0(c0(1) DESC);
INSERT INTO t0(c0) VALUES (1), (NULL); 

SELECT t0.c0 AS ref0 FROM t0; -- size 2

SELECT ALL t0.c0 AS ref0 FROM t0 WHERE ('̏D') > (t0.c0); -- size 0
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (! (('̏D') > (t0.c0))); -- size 0
SELECT ALL t0.c0 AS ref0 FROM t0 WHERE (('̏D') > (t0.c0)) IS UNKNOWN; -- size 1
[3 Apr 2024 8:18] MySQL Verification Team
Hello Ming Wei Tan,

Thank you for the report and test case.

regards,
Umesh