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)