You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Stamatis Zampetakis (Jira)" <ji...@apache.org> on 2023/09/29 14:35:00 UTC

[jira] [Updated] (HIVE-27760) Filter on date type partitioning column producing 0 results

     [ https://issues.apache.org/jira/browse/HIVE-27760?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Stamatis Zampetakis updated HIVE-27760:
---------------------------------------
    Description: 
Filter on date type partitioning columns producing 0 results.

{*}Reproduction steps{*}:

1. test.q
{noformat}
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';{noformat}
2. Command to execute (pass different timezone than server)
{noformat}
mvn test -Dtest=TestMiniTezCliDriver -Dqfile=test.q -Dtest.output.overwrite=true -Duser.timezone=Asia/Hong_Kong{noformat}
 

*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 |https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1370] 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:* In MetaStoreDirectSql.java, we should use java.time.format.DateTimeFormatter with java.time.LocalDate so that it will return proper date string.

 

  was:
Filter on date type partitioning columns producing 0 results.

{*}Reproduction steps{*}:

1. test.q
{noformat}
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';{noformat}
2. Command to execute (pass different timezone than server)
{noformat}
mvn test -Dtest=TestMiniTezCliDriver -Dqfile=test.q -Dtest.output.overwrite=true -Duser.timezone=Asia/Hong_Kong{noformat}
 

*RootCause:* As a part of [HIVE-27373|http://example.com/] 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 |https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1370] 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:* In MetaStoreDirectSql.java, we should use java.time.format.DateTimeFormatter with java.time.LocalDate so that it will return proper date string.

 


> Filter on date type partitioning column producing 0 results
> -----------------------------------------------------------
>
>                 Key: HIVE-27760
>                 URL: https://issues.apache.org/jira/browse/HIVE-27760
>             Project: Hive
>          Issue Type: Bug
>          Components: HiveServer2
>            Reporter: Dayakar M
>            Assignee: Dayakar M
>            Priority: Major
>
> Filter on date type partitioning columns producing 0 results.
> {*}Reproduction steps{*}:
> 1. test.q
> {noformat}
> 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';{noformat}
> 2. Command to execute (pass different timezone than server)
> {noformat}
> mvn test -Dtest=TestMiniTezCliDriver -Dqfile=test.q -Dtest.output.overwrite=true -Duser.timezone=Asia/Hong_Kong{noformat}
>  
> *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 |https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/MetaStoreDirectSql.java#L1370] 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:* In MetaStoreDirectSql.java, we should use java.time.format.DateTimeFormatter with java.time.LocalDate so that it will return proper date string.
>  



--
This message was sent by Atlassian Jira
(v8.20.10#820010)