From 03b4a2d6e5b3d6e4bd88c3451cbff8ba4df7bd71 Mon Sep 17 00:00:00 2001 From: Alexander Barkov Date: Wed, 29 Mar 2023 11:56:44 +0400 Subject: MDEV-26765 UNIX_TIMESTAMP(CURRENT_TIME()) return null ?!? Problem: UNIX_TIMESTAMP() called for a expression of the TIME data type returned NULL. Inside Type_handler_timestamp_common::Item_val_native_with_conversion the call for item->get_date() did not convert TIME to DATETIME automatically (because it does not have to, by design). As a result, Type_handler_timestamp_common::TIME_to_native() received a MYSQL_TIME value with zero date 0000-00-00 and therefore returned "true" (indicating SQL NULL value). Fix: Removing the call for item->get_date(). Instantiating Datetime(item) instead. This forces automatic TIME to DATETIME conversion (unless @@old_mode is zero_date_time_cast). --- mysql-test/main/old-mode.test | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/main/old-mode.test') diff --git a/mysql-test/main/old-mode.test b/mysql-test/main/old-mode.test index a09de1cf87d..d3fc254110d 100644 --- a/mysql-test/main/old-mode.test +++ b/mysql-test/main/old-mode.test @@ -149,3 +149,23 @@ SELECT t1.a, UNIX_TIMESTAMP(t1.a), t2.a, UNIX_TIMESTAMP(t2.a) FROM t1 t1, t1 t2 DROP TABLE t1; SET time_zone=DEFAULT; SET global mysql56_temporal_format=true; + + +--echo # +--echo # MDEV-26765 UNIX_TIMESTAMP(CURRENT_TIME()) return null ?!? +--echo # + +SET old_mode=zero_date_time_cast; +SET @@time_zone='+00:00'; +SET timestamp=1234567; +SELECT CURRENT_TIMESTAMP; +SELECT UNIX_TIMESTAMP(CURRENT_TIME()); +SELECT UNIX_TIMESTAMP(TIME'06:56:07'); +SELECT UNIX_TIMESTAMP(TIME'10:20:30'); +CREATE OR REPLACE TABLE t1 (a TIME); +INSERT INTO t1 VALUES (TIME'06:56:07'),('10:20:30'); +SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY a; +DROP TABLE t1; + +SET @@time_zone=DEFAULT; +SET TIMESTAMP=DEFAULT; -- cgit v1.2.1