Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Fixed
-
Impala 2.8.0
-
ghx-label-3
Description
Converting from unix times at the first supported TIMESTAMP second (unix time -17987443201) with partial seconds appear to be wrong.
E.g. these are handled correctly:
[localhost:21000] > select cast(-17987443201.0 as timestamp); +-----------------------------------+ | cast(-17987443201.0 as timestamp) | +-----------------------------------+ | NULL | +-----------------------------------+ Fetched 1 row(s) in 0.01s [localhost:21000] > select cast(-17987443200.0 as timestamp); +-----------------------------------+ | cast(-17987443200.0 as timestamp) | +-----------------------------------+ | 1400-01-01 00:00:00 | +-----------------------------------+ Fetched 1 row(s) in 0.01s
This is wrong, it should be NULL:
[localhost:21000] > select cast(-17987443200.1 as timestamp); +-----------------------------------+ | cast(-17987443200.1 as timestamp) | +-----------------------------------+ | 1400-01-01 00:00:00.100000000 | +-----------------------------------+ Fetched 1 row(s) in 0.01s
This also affects the DECIMAL to TIMESTAMP conversion path:
[localhost:21000] > select cast(cast(-17987443200.1 as decimal(31,1)) as timestamp); +----------------------------------------------------------+ | cast(cast(-17987443200.1 as decimal(31,1)) as timestamp) | +----------------------------------------------------------+ | 1400-01-01 00:00:00.100000000 | +----------------------------------------------------------+
For reference, here's what postgres does (it supports a wider timestamp range):
mj=# set timezone to 'GMT';
SET
mj=# select to_timestamp(0);
to_timestamp
------------------------
1970-01-01 00:00:00+00
(1 row)
mj=# select to_timestamp(-17987443200.1);
to_timestamp
-------------------------------
1399-12-31 23:59:59.900002+00
(1 row)
mj=# select to_timestamp(-17987443200.0);
to_timestamp
------------------------
1400-01-01 00:00:00+00
(1 row)
I'm not sure exactly what the issue is, but may be related to boost intervals (e.g. IMPALA-1675 which affected timestamp-functions) in timestamp-value. See:
static TimestampValue FromUnixTimeNanos(time_t unix_time, int64_t nanos) { boost::posix_time::ptime temp = UnixTimeToPtime(unix_time); temp += boost::posix_time::nanoseconds(nanos); return TimestampValue(temp); } /// Returns a TimestampValue where the integer part of the specified 'unix_time' /// specifies the number of seconds (see above), and the fractional part is converted /// to nanoseconds and added to the resulting TimestampValue. static TimestampValue FromSubsecondUnixTime(double unix_time) { const time_t unix_time_whole = unix_time; boost::posix_time::ptime temp = UnixTimeToPtime(unix_time_whole); temp += boost::posix_time::nanoseconds((unix_time - unix_time_whole) / ONE_BILLIONTH); return TimestampValue(temp); }
Next step is to debug this and figure out exactly why the wrong value is being returned. It shouldn't be too hard because the affected code is very narrow.
Attachments
Issue Links
- depends upon
-
IMPALA-5664 Unix time to timestamp conversions may crash impala (boost exception)
- Resolved