| Bug #94612 | Wrong results are returned with "group by" | ||
|---|---|---|---|
| Submitted: | 10 Mar 2019 2:18 | Modified: | 10 Mar 2019 5:27 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.15-rds-dev, 8.0.15, 5.7.25,5.6.43 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[10 Mar 2019 5:27]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[13 Dec 2020 18:00]
Vladimir Menkov
This is still an issue in MySQL 5.7.32. Example:
create table Foo (t datetime);
insert into Foo values('2020-01-01 00:00:01');
insert into Foo values('2020-01-02 00:00:01');
insert into Foo values('2020-01-03 00:00:01');
select t, to_seconds(t) from Foo;
+---------------------+---------------+
| t | to_seconds(t) |
+---------------------+---------------+
| 2020-01-01 00:00:01 | 63745056001 |
| 2020-01-02 00:00:01 | 63745142401 |
| 2020-01-03 00:00:01 | 63745228801 |
+---------------------+---------------+
So far so good. Now, use group by:
select t, to_seconds(t) from Foo group by t;
+---------------------+---------------+
| t | to_seconds(t) |
+---------------------+---------------+
| 2020-01-01 00:00:01 | 2147483647 |
| 2020-01-02 00:00:01 | 2147483647 |
| 2020-01-03 00:00:01 | 2147483647 |
+---------------------+---------------+
So the to_seconds(t) value for each row becomes 2147483647, which is equal to 2^31-1, i.e. the largest positive value representable by a signed 4-byte integer. So this looks like some kind of data conversion problem inside the MySQL server, in a situation where it ought not occur.
Interestingly, the problem is obviated if instead of to_second(t) we use to_second(max(t)), or max(to_second(t)) (which, theoretically, should produce the same result, since we group on t anyway):
select t, to_seconds(t), max(t), to_seconds(max(t)), max(to_seconds(t)) from Foo group by t;
+---------------------+---------------+---------------------+--------------------+--------------------+
| t | to_seconds(t) | max(t) | to_seconds(max(t)) | max(to_seconds(t)) |
+---------------------+---------------+---------------------+--------------------+--------------------+
| 2020-01-01 00:00:01 | 2147483647 | 2020-01-01 00:00:01 | 63745056001 | 63745056001 |
| 2020-01-02 00:00:01 | 2147483647 | 2020-01-02 00:00:01 | 63745142401 | 63745142401 |
| 2020-01-03 00:00:01 | 2147483647 | 2020-01-03 00:00:01 | 63745228801 | 63745228801 |
+---------------------+---------------+---------------------+--------------------+-
Description: The to_seconds() function returns the wrong answer with "group by". How to repeat: mysql> CREATE TABLE test_table2 ( id bigint(20) NOT NULL, time_test time DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; mysql> insert into test_table2 values(1, '15:47:28'); mysql> select to_seconds(timediff(time_test,"19:30:00")) as col1 from test_table2; +-------------+ | col1 | +-------------+ | 63719209048 | +-------------+ 1 row in set (0.00 sec) mysql> select to_seconds(timediff(time_test,"19:30:00")) as col1 from test_table2 group by col1; +------------+ | col1 | +------------+ | 2147483647 | +------------+ 1 row in set (0.00 sec)