[ https://issues.apache.org/jira/browse/HIVE-26778?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Syed Shameerur Rahman updated HIVE-26778: ----------------------------------------- Description: The original feature to push down date data type while doing partition pruning via direct sql/JDO was added as part of the jira : https://issues.apache.org/jira/browse/HIVE-5679 Since the behavior of Hive has changed with CBO, Now when CBO is turned on, The date data types are not pushed down to metastore due to CBO adding extra keyword 'DATE' with the original filter since the filter parser is not handled to parse this extra keyword it fails and hence the date data type is not pushed down to the metastore. {code:java} select * from test_table where date_col = '2022-01-01'; {code} When CBO is turned on, The filter predicate generated is date_col=DATE'2022-01-01' which the filter parser fails to recognize. *Steps to reproduce* The following query will generate "{color:#6a8759}Error parsing partition filter; lexer error" [https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/PartFilterExprUtil.java#L128]{color} {color:#172b4d}When CBO is turned off (set hive.cbo.enable=false) we don't see this error message.{color} {code:java} create table part_time(a int) partitioned by(date_c date); insert into part_time partition(date_c='2000-01-01') values (1); insert into part_time partition(date_c='2000-02-01') values (1); insert into part_time partition(date_c='2000-03-01') values (1); select * from part_time where date_c = '2000-03-01'; {code} *Performance Improvement* In my testing setup of table having *10k partitions* in the table. When we do a select query on one of the partitions without the change it was *300 ms* and after the change it was {*}14 ms{*}. was: The original feature to push down date data type while doing partition pruning via direct sql/JDO was added as part of the jira : https://issues.apache.org/jira/browse/HIVE-5679 Since the behavior of Hive has changed with CBO, Now when CBO is turned on, The date data types are not pushed down to metastore due to CBO adding extra keyword 'DATE' with the original filter since the filter parser is not handled to parse this extra keyword it fails and hence the date data type is not pushed down to the metastore. {code:java} select * from test_table where date_col = '2022-01-01'; {code} When CBO is turned on, The filter predicate generated is date_col=DATE'2022-01-01' which the filter parser fails to recognize. *Steps to reproduce* The following query will generate "{color:#6a8759}Error parsing partition filter; lexer error" [https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/PartFilterExprUtil.java#L128]{color} {color:#172b4d}When CBO is turned off (set hive.cbo.enable=false) we don't see this error message.{color} {code:java} create table part_time(a int) partitioned by(date_c date); insert into part_time partition(date_c='2000-01-01') values (1); insert into part_time partition(date_c='2000-02-01') values (1); insert into part_time partition(date_c='2000-03-01') values (1); select * from part_time where date_c = '2000-03-01'; {code} *Performance Improvement* In my testing setup of table having 10k partitions in the table. When we do a select query on one of the partitions without the change it was 300 ms and after the change it was 14 ms. > Pushdown Date data type to metastore via direct sql / JDO > --------------------------------------------------------- > > Key: HIVE-26778 > URL: https://issues.apache.org/jira/browse/HIVE-26778 > Project: Hive > Issue Type: Bug > Components: Standalone Metastore > Affects Versions: 4.0.0 > Reporter: Syed Shameerur Rahman > Assignee: Syed Shameerur Rahman > Priority: Major > Labels: pull-request-available > Time Spent: 50m > Remaining Estimate: 0h > > The original feature to push down date data type while doing partition > pruning via direct sql/JDO was added as part of the jira : > https://issues.apache.org/jira/browse/HIVE-5679 > Since the behavior of Hive has changed with CBO, Now when CBO is turned on, > The date data types are not pushed down to metastore due to CBO adding extra > keyword 'DATE' with the original filter since the filter parser is not > handled to parse this extra keyword it fails and hence the date data type is > not pushed down to the metastore. > {code:java} > select * from test_table where date_col = '2022-01-01'; > {code} > When CBO is turned on, The filter predicate generated is > date_col=DATE'2022-01-01' which the filter parser fails to recognize. > > *Steps to reproduce* > The following query will generate "{color:#6a8759}Error parsing partition > filter; lexer error" > [https://github.com/apache/hive/blob/master/standalone-metastore/metastore-server/src/main/java/org/apache/hadoop/hive/metastore/PartFilterExprUtil.java#L128]{color} > > {color:#172b4d}When CBO is turned off (set hive.cbo.enable=false) we don't > see this error message.{color} > {code:java} > create table part_time(a int) partitioned by(date_c date); > insert into part_time partition(date_c='2000-01-01') values (1); > insert into part_time partition(date_c='2000-02-01') values (1); > insert into part_time partition(date_c='2000-03-01') values (1); > select * from part_time where date_c = '2000-03-01'; > {code} > > *Performance Improvement* > In my testing setup of table having *10k partitions* in the table. When we do > a select query on one of the partitions without the change it was *300 ms* > and after the change it was {*}14 ms{*}. -- This message was sent by Atlassian Jira (v8.20.10#820010)