Bug #119305 The query results of enabling and disabling materialization are inconsistent.

• Submitted: 5 Nov 4:02
• Reporter: Alice Alice
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.41
• OS: Linux
• CPU Architecture: x86

[5 Nov 4:02] Alice Alice

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;