MySQL Bugs: #116343: An unexpected result occurs when using an alias within a subquery in SQL

Bug #116343	An unexpected result occurs when using an alias within a subquery in SQL
Submitted:	11 Oct 2024 12:08	Modified:	14 Oct 2024 7:22
Reporter:	ru tu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0 (
wkey INTEGER
);

CREATE TABLE t1 (
pkey INTEGER PRIMARY KEY,
c1 INTEGER
);

INSERT INTO t0 VALUES(1);
INSERT INTO t1 VALUES(2,3);SELECT *
FROM t0 AS ref_0
WHERE EXISTS (
  SELECT
    1 AS c0
  FROM
    (t1  
      LEFT OUTER JOIN (
          SELECT
            t0.wkey AS c1
          FROM t0   
      ) AS subq_0
    ON (t1.c1 = subq_0.c1 ))
  WHERE  
           (subq_0.c1 <> (t1.pkey)));

The execution of the above statement returns an empty set, which is expected. However, when I changed t0.wkey to ref_0.wkey in the join clause, I got the following statement. They seem equivalent; ref_0 is an alias for table t0. The execution result should also be the same empty set. But it returned 1.
SELECT *
FROM t0 AS ref_0
WHERE EXISTS (
  SELECT
    1 AS c0
  FROM
    (t1  
      LEFT OUTER JOIN (
          SELECT
            ref_0.wkey AS c1
          FROM t0   
      ) AS subq_0
    ON (t1.c1 = subq_0.c1 ))
  WHERE  
           (subq_0.c1 <> (t1.pkey)));

There seems to be some kind of bug; perhaps aliases should not be used with t0 in this way. If that's the case, I think an error should be returned to prevent users from using it like this.

How to repeat:

DROP TABLE IF EXISTS t0;
DROP TABLE IF EXISTS t1;

CREATE TABLE t0 (
wkey INTEGER
);

CREATE TABLE t1 (
pkey INTEGER PRIMARY KEY,
c1 INTEGER
);

INSERT INTO t0 VALUES(1);
INSERT INTO t1 VALUES(2,3);

SELECT *
FROM t0 AS ref_0
WHERE EXISTS (
  SELECT
    1 AS c0
  FROM
    (t1  
      LEFT OUTER JOIN (
          SELECT
            t0.wkey AS c1
          FROM t0   
      ) AS subq_0
    ON (t1.c1 = subq_0.c1 ))
  WHERE  
           (subq_0.c1 <> (t1.pkey)));

SELECT *
FROM t0 AS ref_0
WHERE EXISTS (
  SELECT
    1 AS c0
  FROM
    (t1  
      LEFT OUTER JOIN (
          SELECT
            ref_0.wkey AS c1
          FROM t0   
      ) AS subq_0
    ON (t1.c1 = subq_0.c1 ))
  WHERE  
           (subq_0.c1 <> (t1.pkey)));

Hi Mr. tu,

Thank you for your bug report.

We have successfully reproduced it on the latest 8.0, 8.4 and 9.0.

This is now a verified bug report.

Thank you.

Posted by developer:
 
This is not a bug.
Using correlation names or range variables as aliases for table expressions
is defined by the SQL standard, and is the way that you can refer to
multiple instances of a table within a query block (self join),
or in an inner and an outer query block (the latter being used for outer references).

But to get the query semantics right, it is very important that these names
are used consistently when referencing columns.

Thank you, Roy.