Bug #114060 Unreasonable SQL Error (1054)

• Submitted: 20 Feb 2024 10:13
• Modified: 20 Feb 2024 13:42
• Reporter: Eugeny Nt
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.28
• OS: Ubuntu
• CPU Architecture: Any

[20 Feb 2024 10:13] Eugeny Nt

Description:
I cannot execute my query because of error like "/* SQL Error (1054): Unknown column 'tmp_24.dt2' in 'on clause' */".

I made a simple example how to reproduce the bug. It works on 8.0.33, but not on 8.0.28

How to repeat:
SELECT *
FROM (
	SELECT DATE(`dt2`) AS `dt3` 
	FROM (
		SELECT * FROM (
			SELECT 1 AS `c1`, 1 AS `c2` 
		) t1
		LEFT JOIN (
			SELECT `f1`, `f2`, MAX(`dt`) AS `dt2` 
			FROM (
				SELECT 1 AS `f1`, 1 AS `f2`, NOW() AS `dt`
			) tmp_23 
			GROUP BY `f1`, `f2`
		) tmp_24 
		ON ((`c1` <=> `f1`) AND (`c2` <=> `f2`))
	) tmp_25
) tmp_26
WHERE `dt3` < '2024-02-05'

[20 Feb 2024 11:13] MySQL Verification Team

Hi Mr. Nt,

Thank you very much for your bug report.

However, we are not able to repeat it with 8.0.36:

SELECT *
     FROM (
       SELECT DATE(`dt2`) AS `dt3`
       FROM (
         SELECT * FROM (
           SELECT 1 AS `c1`, 1 AS `c2`
         ) t1
         LEFT JOIN (
           SELECT `f1`, `f2`, MAX(`dt`) AS `dt2`
           FROM (
             SELECT 1 AS `f1`, 1 AS `f2`, NOW() AS `dt`
           ) tmp_23
           GROUP BY `f1`, `f2`
         ) tmp_24
         ON ((`c1` <=> `f1`) AND (`c2` <=> `f2`))
       ) tmp_25
     ) tmp_26
     WHERE `dt3` < '2024-02-05';

Empty set (0.03 sec)

We also ran "SHOW WARNINGS" and we did not get a single warning.

Can't repeat.

[20 Feb 2024 12:50] Eugeny Nt

I reported MySQL 8.0.28 version.

[20 Feb 2024 13:32] MySQL Verification Team

Hi,

MySQL 8.0.28 is not a version. MySQL 8.0.28 is a bug fix release, just like 8.0.36 is a bug fix release.

Hence, we only test with the latest release, since upgrading within 8.0 is trivial.

[20 Feb 2024 13:42] Eugeny Nt

Ok, I see. Thank you.