[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17822550#comment-17822550
]
Denys Kuzmenko commented on HIVE-27775:
---
Merged to master.
Thanks [~dengzh] for the patch!
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
> Labels: pull-request-available
> Fix For: 4.0.0
>
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.getPartitionsSpecByExprInternal(SessionHiveMetaStoreClient.java:2279)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsSpecByExpr(HiveMetaStoreClient.java:2484)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.listPartitionsSpecByExpr(SessionHiveMetaStoreClient.java:1346)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
>
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17809883#comment-17809883
]
Stamatis Zampetakis commented on HIVE-27775:
To help advance the resolution of this ticket let's focus on the issue reported
in the description. For sure there are more bugs around this but let's handle
one at the time.
The bug shows up in the tests cause they use the {{VerifyingObjectStore}}. The
{{VerifyingObjectStore}} is not used in production and that's why I am asking
if there is a way for this bug to manifestate without using this
implementation. Typically, if direct SQL returns the correct result then we are
not going to go through JDO. IS there a way (via config or otherwise) to bypass
the direct SQL mechanism and use JDO exclusively?
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
> Labels: pull-request-available
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
>
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17808855#comment-17808855
]
Zhihua Deng commented on HIVE-27775:
Not sure how many users cloud use timestamp as the type for the partition
column, looks like this is a new feature introduced in Hive 4.0.
>From the Jira description, the partition predicate in the query
{code:java}
SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00'; {code}
returns an empty result on direct sql, while a partition('2023-03-26 02:30:00')
on the JDO, provided the TIMESTAMP data type in Hive is timezone agnostic, so I
assume the result from JDO is correct.
Besides this difference, when I tested the date/timestamp partition column
against Postgres and MySQL, there're some problems:
Postgres(direct sql):
operator does not exist: timestamp without time zone = character varying
MySQL(direct sql):
You have an error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near 'TIMESTAMP) else null
end) as TIMESTAMP) = '2023-03-26 03:30:00'))' at line 1
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
> Labels: pull-request-available
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
>
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17808086#comment-17808086
]
Stamatis Zampetakis commented on HIVE-27775:
[~dengzh] [~wechar] Have we confirmed that this bug is something that can
affect production? It seems that direct SQL returns the correct result so my
question is actually the following. Is it possible to disable direct SQL for
this code path and hit the JDO bug?
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
> Labels: pull-request-available
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.getPartitionsSpecByExprInternal(SessionHiveMetaStoreClient.java:2279)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsSpecByExpr(HiveMetaStoreClient.java:2484)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.listPartitionsSpecByExpr(SessionHiveMetaStoreClient.java:1346)
> at
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17800453#comment-17800453
]
Wechar commented on HIVE-27775:
---
+1. We also noticed this bug that jdo query would return empty result for
timestamp filter in the benchmark test of HIVE-27827.
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
> Labels: pull-request-available
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.getPartitionsSpecByExprInternal(SessionHiveMetaStoreClient.java:2279)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsSpecByExpr(HiveMetaStoreClient.java:2484)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.listPartitionsSpecByExpr(SessionHiveMetaStoreClient.java:1346)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
>
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17794502#comment-17794502
]
Zhihua Deng commented on HIVE-27775:
On Jdo path, we use the partition name to fetch the matched partitions,
{code:java}
SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MPartition' AS
DN_TYPE,A0.CREATE_TIME,A0.LAST_ACCESS_TIME,A0.PART_NAME AS
NUCORDER0,A0.WRITE_ID,A0.PART_ID FROM PARTITIONS A0 LEFT OUTER JOIN TBLS B0 ON
A0.TBL_ID = B0.TBL_ID LEFT OUTER JOIN DBS C0 ON B0.DB_ID = C0.DB_ID WHERE
B0.TBL_NAME = <'payments'> AND C0."NAME" = <'default'> AND C0.CTLG_NAME =
<'hive'> AND A0.PART_NAME = <'txn_datetime=2023-03-26 03%3A30%3A00'> ORDER BY
NUCORDER0 {code}
In the above example, the filter A0.PART_NAME = <'txn_datetime=2023-03-26
03%3A30%3A00'> A0.PART_NAME is timezone agnostic, this could lead to wrong
result if <'txn_datetime=2023-03-26 03%3A30%3A00'> is timezone based timestamp.
Compared to direct mode,
{code:java}
select "PARTITIONS"."PART_ID" from "PARTITIONS" inner join "TBLS" on
"PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" and "TBLS"."TBL_NAME" =
<'payments'> inner join "DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID" and
"DBS"."NAME" = <'default'> inner join "PARTITION_KEY_VALS" "FILTER0" on
"FILTER0"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER0"."INTEGER_IDX" = 0
where "DBS"."CTLG_NAME" = <'hive'> and ((cast((case when
"FILTER0"."PART_KEY_VAL" <> <'__HIVE_DEFAULT_PARTITION__'> and
"TBLS"."TBL_NAME" = <'payments'> and "DBS"."NAME" = <'default'> and
"DBS"."CTLG_NAME" = <'hive'> and "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
and "FILTER0"."INTEGER_IDX" = 0 then cast("FILTER0"."PART_KEY_VAL" as
TIMESTAMP) else null end) as TIMESTAMP) = <'2023-03-26 03:30:00'>)) {code}
the filter is cast("FILTER0"."PART_KEY_VAL" as TIMESTAMP) else null end) as
TIMESTAMP) = <'2023-03-26 03:30:00'>, so if we push a timezone based
<'2023-03-26 03:30:00'> and the underlying database can handle
cast("FILTER0"."PART_KEY_VAL" as TIMESTAMP) else null end) as TIMESTAMP)
properly, then we could get the expected result.
However when I switch the backing db to Postgres, the direct sql throws an
exception:
{noformat}
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist:
timestamp without time zone = character varying
Hint: No operator matches the given name and argument types. You might need
to add explicit type casts.
Position: 662{noformat}
MySQL as well:
{code:java}
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for the
right syntax to use near 'TIMESTAMP) else null end) as TIMESTAMP) = '2023-03-26
03:30:00'))' at line 1 {code}
So I think we should fix the errors on direct sql path and make the
timestamp/date timezone agnostic on JDO path.
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore:
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17793653#comment-17793653
]
Denys Kuzmenko commented on HIVE-27775:
---
[~dengzh], could you please share any updates, we are considering this as a
blocker for 4.0
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Assignee: Zhihua Deng
>Priority: Critical
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.getPartitionsSpecByExprInternal(SessionHiveMetaStoreClient.java:2279)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsSpecByExpr(HiveMetaStoreClient.java:2484)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.listPartitionsSpecByExpr(SessionHiveMetaStoreClient.java:1346)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
>
[jira] [Commented] (HIVE-27775) DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
[
https://issues.apache.org/jira/browse/HIVE-27775?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17772556#comment-17772556
]
Stamatis Zampetakis commented on HIVE-27775:
We have discovered this bug while writting tests for HIVE-27760.
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift
> --
>
> Key: HIVE-27775
> URL: https://issues.apache.org/jira/browse/HIVE-27775
> Project: Hive
> Issue Type: Bug
> Components: Standalone Metastore
>Affects Versions: 4.0.0-beta-1
>Reporter: Stamatis Zampetakis
>Priority: Major
>
> DirectSQL and JDO results are different when fetching partitions by timestamp
> in DST shift.
> {code:sql}
> --! qt:timezone:Europe/Paris
> CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime
> TIMESTAMP) STORED AS ORC;
> INSERT into payments VALUES('---', '2023-03-26 02:30:00');
> SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';
> {code}
> The '2023-03-26 02:30:00' is a timestamp that in Europe/Paris timezone falls
> exactly in the middle of the DST shift. In this particular timezone this date
> time never really exists since we are jumping directly from 02:00:00 to
> 03:00:00. However, the TIMESTAMP data type in Hive is timezone agnostic
> (https://cwiki.apache.org/confluence/display/Hive/Different+TIMESTAMP+types)
> so it is a perfectly valid timestamp that can be inserted in a table and we
> must be able to recover it back.
> For the SELECT query above, partition pruning kicks in and calls the
> ObjectStore#getPartitionsByExpr method in order to fetch the respective
> partitions matching the timestamp from HMS.
> The tests however reveal that DirectSQL and JDO paths are not returning the
> same results leading to an exception when VerifyingObjectStore is used.
> According to the error below DirectSQL is able to recover one partition from
> HMS (expected) while JDO/ORM returns empty (not expected).
> {noformat}
> 2023-10-06T03:51:19,406 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.VerifyingObjectStore: Lists are not the same size: SQL 1, ORM 0
> 2023-10-06T03:51:19,409 ERROR [80252df4-3fdc-4971-badf-ad67ce8567c7 main]
> metastore.RetryingHMSHandler: MetaException(message:Lists are not the same
> size: SQL 1, ORM 0)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.verifyLists(VerifyingObjectStore.java:148)
> at
> org.apache.hadoop.hive.metastore.VerifyingObjectStore.getPartitionsByExpr(VerifyingObjectStore.java:88)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:97)
> at com.sun.proxy.$Proxy57.getPartitionsByExpr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HMSHandler.get_partitions_spec_by_expr(HMSHandler.java:7330)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.apache.hadoop.hive.metastore.RetryingHMSHandler.invokeInternal(RetryingHMSHandler.java:98)
> at
> org.apache.hadoop.hive.metastore.AbstractHMSHandlerProxy.invoke(AbstractHMSHandlerProxy.java:82)
> at com.sun.proxy.$Proxy59.get_partitions_spec_by_expr(Unknown Source)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getPartitionsSpecByExprInternal(HiveMetaStoreClient.java:2472)
> at
> org.apache.hadoop.hive.ql.metadata.HiveMetaStoreClientWithLocalCache.getPartitionsSpecByExprInternal(HiveMetaStoreClientWithLocalCache.java:396)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.getPartitionsSpecByExprInternal(SessionHiveMetaStoreClient.java:2279)
> at
> org.apache.hadoop.hive.metastore.HiveMetaStoreClient.listPartitionsSpecByExpr(HiveMetaStoreClient.java:2484)
> at
> org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.listPartitionsSpecByExpr(SessionHiveMetaStoreClient.java:1346)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at
