Alice Sherepa added a comment - 2017-11-17 14:24 - edited 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%'" } } } |

Alexander Barkov added a comment - 2017-12-08 06:14 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%' ;

Alexander Barkov added a comment - 2017-12-08 06:30 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%' ;

Alexander Barkov added a comment - 2017-12-08 07:48 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%' ;