[
https://issues.apache.org/jira/browse/HIVE-10296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14709783#comment-14709783
]
Sergey Shelukhin commented on HIVE-10296:
-----------------------------------------
this case was incomplete and was recently improved to include other join
filters (see "dbHasJoinCastBug" there). However, we didn't realize postgres
also has this bug, so it needs to be added to the list.
With the full case filter it should not try to cast non-numerics unless they
are actually stored in the numeric column. Can you check where "DEFAULT_BINSRC"
value is coming from?
> 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
>
> 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
(v6.3.4#6332)
