[
https://issues.apache.org/jira/browse/PHOENIX-6807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812563#comment-17812563
]
Istvan Toth commented on PHOENIX-6807:
--------------------------------------
The basic problem is that Phoenix has super non-standard date handling.
The sqlline problem is known, and [~RichardAntal] has had a PR open it for ages.
It would be great if you could help push that forward.
As for the comparison, this is one of those issues that won't work if the user
doesn't know the pecularities of Phoenix date handling. While shlepping around
four meaningless bytes might solve this particular problem, anyone who expects
phoenix date handling to be remotely standard will soon run into other issues.
I think that amending the docs and emphasiszing that the type is DATE (while
linking to a doc that explains Phoenix date handling) would be enough.
> 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.2.0, 5.1.2
> Reporter: Tanuj Khurana
> Assignee: Tanuj Khurana
> Priority: Minor
>
> 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)
