MySQL Bugs: #113887: MySQL fails to execute query if a subquery refer to a column of parent table

Bug #113887	MySQL fails to execute query if a subquery refer to a column of parent table
Submitted:	4 Feb 2024 2:12	Modified:	14 Jun 2024 21:31
Reporter:	Diancheng Wang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It should not report error. Currently MySQL reports:

ERROR 1247 (42S22): Reference 'c1' not supported (forward reference in item list)

How to repeat:
Table definations:
CREATE TABLE `t8` (
  `a` int DEFAULT NULL,
  `b` int DEFAULT NULL
);
CREATE TABLE `td4` (
  `id` int DEFAULT NULL
);
CREATE TABLE `t6` (
  `a` int DEFAULT NULL
);

The reproduce query:

select  ref_0.b as c0
from
  test.t8 as ref_0
where (EXISTS (
        select  ref_0.b as c10
          from
            (select (
                    select  ref_2.a as c0
                      from (select  ref_4.id as c0,
                              ref_0.a as c1
                            from
                              test.td4 as ref_4) as subq_0
                      limit 1) as c0,
                  ref_0.a as c2
                from
                  test.t6 as ref_2) as subq_1
          limit 122));

BTW, it is OK if I change query to:

select  ref_0.b as c0
from
  test.t8 as ref_0
where (EXISTS (
        select  ref_0.b as c10
          from
            (select (
                    select  ref_2.a as c0
                      from (select  ref_4.id as c0,
                              ref_0.a as c1
                            from
                              test.td4 as ref_4) as subq_0
                      limit 1) as c0,
                  ref_0.b as c2
                from
                  test.t6 as ref_2) as subq_1));

Hi Mr. Wang,

Thank you for your bug report.

We managed to reproduce your bug report in 8.0.36 and 8.3.0:

ERROR 1247 (42S22) at line 7: Reference 'c1' not supported (forward reference in item list)

Since a difference is only in LIMIT, this is now a verified bug report.

Thank you for your contribution.

Documented fix in the MySQL 9.0.0 changelog as follows:

    The server rejected a query containing a subquery which referred
    to a column of the parent table.

Closed.

Thank you, Jon.