Description:
Two queries are equivalent (the second one is made by taking the first one and removing a useless "left join(SELECT 1) c on 1") , but they give different results. Analysis of the data in tables suggests that the correct result is one row of NULLs.
Verified with 'trunk' pulled today (debug build) and 8.0.41.
How to repeat:
I am going to attach 3 files of SQL (one per table) Please execute them with 'source <filename>'. Then run:
SELECT a.char_col, a.varbinary_col FROM multi_tbl_3_all_type2index a right join multi_tbl_3_all_typeindex_desc on(a.varbinary_col, a.decimal_col) in(select col1, col2 from multi_tbl_3_all_type2index_desc right join(SELECT varbinary_col col1, decimal_col col2 from multi_tbl_3_all_typeindex_desc where varbinary_col) b on 'aaaa' left join(SELECT 1) c on 1) group by 1,2 order by 1,2;
and
SELECT a.char_col, a.varbinary_col FROM multi_tbl_3_all_type2index a right join multi_tbl_3_all_typeindex_desc on(a.varbinary_col, a.decimal_col) in(select col1, col2 from multi_tbl_3_all_type2index_desc right join(SELECT varbinary_col col1, decimal_col col2 from multi_tbl_3_all_typeindex_desc where varbinary_col) b on 'aaaa') group by 1,2 order by 1,2;
I get
mysql> SELECT a.char_col, a.varbinary_col FROM multi_tbl_3_all_type2index a right join multi_tbl_3_all_typeindex_desc on(a.varbinary_col, a.decimal_col) in(select col1, col2 from multi_tbl_3_all_type2index_desc right join(SELECT varbinary_col col1, decimal_col col2 from multi_tbl_3_all_typeindex_desc where varbinary_col) b on 'aaaa' left join(SELECT 1) c on 1) group by 1,2 order by 1,2;
+----------+------------------------------+
| char_col | varbinary_col |
+----------+------------------------------+
| NULL | NULL |
+----------+------------------------------+
1 row in set, 7 warnings (0,01 sec)
mysql> SELECT a.char_col, a.varbinary_col FROM multi_tbl_3_all_type2index a right join multi_tbl_3_all_typeindex_desc on(a.varbinary_col, a.decimal_col) in(select col1, col2 from multi_tbl_3_all_type2index_desc right join(SELECT varbinary_col col1, decimal_col col2 from multi_tbl_3_all_typeindex_desc where varbinary_col) b on 'aaaa' ) group by 1,2 order by 1,2;
+----------+------------------------------+
| char_col | varbinary_col |
+----------+------------------------------+
| aaa2 | 0x64646432 |
| aaaa | 0x6464646464646464646431 |
| aabb | 0x6464646464646464646431 |
| aacd | 0x6464646464646464646431 |
| ab | 0x61 |
| ab | 0x64 |
+----------+------------------------------+
6 rows in set, 1 warning (0,00 sec)
Note: this bug seems to be a bit random: run one query then the other, and repeat, after a few tries you'll get the wrong non-NULL result.
