Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-27775

DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift

    XMLWordPrintableJSON

Details

    Description

      DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift.

      --! qt:timezone:Europe/Paris
CREATE EXTERNAL TABLE payments (card string) PARTITIONED BY(txn_datetime TIMESTAMP) STORED AS ORC;
INSERT into payments VALUES('3333-4444-2222-9999', '2023-03-26 02:30:00');
SELECT * FROM payments WHERE txn_datetime = '2023-03-26 02:30:00';

      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).

      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 java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:213)
	at com.sun.proxy.$Proxy60.listPartitionsSpecByExpr(Unknown Source)
	at org.apache.hadoop.hive.ql.metadata.Hive.getPartitionsByExpr(Hive.java:4507)
	at org.apache.hadoop.hive.ql.optimizer.ppr.PartitionPruner.getPartitionsFromServer(PartitionPruner.java:457)
	at org.apache.hadoop.hive.ql.optimizer.ppr.PartitionPruner.prune(PartitionPruner.java:230)
	at org.apache.hadoop.hive.ql.optimizer.calcite.RelOptHiveTable.computePartitionList(RelOptHiveTable.java:480)
	at org.apache.hadoop.hive.ql.optimizer.calcite.rules.HivePartitionPruneRule.perform(HivePartitionPruneRule.java:63)
	at org.apache.hadoop.hive.ql.optimizer.calcite.rules.HivePartitionPruneRule.onMatch(HivePartitionPruneRule.java:46)
	at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:333)
	at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:542)
	at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:407)
	at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:243)
	at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
	at org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:202)
	at org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:189)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.executeProgram(CalcitePlanner.java:2471)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.executeProgram(CalcitePlanner.java:2430)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.applyPreJoinOrderingTransforms(CalcitePlanner.java:1968)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1712)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1593)
	at org.apache.calcite.tools.Frameworks.lambda$withPlanner$0(Frameworks.java:131)
	at org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:914)
	at org.apache.calcite.tools.Frameworks.withPrepare(Frameworks.java:180)
	at org.apache.calcite.tools.Frameworks.withPlanner(Frameworks.java:126)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.logicalPlan(CalcitePlanner.java:1345)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:572)
	at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:13048)
	at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:467)
	at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:327)
	at org.apache.hadoop.hive.ql.Compiler.analyze(Compiler.java:224)
	at org.apache.hadoop.hive.ql.Compiler.compile(Compiler.java:107)
	at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:519)
	at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:471)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:436)
	at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:430)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.compileAndRespond(ReExecDriver.java:121)
	at org.apache.hadoop.hive.ql.reexec.ReExecDriver.run(ReExecDriver.java:227)
	at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:257)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd1(CliDriver.java:201)
	at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:127)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:425)
	at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:356)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClientInternal(QTestUtil.java:733)
	at org.apache.hadoop.hive.ql.QTestUtil.executeClient(QTestUtil.java:703)
	at org.apache.hadoop.hive.cli.control.CoreCliDriver.runTest(CoreCliDriver.java:115)
	at org.apache.hadoop.hive.cli.control.CliAdapter.runTest(CliAdapter.java:157)
	at org.apache.hadoop.hive.cli.TestMiniLlapLocalCliDriver.testCliDriver(TestMiniLlapLocalCliDriver.java:62)

      Attachments

        Issue Links

          Discovered while testing

          Bug - A problem which impairs or prevents the functions of the product. HIVE-27760 WHERE condition on DATE type partitioning column leads to wrong results

          • Major - Major loss of function.
          • Closed
          links to

          Web Link GitHub Pull Request #4940

          Web Link GitHub Pull Request #4959

          Activity

            People

              dengzh Zhihua Deng
              zabetak Stamatis Zampetakis
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: