|
Thanks for the report! Reproducible with Innodb, but not with Myisam, (after 10.2.2)
testcase:
CREATE TABLE `t1` (
|
`a` VARCHAR(50) COLLATE 'utf8mb4_unicode_nopad_ci' ,
|
`b` int,
|
INDEX (a)
|
) ENGINE=InnoDB;
|
|
INSERT INTO `t1` VALUES ('123', 123);
|
#wrong results --empty set -expected 123 123
|
SELECT a,b FROM t1 WHERE a LIKE '123%';
|
SELECT * FROM t1 WHERE a LIKE '123%';
|
|
#correct results
|
SELECT a FROM t1 WHERE a LIKE '123%';
|
SELECT a,b FROM t1 IGNORE INDEX (a) WHERE a LIKE '123%';
|
|
MariaDB [test]> select version();
|
+-----------------------+
|
| version() |
|
+-----------------------+
|
| 10.2.11-MariaDB-debug |
|
+-----------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> SELECT a,b FROM t1 WHERE a LIKE '123%';
|
Empty set (0.00 sec)
|
|
MariaDB [test]> SELECT * FROM t1 WHERE a LIKE '123%';
|
Empty set (0.00 sec)
|
|
MariaDB [test]> SELECT a FROM t1 WHERE a LIKE '123%';
|
+------+
|
| a |
|
+------+
|
| 123 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> SELECT a,b FROM t1 IGNORE INDEX (a) WHERE a LIKE '123%';
|
+------+------+
|
| a | b |
|
+------+------+
|
| 123 | 123 |
|
+------+------+
|
1 row in set (0.00 sec)
|
ANALYZE FORMAT=JSON SELECT * FROM t1 WHERE a LIKE '123%';
|
--------------
|
| ANALYZE
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.0539,
|
"table": {
|
"table_name": "t1",
|
"access_type": "range",
|
"possible_keys": ["a"],
|
"key": "a",
|
"key_length": "203",
|
"used_key_parts": ["a"],
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 0,
|
"r_total_time_ms": 0.0284,
|
"filtered": 100,
|
"r_filtered": 100,
|
"index_condition": "t1.a like '123%'"
|
}
|
}
|
} |
|
1 row in set (0.00 sec)
|
ANALYZE FORMAT=JSON SELECT a,b FROM t1 IGNORE INDEX (a) WHERE a LIKE '123%'
|
{
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 0.1091,
|
"table": {
|
"table_name": "t1",
|
"access_type": "ALL",
|
"r_loops": 1,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.0729,
|
"filtered": 100,
|
"r_filtered": 100,
|
"attached_condition": "t1.a like '123%'"
|
}
|
}
|
} |
|
|
|
Also repeatable with MyISAM, with more than one rows in the table:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(50) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_nopad_ci' NOT NULL,
|
b VARCHAR(50) CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci' NOT NULL,
|
INDEX `a` (`a`),
|
INDEX `b` (`b`)
|
) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('111', '111');
|
INSERT INTO t1 VALUES ('222', '222');
|
INSERT INTO t1 VALUES ('333', '333');
|
INSERT INTO t1 VALUES ('444', '444');
|
SELECT * FROM t1 WHERE a LIKE '111%';
|
SELECT * FROM t1 IGNORE INDEX (a) WHERE a LIKE '111%';
|
|
|
The same problem is repeatable with UCA collations for other Unicode character sets, e.g. ucs2:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(50) CHARACTER SET ucs2 COLLATE ucs2_unicode_nopad_ci NOT NULL,
|
b VARCHAR(50) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci NOT NULL,
|
INDEX `a` (`a`),
|
INDEX `b` (`b`)
|
) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES ('111', '111');
|
INSERT INTO t1 VALUES ('222', '222');
|
INSERT INTO t1 VALUES ('333', '333');
|
INSERT INTO t1 VALUES ('444', '444');
|
SELECT * FROM t1 WHERE a LIKE '111%';
|
SELECT * FROM t1 IGNORE INDEX (a) WHERE a LIKE '111%';
|
|
|
The same problem is repeatable with 8bit collations:
CREATE OR REPLACE TABLE t1 (
|
a VARCHAR(50) NOT NULL,
|
b VARCHAR(50) NOT NULL,
|
INDEX `a` (`a`)
|
) ENGINE=MyISAM CHARACTER SET latin1 COLLATE latin1_swedish_nopad_ci;
|
INSERT INTO t1 VALUES ('111', '111');
|
INSERT INTO t1 VALUES ('222', '222');
|
INSERT INTO t1 VALUES ('333', '333');
|
INSERT INTO t1 VALUES ('444', '444');
|
SELECT * FROM t1 WHERE a LIKE '111%';
|
SELECT * FROM t1 IGNORE INDEX (a) WHERE a LIKE '111%';
|
|