unexpected unknown column error

[XuHuaiyu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

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

2. What did you expect to see? (Required)

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 (Required)

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

4. What is your TiDB version? (Required)

7e15333 in master

[qw4990]

The minimal reproducible case:

select 1 as a,(select a )

[qw4990]

The root cause is that in expressionRewriter.toColumn the sub-query cannot get the outer field-list(which is [1 as a] in this query).
This issue is not easy to fix, and consider its influence scope, we decide not to fix it now and degrade its severity to moderate.

[caoyipeng2008]

The root cause is that in expressionRewriter.toColumn the sub-query cannot get the outer field-list(which is [1 as a] in this query). This issue is not easy to fix, and consider its influence scope, we decide not to fix it now and degrade its severity to moderate.

I tried the case:
select 1;

then get the error result:
Error Code: 1054. Unknown column '0' in 'field list'

this issue influence the mybatis pagehelper tool , course ' select count(0)'

It seems a terrible issue

[winoros]

The root cause is that in expressionRewriter.toColumn the sub-query cannot get the outer field-list(which is [1 as a] in this query). This issue is not easy to fix, and consider its influence scope, we decide not to fix it now and degrade its severity to moderate.

I tried the case: select 1;

then get the error result: Error Code: 1054. Unknown column '0' in 'field list'

this issue influence the mybatis pagehelper tool , course ' select count(0)'

It seems a terrible issue

@caoyipeng2008 Hi, what's the impacted SQL in the mybatis's pagehelper tool?

[caoyipeng2008]

The root cause is that in expressionRewriter.toColumn the sub-query cannot get the outer field-list(which is [1 as a] in this query). This issue is not easy to fix, and consider its influence scope, we decide not to fix it now and degrade its severity to moderate.

I tried the case: select 1;
then get the error result: Error Code: 1054. Unknown column '0' in 'field list'
this issue influence the mybatis pagehelper tool , course ' select count(0)'
It seems a terrible issue

@caoyipeng2008 Hi, what's the impacted SQL in the mybatis's pagehelper tool?

page helper tool use 'select count(0)' as the default to get the total count.
however I found it could be config to 'select count(*)'.
The project : https://github.com/pagehelper/Mybatis-PageHelper

but this issue make some trouble. and alse any select 'number' as column should be avoided.

[AilinKid]

similar case as it depicted

mysql> explain select (select a union select a) as a, 1 as b;
ERROR 1247 (42S22): Reference 'a' not supported (forward reference in item list)

which means scalar subquery can only refer forward-defined items in the outer select list.

[longfeiss]

MySQL allows backward reference which may be useful in some situations.
Below are some cases of MySQL：

create table t1(id int, c1 int, c2 int);
create table t3(id int, c3 int);


mysql> select id+1 as foobar, (select c1 from t1 where id = foobar) from t3;
Empty set (0.00 sec)

mysql> select abs(-1)+1 as foobar, (select c1 from t1 where id = foobar) from t3;
Empty set (0.00 sec)

mysql> select count(*) as foobar, (select c1 from t1 where id = foobar) from t3;
ERROR 1247 (42S22): Reference 'foobar' not supported (reference to group function)

mysql> select count(*) as foobar, (select c1 from t1 having id = foobar) from t3;
+--------+----------------------------------------+
| foobar | (select c1 from t1 having id = foobar) |
+--------+----------------------------------------+
|      0 |                                   NULL |
+--------+----------------------------------------+

mysql> select count(*) as foobar, (select c1 from t1 group by foobar) from t3;
ERROR 1247 (42S22): Reference 'foobar' not supported (reference to group function)

mysql> select id+1 as foobar, (select c1 from t1 group by foobar) from t3;
Empty set (1.86 sec)

mysql> select (select c1 from t1 where id = foobar), id as foobar from t3;
ERROR 1247 (42S22): Reference 'foobar' not supported (forward reference in item list)

mysql> select id as foobar, foobar as foobar1 from t3;
ERROR 1054 (42S22): Unknown column 'foobar' in 'field list'

mysql> select count(*) as foobar, (select c1 from t1 where id = foobar) from t3;
Empty set (0.00 sec)


mysql> select id+1 as foobar from t3 having (select 1 from t1 having id < foobar) < 10;
Empty set (47.81 sec)

mysql> select (select 1 from t1 having id < foobar), id+1 as foobar from t3;
ERROR 1247 (42S22): Reference 'foobar' not supported (forward reference in item list)