PR #21743 disables Parquet pruning for `= ANY([literals])`

[cetra3]

Describe the bug

PR #21743 (commit 4bff17ebd) unified ANY/ALL planning under a CASE expression to align NULL semantics with PostgreSQL.

As a side effect, the new LogicalPlan shape is no longer recognized by PruningPredicate, which disables Parquet row-group statistics pruning, bloom-filter pruning, and page-index pruning for <expr> <op> ANY(<array literal>) queries.

To Reproduce

This will fail:

#[tokio::test]
async fn prune_int32_eq_any_literal_array() {
    RowGroupPruningTest::new()
        .with_scenario(Scenario::Int)
        .with_query("SELECT * FROM t where i32 = ANY([100])")
        .with_expected_errors(Some(0))
        .with_matched_by_stats(Some(0))
        .with_pruned_by_stats(Some(0))
        .with_pruned_files(Some(1))
        .with_matched_by_bloom_filter(Some(0))
        .with_pruned_by_bloom_filter(Some(0))
        .with_expected_rows(0)
        .test_row_group_prune()
        .await;
}

Running with the datafusion CLI, you can see it as well:

CREATE EXTERNAL TABLE alltypes_plain
STORED AS PARQUET
LOCATION 'parquet-testing/data/alltypes_plain.parquet';

EXPLAIN VERBOSE SELECT id FROM alltypes_plain WHERE id = ANY([1, 2, 3]);

Gives the following pruning predicate:

pruning_predicate=id_null_count@0 > 0 OR id_null_count@0 != row_count@1

Whereas an IN statement works fine

CREATE EXTERNAL TABLE alltypes_plain
STORED AS PARQUET
LOCATION 'parquet-testing/data/alltypes_plain.parquet';

EXPLAIN VERBOSE SELECT id FROM alltypes_plain WHERE id IN (1, 2, 3);

pruning_predicate=id_null_count@2 != row_count@3 AND id_min@0 <= 1 AND 1 <= id_max@1 OR id_null_count@2 != row_count@3 AND id_min@0 <= 2 AND 2 <= id_max@1 OR id_null_count@2 != row_count@3 AND id_min@0 <= 3 AND 3 <= id_max@1,

Expected behavior

The test will pass

Additional context

No response

[alamb]

@cetra3 if you have some thoughts, perhaps you can raise a PR with a proposal for how to fix it? Perhaps we could start with a PR to revert these changes while we sort it out?

[cetra3]

I think if we want postgresql semantics for nulls, then we should adjust the array udfs to internally sort out the null cases and then emit them accordingly.

Or we could introduce a new UDF for any specifically, which was mentioned in the original PR.

But I am torn as to whether we need them (postgresql null semantics), since the current udfs don't honour them and I don't know if there is a strong mandate in the rest of the code base.

If the intent is to move towards them, and closer to postgres's behaviour maybe we should just adjust the existing udfs rather than introducing new behaviour equivalent ones

[alamb]

I think if we want postgresql semantics for nulls, then we should adjust the array udfs to internally sort out the null cases and then emit them accordingly.

This makes sense to me

In my personal opinion, at this point in DataFusion's lifetime, we should bias towards "stay with existing behavior" and don't change behavior just to conform with postgres.

I suggest we revert the original PR