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
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• 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.

[17 Jun 9:41] MySQL Verification Team

Thank you, Jon.