select var_pop(distinct xxx) could successfully execute in TiDB but failed in MySQL

[Win-Man]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

• TiDB

mysql> create table t1 (grp int, a bigint unsigned, c char(10) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,1,"a");
Query OK, 1 row affected (0.01 sec)

mysql> select var_pop(distinct grp) from t1;
+-----------------------+
| var_pop(distinct grp) |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)

• MySQL -8.0.19

[email protected] : gangshen 02:25:53> create table t1 (grp int, a bigint unsigned, c char(10) not null);
Query OK, 0 rows affected (0.02 sec)

[email protected] : gangshen 02:25:57>  insert into t1 values (1,1,"a");
Query OK, 1 row affected (0.00 sec)

[email protected] : gangshen 02:26:01> select var_pop(distinct grp) from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct grp) from t1' at line 1

• MySQL 5.7.31:

mysql [localhost:5731] {msandbox} ((none)) > select var_pop(distinct grp) from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct grp) from t1' at line 1

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

select var_pop(distinct xxx) failed.
I‘ve refered MySQL docs: link
It seems var_pop/stdev func couldn't contains distinct in it.

3. What did you see instead (Required)

It could execute successfully.

4. What is your TiDB version? (Required)

• nightly

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[time-and-fate]

/label sig/execution

[hidehalo]

/pick-up

[ti-challenge-bot]

Pick up success.

[ti-challenge-bot]

@hidehalo You did not submit PR within 7 days, so give up automatically.

[hidehalo]

/pick-up

[ti-challenge-bot]

Pick up success.

[dyzsr]

Postgres also support std/var_pop/var_samp(distinct), do we really have to disable it? @XuHuaiyu

[kennytm]

(for those not following, please check the discussion in pingcap/parser#1116.)

[ti-challenge-bot]

@hidehalo You did not submit PR within 7 days, so give up automatically.