No partition predicate found for Alias even when the partition predicate in present in the query

jeff picture jeff · Aug 20, 2016 · Viewed 28k times · Source

I have a table pos.pos_inv in hdfs which is partitioned by yyyymm. Below is the query:

select DATE_ADD(to_date(from_unixtime(unix_timestamp(Inv.actvydt, 'MM/dd/yyyy'))),5), 
       to_date(from_unixtime(unix_timestamp(Inv.actvydt, 'MM/dd/yyyy'))),yyyymm 
   from pos.pos_inv inv 
      INNER JOIN pos.POSActvyBrdg Brdg ON Brdg.EIS_POSActvyBrdgId = Inv.EIS_POSActvyBrdgId 
      where to_date(from_unixtime(unix_timestamp(Inv.nrmlzdwkenddt, 'MM/dd/yyyy'))) 
       BETWEEN DATE_SUB(to_date(from_unixtime(unix_timestamp(Inv.actvydt, 'MM/dd/yyyy'))),6) 
        and DATE_ADD(to_date(from_unixtime(unix_timestamp(Inv.actvydt, 'MM/dd/yyyy'))),6) 
        and inv.yyyymm=201501

I have provided the partition value for the query as 201501, but still i get the error"

 Error while compiling statement: FAILED: SemanticException [Error 10041]: No partition predicate found for Alias "inv" Table "pos_inv"

(schema)The partition, yyyymm is int type and actvydt is date stored as string type.

Answer

Alvaro Joao picture Alvaro Joao · Nov 29, 2016

This happens because hive is set to strict mode. this allow the partition table to access the respective partition /folder in hdfs .

  set hive.mapred.mode=unstrict;  it will work