HiveQL WHERE子句中array_item过滤条件失效问题排查

问题原因 #

这是Hive 2.1.1版本的已知优化逻辑缺陷：当查询同时包含lateral view explode和IN子查询时，Hive的查询优化器会错误调整过滤条件的执行逻辑——它误将针对explode后行的array_item in (20,30)过滤条件判定为可以下推到原sample表，但原表不存在array_item字段，最终导致该过滤条件被直接忽略，所有explode后的行都被保留。而当用具体值替代子查询时，优化器能正确识别过滤条件的作用范围，因此过滤正常生效。

解决方案 #

1. 提前过滤原表，再执行explode #

将针对col的过滤逻辑移到CTE中，先筛选出符合条件的行，再对数组进行explode，避免优化器误判：

with sample as (
              select 1 as col, array(10)         as array_col
    union all select 2 as col, array(10, 20, 30) as array_col
    union all select 3 as col, array(20, 30)     as array_col
    union all select 4 as col, array(30)         as array_col
),
filtered_sample as (
    select col, array_col
    from sample
    where col in (select col_target from targets)
),
targets as (
    select 2 as col_target
)
select
    col
    , array_col
    , array_item
from
    filtered_sample
    lateral view explode(array_col) a as array_item
where
    array_item in (20, 30)

2. 用JOIN替代IN子查询 #

将IN子查询替换为JOIN操作，优化器能正确识别所有过滤条件的作用范围：

with sample as (
              select 1 as col, array(10)         as array_col
    union all select 2 as col, array(10, 20, 30) as array_col
    union all select 3 as col, array(20, 30)     as array_col
    union all select 4 as col, array(30)         as array_col
),
targets as (
    select 2 as col_target
)
select
    s.col
    , s.array_col
    , array_item
from
    sample s
join targets t on s.col = t.col_target
lateral view explode(s.array_col) a as array_item
where
    array_item in (20, 30)

3. 临时关闭特定优化参数（不推荐） #

如果无法修改查询语句，可以尝试关闭过滤条件下推的优化参数（会影响整体查询性能，需谨慎）：

-- 先执行参数设置
set hive.optimize.filter.pushdown=false;

-- 再执行原查询
with sample as (
              select 1 as col, array(10)         as array_col
    union all select 2 as col, array(10, 20, 30) as array_col
    union all select 3 as col, array(20, 30)     as array_col
    union all select 4 as col, array(30)         as array_col
),
targets as (
    select 2 as col_target
)
select
    col
    , array_col
    , array_item
from
    sample
    lateral view explode(array_col) a as array_item
where
    true
    and array_item in (20, 30)
    and sample.col in (select col_target from targets)

内容的提问来源于stack exchange，提问作者strikesmash