Question: Does filtering on the Hive metadata "$path" column work?

[draganrelu]

Hi,

Suppose we have the following parquet files in S3:

s3://history/results/dt=2024-12-01/results_alice_0.parquet
s3://history/results/dt=2024-12-01/results_alice_1.parquet
s3://history/results/dt=2024-12-01/results_alice_2.parquet
s3://history/results/dt=2024-12-01/results_bob_0.parquet
s3://history/results/dt=2024-12-01/results_bob_1.parquet
s3://history/results/dt=2024-12-02/results_alice_0.parquet
s3://history/results/dt=2024-12-02/results_bob_0.parquet
...

And we create a Trino table results with the storage location at s3://history/results/, partitioned by dt.

If we do a query that filters both on the partitioning column dt but also on the Hive metadata column $path :

select * from results
where dt = date('2024-12-01')
and "$path" like "%alice%";

This query will return the elements from

s3://history/results/dt=2024-12-01/results_alice_0.parquet
s3://history/results/dt=2024-12-01/results_alice_1.parquet
s3://history/results/dt=2024-12-01/results_alice_2.parquet

But would it also read and parse the content of the bob files in that day?

s3://history/results/dt=2024-12-01/results_bob_0.parquet
s3://history/results/dt=2024-12-01/results_bob_1.parquet

?

In other words, does query filtering on the $path column do "pre-filtering" (skips reading the content of certain files) or "post-filtering" (reads the contents of all files and then filters the results)?

Thanks,
Relu