not compatible with MySQL when handling some subqueries with aggregation functions

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

CREATE TABLE t ( v decimal(20, 3) );
insert into t values (1);
select sum(v)/123*123 from t;  // MySQL & TiDB: 1.0000000
select x * 123 from (
  select sum(v)/123 as x from t
) t; // MySQL: 1.0000023, TiDB: 1.0000000

It seems a bug in MySQL since it loses precision.

2. What did you expect to see?

In MySQL:

mysql> select sum(v)/123*123 from t;
+----------------+
| sum(v)/123*123 |
+----------------+
|      1.0000000 |
+----------------+
1 row in set (0.00 sec)

mysql> select x * 123 from (
    ->   select sum(v)/123 as x from t
    -> ) t;
+-----------+
| x * 123   |
+-----------+
| 1.0000023 |
+-----------+
1 row in set (0.00 sec)

3. What did you see instead?

In TiDB:

mysql> select sum(v)/123*123 from t;
+----------------+
| sum(v)/123*123 |
+----------------+
|      1.0000000 |
+----------------+
1 row in set (0.00 sec)

mysql> select x * 123 from (
    ->   select sum(v)/123 as x from t
    -> ) t;
+-----------+
| x * 123   |
+-----------+
| 1.0000000 |
+-----------+
1 row in set (0.00 sec)

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

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.10
Git Commit Hash: 75ff39ac142c7a1191988239358b1aa8e7ef9ace
Git Branch: HEAD
UTC Build Time: 2020-05-11 05:52:30
GoVersion: go version go1.13.1 darwin/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[mmyj]

/pick-up

[mmyj]

/assign