Tanuj Khurana created PHOENIX-6807:
--------------------------------------
Summary: Change return type of PHOENIX_ROW_TIMESTAMP() function
from DATE -> TIMESTAMP
Key: PHOENIX-6807
URL: https://issues.apache.org/jira/browse/PHOENIX-6807
Project: Phoenix
Issue Type: Improvement
Affects Versions: 5.1.2, 5.2.0
Reporter: Tanuj Khurana
Assignee: Tanuj Khurana
Today, PHOENIX_ROW_TIMESTAMP() function returns a DATE data type. This causes
multiple issues:
{code:java}
// 0: jdbc:phoenix:localhost> create table T (id varchar primary key, ts
timestamp);
No rows affected (0.703 seconds)
0: jdbc:phoenix:localhost> upsert into T values('a', TO_TIMESTAMP('2005-10-01
14:03:22.559'));
1 row affected (0.05 seconds)
0: jdbc:phoenix:localhost> upsert into T values('b', TO_TIMESTAMP('2015-09-01
23:03:22.559'));
1 row affected (0.005 seconds)
0: jdbc:phoenix:localhost> upsert into T values('c', TO_TIMESTAMP('2022-09-01
03:03:24.897'));
1 row affected (0.008 seconds)
0: jdbc:phoenix:localhost> !outputformat csv
0: jdbc:phoenix:localhost> select ts, PHOENIX_ROW_TIMESTAMP() from T;
'TS','PHOENIX_ROW_TIMESTAMP(0.)'
'2005-10-01 07:03:22.559','2022-10-07'
'2015-09-01 16:03:22.559','2022-10-07'
'2022-08-31 20:03:24.897','2022-10-07'
3 rows selected (0.012 seconds) {code}
Notice, how the time component is dropped when we use sqlline to print
PHOENIX_ROW_TIMESTAMP() values. In comparison, the timestamp column is
displayed correctly. This is a major drawback IMO since one of the primary
motivation of implementing the PHOENIX_ROW_TIMESTAMP() function was to aid in
debugging.
There is another issue with returning DATE type. Consider the query below:
{code:java}
SELECT * from T where PHOENIX_ROW_TIMESTAMP() = <Timestamp value>{code}
This query always returns 0 rows. This is because the timestamp value which is
12 bytes can't be coerced to a DATE type so the where compiler compiles the
equality expression to an always *FALSE* expression.
I propose changing the return type of PHOENIX_ROW_TIMESTAMP() to TIMESTAMP. It
solves both the issues listed above and makes sense since PHOENIX_ROW_TIMESTAMP
has timestamp in it :)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
