pingcap/tidb
not compatible with MySQL when handling some subqueries with aggregation functions
開放
#17,083 建立於 2020年5月11日
component/expressionhelp wantedseverity/moderatesig/executiontype/bug
倉庫指標
- 星標
- (40,090 顆星)
- PR 合併指標
- (平均合併 14天 4小時) (30 天內合併 346 個 PR)
描述
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 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+