stop rounding times

[shogo82148]

Description

fixes #1121

Checklist

• Code compiles correctly
• Created tests which fail without the change (if possible)
• All tests passing
• Extended the README / documentation, if necessary
• Added myself / the copyright holder to the AUTHORS file

[methane]

https://dev.mysql.com/doc/refman/8.0/en/fractional-seconds.html

MySQL supports only milliseconds. Do we need to send nanoseconds?

[shogo82148]

MySQL supports only milliseconds.

You mean microseconds?

At least, we need to send 100 nanoseconds (7 digits) precision.
because the DATETIME converter can handle them.

Some examples:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123457                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SET @@sql_mode = sys.list_add(@@sql_mode, 'TIME_TRUNCATE_FRACTIONAL');
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234564', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6));
+-----------------------------------------------------+
| CONVERT('2020-11-27 10:00:00.1234565', DATETIME(6)) |
+-----------------------------------------------------+
| 2020-11-27 10:00:00.123456                          |
+-----------------------------------------------------+
1 row in set (0.01 sec)

As you say, currently MySQL only supports up to microseconds.
However, there is no guarantee that it will not support nanoseconds in the future.
So, I think that we should send all digits we know.

[methane]

I think you are right, but still feel it is a bit too much.

#1121 is really a bug. Round off 1.4449995 to 1.45 doesn't make sense at all.
Compared to it, behavior consistency between round off and round down is very minor. Should we send another 3 digits only for this minor consistency?

Let's check another connectors behavior.

[methane]

It seems Connector/J just nano/1000 when store it in binary protocol.

https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java#L325
https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-impl/java/com/mysql/cj/ServerPreparedQueryBindValue.java#L330

But they round or truncate nanoseconds based on TIME_TRUNCATE_FRACTIONAL sql_mode.

https://github.com/mysql/mysql-connector-j/blob/d64b664fa93e81296a377de031b8123a67e6def2/src/main/core-api/java/com/mysql/cj/util/TimeUtil.java#L183-L186

[shogo82148]

OK. I'll check another connectors behavior, too.

There is the issue about this, so we should discuss in #1121.
I'll convert this pull request to draft.
Let's continue discussion in #1121.

[methane]

I think this pull request is good for v1.6. @shogo82148 would you merge master branch to run CI?

[shogo82148]

@methane I did!

[methane]

Is it safe to trim trailing 0s?

[shogo82148]

Maybe, it is.

[shogo82148]

It looks that starting MySQL server fails on macOS. it is not due to my pull request.

https://github.com/go-sql-driver/mysql/pull/1172/checks?check_run_id=1806566232

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
Starting MySQL
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 653: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
Logging to '/usr/local/var/mysql/Mac-1612182949676.local.err'.
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 144: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 199: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 916: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
/usr/local/Cellar/mysql/8.0.23/bin/mysqld_safe: line 144: /usr/local/var/mysql/Mac-1612182949676.local.err: No such file or directory
. ERROR! The server quit without updating PID file (/usr/local/var/mysql/Mac-1612182949676.local.pid).
Error: Process completed with exit code 1.

[shogo82148]

Thank you for your review!