MDEV-6105: Emoji unicode character string search query makes mariadb performance down

- When range optimizer cannot the lookup value into [VAR]CHAR(n) column,
  it should produce:
  = "Impossible range" for equality
  = "no range" for non-equalities.

3 files changed, 103 insertions, 0 deletions

diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index d41bfaa2c67..21acfee897e 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -2046,3 +2046,57 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# MDEV-6105: Emoji unicode character string search query makes mariadb performance down
+#
+SET NAMES utf8;
+DROP TABLE IF EXISTS t1;
+Warnings:
+Note	1051	Unknown table 't1'
+CREATE TABLE t1(
+id int AUTO_INCREMENT,
+fd varchar(20),
+primary key(id),
+index ix_fd(fd)
+)engine=innodb default charset=UTF8;
+INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim');
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+# The following should show "Impossible WHERE" :
+explain
+SELECT * FROM t1 WHERE fd='😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+SELECT * FROM t1 WHERE fd='😁';
+id	fd
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+# The following must not use range access:
+explain select count(*) from t1 where fd <'😁';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	ix_fd	ix_fd	63	NULL	41410	Using where; Using index
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 where fd <'😁';
+count(*)
+40960
+Warnings:
+Warning	1366	Incorrect string value: '\xF0\x9F\x98\x81' for column 'fd' at row 1
+select count(*) from t1 ignore index (ix_fd) where fd <'😁';
+count(*)
+40960
+drop table t1;
+set names default;
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index dc9ca0012fe..5791509a739 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1638,3 +1638,41 @@ UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORDER BY f1;
 SELECT * FROM v3;
 DROP TABLE t1;
 DROP VIEW v3;
+
+--echo #
+--echo # MDEV-6105: Emoji unicode character string search query makes mariadb performance down
+--echo #
+SET NAMES utf8;
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1(
+id int AUTO_INCREMENT,
+fd varchar(20),
+primary key(id),
+index ix_fd(fd)
+)engine=innodb default charset=UTF8;
+INSERT INTO t1(id, fd) VALUES (null, ''),(null, 'matt'),(null, 'pitt'),(null, 'lee'),(null, 'kim');
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+INSERT INTO t1 (fd) SELECT fd FROM t1;
+--echo # The following should show "Impossible WHERE" :
+explain
+SELECT * FROM t1 WHERE fd='😁';
+SELECT * FROM t1 WHERE fd='😁';
+
+--echo # The following must not use range access:
+explain select count(*) from t1 where fd <'😁';
+select count(*) from t1 where fd <'😁';
+select count(*) from t1 ignore index (ix_fd) where fd <'😁';
+
+drop table t1;
+set names default;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 66bd287d86a..5db080394eb 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -7775,6 +7775,17 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
   if (field->cmp_type() == STRING_RESULT && value->cmp_type() != STRING_RESULT)
     goto end;
   err= value->save_in_field_no_warnings(field, 1);
+  if (err == 2 && field->cmp_type() == STRING_RESULT)
+  {
+    if (type == Item_func::EQ_FUNC)
+    {
+      tree= new (alloc) SEL_ARG(field, 0, 0);
+      tree->type= SEL_ARG::IMPOSSIBLE;
+    }
+    else 
+      tree= NULL; /*  Cannot infer anything */
+    goto end;
+  }
   if (err > 0)
   {
     if (field->cmp_type() != value->result_type())