MySQL Bugs: #110925: Confusing time values

Bug #110925	Confusing time values
Submitted:	5 May 2023 10:07	Modified:	9 May 2023 20:58
Reporter:	Pedro Ferreira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S6 (Debug Builds)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	values

Description:
Run this:

VALUES ROW(CASE 1 WHEN 2 THEN TIME '0:0:0.3' END),ROW(TIME '0:0:0.2');

it gives an assertion error at include/integer_digits.h:142
assert(digits >= count_digits(number));

Also, I find these query results wrong:

mysql> VALUES ROW(NULL),ROW(TIME '-719:0:0.2323049640');
+----------+
| column_0 |
+----------+
| NULL     |
| NULL     |
+----------+
Are both values NULL?

mysql> VALUES ROW(CASE 1 WHEN 2 THEN TIME '0:0:0' END),ROW(TIME '0:0:0');
+-------------+
| column_0    |
+-------------+
| 24976:00:00 |
| 00:00:00    |
+-------------+
The first value should be NULL here?

The compilation parameters are the same as issue 108148:

-DWITH_DEBUG=1 -DWITH_ASAN=ON -DWITH_UBSAN=ON and boost library version 1.77

How to repeat:
Run the queries above.

Hi Mr. Ferreira,

Thank you for your bug report.

However, we can not repeat the behaviour that you are reporting.

This is all that we got:

"VALUES ROW(NULL),ROW(TIME '-719:0:0.2323049640')"

+----------+
| column_0 |
+----------+
| NULL     |
| NULL     |
+----------+

We inspected the error log and there are no warnings nor error messages.

HI,

Result of the second query is:

+-----------+
| column_0  |
+-----------+
| -01:00:00 |
| 00:00:00  |
+-----------+

Which is OK, since the first value has to be random.

Please use the same server arguments as me:

--no-defaults --user=mysql --basedir=... --datadir=... --plugin-dir=... --log-error=... --log-error-verbosity=3 --pid-file=... --port=3306 --mysqlx-port=3307 --slave-parallel-type=LOGICAL_CLOCK --slave-parallel-workers=4 --slave-preserve-commit-order=ON --enforce-gtid-consistency --gtid-mode=ON --binlog-format=ROW --server-id=1 --core-file --default-authentication-plugin=mysql_native_password

We have got the same result.

Can you try this configuration file?

[mysqld]
server_id=1
skip-log-bin
innodb_buffer_pool_size=4G
performance_schema=off
explicit_defaults_for_timestamp=OFF
character_set_server=utf8mb4
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
completion_type=NO_CHAIN
event_scheduler=ON
explicit_defaults_for_timestamp=OFF
transaction_isolation=REPEATABLE-READ
autocommit=ON
innodb_strict_mode=OFF
secure_file_priv=
innodb_adaptive_hash_index=OFF
max_prepared_stmt_count=1048576
slow_query_log = ON
long_query_time = 5

I still get the assertion error.

Hi Mr. Ferreira,

Actually, options and arguments are irrelevant.

You can get that assert only with debug binary.

Hence, this is a bug in debug binary, which makes it a very low priority bug.

Verified as a debug bug.

Documented fix as follows in the MySQL 8.1.0 changelog:

    In certain cases, VALUES ROW() did not handle correctly
    expressions which evaluated to NULL.

Closed.

Thank you, Jon.