P3-[4.0 Bug Hunting]-[Aggregate Function]-no warning and wrong result on date column

[zhangysh1995]

Bug Report

1. What did you do?

drop table if exists t0; create table t0 (col0 DATE, col1 VARCHAR(18), col2 BIGINT, col3 INT, col4 DATETIME) charset=latin1, engine=MyISAM;

insert into t0 values ('1000-01-02', '12ab7f97-b847-', '9223372036854775807', '-1', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '2d3897fd-87a8-49', '9223372036854775807', '2147483647', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '3d6ab42d-6205-', '-9223372036854775808', '2147483647', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', 'd4', '-1', '-2147483648', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '75e3be2', '1', '0', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '364ce', '-9223372036854775808', '2147483647', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', 'bf7d4c41-a006-401e', '1', '-2147483648', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '36e1f594-ba69-47b', '-9223372036854775808', '2147483647', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '10ad0402-', '0', '-1', '1000-01-02 00:00:00');

insert into t0 values ('1000-01-02', '678ab1cc-8eb9-4069', '1', '0', '1000-01-02 00:00:00');

select max(col0), min(col0), avg(col0) from t0;

2. What did you expect to see?

This function should return the correct average of the date not converting the value to a numeric. In MySQL it converts to float. This could be considered as a feature more than a bug. See https://bugs.mysql.com/bug.php?id=9874 for details.

I think it is better to raise a warning on such case, avg() expect a numeric argument, passed is date. converting to number

mysql> select max(col0), min(col0), avg(col0) from t0;
+------------+------------+---------------+
| max(col0)  | min(col0)  | avg(col0)     |
+------------+------------+---------------+
| 1000-01-02 | 1000-01-02 | 10000102.0000 |
+------------+------------+---------------+
1 row in set (0.00 sec)

3. What did you see instead?

mysql> select max(col0), min(col0), avg(col0) from t0;
+------------+------------+-----------+
| max(col0)  | min(col0)  | avg(col0) |
+------------+------------+-----------+
| 1000-01-02 | 1000-01-02 |  10000102 |
+------------+------------+-----------+
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: None
Edition: None
Git Commit Hash: None
Git Branch: None
UTC Build Time: None
GoVersion: go1.13.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[shuke987]

/bug P3