CentOS 5.6 x86_64

– The full SQL script demonstrating the problem:

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;
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;
SELECT * FROM t1 WHERE fd='��';

– A join with another table with an UTF8MB4 VARCHAR column is also slow:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (fd VARCHAR(10)) CHARSET=UTF8MB4;
INSERT INTO t2 VALUES (_utf8mb4'��');
SELECT * FROM t1,t2 WHERE t1.fd=t2.fd;

I think, the problem starts to happen earlier than the location pointed by Matt74. get_best_combination() is called after join optimization. The first sign of the problem is in range optimization (i.e. before join optimization has started):

Debugging the query
SELECT * FROM t1 WHERE fd='��';

and being at:
#0 get_mm_leaf (...)
#1 0x000000000096d38e in get_mm_parts (...)
#2 0x000000000096c03f in get_func_mm_tree (...)
#3 0x000000000096c251 in get_full_func_mm_tree (...)
#4 0x000000000096cfe9 in get_mm_tree (...)
#5 0x0000000000961f9d in SQL_SELECT::test_quick_select (...)

the execution calls:

err= value->save_in_field_no_warnings(field, 1);

and gets err=2. However, it ignores the return value, and produces the range of empty strings - ['', '']. Then it proceeds to get #rows estimates for it, construct possible range access, and eventually convert it into ref(const) access with create_ref_for_key().

Questions:

• Does the problem affect only range optimizer, or ref access is affected, too?

• Can one fix the range optimizer by having get_mm_parts() return SEL_TREE(SEL_TREE::IMPOSSIBLE) when
  when value->save_in_field_no_warnings() returned 2? (This is probably much more complex than that. What if one runs
  t.key BETWEEN 'okay-string' AND '��' . Here, one endpoint can be represented in UTF-8, while the other cannot. If both endpoints cannot be represented, does it mean we can return SEL_TREE::IMPOSSIBLE from get_mm_parts?)

More similar smiley characters for the unicode input-challenged.

Codes of these characters http://apps.timwhitlock.info/emoji/tables/unicode

Re the example with join: note that index access on t1.fd is not used:

explain SELECT * FROM t1,t2 WHERE t1.fd=t2.fd;

+------+-------------+-------+-------+---------------+-------+---------+------+-------+--------------------------------------------------------------+

| id   | select_type | table | type  | possible_keys | key   | key_len | ref  | rows  | Extra                                                        |

+------+-------------+-------+-------+---------------+-------+---------+------+-------+--------------------------------------------------------------+

|    1 | SIMPLE      | t2    | ALL   | NULL          | NULL  | NULL    | NULL |     1 |                                                              |

|    1 | SIMPLE      | t1    | index | NULL          | ix_fd | 63      | NULL | 42652 | Using where; Using index; Using join buffer (flat, BNL join) |

+------+-------------+-------+-------+---------------+-------+---------+------+-------+--------------------------------------------------------------+

in debugger, one can see that the WHERE clause is converted to:

$52 = 0x198cc00 "(convert(`j2`.`t1`.`fd` using utf8mb4) = `j2`.`t2`.`fd`)"

The above means that ref access is not affected. The problem is only in range access.

For range access, we need to find out what range can be inferred from "t.key CMP $value" when $value cannot be represented in the charset of t.key.

For the record: direct mentions of non-utf8 characters in MySQL client (like fd='��') cause another bug, MDEV-6218 (which is repeatable on mysql also). That bug, however is different from this one.

So, this bug only repeats when we have

• SET NAMES utf8;
• then use a literal that is not a valid UTF-8 character.

If I attempt to use the right character set, I get an error:

MariaDB [j5]> set names utf8mb4;

Query OK, 0 rows affected (0.00 sec)

MariaDB [j5]> explain SELECT * FROM t1 WHERE fd='��';

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation '='

Notes from discussion with with bar: Let's explore how the comparison is done in a non-index case:

MariaDB [j5]> set names utf8;

Query OK, 0 rows affected (0.00 sec)

MariaDB [j5]> create table t3 (val varchar(20)) CHARSET=utf8;

Query OK, 0 rows affected (0.86 sec)

MariaDB [j5]> insert into t3 values (''),('?'),('a');

Query OK, 3 rows affected (0.12 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [j5]>  select val, val='��' from t3;

+------+------------+

| val  | val='��'     |

+------+------------+

|      |          0 |

| ?    |          0 |

| a    |          0 |

+------+------------+

3 rows in set (0.00 sec)

MariaDB [j5]> select collation('��');

+-------------------+

| collation('��')     |

+-------------------+

| utf8_general_ci   |

+-------------------+

1 row in set (0.00 sec)

That is, this utf-8 character that isn't really an utf-8 character is not equal to neither empty string, nor the '?' sign.

Debugging, one can see that the comparison is done in my_strnncollsp_utf8(), which has this code:

    s_res=my_utf8_uni(cs,&s_wc, s, se);

    t_res=my_utf8_uni(cs,&t_wc, t, te);

    if ( s_res <= 0 || t_res <= 0 )

    {

      /* Incorrect string, compare byte by byte value */

      return bincmp(s, se, t, te);

    }

that is, characters that don't fit into UTF-8 cause the comparison to be done in a byte-by-byte way.

... Now, if we want index lookups to work in the same way as comparison works with non-indexed columns:

• "key_col= $unrepresentable_char" is always false, because $unrepresentable_char will not be byte-by-byte equal to value of key_col (attempt to INSERT an $unrepresentable_char into the table will force its conversion into a question mark)

• as for "key_col < $unrepresentable_char", and other comparisons: byte-by-byte comparison doesn't map into any range in UTF-8 collation.

For non-equality comparisons, index scans are not equivalent to evaluating the WHERE (which is a separate bug):

MariaDB [j5]> select count(*) from t1 where fd <'��';

+----------+

| count(*) |

+----------+

|    16384 |

+----------+

1 row in set, 1 warning (0.08 sec)

MariaDB [j5]> select count(*) from t1 ignore index (ix_fd) where fd <'��';

+----------+

| count(*) |

+----------+

|    81920 |

+----------+

1 row in set (0.37 sec)

Possible solution:

• "key_col=$unrepresentable_char" should produce SEL_TREE(SEL_TREE::IMPOSSIBLE)
• all other "key_col CMP $unrepresentable_char" should produce NULL (i.e "cant use this for range access")

the second may cause queries to run slower than they ran before, but they will not produce incorrect results (like shown in the previous comment).

Oracle's MySQL has some related fixes:

• Bug#11752201 ( jorgen.loland@oracle.com-20120525130050-mejp6m431gyx2z0d) - it fixes a different problem, but the effect is that they don't get the wrong query results like shown two comments earlier
• A refactoring of Item::save_in_field and co.: jorgen.loland@oracle.com-20120507082918-pex86bcedbzemtsb .

Pushed a "simple" fix into 5.5, without backport of 5.6's refactoring. (porting 5.6's refactoring would not fully this this bug, so it would be overdoing it. refactoring should be pushed into 10.0)