Param as column alias can't be parsed in group statement

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table test(id int, col int);
prepare stmt from "select id, ? as col1 from test where col=? group by id,col1";
set @a=100, @b=100;
execute stmt using @a,@b;

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

No error

3. What did you see instead (Required)

ERROR 1105 (HY000): Unknown column '100' in 'group statement'

4. What is your TiDB version? (Required)

v8.1.0

[YangKeao]

I think it's caused by the special behavior of group by ?, which will group by the N-th field. Ref #8153 🤦 . It's referred as *ast.PositionExpr in TiDB.

Every day I can learn a new mystery behavior of SQL.

[qw4990]

A workaround:

mysql>   prepare stmt from "select id, col1 from (select id, ? as col1 from test where col=?) t group by id,col1";
Query OK, 0 rows affected (0.00 sec)

mysql> set @a=100, @b=100;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @a,@b;
Empty set (0.01 sec)

[elsa0520]

Maybe order by will have the same problem ~ @qw4990

[qw4990]

Maybe order by will have the same problem ~ @qw4990

I tested "order-by" as well and it doesn't have this problem since the optimizer doesn't replace order by col to order by ? directly. So this seems a special "optimization" for Agg only.