MariaDB/MySQL TO_SECONDS and AGGREGATE functions

Question

I want to use TO_SECONDS with aggregate functions (AVG, COUNT) to summarize my table. However, the result was not what I expected. Here is an example table:

MariaDB [test]> select * from mytable;
+----+---------------------+------+
| id | ts                  | val  |
+----+---------------------+------+
|  1 | 2016-01-01 01:02:03 |    1 |
|  2 | 2016-01-01 01:02:04 |    2 |
|  3 | 2016-01-01 01:02:04 |    3 |
|  4 | 2016-01-01 01:02:05 |    4 |
|  5 | 2016-01-01 01:02:05 |    5 |
+----+---------------------+------+

Query #1 (OK):

MariaDB [test]> select to_seconds(ts) as tsec from mytable;
+-------------+
| tsec        |
+-------------+
| 63618829323 |
| 63618829324 |
| 63618829324 |
| 63618829325 |
| 63618829325 |
+-------------+

Query #2 (?):

MariaDB [test]> select to_seconds(ts) as tsec, avg(val) mval from mytable group by tsec;
+------------+------+
| tsec       | mval |
+------------+------+
| 2147483647 |    3 |
+------------+------+

Expected result:

+-------------+------+
| tsec        | mval |
+-------------+------+
| 63618829323 |    1 |
| 63618829324 |  2.5 |
| 63618829325 |  4.5 |
+-------------+------+

SQL Fiddle: http://sqlfiddle.com/#!9/17616a/6
MariaDB version> mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1

Of course I can use other DATE/TIME functions (UNIX_TIMESTAMP, etc) to perform the task. However, I want to know why the result is different.
What am I missing? Do I misunderstood the usage of TO_SECONDS?

This would appear to be a data type problem. The return value is being treated as an integer rather than a big int. — Gordon Linoff
– Gordon Linoff, Commented Sep 18, 2016 at 13:20

Gordon Linoff · Accepted Answer · 2016-09-18 13:22:51Z

1

This is a strange data type problem. The following does work:

select cast(to_seconds(ts) as decimal(20, 0)) as tsec, avg(val)
from mytable
group by tsec;

I don't know why the return value of to_seconds() would be large enough to store the value when you select it, but then gets converted to an integer when you use group by.

answered Sep 18, 2016 at 13:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

putu Over a year ago

Thank you for the quick answer.

Vladimir Menkov · Accepted Answer · 2020-12-13 18:03:22Z

I cannot answer why this happens, but confirm that this still happens in MySQL ver. 5.7.32, and suggest a (kludgy) workaround.

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 |
+---------------------+---------------+

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. This looks like some kind of data conversion problem inside the MySQL server, in a situation where it ought not occur.

Essentially the same problem was reported as a MySQL bug in March 2019 ( https://bugs.mysql.com/bug.php?id=94612 ), but no activity, other than an acknowledgement, has been added to that bug report since.

Interestingly, the problem is obviated if instead of to_seconds(t) we use to_seconds(max(t)), or max(to_seconds(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 |
+---------------------+---------------+---------------------+--------------------+--------------------+

Collectives™ on Stack Overflow

MariaDB/MySQL TO_SECONDS and AGGREGATE functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related