[jira] [Commented] (HIVE-10122) Hive metastore filter-by-expression is broken for non-partition expressions

[Mostafa Mokhtar (JIRA)]

    [ 
https://issues.apache.org/jira/browse/HIVE-10122?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14384917#comment-14384917
 ] 

Mostafa Mokhtar commented on HIVE-10122:
----------------------------------------

[~sershe]
Ok, now I understand what is going on, this is the problematic metastore query 

{code}
SELECT 
    A0.PART_NAME AS NUCORDER0
FROM
    PARTITIONS A0
        LEFT OUTER JOIN
    TBLS B0 ON A0.TBL_ID = B0.TBL_ID
        LEFT OUTER JOIN
    DBS C0 ON B0.DB_ID = C0.DB_ID
WHERE
    C0.NAME = 'tpcds_bin_partitioned_orc_30000'
        AND B0.TBL_NAME = 'store_sales'
ORDER BY NUCORDER0;
{code}

Then the remaining ones use the PART_NAME and PART_ID from the previous query
{code}
select 
    PARTITIONS.PART_ID
from
    PARTITIONS
        inner join
    TBLS ON PARTITIONS.TBL_ID = TBLS.TBL_ID
        and TBLS.TBL_NAME = 'store_sales'
        inner join
    DBS ON TBLS.DB_ID = DBS.DB_ID
        and DBS.NAME = 'tpcds_bin_partitioned_orc_30000'
where
    PARTITIONS.PART_NAME in ('ss_sold_date_sk=2450816' , 
'ss_sold_date_sk=2450817');
{code}
{code}
select 
    PARTITIONS.PART_ID,
    SDS.SD_ID,
    SDS.CD_ID,
    SERDES.SERDE_ID,
    PARTITIONS.CREATE_TIME,
    PARTITIONS.LAST_ACCESS_TIME,
    SDS.INPUT_FORMAT,
    SDS.IS_COMPRESSED,
    SDS.IS_STOREDASSUBDIRECTORIES,
    SDS.LOCATION,
    SDS.NUM_BUCKETS,
    SDS.OUTPUT_FORMAT,
    SERDES.NAME,
    SERDES.SLIB
from
    PARTITIONS
        left outer join
    SDS ON PARTITIONS.SD_ID = SDS.SD_ID
        left outer join
    SERDES ON SDS.SERDE_ID = SERDES.SERDE_ID
where
    PART_ID in (59203 , 58422)
order by PART_NAME asc;
{code}


If filters are on the partitioned column only as in 
{code} 
select ss_item_sk  rowcount from store_sales where ss_sold_date_sk between 
2450816 and 2450817  ;
{code}

Then PARTITIONS table is queried with a partition filter.
{code}

select 
    PARTITIONS.PART_ID
from
    PARTITIONS
        inner join
    TBLS ON PARTITIONS.TBL_ID = TBLS.TBL_ID
        and TBLS.TBL_NAME = 'store_sales'
        inner join
    DBS ON TBLS.DB_ID = DBS.DB_ID
        and DBS.NAME = 'tpcds_bin_partitioned_orc_30000'
        inner join
    PARTITION_KEY_VALS FILTER0 ON FILTER0.PART_ID = PARTITIONS.PART_ID
        and FILTER0.INTEGER_IDX = 0
where
    ((((case
        when
            TBLS.TBL_NAME = 'store_sales'
                and DBS.NAME = 'tpcds_bin_partitioned_orc_30000'
        then
            cast(FILTER0.PART_KEY_VAL as decimal (21 , 0 ))
        else null
    end) >= 2450816)
        and ((case
        when
            TBLS.TBL_NAME = 'store_sales'
                and DBS.NAME = 'tpcds_bin_partitioned_orc_30000'
        then
            cast(FILTER0.PART_KEY_VAL as decimal (21 , 0 ))
        else null
    end) <= 2450817)));
{code}


For 2K partitions there is no measurable performance difference between 
{code}
 explain select ss_item_sk  rowcount from store_sales where ss_sold_date_sk 
between 2450816 and 2450817  ;
{code}

and 
{code}
explain select ss_item_sk  rowcount from store_sales where ss_sold_date_sk 
between 2450816 and 2450817  and ss_ticket_number  > 100000000 and ss_item_sk > 
50;
{code}

> Hive metastore filter-by-expression is broken for non-partition expressions
> ---------------------------------------------------------------------------
>
>                 Key: HIVE-10122
>                 URL: https://issues.apache.org/jira/browse/HIVE-10122
>             Project: Hive
>          Issue Type: Bug
>          Components: Metastore
>    Affects Versions: 0.14.0, 1.0.0, 1.1.0
>            Reporter: Sergey Shelukhin
>
> See 
> https://issues.apache.org/jira/browse/HIVE-10091?focusedCommentId=14382413&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-14382413
> These two lines of code
> {noformat}
>     // Replace virtual columns with nulls. See javadoc for details.
>     prunerExpr = removeNonPartCols(prunerExpr, extractPartColNames(tab), 
> partColsUsedInFilter);
>     // Remove all parts that are not partition columns. See javadoc for 
> details.
>     ExprNodeDesc compactExpr = compactExpr(prunerExpr.clone());
> {noformat}
> are supposed to take care of this; I see there were bunch of changes to this 
> code over some time, and now it appears to be broken.
> Thanks to [~thejas] for info.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)