[HIVE-27760] WHERE condition on DATE type partitioning column leads to wrong results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0
• Component/s: HiveServer2
• Labels: pull-request-available
• Assignee: Dayakar M
• Reporter: Dayakar M
• Created: 29/Sep/23 08:50
• Updated: 29/Mar/24 17:49
• Resolved: 06/Oct/23 08:58

Description

Filter on date type partitioning columns producing 0 results.

Reproduction steps:

1. test.q

CREATE EXTERNAL TABLE test(a string,b String) PARTITIONED BY(PartitionDate DATE) STORED AS ORC;
INSERT into test(PartitionDate, a,b) VALUES('2023-01-01','2023-01-01','2023-01-01');
INSERT into test(PartitionDate, a,b) VALUES('2023-01-02','2023-01-02','2023-01-02');

select count(*) from test where PartitionDate = '2023-01-01';

2. Command to execute (pass different timezone than server)

mvn test -Dtest=TestMiniTezCliDriver -Dqfile=test.q -Dtest.output.overwrite=true -Duser.timezone=Asia/Hong_Kong

 

RootCause: As a part of HIVE-27373 issue fix to parse the string to java.sql.Date object, java.text.SimpleDateFormat is replaced with java.time.format.DateTimeFormatter using java.time.LocalDate which represents a Date without TimeZone.  Here this input is passed here  which uses SimpleDateFormat(parsing dates in a locale-sensitive manner) and java.sql.Date. Here user timezone is passed different so actual value is getting changed to a different value (for example 2023-01-01 is changed to 2022-12-31) which is not matching with any partition so nothing gets returned.

Solution:

1. In MetaStoreDirectSql.java, we should use java.time.format.DateTimeFormatter with java.time.LocalDate so that it will return proper date string.

        Or

2. Revert the code changes(only formatter and LocalDate) done for HIVE-27373 and use SimpleDateFormat and java.sql.Date.

Issue Links

• Testing discovered: HIVE-27775 DirectSQL and JDO results are different when fetching partitions by timestamp in DST shift
• links to: GitHub Pull Request #4760
• links to: GitHub Pull Request #4766