Bug #111073 The mutil-layer subquery results are inconsistent after group by.

• Submitted: 18 May 2023 12:14
• Modified: 18 May 2023 12:50
• Reporter: dong Harry
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7.41
• OS: CentOS
• CPU Architecture: x86

[18 May 2023 12:14] dong Harry

Description:
I found that with multi-level subqueries and join, the data returned by group by will be NULL.

How to repeat:
1)query command1:

select  
  subq_1.c0 as c0
from 
  (select  
        subq_0.c4 as c0
      from 
        (select  
                94 as c4
              from 
                sqltester.alltypecol_t1 as ref_0
              ) as subq_0
          right join sqltester.t0 as ref_1
          on (true)
      ) as subq_1;

result:
+------+
| c0   |
+------+
| 94  |
+------+

2)query command2:

select  
  subq_1.c0 as c0
from 
  (select  
        subq_0.c4 as c0
      from 
        (select  
                94 as c4
              from 
                sqltester.alltypecol_t1 as ref_0
              ) as subq_0
          right join sqltester.t0 as ref_1
          on (true)
      ) as subq_1

group by 1
order by 1 desc;

result:
+------+
| c0   |
+------+
| NULL |

expect:

+------+
| c0   |
+------+
| 94  |
+------+

Suggested fix:
mysql8 will return a expective result

[18 May 2023 12:50] MySQL Verification Team

Hi Mr. Harry,

Thank you for your bug report.

However, we can not repeat your query since we do not have your table and your data.

Also, version 5.7 is now maintained for hard bugs only, but this is not a hard bug. The fact that it is only fixed in 8.0 confirms that 5.7 is too mature to be changed for low severity bugs.

Can't repeat.