indexjoin should take collation into consideration

[XuHuaiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- open collate

drop table t1, t2;

create table t1 (
b enum('A', 'B') collate utf8mb4_general_ci
);
insert into t1 values ('A');

create table t2 (
b enum('b','a') collate utf8mb4_general_ci not null,
unique(b)
);
insert into t2 values ('a');

select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;

2. What did you expect to see? (Required)

+------+---+
| b    | b |
+------+---+
| A    | a |
+------+---+

3. What did you see instead (Required)

empty set

4. What is your TiDB version? (Required)

master @ 349adf8

[wjhuang2016]

I don't think it's a collation bug:

mysql> create table t1 (
    -> b enum('A', 'B') collate utf8mb4_general_ci
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values ('A');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> create table t2 (
    -> b enum('a','b') collate utf8mb4_general_ci not null,
    -> unique(b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values ('a');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
+------+---+
| b    | b |
+------+---+
| A    | a |
+------+---+
1 row in set (0.00 sec)

And

mysql> drop table t1, t2;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> create table t1 (
    -> b enum('A', 'B') collate utf8mb4_general_ci
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('A');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> create table t2 (
    -> b enum('B','A') collate utf8mb4_general_ci not null,
    -> unique(b)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values ('a');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select /*+ inl_join(t2)*/ * from t1, t2 where t1.b=t2.b;
Empty set (0.00 sec)