This section covers BigQuery partition elimination behavior when a partitioned table is joined to a driving table with a join condition on a non-partitioning field expression (NPFE) and a driving table field.

General observations:

Partition elimination was not evident in the query plan for the query below.

select
    t1.ID
from `BIGQUERY_SAMPLES.FLDPART28_R140M_F100_TABLE_2` t1
join `BIGQUERY_SAMPLES.DATES` dts
on
    dts.CAL_DAYOFWEEK = t1.ID + 1
where
    dts.CAL_DT = '2018-01-29'
;