Error 1054: Unknown column 'a' in 'field list'

[XuHuaiyu]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

select 1 as a,(select a union select a);

2. What did you expect to see?

mysql> select 1 as a,(select a union select a);
+---+---------------------------+
| a | (select a union select a) |
+---+---------------------------+
| 1 |                         1 |
+---+---------------------------+
1 row in set (0.00 sec)

3. What did you see instead?

tidb> select 1 as a,(select a union select a);
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

tidb> select tidb_version();                                                        +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.0-rc.3-147-g379ee5b19
Git Commit Hash: 379ee5b1915a37d14e45230acf4d911c6b80d07d
Git Branch: master
UTC Build Time: 2018-11-06 03:12:11
GoVersion: go version go1.11 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

SIG slack channel

#sig-exec

Score

300

Mentor

• @lzmhhh123

[jingyugao]

/assign

[ghost]

Confirming that this issue can still be reproduced in master:

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-771-gca41972fb
Edition: Community
Git Commit Hash: ca41972fbac068c8a5de107d9075f09ac68842ac
Git Branch: master
UTC Build Time: 2020-07-14 02:41:21
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

mysql> select 1 as a,(select a union select a);
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

[xuyifangreeneyes]

I found that whether a can be referenced in subquery is different for the following two cases.

mysql> select 1 as a, (select a);
+---+------------+
| a | (select a) |
+---+------------+
| 1 |          1 |
+---+------------+
1 row in set (0.00 sec)

mysql> select 1 as a where 1 = (select a);
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

MySQL correlated subquery specification seems not mention the difference of the two cases.

[xuyifangreeneyes]

select 1 as a where 1 = (select a) is illegal. For SQL like select ... from ... where ..., order of building plan is From Clause -> Where Clause -> Select(Project) Clause. Hence, when we handle Where Clause where 1 = (select a), we haven't met 1 as a and it throws Unknown column 'a' in 'field list' error.

[winoros]

If we want to fix it now.
For a case like the following one:

• we have table t0 whose columns are c0 and c1.
• we have SQL select c0 as cc, (select c0 where cc = c1) ccc, (select ccc where c1 > 0) from t0

We will build plan like proj(ccc, cc, col_for_subq)->Apply(proj(ccc, cc, Source(t0).Columns...), Apply(proj(cc, Source(t0).Columns...), Source(t0))