MySQL Bugs: #113551: Discrepancy in MySQL 8.2.0 Documentation Regarding Subquery Scoping Rules

Bug #113551	Discrepancy in MySQL 8.2.0 Documentation Regarding Subquery Scoping Rules
Submitted:	4 Jan 2024 10:04	Modified:	10 Jan 2024 7:34
Reporter:	Kang Rong	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.2.0	OS:	Linux (mysql docker image 8.2.0)
Assigned to:		CPU Architecture:	Any
Tags:	alias, document, scope rule, subquery

Description:
The current behavior of MySQL 8.2.0 subqueries does not match the scoping rule as described in the official MySQL documentation (https://dev.mysql.com/doc/refman/8.2/en/correlated-subqueries.html).

Specifically, the documentation states that MySQL evaluates from inside to outside, and in the case of nested subqueries with the same alias, it should reference the corresponding column of the most-inner subquery's table. However, the observed behavior allows references to outer tables where the documentation suggests this should not occur, without generating an expected error.

How to repeat:

1. Set up the database and tables:
```sql
CREATE DATABASE IF NOT EXISTS db_test;
USE db_test;
DROP TABLE IF EXISTS t1, t2, t3;

CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT);
CREATE TABLE t2 (c1 INT PRIMARY KEY, c3 INT);
CREATE TABLE t3 (c1 INT PRIMARY KEY, c4 INT);
```

2. Execute the queries that do not adhere to the documented scoping rules:

Example 1:
```sql
SELECT c1 FROM t1 AS x
WHERE x.c1 = (SELECT c1 FROM t2 AS x
  WHERE x.c1 = (SELECT c1 FROM t3
    WHERE x.c2 = t3.c1));
```
Expected Error: `Unknown column 'x.c2' in 'where clause'`

Actual Result: Empty set (0.01 sec), no error.

Explain: In this query, the innermost subquery's WHERE clause attempts to reference x.c2. According to the documentation, this should not be possible since t2, which is aliased as x in the outer subquery, does not have a column c2. Therefore, MySQL is expected to throw an error indicating Unknown column 'x.c2' in 'where clause', because the alias x in the innermost subquery should not be able to access columns of t1.

Example 2:
```sql
SELECT c1 FROM t1 AS x
WHERE x.c1 = (SELECT c1 FROM t2 AS x
  WHERE x.c1 = (SELECT c1 FROM t3
    WHERE x.c2 = t3.c1 AND x.c3 = t3.c4));
```
Expected Behavior: Error due to ambiguous alias usage.
Actual Result: Empty set (0.01 sec), no error.
Explain: The innermost subquery appears to reference x.c1 and x.c2, potentially aliasing x to both t1 and t2. The expected behavior here, based on standard SQL practices, might be to report an error for ambiguous alias usage, as it's unclear which table x refers to. The documentation seems to suggest that an alias should only map to a single table within its scope, raising the question of whether this behavior is intentional.

Suggested fix:
It would be great if the team could take a look at the behavior of subquery alias scoping and see if there's a mismatch with the documentation. If the current behavior is expected, perhaps a clarification in the documentation would help prevent any user confusion. If it's not, then adjusting the query execution to align with the docs would be awesome. Thanks for looking into this!

Hello!

Thank you for the bug report.
Imho this is not a bug, as per the documentation https://dev.mysql.com/doc/refman/8.2/en/correlated-subqueries.html

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

Regards,
Ashwini Patil

Dear Ashwini Patil and the MySQL Verification Team,

Thank you for your response. While I understand MySQL's approach to column scoping in subqueries, it appears to deviate from the current documentation, potentially leading to misunderstandings.

As a reader, I interpret the documentation to imply that an alias in a subquery shadows the same alias in outer queries, yet there is no mention of MySQL searching outer scopes for a column missing in inner scopes.

To enhance clarity, might we consider updating the documentation? A revised note could read:

```
Note: In subqueries, if a referenced column is not found within the immediate table alias, MySQL will continue to search for the column in the enclosing queries. This behavior ensures that MySQL does not immediately throw an error if an inner query's alias lacks the column, but instead looks for the column at outer query levels.
```

I believe such an update would greatly benefit users by clearly setting expectations for MySQL's behavior.

Warm regards,

Rong

Hello Rong,

Thank you for the feedback.

Regards,
Ashwini Patil