Description:
mysql> set optimizer_switch="materialization=on";
2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;Query OK, 0 rows affected (0.00 sec)
mysql> select ref_1.C_STREET_1 as c0, ref_1.C_SINCE as c1 from t0_range_key_subpartition as ref_0 left join t12 as ref_1 on (EXISTS ( select ref_3.varbinary_col as c0, ref_2.c_d_id as c1 from t0_hash_partition_p1 as ref_2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;
+------+---------------------+
| c0 | c1 |
+------+---------------------+
| NULL | 2020-11-05 09:29:49 |
| NULL | 2020-11-05 09:29:48 |
+------+---------------------+
2 rows in set (0.02 sec)
mysql> set optimizer_switch="materialization=on";
Query OK, 0 rows affected (0.00 sec)
mysql> select ref_1.C_STREET_1 as c0, ref_1.C_SINCE as c1 from t0_range_key_subpartition as ref_0 left join t12 as ref_1 on (EXISTS ( select ref_3.varbinary_col as c0, ref_2.c_d_id as c1 from t0_hash_partition_p1 as ref_2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;
+------+---------------------+
| c0 | c1 |
+------+---------------------+
| NULL | 2020-11-05 09:29:49 |
| NULL | 2020-11-05 09:29:48 |
+------+---------------------+
2 rows in set (0.02 sec)
mysql> set optimizer_switch="materialization=off";
Query OK, 0 rows affected (0.00 sec)
mysql> select ref_1.C_STREET_1 as c0, ref_1.C_SINCE as c1 from t0_range_key_subpartition as ref_0 left join t12 as ref_1 on (EXISTS ( select ref_3.varbinary_col as c0, ref_2.c_d_id as c1 from t0_hash_partition_p1 as ref_2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;
+------+------+
| c0 | c1 |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.43 sec)
How to repeat:
create database test;
use test
source test_table.sql
set optimizer_switch="materialization=on";
select ref_1.C_STREET_1 as c0, ref_1.C_SINCE as c1 from t0_range_key_subpartition as ref_0 left join t12 as ref_1 on (EXISTS ( select ref_3.varbinary_col as c0, ref_2.c_d_id as c1 from t0_hash_partition_p1 as ref_2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;
set optimizer_switch="materialization=off";
select ref_1.C_STREET_1 as c0, ref_1.C_SINCE as c1 from t0_range_key_subpartition as ref_0 left join t12 as ref_1 on (EXISTS ( select ref_3.varbinary_col as c0, ref_2.c_d_id as c1 from t0_hash_partition_p1 as ref_2 left join alltypecol_t0 as ref_3 on ((ref_3.mediumtext_col is NULL)) where (ref_3.mediumblob_col is not NULL) )) group by 1,2 order by 1,2 desc;
