[ https://issues.apache.org/jira/browse/HIVE-10296?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16629547#comment-16629547 ]
Karthik Manamcheri commented on HIVE-10296: ------------------------------------------- [~saucam] [~sershe] I am also facing this and would like to fix this. Is the suggested fix to add it to the list? To answer your question about DEFAULT_BINSRC, I am sure its the first value which is non-numeric in PARTITION_KEY_VALS > 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 > Priority: Major > > 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 (v7.6.3#76005)