Bug #99971 colmn name from outer query cannot be resolved in the subquery

• Submitted: 24 Jun 2020 11:50
• Modified: 25 Jun 2020 12:14
• Reporter: Yushan ZHANG
• Status: Not a Bug
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0
• OS: Any
• CPU Architecture: Any
• Tags: parser, subquery

[24 Jun 2020 11:50] Yushan ZHANG

Description:
Test case taken from: https://bugs.mysql.com/bug.php?id=5247

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
| b     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    2 |    4 |
|    2 |    5 |
|    3 |   10 |
+------+------+
5 rows in set (0.00 sec)

-- cannot execute
mysql> select a+1 as c from t1 where exists (select * from t1 where a=2 and b=c) and b >=4;
ERROR 1054 (42S22): Unknown column 'c' in 'where clause'

How to repeat:
create table t1 (a int, b int);
insert into t1 values (1,2), (1,3), (2,4), (2,5), (3,10);
select a+1 as c from t1 where exists (select * from t1 where a=2 and b=c) and b >=4;

[24 Jun 2020 14:25] MySQL Verification Team

Hi Mr. ZHANG,

Thank you for your bug report.

I repeated your bug in both latest 5.7 and 8.0. I have analysed latest SQL standard and I do not see any reason why an alias column could not be used in the nested query.

Verified as reported.

[25 Jun 2020 0:15] Øystein Grøvlen

Why should it be allowed in subquery when it is generally not allowed in WHERE clause:

mysql> select a+1 as c from t1 where c = 2;
ERROR 1054 (42S22): Unknown column 'c' in 'where clause'

[25 Jun 2020 11:53] MySQL Verification Team

Hi  Øystein Grøvlen,

I have analysed SQL standard. Could not find anything on why would this be prohibited.

Can you provided a reasoning behind it ????

[25 Jun 2020 12:14] MySQL Verification Team

Actually, this turned out to be documented limitation and it will stay so indefinitely.

 https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html