Bug #121578 Ref lookup misses matching row under expanding collation with full-key index
Submitted: 22 Oct 12:47 Modified: 23 Oct 5:51
Reporter: Laurynas Biveinis (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.4.13, 9.7.4, 26.10.0 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 12:47] Laurynas Biveinis 
Description:
CREATE TABLE t(
       id INT PRIMARY KEY,
       a INT,
       s VARCHAR(1) COLLATE utf8mb4_0900_ai_ci,
       KEY k(a, s)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1, 1, 'ß');

# Returns zero rows - incorrect
SELECT id FROM t FORCE INDEX(k) WHERE a = 1 AND s = 'ss';
# Returns one row - correct
SELECT id FROM t IGNORE INDEX(k) WHERE a = 1 AND s = 'ss';

With prefix indexes this would be bug 20447.

How to repeat:
See above
[22 Oct 13:10] Laurynas Biveinis 
See also bug 121580
[23 Oct 5:51] Chaithra Marsur Gopala Reddy 
Hi Laurynas Biveinis,

Thank you for the test case. Verified as described. However we already have a similar bug filed sometime back. So marking this as duplicate of Bug#113642. Bug#113642 is marked private for some reason. That will be corrected.

Thanks,
Chaithra