You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "ASF GitHub Bot (Jira)" <ji...@apache.org> on 2020/06/04 21:55:00 UTC
[jira] [Work logged] (HIVE-10296) Cast exception observed when hive
runs a multi join query on metastore (postgres), since postgres pushes the
filter into the join, and ignores the condition before applying cast
[ https://issues.apache.org/jira/browse/HIVE-10296?focusedWorklogId=441552&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-441552 ]
ASF GitHub Bot logged work on HIVE-10296:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 04/Jun/20 21:54
Start Date: 04/Jun/20 21:54
Worklog Time Spent: 10m
Work Description: belugabehr closed pull request #32:
URL: https://github.com/apache/hive/pull/32
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Issue Time Tracking
-------------------
Worklog Id: (was: 441552)
Remaining Estimate: 0h
Time Spent: 10m
> Cast exception observed when hive runs a multi join query on metastore (postgres), since postgres pushes the filter into the join, and ignores the condition before applying cast
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-10296
> URL: https://issues.apache.org/jira/browse/HIVE-10296
> Project: Hive
> Issue Type: Bug
> Affects Versions: 0.13.1
> Reporter: Yash Datta
> Assignee: Karthik Manamcheri
> Priority: Major
> Fix For: 4.0.0, 3.2.0
>
> Attachments: HIVE-10296.1.patch
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> Try to drop a partition from hive:
> ALTER TABLE f___edr_bin_source___900_sub_id DROP IF EXISTS PARTITION ( exporttimestamp=1427824800, timestamp=1427824800)
> This triggers a query on the metastore like this :
> "select "PARTITIONS"."PART_ID" from "PARTITIONS" inner join "TBLS" on "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" and "TBLS"."TBL_NAME" = ? inner join "DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID" and "DBS"."NAME" = ? inner join "PARTITION_KEY_VALS" "FILTER0" on "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER0"."INTEGER_IDX" = 0 inner join "PARTITION_KEY_VALS" "FILTER1" on "FILTER1"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER1"."INTEGER_IDX" = 1 where ( (((case when "TBLS"."TBL_NAME" = ? and "DBS"."NAME" = ? then cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) else null end) = ?) and ((case when "TBLS"."TBL_NAME" = ? and "DBS"."NAME" = ? then cast("FILTER1"."PART_KEY_VAL" as decimal(21,0)) else null end) = ?)) )"
> In some cases, when the internal tables in postgres (metastore) have some amount of data, the query plan pushes the condition down into the join.
> Now because of DERBY-6358 , case when clause is used before the cast, but in this case , cast is evaluated before condition being evaluated. So in case we have different tables partitioned on string and integer columns, cast exception is observed!
> 15/04/06 08:41:20 ERROR metastore.ObjectStore: Direct SQL failed, falling back to ORM
> javax.jdo.JDODataStoreException: Error executing SQL query "select "PARTITIONS"."PART_ID" from "PARTITIONS" inner join "TBLS" on "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID" and "TBLS"."TBL_NAME" = ? inner join "DBS" on "TBLS"."DB_ID" = "DBS"."DB_ID" and "DBS"."NAME" = ? inner join "PARTITION_KEY_VALS" "FILTER0" on "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER0"."INTEGER_IDX" = 0 inner join "PARTITION_KEY_VALS" "FILTER1" on "FILTER1"."PART_ID" = "PARTITIONS"."PART_ID" and "FILTER1"."INTEGER_IDX" = 1 where ( (((case when "TBLS"."TBL_NAME" = ? and "DBS"."NAME" = ? then cast("FILTER0"."PART_KEY_VAL" as decimal(21,0)) else null end) = ?) and ((case when "TBLS"."TBL_NAME" = ? and "DBS"."NAME" = ? then cast("FILTER1"."PART_KEY_VAL" as decimal(21,0)) else null end) = ?)) )".
> at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)
> at org.datanucleus.api.jdo.JDOQuery.executeWithArray(JDOQuery.java:321)
> at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilterInternal(MetaStoreDirectSql.java:300)
> at org.apache.hadoop.hive.metastore.MetaStoreDirectSql.getPartitionsViaSqlFilter(MetaStoreDirectSql.java:211)
> at org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1915)
> at org.apache.hadoop.hive.metastore.ObjectStore$3.getSqlResult(ObjectStore.java:1909)
> at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2208)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1909)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1882)
> org.postgresql.util.PSQLException: ERROR: invalid input syntax for type numeric: "__DEFAULT_BINSRC__"
> 15/04/06 08:41:20 INFO metastore.ObjectStore: JDO filter pushdown cannot be used: Filtering is supported only on partition keys of type string
> 15/04/06 08:41:20 ERROR metastore.ObjectStore:
> javax.jdo.JDOException: Exception thrown when executing query
> at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:596)
> at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:275)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionNamesNoTxn(ObjectStore.java:1700)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionNamesPrunedByExprNoTxn(ObjectStore.java:2003)
> at org.apache.hadoop.hive.metastore.ObjectStore.access$400(ObjectStore.java:146)
> at org.apache.hadoop.hive.metastore.ObjectStore$3.getJdoResult(ObjectStore.java:1937)
> at org.apache.hadoop.hive.metastore.ObjectStore$3.getJdoResult(ObjectStore.java:1909)
> at org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.run(ObjectStore.java:2214)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExprInternal(ObjectStore.java:1909)
> at org.apache.hadoop.hive.metastore.ObjectStore.getPartitionsByExpr(ObjectStore.java:1882)
--
This message was sent by Atlassian Jira
(v8.3.4#803005)