Bug #106716 Incorrect Inner hash join when transforming materialization condition.

Description:
Incorrect Inner hash join when transforming materialization condition.

How to repeat:
CREATE TABLE `t0` (
  `c0` float DEFAULT NULL
);

CREATE TABLE `t1` (
  `c0` float DEFAULT NULL
);

INSERT INTO `t0` VALUES (NULL),(NULL),(NULL),(675842000),(1972),(292269000),(NULL),(421510000),(-1725340000),(1553690000);

INSERT INTO `t1` VALUES (1985),(292269000),(2006),(2068090000),(-1513190000),(NULL),(1974),(-921353000),(793842000),(NULL),(-1346170000),(NULL),(-1066950000),(1947);

mysql> SET optimizer_switch='default';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ALL t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0 = t0.c0 WHERE t1.c0 IN (SELECT t0.c0 FROM t0 WHERE (t1.c0 NOT IN (SELECT t1.c0 FROM t1 )) = (1) IN (t1.c0) );
Empty set (0.00 sec)

SET optimizer_switch='materialization=off';

mysql> SELECT ALL t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0 = t0.c0 WHERE t1.c0 IN (SELECT t0.c0 FROM t0 WHERE (t1.c0 NOT IN (SELECT t1.c0 FROM t1 )) = (1) IN
(t1.c0) );
+-----------+
| c0        |
+-----------+
| 292269000 |
+-----------+
1 row in set (0.01 sec)

mysql> explain format=tree SELECT ALL t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0
 = t0.c0 WHERE t1.c0 IN (SELECT t0.c0 FROM t0 WHERE (t1.c0 NOT IN (SELECT t1.c0
FROM t1 )) 
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Remove duplicate (t0, t1) rows using temporary table (weedout)  (cost=25.75 rows=14)
    -> Inner hash join (t1.c0 = t0.c0), (<in_optimizer>(t1.c0,not <exists>(select #3)) = (1 = t0.c0))  (cost=25.75 rows=14)
        -> Table scan on t1  (cost=0.04 rows=14)
        -> Hash
            -> Inner hash join (t0.c0 = t0.c0)  (cost=11.50 rows=10)
                -> Table scan on t0  (cost=0.04 rows=10)
                -> Hash
                    -> Table scan on t0  (cost=1.25 rows=10)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> SET optimizer_switch='materialization=on';
Query OK, 0 rows affected (0.00 sec)

mysql> explain format=tree SELECT ALL t1.c0 FROM t1 RIGHT OUTER JOIN t0 ON t1.c0 = t0.c0 WHERE t1.c0 IN (SELECT t0.c0 FROM t0 WHERE (t1.c0 NOT IN (SELECT t1.c0 FROM t1 )) = (1) IN (t1.c0) );
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Remove duplicate (t0, t1) rows using temporary table (weedout)  (cost=25.75 rows=14)
    -> Inner hash join (t1.c0 = t0.c0), (<in_optimizer>(t1.c0,t0.c0 not in (select #3)) = (1 = t0.c0))  (cost=25.75 rows=14)
        -> Table scan on t1  (cost=0.04 rows=14)
        -> Hash
            -> Inner hash join (t0.c0 = t0.c0)  (cost=11.50 rows=10)
                -> Table scan on t0  (cost=0.04 rows=10)
                -> Hash
                    -> Table scan on t0  (cost=1.25 rows=10)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.01 sec)

Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh