[
https://issues.apache.org/jira/browse/PHOENIX-6807?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17812166#comment-17812166
]
Istvan Toth commented on PHOENIX-6807:
--------------------------------------
I have no issues with the new keyword, but I still think that using a 12 byte
type for 8 byte data is not a wise decision, [~kadir].
The extra memory, network, and processing requirement for 12 vs 8 bytes is a
real performance hit, versus a purely cosmetic improvement of using the 12 byte
type.
We could add a "DATETIME" type, like mysql does for 8 byte timestamps, and use
that for ROW_TIMESTAMP.
This would also let users be more explicit about their types, even though it
would be just an alias (though we could add a separate format string for
processing it)
> 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)
