planner: predicates cannot be pushed into sub-queries for HashJoin

Enhancement

mysql> CREATE TABLE t1 (
    -> risk_id varchar(32) NOT NULL,
    -> tran_id int NOT NULL,
    -> KEY (`risk_id`)
    -> );

mysql> CREATE TABLE t2 (
    -> risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
    -> -- risk_id varchar(32) NOT NULL,
    -> tran_id int NOT NULL,
    -> KEY (`risk_id`)
    -> );

mysql> explain select /*+ hash_join(t1) */ t1.risk_id 
    -> from t1 
    -> where t1.risk_id = 'xxxx' 
    -> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| id                          | estRows  | task      | access object                    | operator info                                           |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_18                 | 8.00     | root      |                                  | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_24(Build)     | 10000.00 | root      |                                  | index:IndexFullScan_23                                  |
| │ └─IndexFullScan_23        | 10000.00 | cop[tikv] | table:t2, index:risk_id(risk_id) | keep order:false, stats:pseudo                          |
| └─IndexReader_20(Probe)     | 10.00    | root      |                                  | index:IndexRangeScan_19                                 |
|   └─IndexRangeScan_19       | 10.00    | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo   |
+-----------------------------+----------+-----------+----------------------------------+---------------------------------------------------------+

The predicate t1.risk_id = 'xxxx' should be propagated to t2 and the optimizer should generate a IndexRangeScan for t2. If remove COLLATE utf8mb4_general_ci, this predicate can be pushed down.

mysql> CREATE TABLE t2 (
    -> -- risk_id varchar(32) COLLATE utf8mb4_general_ci NOT NULL,
    -> risk_id varchar(32) NOT NULL,
    -> tran_id int NOT NULL,
    -> KEY (`risk_id`)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> explain select /*+ hash_join(t1) */ t1.risk_id 
    -> from t1 
    -> where t1.risk_id = 'xxxx' 
    -> and exists (select 1 from t2 where t2.risk_id=t1.risk_id);
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| id                          | estRows | task      | access object                    | operator info                                           |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+
| HashJoin_21                 | 8.00    | root      |                                  | semi join, equal:[eq(test.t1.risk_id, test.t2.risk_id)] |
| ├─IndexReader_25(Build)     | 10.00   | root      |                                  | index:IndexRangeScan_24                                 |
| │ └─IndexRangeScan_24       | 10.00   | cop[tikv] | table:t2, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo   |
| └─IndexReader_23(Probe)     | 10.00   | root      |                                  | index:IndexRangeScan_22                                 |
|   └─IndexRangeScan_22       | 10.00   | cop[tikv] | table:t1, index:risk_id(risk_id) | range:["xxxx","xxxx"], keep order:false, stats:pseudo   |
+-----------------------------+---------+-----------+----------------------------------+---------------------------------------------------------+