MySQL Bugs: #116971: CTE With clause bug

Bug #116971	CTE With clause bug
Submitted:	13 Dec 2024 12:03	Modified:	16 Dec 2024 7:24
Reporter:	Sagar Vyas	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If there are two tables T1 and T2, I have created two temperoray table T1_temp and T2_temp with some columns selected form T1 and T2

With T1_temp as (select fullName, city from T1),
T2_temp as (select id, userId, status from T2 )
SELECT s.id, s.userId, s.status FROM T2_temp s 
where s. userId =  (SELECT DISTINCT id FROM T1_temp limit 1) ;

When I run this query, It runs successfully, here "id" column is not in the select list of T1_temp but is referenced form the base table T1.

How to repeat:

By Creating two tables:

CREATE TABLE T1 (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    fullName VARCHAR(255) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE T2 (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    userId BIGINT NOT NULL,
    status VARCHAR(50)
);

and run query:

With T1_temp as (select fullName, city from T1),
T2_temp as (select id, userId, status from T2 )
SELECT s.id, s.userId, s.status FROM T2_temp s 
where s. userId =  (SELECT DISTINCT id FROM T1_temp limit 1) ;

Suggested fix:
Error should be returned like:

Error Code: 1054. Unknown column 'id' in 'field list'

queryI have provided is working that is the bug.

My t1_temp CTE does not have a column "id" in the select list and still I am able to do a select on that column.
Even If I do a select on a column which is unique to the table and not in the CTE, the query still works.

Even if I don't use CTE,

select * from T1 where fullName in (select fullName from T2);

this is strange because there's no column "fullname" in T2.

I have tried other sql servers where it fails.

Hi Mr. Vyas,

That is incredible, but this is very much true.

This goes against every SQL standard.

This is a minor bug, but still truly a bug .......

Verified as S3 bug for all currently supported versions from 8.0 to 9.1.

Thank you for your contribution.

Posted by developer:
 
This is not a bug.
In the subquery, the column "id" is available as an outer reference
from the outer query on the CTE T2_temp. You can make it explicit by
substituting "id" with "s.id" in the subquery.

Thank you, Roy.