[MDEV-8626] Bad result set with expansions when using a prefix key Created: 2015-08-14  Updated: 2022-12-16

Status: Confirmed
Project: MariaDB Server
Component/s: Character Sets, Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.1, 10.3

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description   

This bug is very similar to MDEV-8625.

The script with no index:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
 
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('ä');
 
SELECT * FROM t1 WHERE a<='ae';

and the script with KEY(a):

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci, KEY(a));
 
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('ä');
 
SELECT * FROM t1 WHERE a<='ae';

correctly return two rows:

+------+
 
| a    |
 
+------+
 
| a    |
 
| ä    |
 
+------+

This script with KEY(a(1)):

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci, KEY(a(1)));
 
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('ä');
 
SELECT * FROM t1 WHERE a<='ae';

erroneously returns only one row:

+------+
 
| a    |
 
+------+
 
| a    |
 
+------+

The problem happens because when building a search value for the prefix key lookup, the character combination 'ae' (which is equal to 'ä') is broken apart and only the leading letter 'a' is stored into the search value.
Generated at Thu Feb 08 07:28:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.