Bug #113887 MySQL fails to execute query if a subquery refer to a column of parent table
Submitted: 4 Feb 2:12 Modified: 14 Jun 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

[4 Feb 2:12] Diancheng Wang
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));
[5 Feb 11:19] MySQL Verification Team
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.
[14 Jun 21:31] Jon Stephens
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.