WHERE condition on DATE type partitioning column leads to wrong results

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.