This is an automated email from the ASF dual-hosted git repository.
peter-toth pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new da165c1c6587 [SPARK-60702][SQL] Don't skip a runtime bloom filter for
a DPP filter that physical planning drops
da165c1c6587 is described below
commit da165c1c6587412aedde4a061910d888e705ab4a
Author: Peter Toth <[email protected]>
AuthorDate: Sat Nov 7 15:53:37 2026 +0100
[SPARK-60702][SQL] Don't skip a runtime bloom filter for a DPP filter that
physical planning drops
### What changes were proposed in this pull request?
`InjectRuntimeFilter` adds no bloom filter on a join key that already has a
DPP filter. An `onlyInBroadcast` DPP filter runs only if physical planning
finds a broadcast to reuse. Otherwise physical planning replaces it with
`true`. This PR stops counting such a filter when nothing in the plan could
give it a broadcast. For a key with a DPP filter, the rule now takes three
steps:
1. **Reusable broadcasts (`reused`).** The rule collects the join sides
that a broadcast join could broadcast and a DPP filter could reuse:
- for an equi-join, the side that `getBroadcastHashJoinBuildSide` picks.
That is the side with a broadcast hint, or else a side small enough to
broadcast that the join type can build from, unless a `SHUFFLE_HASH` hint asks
for a shuffle hash join. It ignores the other join hints;
- for a null-aware anti join, its right side, under its own thresholds,
and only without AQE. Physical planning keeps a DPP filter over that plan,
though the filter builds its own broadcast (SPARK-60758). AQE plans such a
filter as `true`.
A side is compared without the DPP filters on top of it, since cleanup
can still drop them. It stays in the list only if it has the same result as a
plan that a DPP filter could reuse: the filter's build plan, or the source plan
of its broadcast value projection (SPARK-58265). The DPP filters include those
that the optimizer merged into a larger filter in a subquery. With exchange
reuse off, the list is empty.
2. **Kept DPP filters (`isKept`).** An `onlyInBroadcast` DPP filter counts
as kept if one of its plans has the same result as a plan in `reused`. Any
other DPP filter counts as kept. So does every DPP filter in a subquery. An
uncorrelated IN subquery is joined with the rest of the query later, where
`PartitionPruning` puts a new DPP filter on the key, and the rule decides that
one against every broadcast. A scalar subquery, which includes an uncorrelated
EXISTS, is planned on its own, [...]
3. **Safe pushdown (`canPushDown`).** For a key whose DPP filters are all
dropped, pushdown moves the new bloom filter along its key, down to the leaves
the key comes from, and into every branch of a union. It rewrites the key
through the aliases on its way. The rule follows the same lineages, with the
new `findExpressionAndTrackAllLineagesDown` in `PredicateHelper`, which with
`strict` gives up when it cannot follow the key into a branch. The bloom filter
is added only if, at every leaf:
- the leaf is not in a plan in `reused`. There the bloom filter would
change the broadcast, and physical planning would drop the DPP filter that
reuses it. A reused broadcast elsewhere in the join side does not matter, such
as a broadcast dimension joined to the same fact table;
- the key cannot fail, as `ExprUtils.canEvaluateUnconditionally`
decides. A numeric up-cast over a key that cannot fail cannot fail either
(`Cast.canUpCast` between numeric types), nor can the normalization of a
floating point key. Each is checked against its own child. On a partition
column, the bloom filter becomes a partition filter, which evaluates the key on
every partition before the data filters. So a key that can fail could fail the
query (SPARK-60809).
For a key whose DPP filters are all dropped, the key's type must also
compare by its bytes (`UnsafeRowUtils.isBinaryStable`), since the bloom filter
hashes them. A collated string does not. The bloom filter also evaluates its
creation side again, apart from the join. So the rule also:
- adds the bloom filter only if the plan that its subquery reads gives the
same rows again (SPARK-60808). It must read no CTE, whose reference hides the
determinism of its plan, a cache only if Spark can tell that scanning it again
gives the same rows, and a V2 scan only if `V2ScanRelationPushDown` marks every
operation pushed into it as working on each row by itself, unlike a pushed
limit or top-N. Nor may it read a file metadata column, such as
`_metadata.file_block_start`, which de [...]
- adds the bloom filter only if nothing that its subquery evaluates can
fail, as for the key above. That covers its filters, and the projections that
the key and the filters need. A DPP filter there counts only if it is
`onlyInBroadcast`, since it finds no broadcast in the subquery and is `true`
there. A DPP filter with a benefit would run its own subquery, though cleanup
can drop it from the join. The subquery can read more of the creation side than
the join does, since filters on ot [...]
- leaves the key's own DPP filters out of the creation side, since physical
planning drops them.
Keys without a DPP filter get their bloom filters as on master, apart from
the known limits about the filter count.
### Why are the changes needed?
With the default
`spark.sql.optimizer.dynamicPartitionPruning.reuseBroadcastOnly`,
`PartitionPruning` adds an `onlyInBroadcast` DPP filter on a join key that the
scan offers for runtime filtering, such as a partition column.
`InjectRuntimeFilter` then skips that key. In a shuffle join or a
storage-partitioned join, physical planning replaces the DPP filter with
`true`. So the key ends up with no runtime filter at all.
For example:
- a V1 table partitioned by the join key, in a shuffle join;
- a V2 scan that offers the source column of a `bucket(N, id)` partition
transform for runtime filtering, as Iceberg's does, in a shuffle join and in a
storage-partitioned join. A bloom filter does not prune any splits there. It
filters rows, and a scan can apply it as a storage filter.
The same happens in a broadcast join that broadcasts the pruned side, and
with exchange reuse off.
The key still has to pass the usual checks for a bloom filter. With the
default thresholds, `spark.sql.autoBroadcastJoinThreshold` and
`spark.sql.optimizer.runtime.bloomFilter.creationSideThreshold` are both 10MB.
A join side small enough to be the creation side is then usually small enough
to broadcast. So the examples need a lower broadcast threshold, or a creation
side that is a smaller part of the join side, such as a filtered scan under
another join.
Discussed on #58635:
https://github.com/apache/spark/pull/58635#issuecomment-6468432320
### Does this PR introduce _any_ user-facing change?
Yes, a plan change. Such a join key can now get a runtime bloom filter,
where it had no runtime filter before. The results stay the same, apart from
the cases in the Known limits.
### How was this patch tested?
`InjectRuntimeFilterSuite`:
- The two existing DPP tests used a shuffle join with an `onlyInBroadcast`
DPP filter, which is the case this PR changes. They now turn
`reuseBroadcastOnly` off, so their DPP filter runs as its own subquery and
still keeps the bloom filter off its key. They also check that subquery.
- New tests, with AQE on and off unless noted:
- a shuffle join gets the bloom filter, with the partitioned table on
either side, and physical planning replaces the DPP filter with `true`. The
creation side is not empty, so the bloom filter meets the partition values, and
the query returns rows;
- no bloom filter when the DPP filter reuses the broadcast of a broadcast
join that has a shuffle below it. The join broadcasts by a hint, with the
partitioned table on either side, or by size. With DPP off, the bloom filter.
With exchange reuse off, the bloom filter, and the DPP filter is `true`;
- no bloom filter when the DPP filter reuses the broadcast of another
join, in the other branch of a union;
- with AQE off, no bloom filter when a null-aware anti join broadcasts
the build plan of the DPP filter. Physical planning keeps the filter, though it
builds its own broadcast. With AQE on, which plans the filter as `true`, and
with DPP off, the bloom filter;
- a right outer join whose right side is small enough to broadcast: the
join type rules out that broadcast, so the DPP filter does not count and is
`true`. The aggregate below the join shuffles, so the join gets the bloom
filter;
- a join whose `SHUFFLE_HASH` hint rules out the broadcast: the DPP
filter does not count and is `true`. The aggregate below the join shuffles, so
the join gets the bloom filter;
- no bloom filter if the creation side that the subquery reads is not
deterministic, or, with AQE off, if the pruned side is not deterministic and
would be the creation side. Without DPP, the latter gets the bloom filter, as
on master;
- no bloom filter for a key with a dropped DPP filter that can fail, also
behind an alias, under a narrowing cast, also over an up-cast or under the
normalization of a floating point key, a decimal to bigint cast or, with AQE
off, a date to timestamp cast, or in one branch of a union, also in a branch
whose lineage the rule cannot follow. A numeric up-cast of the key, and a
floating point key, also over a `coalesce`, get the bloom filter. So does a
creation filter with a `coalesce`, [...]
- with AQE off, no bloom filter from a creation side that reads a
non-deterministic CTE;
- a non-deterministic column that the bloom filter does not read keeps
the bloom filter;
- with AQE off, no bloom filter from a cache that is not materialized
yet, or whose plan Spark cannot show to be repeatable. A materialized cache of
a scan gives the bloom filter;
- with AQE off, no bloom filter from a creation side that filters on
`_metadata.file_block_start`;
- with AQE off, no bloom filter from a creation side that reads a V2 scan
with a pushed top-N. With DPP off, that scan gives `p.f5` the bloom filter.
With DPP on, the other side of the join gets it;
- no bloom filter for a key with a dropped DPP filter of a collated
string type. The query returns its row;
- no bloom filter whose subquery would cast `'abc'` on partitions that a
kept DPP filter prunes only in the join. That filter comes from an upper join,
with the cast in the creation key or in a creation filter, or, with AQE off,
from another branch of a union. With the cast only in the filter, the other
side of the join gets the bloom filter instead, also with the cast under `or`,
`not`, `in` or a null check. A creation side whose DPP filter runs as its own
subquery gives no bloom f [...]
- no bloom filter whose creation key would cast `'abc'` on a row that the
bloom filter of another key drops before the join;
- no bloom filter in an IN subquery whose DPP filter reuses a broadcast
outside the subquery. Without that broadcast, the key gets the bloom filter;
- with AQE off, no bloom filter for a key whose DPP filter physical
planning drops, where the bloom filter would go into the broadcast that another
DPP filter reuses. The bloom filter is for a shuffle join inside that
broadcast, with the partitioned table on either side. Or it goes through a
union into the other branch, where the broadcast is over a cache. The other DPP
filter keeps reusing its broadcast. With DPP off, the shuffle join gets the
bloom filter;
- no bloom filter on a key whose DPP filter reuses a broadcast side that
has a DPP filter of its own, which cleanup drops later, with that side on
either side of the join. The other key of the join, whose DPP filter cleanup
drops, gets one;
- with AQE off and `reuseBroadcastOnly` off, no bloom filter that would
go into the broadcast that a DPP filter with a benefit reuses;
- a key without a DPP filter keeps the bloom filter that master adds,
also where pushdown moves it into a broadcast that a DPP filter reuses;
- when the pruned side is the creation side of the bloom filter, on
either side of the join, the bloom filter's subquery reads that side and leaves
out the key's DPP filter;
- a broadcast join that broadcasts the pruned side gets the bloom filter,
and the DPP filter is `true`;
- with a broadcast value projection: when nothing can broadcast the
projection's source, the bloom filter, and the DPP filter is `true`. With AQE
off and the inner join broadcasting the source, no bloom filter, and the DPP
filter reads the projected broadcast values. With AQE on, no bloom filter, and
the DPP filter is `true` (SPARK-60703). With the projection off, the bloom
filter.
`KeyGroupedPartitioningCatalystRuntimeFilterSuite`: a storage-partitioned
join over a scan that offers the source column of `bucket(4, id)` for runtime
filtering gets the bloom filter among the scan's runtime filters, with AQE on
and off. The DPP filter is `true`, and the join plans no shuffle.
`StorageFilterPushDownSuite`: two tests turned DPP off, so that DPP would
not take the bloom filter's place. They now run with DPP on. The test source
now counts a runtime filter call only for a filter on a column, so the `true`
DPP filter does not count as the bloom filter reaching runtime filtering.
A temporary check in both DPP planners, not part of this PR and attached in
a comment, logged every DPP filter that physical planning keeps on a key that
also has a bloom filter. A bloom filter needs an application side over
`spark.sql.optimizer.runtime.bloomFilter.applicationSideScanSizeThreshold`,
10GB by default. So at the default thresholds, only the suites that lower it
and the TPC-DS sf100 suites with statistics can show the case. The check ran:
- over these suites, the DPP suites (V1, V2 and V2 filter, AQE on and off),
`KeyGroupedPartitioningSuite`, and the TPC-DS and TPC-H plan stability suites.
It logged none with this PR, none on master, and 62 with a rule that ignores
every `onlyInBroadcast` DPP filter, as a control, of them 54 in the TPC-DS
sf100 plans;
- over the DPP suites and the TPC-DS v1.4 and TPC-H plan stability suites,
with that threshold at 0. It logged none with this PR, and 36 with a control
rule that counts no `onlyInBroadcast` DPP filter as kept, of them 24 in the DPP
suites. Some plan assertions of these suites fail at that threshold, as
expected.
The plan stability suites pass without golden file changes.
A scratch run, not part of this PR, with AQE on and off:
- a V1 table partitioned by the join key, in a shuffle join;
- a V2 scan that offers the source column of `bucket(4, id)` for runtime
filtering, in a shuffle join and in a storage-partitioned join.
With DPP on, each gets the bloom filter, and the storage-partitioned join
still plans no shuffle.
Known limits:
- The check counts a DPP filter whenever a join could broadcast its build
plan, even when physical planning does not reuse that broadcast. The key then
keeps getting no runtime filter, as on master. For example:
- a join whose `MERGE` hint asks for a sort merge join, or a
`SHUFFLE_REPLICATE_NL` hint on an inner join. The check does not model those
hints. A veto would have to follow every condition of `JoinSelection`, and a
later change there could put a bloom filter next to a kept DPP filter;
- join keys that no hash join supports;
- a broadcast side that keeps a DPP filter of its own, which the DPP
filter's build plan does not have;
- under AQE, a join that broadcasts the same plan on other keys. AQE
compares the broadcast mode too.
- The rule also skips a new bloom filter when pushdown would stop above the
broadcast, and under AQE, which may not reuse the broadcast. The key then gets
no bloom filter, as on master.
- If the rule cannot follow the key into a union branch, the key gets no
bloom filter, as on master. That includes a branch whose key is a literal.
- In a scalar subquery, which includes an uncorrelated EXISTS, a key with a
DPP filter gets no bloom filter, as on master.
- A creation side that reads a CTE gives no bloom filter, as on master,
even a deterministic `MATERIALIZED` CTE. So does a cache that Spark cannot show
to give the same rows again, such as one that is not materialized yet, and any
read of a `_metadata` column, though only its split fields depend on how the
scan splits the files. So does a V2 scan that Spark does not mark as reading
its rows one by one, such as one with a pushed limit, top-N, offset, sample,
aggregate or join, or the t [...]
- For a key with a dropped DPP filter, a creation side whose subquery would
evaluate something that can fail gives no bloom filter, as on master. For
example, a string function, a string to int cast in a filter, or the bloom
filter of a lower join. The search for the creation side does not then try the
other side of an inner join.
- The bloom filter's subquery reads the partitions that a kept DPP filter
of another key prunes only in the join. If such a partition has a file that
cannot be read, the subquery fails the query (SPARK-60842 for keys without a
DPP filter).
- A `LogicalRDD` that is not a materialized checkpoint also hides the
determinism of its plan, such as the DataFrame that `foreachBatch` gets for a
micro-batch, or a lazy checkpoint (`Dataset.checkpoint(eager = false)`). The
rule cannot see it, since `LogicalRDD` is in `sql/core`. A bloom filter from a
non-deterministic plan under such a leaf can then drop rows of the join.
- A source can also pick its rows in its own query, such as a JDBC `query`
option or a database view with a `LIMIT`. A second read can then return other
rows. The rule cannot see that either, so a bloom filter from such a source can
drop rows of the join, as for a key without a DPP filter on master.
- A bloom filter for a key without a DPP filter can still go into the
broadcast that a DPP filter reuses, as on master. Physical planning then
replaces that DPP filter with `true`.
- `MergeSubplans` can merge the new bloom filter's subquery with a scalar
subquery inside a reused broadcast. That changes the broadcast in the main
plan, but not the DPP filter's build plan, so physical planning replaces the
DPP filter with `true`.
- A new bloom filter on a scan can keep two scans from sharing an exchange,
as any bloom filter does. On master, a `true` DPP filter does not keep them
apart.
- A DPP filter with a broadcast value projection counts as kept whenever a
join could broadcast the projection's source plan. Physical planning also
needs, without AQE, a broadcast hash join that is not null-aware and has the
same broadcast mode. Under AQE, it plans such a filter as `true` when the plan
under that broadcast has no shuffle, see SPARK-60703. And at run time the
projection gives up past its size limits. The key then gets no runtime filter,
as on master.
- The new bloom filters count against
`spark.sql.optimizer.runtimeFilter.number.threshold`. In a query that reaches
that limit, a bloom filter that master adds for a later join can be left out.
- A new bloom filter at a join also keeps a later join on the same key from
adding its own, as any bloom filter on that key does. So the key can get its
bloom filter from another join than on master.
- The usual bloom filter checks still apply. `isProbablyShuffleJoin` does
not look at the join type, so a join whose type rules out the broadcast needs a
shuffle below it. A partitioned catalog table that was never analyzed reports
the default size, which the application side check counts as 0, so it gets no
bloom filter.
An exact fix would decide at physical planning, where the fate of the DPP
filter is known. That is a larger change, left for a follow-up.
### Was this patch authored or co-authored using generative AI tooling?
Generated-by: Claude Code with Claude Opus 5.5
Closes #59916 from peter-toth/SPARK-60702-dpp-dropped-in-planning-bloom.
Authored-by: Peter Toth <[email protected]>
Signed-off-by: Peter Toth <[email protected]>
skip-checks: true
---
.../sql/catalyst/expressions/predicates.scala | 44 +-
.../catalyst/optimizer/InjectRuntimeFilter.scala | 193 ++++-
.../spark/sql/catalyst/optimizer/joins.scala | 8 +-
.../spark/sql/InjectRuntimeFilterSuite.scala | 775 ++++++++++++++++++++-
.../connector/KeyGroupedPartitioningSuite.scala | 37 +-
.../sql/connector/StorageFilterPushDownSuite.scala | 10 +-
.../sql/connector/StorageFilterTestSource.scala | 10 +-
7 files changed, 1007 insertions(+), 70 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala
index 41669d7d1ac4..add65c763fb7 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/predicates.scala
@@ -118,36 +118,42 @@ trait PredicateHelper extends AliasHelper with Logging {
*/
def findExpressionAndTrackLineageDown(
exp: Expression,
- plan: LogicalPlan): Option[(Expression, LogicalPlan)] = {
- if (exp.references.isEmpty) return None
+ plan: LogicalPlan): Option[(Expression, LogicalPlan)] =
+ findExpressionAndTrackAllLineagesDown(exp, plan).headOption
+
+ /**
+ * Like [[findExpressionAndTrackLineageDown]], but returns every lineage,
not only the first one.
+ * It follows each branch of a union, and each child that has the references
of exp. With
+ * `strict`, it returns no lineage at all if it cannot follow exp down one
of those.
+ */
+ def findExpressionAndTrackAllLineagesDown(
+ exp: Expression,
+ plan: LogicalPlan,
+ strict: Boolean = false): Seq[(Expression, LogicalPlan)] = {
+ if (exp.references.isEmpty) return Nil
+
+ // The lineages of each expression in the plan it is paired with.
+ def lineages(pairs: Seq[(Expression, LogicalPlan)]): Seq[(Expression,
LogicalPlan)] = {
+ val found = pairs.map { case (e, p) =>
findExpressionAndTrackAllLineagesDown(e, p, strict) }
+ if (strict && found.exists(_.isEmpty)) Nil else found.flatten
+ }
plan match {
case p: Project =>
val aliases = getAliasMap(p)
- findExpressionAndTrackLineageDown(replaceAlias(exp, aliases), p.child)
+ findExpressionAndTrackAllLineagesDown(replaceAlias(exp, aliases),
p.child, strict)
// we can unwrap only if there are row projections, and no aggregation
operation
case a: Aggregate =>
val aliasMap = getAliasMap(a)
- findExpressionAndTrackLineageDown(replaceAlias(exp, aliasMap), a.child)
+ findExpressionAndTrackAllLineagesDown(replaceAlias(exp, aliasMap),
a.child, strict)
case l: LeafNode if exp.references.subsetOf(l.outputSet) =>
- Some((exp, l))
+ Seq((exp, l))
case u: Union =>
val index = u.output.indexWhere(_.semanticEquals(exp))
- if (index > -1) {
- u.children
- .flatMap(child =>
findExpressionAndTrackLineageDown(child.output(index), child))
- .headOption
- } else {
- None
- }
+ if (index > -1) lineages(u.children.map(child => (child.output(index),
child))) else Nil
case other =>
- other.children.flatMap {
- child => if (exp.references.subsetOf(child.outputSet)) {
- findExpressionAndTrackLineageDown(exp, child)
- } else {
- None
- }
- }.headOption
+ lineages(other.children.filter(child =>
exp.references.subsetOf(child.outputSet))
+ .map(child => (exp, child)))
}
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InjectRuntimeFilter.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InjectRuntimeFilter.scala
index ac67141c4e60..d5385e11bf18 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InjectRuntimeFilter.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/InjectRuntimeFilter.scala
@@ -17,16 +17,16 @@
package org.apache.spark.sql.catalyst.optimizer
-import scala.annotation.tailrec
-
import org.apache.spark.sql.catalyst.expressions._
import org.apache.spark.sql.catalyst.expressions.aggregate.BloomFilterAggregate
-import org.apache.spark.sql.catalyst.planning.ExtractEquiJoinKeys
+import org.apache.spark.sql.catalyst.planning.{ExtractEquiJoinKeys,
ExtractSingleColumnNullAwareAntiJoin}
import org.apache.spark.sql.catalyst.plans.logical._
import org.apache.spark.sql.catalyst.rules.Rule
import org.apache.spark.sql.catalyst.trees.TreePattern.{INVOKE,
JSON_TO_STRUCT, LIKE_FAMLIY, PYTHON_UDF, REGEXP_EXTRACT_FAMILY, REGEXP_REPLACE,
SCALA_UDF}
import org.apache.spark.sql.catalyst.util.UnsafeRowUtils
+import org.apache.spark.sql.execution.datasources.v2.DataSourceV2ScanRelation
import org.apache.spark.sql.internal.SQLConf
+import org.apache.spark.sql.types.NumericType
/**
* Insert a runtime filter on one side of the join (we call this side the
application side) if
@@ -385,23 +385,99 @@ object InjectRuntimeFilter extends Rule[LogicalPlan] with
PredicateHelper with J
}
}
- // This checks if there is already a DPP filter, as this rule is called just
after DPP.
- @tailrec
- private def hasDynamicPruningSubquery(
- left: LogicalPlan,
- right: LogicalPlan,
- leftKey: Expression,
- rightKey: Expression): Boolean = {
- (left, right) match {
- case (Filter(DynamicPruningSubquery(pruningKey, _, _, _, _, _, _),
plan), _) =>
- pruningKey.fastEquals(leftKey) || hasDynamicPruningSubquery(plan,
right, leftKey, rightKey)
- case (_, Filter(DynamicPruningSubquery(pruningKey, _, _, _, _, _, _),
plan)) =>
- pruningKey.fastEquals(rightKey) ||
- hasDynamicPruningSubquery(left, plan, leftKey, rightKey)
- case _ => false
+ // The plans that a broadcast hash join of `plan` could broadcast, as
+ // `getBroadcastHashJoinBuildSide` picks them, but no null-aware anti join
under AQE. The list can
+ // hold more plans than physical planning broadcasts, and no fewer that a
DPP filter can reuse,
+ // unless the statistics change after this rule. It takes each side without
the DPP filters on top
+ // of it, since cleanup can still drop them.
+ private def broadcastablePlans(plan: LogicalPlan): Seq[LogicalPlan] =
plan.flatMap {
+ // AQE plans a DPP filter as `true` even if a null-aware anti join
broadcasts its build plan.
+ // Without AQE, physical planning keeps such a filter, though the filter
builds its own
+ // broadcast.
+ case ExtractSingleColumnNullAwareAntiJoin(_, _) if
conf.adaptiveExecutionEnabled => Nil
+ case join: Join =>
+ getBroadcastHashJoinBuildSide(join, conf).map {
+ case BuildLeft => withoutDppFilters(join.left)
+ case BuildRight => withoutDppFilters(join.right)
+ }
+ case _ => Nil
+ }
+
+ // `plan` without the DPP filters on top of it that `drop` picks, by default
all of them.
+ private def withoutDppFilters(
+ plan: LogicalPlan,
+ drop: DynamicPruningSubquery => Boolean = _ => true): LogicalPlan = plan
match {
+ case f @ Filter(dpp: DynamicPruningSubquery, child) =>
+ val newChild = withoutDppFilters(child, drop)
+ if (drop(dpp)) newChild else f.withNewChildren(Seq(newChild))
+ case _ => plan
+ }
+
+ // Whether evaluating `e` cannot fail, also on rows that the join does not
read. It asks
+ // `ExprUtils.canEvaluateUnconditionally`, after it replaces, bottom-up, two
wrappers that cannot
+ // fail either over a child that it takes: a numeric up-cast, and the
normalization that
+ // `NormalizeFloatingNumbers` puts around a floating point join key. A
stand-in keeps the type of
+ // what it replaces, so each wrapper is checked against a child of its own
type. An
+ // `onlyInBroadcast` DPP filter counts too, since it finds no broadcast in
the bloom filter's
+ // subquery and is `true` there.
+ private def cannotFail(e: Expression): Boolean = e match {
+ case dpp: DynamicPruningSubquery => dpp.onlyInBroadcast
+ case _ => ExprUtils.canEvaluateUnconditionally(e.transformUp {
+ case c: Cast if isNumericUpCast(c) &&
ExprUtils.canEvaluateUnconditionally(c.child) =>
+ AttributeReference("upCast", c.dataType, c.nullable)()
+ case n @ KnownFloatingPointNormalized(NormalizeNaNAndZero(child))
+ if ExprUtils.canEvaluateUnconditionally(child) =>
+ AttributeReference("normalized", n.dataType, n.nullable)()
+ })
+ }
+
+ private def isNumericUpCast(c: Cast): Boolean =
+ c.child.dataType.isInstanceOf[NumericType] &&
c.dataType.isInstanceOf[NumericType] &&
+ Cast.canUpCast(c.child.dataType, c.dataType)
+
+ // Whether the bloom filter's subquery can evaluate `creationSide` again,
apart from the join. The
+ // plan it reads must give the same rows again (SPARK-60808). So it must
read no CTE, whose
+ // reference hides the determinism of its plan, a cache only if Spark can
tell that scanning it
+ // again gives the same rows, and a V2 scan only if every operation pushed
into it works on each
+ // row by itself. A pushed limit or top-N, for example, lets the source pick
other rows on a
+ // second read. It must not read a file metadata column either, such as
+ // `_metadata.file_block_start`, which depends on how the scan splits its
files. The subquery can
+ // split them differently. Its filters, and the projections that the key and
the filters need,
+ // must pass `cannotFail`, which takes only expressions that are
deterministic in the subquery
+ // and do not fail there. A part of the join side outside that plan can only
drop rows from the
+ // join, so the bloom filter still holds every key that the join sees.
+ private def canEvaluateAgain(creationSide: FilterCreationSide): Boolean = {
+ val evaluated =
+ ColumnPruning(Project(Seq(Alias(creationSide.key, "key")()),
creationSide.plan))
+ !evaluated.exists {
+ case node if node.references.exists(a =>
FileSourceMetadataAttribute.isValid(a.metadata)) =>
+ true
+ case Project(projectList, _) => !projectList.forall(e =>
cannotFail(trimAliases(e)))
+ case Filter(condition, _) =>
!splitConjunctivePredicates(condition).forall(cannotFail)
+ case _: CTERelationRef => true
+ case scan: DataSourceV2ScanRelation => !scan.onlyRowWisePushdowns
+ case leaf: MaterializedLeafNode => !leaf.isOutputRepeatable
+ case _: LeafNode => false
+ case _ => true
}
}
+ // The plans whose broadcast a DPP filter may reuse: its build plan, and for
an `onlyInBroadcast`
+ // filter, the source plan of its broadcast value projection.
+ private def reusablePlans(dpp: DynamicPruningSubquery): Seq[LogicalPlan] =
+ dpp.buildQuery +: dpp.usableBroadcastValueProjection.filter(_ =>
dpp.onlyInBroadcast)
+ .map(_.sourcePlan).toSeq
+
+ // The DPP filters on `key` on top of `side`, where they sit when this rule
runs, just after DPP.
+ private def dynamicPruningSubqueries(
+ side: LogicalPlan,
+ key: Expression): Seq[DynamicPruningSubquery] = side match {
+ case Filter(dpp: DynamicPruningSubquery, child) =>
+ val rest = dynamicPruningSubqueries(child, key)
+ if (dpp.pruningKey.fastEquals(key)) dpp +: rest else rest
+ case _ => Nil
+ }
+
private def hasBloomFilter(plan: LogicalPlan, key: Expression): Boolean = {
plan.exists {
case Filter(condition, _) =>
@@ -417,19 +493,86 @@ object InjectRuntimeFilter extends Rule[LogicalPlan] with
PredicateHelper with J
private def tryInjectRuntimeFilter(plan: LogicalPlan): LogicalPlan = {
var filterCounter = 0
val numFilterThreshold =
conf.getConf(SQLConf.RUNTIME_FILTER_NUMBER_THRESHOLD)
+ // A key with a DPP filter gets no bloom filter, unless physical planning
drops that filter. It
+ // drops an `onlyInBroadcast` DPP filter when no broadcast join broadcasts
a plan that the
+ // filter could reuse. So for a key with a DPP filter, this rule:
+ // 1. Collects `reused`: the join sides that a broadcast join could
broadcast and a DPP filter
+ // could reuse.
+ // 2. Counts an `onlyInBroadcast` DPP filter as kept (`isKept`) if one of
its plans has the same
+ // result as a plan in `reused`. Any other DPP filter counts as kept.
So does every DPP
+ // filter in a subquery. An uncorrelated IN subquery is joined with the
rest of the query
+ // later, where `PartitionPruning` puts a new DPP filter on the key,
and this rule decides
+ // that one against all the broadcasts. A scalar subquery, which
includes an uncorrelated
+ // EXISTS, is planned on its own, so a key there with a DPP filter gets
no bloom filter.
+ // 3. Adds a bloom filter for a key whose DPP filters are all dropped only
if `creationSide`
+ // allows it: the key's type compares by its bytes (`isBinaryStable`),
pushdown can take it
+ // down safely (`canPushDown`), and the subquery can evaluate the
creation side again
+ // (`canEvaluateAgain`). Pushdown moves the bloom filter along its key,
down to the leaves
+ // the key comes from, and into every branch of a union. There it must
not land in a plan in
+ // `reused`. It would change the broadcast, and physical planning would
drop the DPP filter
+ // that reuses it. A reused broadcast elsewhere in the join side does
not matter, such as a
+ // broadcast dimension joined to the same fact table. The key must also
be safe at every leaf
+ // (`cannotFail`). On a partition column, the bloom filter becomes a
partition filter, which
+ // evaluates the key on every partition before the data filters. So a
key that can fail could
+ // fail the query (SPARK-60809).
+ // Physical planning reuses a broadcast only with exchange reuse on.
+ lazy val reused = if (conf.exchangeReuseEnabled) {
+ // After the optimizer ran on an IN subquery on its own, a DPP filter
there can be a conjunct
+ // of a larger filter.
+ val dppPlans = plan.flatMap {
+ case Filter(condition, _) =>
+ splitConjunctivePredicates(condition).collect { case dpp:
DynamicPruningSubquery => dpp }
+ case _ => Nil
+ }.flatMap(reusablePlans)
+ broadcastablePlans(plan).filter(b => dppPlans.exists(_.sameResult(b)))
+ } else {
+ Nil
+ }
+ val inSubquery = plan.isInstanceOf[Subquery]
+ def isKept(dpp: DynamicPruningSubquery): Boolean =
+ !dpp.onlyInBroadcast || inSubquery ||
+ reusablePlans(dpp).exists(p => reused.exists(_.sameResult(p)))
+ // Pushdown rewrites the key of the bloom filter through the aliases on
its way down. Where the
+ // key cannot be followed, pushdown can still move a copy of the bloom
filter, so the check
+ // fails there.
+ def canPushDown(key: Expression, side: LogicalPlan): Boolean = {
+ val lineages = findExpressionAndTrackAllLineagesDown(key, side, strict =
true)
+ lineages.nonEmpty && lineages.forall { case (leafKey, leaf) =>
+ cannotFail(leafKey) && !reused.exists(_.exists(_.fastEquals(leaf)))
+ }
+ }
plan transformUp {
case join @ ExtractEquiJoinKeys(joinType, leftKeys, rightKeys, _, _,
left, right, hint) =>
var newLeft = left
var newRight = right
leftKeys.lazyZip(rightKeys).foreach((l, r) => {
+ lazy val keyDpps = dynamicPruningSubqueries(left, l) ++
dynamicPruningSubqueries(right, r)
// Check if:
- // 1. There is already a DPP filter on the key
- // 2. The keys are simple cheap expressions
+ // 1. The keys are simple cheap expressions
+ // 2. There is no DPP filter on the key that physical planning may
keep
if (filterCounter < numFilterThreshold &&
- !hasDynamicPruningSubquery(left, right, l, r) &&
- isSimpleExpression(l) && isSimpleExpression(r)) {
+ isSimpleExpression(l) && isSimpleExpression(r) &&
!keyDpps.exists(isKept)) {
val oldLeft = newLeft
val oldRight = newRight
+ // The creation side for a bloom filter on `key` of `side`. It
leaves out the key's own
+ // DPP filters, which physical planning drops. An expression such
as a UDF or a `LIKE`
+ // in their build plan would also stop the search for the creation
side, since
+ // `isSimpleExpression` sees the plan of a subquery too. If the
key has DPP filters,
+ // they are all dropped, so step 3 above applies. The bloom filter
evaluates its
+ // creation side again, apart from the join. Its subquery can also
read more of that
+ // side than the join does, since filters on other keys, DPP
filters or bloom filters,
+ // can narrow only the join's side. And the bloom filter hashes
the bytes of the key, so
+ // its type must compare by its bytes, which a collated string
does not.
+ def creationSide(
+ side: LogicalPlan,
+ key: Expression,
+ other: LogicalPlan,
+ otherKey: Expression): Option[FilterCreationSide] =
+ extractBeneficialFilterCreatePlan(
+ side, withoutDppFilters(other, keyDpps.contains), key,
otherKey).filter { c =>
+ keyDpps.isEmpty ||
(UnsafeRowUtils.isBinaryStable(key.dataType) &&
+ canPushDown(key, side) && canEvaluateAgain(c))
+ }
// Check if:
// 1. The current join type supports prune the left side with
runtime filter
// 2. The current join is a shuffle join or a broadcast join that
@@ -438,9 +581,7 @@ object InjectRuntimeFilter extends Rule[LogicalPlan] with
PredicateHelper with J
val hasShuffle = isProbablyShuffleJoin(left, right, hint)
if (canPruneLeft(joinType) && (hasShuffle ||
probablyHasShuffle(left)) &&
!hasBloomFilter(newLeft, l)) {
- extractBeneficialFilterCreatePlan(left, right, l, r).foreach {
creationSide =>
- newLeft = injectFilter(l, newLeft, creationSide)
- }
+ creationSide(left, l, right, r).foreach(c => newLeft =
injectFilter(l, newLeft, c))
}
// Did we actually inject on the left? If not, try on the right
// Check if:
@@ -450,9 +591,7 @@ object InjectRuntimeFilter extends Rule[LogicalPlan] with
PredicateHelper with J
// 3. There is no bloom filter on the right key yet
if (newLeft.fastEquals(oldLeft) && canPruneRight(joinType) &&
(hasShuffle || probablyHasShuffle(right)) &&
!hasBloomFilter(newRight, r)) {
- extractBeneficialFilterCreatePlan(right, left, r, l).foreach {
creationSide =>
- newRight = injectFilter(r, newRight, creationSide)
- }
+ creationSide(right, r, left, l).foreach(c => newRight =
injectFilter(r, newRight, c))
}
if (!newLeft.fastEquals(oldLeft) ||
!newRight.fastEquals(oldRight)) {
filterCounter = filterCounter + 1
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
index 669868bdc482..45b27a4a78da 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/joins.scala
@@ -504,9 +504,11 @@ trait JoinSelectionHelper extends Logging {
// supports cannot honor that hint either, so it does not veto here; the
sizes then still
// produce an answer, which over-approximates `JoinSelection` (it falls
through to a sort
// merge join). That over-approximation predates this method and is kept
deliberately, so
- // that `canPlanAsBroadcastHashJoin` keeps its truth table.
- val noShufflePlannedBefore = !hashJoinSupported(leftKeys, rightKeys) ||
- getShuffleHashJoinBuildSide(join, hintOnly = true, conf).isEmpty
+ // that `canPlanAsBroadcastHashJoin` keeps its truth table.
`hashJoinSupported` comes last,
+ // as it logs a warning for keys that no hash join supports.
+ val noShufflePlannedBefore =
+ getShuffleHashJoinBuildSide(join, hintOnly = true, conf).isEmpty ||
+ !hashJoinSupported(leftKeys, rightKeys)
getBroadcastBuildSide(join, hintOnly = true, conf).orElse {
if (noShufflePlannedBefore) getBroadcastBuildSide(join, hintOnly =
false, conf) else None
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/InjectRuntimeFilterSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/InjectRuntimeFilterSuite.scala
index 62b1e42cc565..07c6e66d8c6c 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/InjectRuntimeFilterSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/InjectRuntimeFilterSuite.scala
@@ -19,13 +19,16 @@ package org.apache.spark.sql
import java.io.File
-import org.apache.spark.sql.catalyst.expressions.{Alias,
BloomFilterMightContain, Literal, ScalarSubquery}
+import org.apache.spark.sql.catalyst.expressions.{Alias,
BloomFilterMightContain, DynamicPruningExpression, DynamicPruningSubquery,
Expression, Literal, ScalarSubquery, XxHash64}
import
org.apache.spark.sql.catalyst.expressions.aggregate.{AggregateExpression,
BloomFilterAggregate}
import org.apache.spark.sql.catalyst.plans.logical.{Aggregate, Filter,
LogicalPlan}
import org.apache.spark.sql.columnar.CachedBatch
-import org.apache.spark.sql.execution.{ReusedSubqueryExec, SubqueryExec}
+import org.apache.spark.sql.connector.StorageFilterTestSource
+import org.apache.spark.sql.connector.catalog.InMemoryTableCatalog
+import org.apache.spark.sql.execution.{FileSourceScanExec, InSubqueryExec,
ProjectedBroadcastValueSubqueryExec, ReusedSubqueryExec, SubqueryBroadcastExec,
SubqueryExec}
import org.apache.spark.sql.execution.adaptive.{AdaptiveSparkPlanHelper,
AQEPropagateEmptyRelation}
import org.apache.spark.sql.execution.columnar.InMemoryRelation
+import org.apache.spark.sql.execution.datasources.LogicalRelationWithTable
import org.apache.spark.sql.execution.planmerging.MergeSubplans
import org.apache.spark.sql.internal.SQLConf
import org.apache.spark.sql.test.SharedSparkSession
@@ -302,6 +305,68 @@ class InjectRuntimeFilterSuite extends SharedSparkSession
checkWithAndWithoutFeatureEnabled(query, shouldReplace = false)
}
+ // Runs `f` with the thresholds of the DPP tests, `reuseBroadcastOnly` on,
and `confs`, with AQE
+ // on and off unless `confs` sets it.
+ def withDppTestConf(confs: (String, String)*)(f: => Unit): Unit = {
+ val adaptiveModes = confs.collectFirst {
+ case (SQLConf.ADAPTIVE_EXECUTION_ENABLED.key, adaptive) => Seq(adaptive)
+ }.getOrElse(Seq("true", "false"))
+ adaptiveModes.foreach { adaptive =>
+ withSQLConf(Seq(
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "3000",
+ SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "2000",
+ SQLConf.DYNAMIC_PARTITION_PRUNING_REUSE_BROADCAST_ONLY.key -> "true",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> adaptive) ++ confs: _*) {
+ withClue(s"adaptive=$adaptive: ")(f)
+ }
+ }
+ }
+
+ // Returns the DPP filters that physical planning left on the file source
scans of `query`: the
+ // class of the subquery that each filter reads, or the filter itself if it
reads none. Only AQE
+ // plans them as the query runs, so only then does it run the query.
+ def dynamicPruningFilters(query: String): Seq[Any] = {
+ // Keep the scans in the final plan even if they return no rows.
+ withSQLConf(
+ SQLConf.ADAPTIVE_OPTIMIZER_EXCLUDED_RULES.key ->
AQEPropagateEmptyRelation.ruleName) {
+ val df = sql(query)
+ if (conf.adaptiveExecutionEnabled) df.collect()
+ collect(df.queryExecution.executedPlan) {
+ case scan: FileSourceScanExec => scan.partitionFilters.collect {
+ case DynamicPruningExpression(in: InSubqueryExec) => in.plan.getClass
+ case DynamicPruningExpression(filter) => filter
+ }
+ }.flatten
+ }
+ }
+
+ // Asserts that `query` gets a bloom filter, and that physical planning
plans its DPP filter as
+ // `true`.
+ def assertRewroteWithBloomFilterForDroppedDpp(query: String): Unit = {
+ assertRewroteWithBloomFilter(query)
+ assert(dynamicPruningFilters(query) == Seq(Literal.TrueLiteral))
+ }
+
+ // The keys that the bloom filters of `query` check.
+ def bloomFilterKeys(query: String): Seq[Expression] =
+ sql(query).queryExecution.optimizedPlan.flatMap(_.expressions)
+ .flatMap(_.collect { case BloomFilterMightContain(_, XxHash64(Seq(key),
_)) => key })
+
+ // The names of the columns that the bloom filters of `query` check.
+ def bloomFilterKeyNames(query: String): Seq[String] =
+ bloomFilterKeys(query).flatMap(_.references.map(_.name))
+
+ // Asserts that `query` gets `numBloomFilters` bloom filters without DPP, so
that a test that sees
+ // fewer with DPP shows that the rule kept them out. In the tests for a key
or a creation side
+ // that is not safe, that is master's bloom filter for a key without a DPP
filter, which can fail
+ // the query or drop rows there: SPARK-58486 (a collated key), SPARK-60808
(a creation side that
+ // can give other rows again), SPARK-60809 (a key that can fail) and
SPARK-60842 (a creation side
+ // that can fail on rows that only the join's side drops).
+ def assertBloomFilterWithoutDpp(query: String, numBloomFilters: Int = 1):
Unit =
+ withSQLConf(SQLConf.DYNAMIC_PARTITION_PRUNING_ENABLED.key -> "false") {
+ assert(bloomFilterKeys(query).size == numBloomFilters)
+ }
+
test("SPARK-58272: safely use fully materialized selectively filtered
caches") {
val cacheName = "cached_bloom_filter_keys"
val query = s"SELECT * FROM bf1 JOIN $cacheName ON bf1.c1 = $cacheName.c2"
@@ -1034,19 +1099,709 @@ class InjectRuntimeFilterSuite extends
SharedSparkSession
test("Runtime bloom filter join: do not add bloom filter if dpp filter
exists " +
"on the same column") {
-
withSQLConf(SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key
-> "3000",
- SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "2000") {
- assertDidNotRewriteWithBloomFilter("select * from bf5part join bf2 on " +
- "bf5part.f5 = bf2.c2 where bf2.a2 = 62")
+ withDppTestConf(SQLConf.DYNAMIC_PARTITION_PRUNING_REUSE_BROADCAST_ONLY.key
-> "false") {
+ val query = "select * from bf5part join bf2 on bf5part.f5 = bf2.c2 where
bf2.a2 = 62"
+ assert(bloomFilterKeys(query).isEmpty)
+ assert(dynamicPruningFilters(query) == Seq(classOf[SubqueryExec]))
}
}
test("Runtime bloom filter join: add bloom filter if dpp filter exists on " +
"a different column") {
-
withSQLConf(SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key
-> "3000",
- SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "2000") {
- assertRewroteWithBloomFilter("select * from bf5part join bf2 on " +
- "bf5part.c5 = bf2.c2 and bf5part.f5 = bf2.f2 where bf2.a2 = 62")
+ withDppTestConf(SQLConf.DYNAMIC_PARTITION_PRUNING_REUSE_BROADCAST_ONLY.key
-> "false") {
+ val query = "select * from bf5part join bf2 on " +
+ "bf5part.c5 = bf2.c2 and bf5part.f5 = bf2.f2 where bf2.a2 = 62"
+ assertRewroteWithBloomFilter(query)
+ assert(dynamicPruningFilters(query) == Seq(classOf[SubqueryExec]))
+ }
+ }
+
+ test("SPARK-60702: add bloom filter if the dpp filter needs a broadcast that
the shuffle " +
+ "join lacks") {
+ // `bf2.a2 = 36` keeps a row of `bf2` that joins, so the bloom filter is
not empty when it meets
+ // the partition values of `bf5part`.
+ Seq(
+ "select * from bf5part join bf2 on bf5part.f5 = bf2.c2 where bf2.a2 =
36",
+ "select * from bf2 join bf5part on bf2.c2 = bf5part.f5 where bf2.a2 =
36").foreach { query =>
+ withDppTestConf() {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ assert(sql(query).collect().nonEmpty)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if the dpp filter reuses the
broadcast of " +
+ "the join") {
+ // The aggregate shuffles below the broadcast join, so the join may get a
bloom filter. The
+ // join broadcasts `bf2` by a hint, with the partitioned table on either
side, or by its size.
+ // Without DPP, or without exchange reuse to give the DPP filter the
broadcast, the join gets
+ // the bloom filter.
+ Seq(
+ "select /*+ broadcast(bf2) */ * from (select f5, sum(a5) from bf5part
group by f5) t " +
+ "join bf2 on t.f5 = bf2.c2 where bf2.a2 = 62" -> "2000",
+ "select /*+ broadcast(bf2) */ * from bf2 join (select f5, sum(a5) from
bf5part " +
+ "group by f5) t on bf2.c2 = t.f5 where bf2.a2 = 62" -> "2000",
+ "select * from (select f5, sum(a5) from bf5part group by f5) t " +
+ "join bf2 on t.f5 = bf2.c2 where bf2.a2 = 62" -> "5000"
+ ).foreach { case (query, threshold) =>
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> threshold) {
+ assertBloomFilterWithoutDpp(query)
+ withSQLConf(SQLConf.EXCHANGE_REUSE_ENABLED.key -> "false") {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ assert(bloomFilterKeys(query).isEmpty)
+ assert(dynamicPruningFilters(query) ==
Seq(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if another join broadcasts the
build plan of " +
+ "the dpp filter") {
+ // The first join is a shuffle join, and its DPP filter reuses the
broadcast of the second.
+ val query = "select p.f5, p.a5, d.* from bf5part p join bf2 d on p.f5 =
d.c2 " +
+ "where d.a2 = 62 union all select /*+ broadcast(d) */ t.f5, t.s, d.*
from " +
+ "(select f5, sum(a5) s from bf5part group by f5) t join bf2 d on t.f5 =
d.c2 " +
+ "where d.a2 = 62"
+ withDppTestConf() {
+ // Without DPP, both `p.f5` and `t.f5` get a bloom filter.
+ assertBloomFilterWithoutDpp(query, 2)
+ assert(bloomFilterKeys(query).isEmpty)
+ // One of the DPP filters can read the broadcast through a reused
subquery.
+ val reuses = Set[Any](classOf[SubqueryBroadcastExec],
classOf[ReusedSubqueryExec])
+ val filters = dynamicPruningFilters(query)
+ assert(filters.size == 2 && filters.forall(reuses.contains), filters)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter that takes a broadcast from a dpp
filter") {
+ // The DPP filter on `q.f5` cannot reuse a broadcast, so it does not keep
the bloom filter off
+ // its key. That bloom filter would go into `r`, the broadcast that the
DPP filter on `p.f5`
+ // reuses. `q` is on either side of its join.
+ Seq(
+ "select /*+ broadcast(r) */ * from bf5part p join (select q.f5 rf, q.a5
ra from " +
+ "bf5filtered q join bf2 on q.f5 = bf2.c2 where bf2.a2 = 62) r on p.f5
= r.rf",
+ "select /*+ broadcast(r) */ * from bf5part p join (select q.f5 rf, q.a5
ra from " +
+ "bf2 join bf5filtered q on bf2.c2 = q.f5 where bf2.a2 = 62) r on p.f5
= r.rf"
+ ).foreach { query =>
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "500",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+
assert(dynamicPruningFilters(query).contains(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter that takes a broadcast from a dpp
filter in " +
+ "another union branch") {
+ // The DPP filter on `u.k` cannot reuse a broadcast, so it does not keep
the bloom filter off
+ // its key. Pushdown would move that bloom filter into both branches, and
so into `d`, the
+ // broadcast that the DPP filter on `q.f5` reuses. Over the cache of `d`,
cleanup drops the
+ // copy of the DPP filter on `u.k`, but not the bloom filter.
+ val query = "select * from (select p.f5 k, p.a5 v from bf5part p union all
select " +
+ "/*+ broadcast(d) */ d.c2 k, q.a5 v from bf5part q join bf2c d on q.f5 =
d.c2 " +
+ "where d.a2 = 62) u join bf3 on u.k = bf3.c3 where bf3.a3 = 62"
+ withTempView("bf2c") {
+ withCache("bf2c") {
+ spark.table("bf2").createOrReplaceTempView("bf2c")
+ spark.catalog.cacheTable("bf2c")
+ withDppTestConf(SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ // Without DPP, pushdown puts a bloom filter into each branch.
+ assertBloomFilterWithoutDpp(query, 2)
+ assert(bloomFilterKeys(query).isEmpty)
+
assert(dynamicPruningFilters(query).contains(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+ }
+
+ test("SPARK-60702: keep the bloom filter off a key whose dpp filter reuses a
broadcast side " +
+ "that has a dpp filter of its own") {
+ // The DPP filter on `q.f5` sits on top of `q` when the rule runs, and
cleanup drops it later
+ // because of `q.f5 = 51`. Then the DPP filter on `t.f5` reuses the
broadcast of `q`, so `t.f5`
+ // gets no bloom filter. `t.c5` gets one, since the DPP filter on its key
is gone. `q` is on
+ // either side of the join.
+ Seq(
+ "select /*+ broadcast(q) */ * from (select f5, c5, sum(a5) s from
bf5part " +
+ "where b5 > 0 group by f5, c5) t join bf5filtered q on t.f5 = q.c5 and
t.c5 = q.f5 " +
+ "where q.a5 > 50 and q.f5 = 51",
+ "select /*+ broadcast(q) */ * from bf5filtered q join (select f5, c5,
sum(a5) s from " +
+ "bf5part where b5 > 0 group by f5, c5) t on q.c5 = t.f5 and q.f5 =
t.c5 " +
+ "where q.a5 > 50 and q.f5 = 51"
+ ).foreach { query =>
+ withDppTestConf() {
+ assertRewroteWithBloomFilter(query)
+ assert(dynamicPruningFilters(query) ==
Seq(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter that takes a broadcast from a dpp
filter that is " +
+ "not only in broadcast") {
+ // With `reuseBroadcastOnly` off, the DPP filter on `p.f5` has a benefit,
so it can run as its
+ // own subquery, but physical planning reuses the broadcast of `r` first.
The DPP filter on
+ // `q.f5` has none, so it is only in broadcast and cannot reuse a
broadcast. A bloom filter on
+ // `q.f5` would go into `r`.
+ withTable("bf5big") {
+ sql("select a5 + id * 100 as a5, b5, c5, d5, e5, f5 from bf5part cross
join range(400)")
+ .write.partitionBy("f5").saveAsTable("bf5big")
+ val query = "select /*+ broadcast(r) */ * from bf5big p join (select
q.f5 rf, q.a5 ra " +
+ "from bf5filtered q join bf5part d on q.f5 = d.c5 where d.a5 = 62) r
on p.f5 = r.rf"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "500",
+ SQLConf.DYNAMIC_PARTITION_PRUNING_REUSE_BROADCAST_ONLY.key ->
"false",
+ SQLConf.DYNAMIC_PARTITION_PRUNING_FALLBACK_FILTER_RATIO.key -> "1.0",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+
assert(dynamicPruningFilters(query).contains(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+
+ test("SPARK-60702: keep the bloom filter of a key without a dpp filter") {
+ // Pushdown moves the bloom filter on `s.k` into `x`, the broadcast that
the DPP filter on
+ // `p.f5` reuses. The key has no DPP filter, so the rule adds the bloom
filter all the same.
+ val query = "select * from (select x.c2 k, count(*) from bf5part p join
(select c2 from bf2 " +
+ "where a2 = 62) x on p.f5 = x.c2 group by x.c2) s join bf3 on s.k =
bf3.c3 " +
+ "where bf3.a3 = 62"
+ withDppTestConf() {
+ assertRewroteWithBloomFilter(query)
+ }
+ }
+
+ test("SPARK-60702: keep the dropped dpp filter of the key out of the bloom
filter's creation " +
+ "side") {
+ // `q` is too small to take the bloom filter, so `b` takes it, and `q` is
its creation side.
+ // The DPP filter on `q.f5` stays out of the bloom filter's subquery. `q`
is on either side of
+ // the join.
+ withTable("bigapp") {
+ sql("select a5 + id * 100 as k, b5 from bf5part cross join range(400)")
+ .write.saveAsTable("bigapp")
+ Seq(
+ "select * from bf5filtered q join bigapp b on q.f5 = b.k where q.a5 >
50 and b.b5 = 1",
+ "select * from bigapp b join bf5filtered q on b.k = q.f5 where q.a5 >
50 and b.b5 = 1"
+ ).foreach { query =>
+ withDppTestConf(
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key ->
"20000") {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ val bloomFilterPlans =
sql(query).queryExecution.optimizedPlan.collect {
+ case Filter(condition, _) => condition.collect {
+ case subquery: ScalarSubquery => subquery.plan
+ }
+ }.flatten
+ assert(bloomFilterPlans.size == 1)
+ val creationSide = bloomFilterPlans.head
+ assert(creationSide.collectLeaves().exists {
+ case LogicalRelationWithTable(_, Some(table)) =>
+ table.identifier.table == "bf5filtered"
+ case _ => false
+ }, creationSide)
+ assert(!creationSide.exists {
+ case Filter(_: DynamicPruningSubquery, _) => true
+ case _ => false
+ }, creationSide)
+ }
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if physical planning keeps the
dpp filter over the " +
+ "build plan of a null-aware anti join") {
+ // The join is a shuffle join. The `not in` subquery broadcasts the build
plan of its DPP
+ // filter. Without AQE, physical planning keeps the filter, though it
builds its own
+ // broadcast. AQE plans the filter as `true`, so there the key gets the
bloom filter.
+ val query = "select * from bf5part p join (select c2 from bf2 where a2 =
62 and c2 is not " +
+ "null) d on p.f5 = d.c2 where p.a5 not in (select c2 from bf2 where a2 =
62 and c2 is not " +
+ "null)"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "500",
+ SQLConf.NULL_AWARE_ANTI_JOIN_BROADCAST_THRESHOLD.key -> "-1") {
+ assertBloomFilterWithoutDpp(query)
+ if (conf.adaptiveExecutionEnabled) {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ } else {
+ assert(bloomFilterKeys(query).isEmpty)
+ assert(dynamicPruningFilters(query) ==
Seq(classOf[SubqueryBroadcastExec]))
+ }
+ }
+ }
+
+ test("SPARK-60702: add bloom filter if the join type rules out the broadcast
the dpp filter " +
+ "needs") {
+ // `bf2` is small enough to broadcast, but a right outer join cannot
broadcast its right side,
+ // so the DPP filter does not count. The aggregate shuffles below the
join, so the join can get
+ // the bloom filter.
+ val query = "select * from (select f5, sum(a5) s from bf5part group by f5)
t " +
+ "right join bf2 on t.f5 = bf2.c2 where bf2.a2 = 62"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "5000") {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+
+ test("SPARK-60702: add bloom filter if a shuffle hash hint rules out the
broadcast the dpp " +
+ "filter needs") {
+ // `bf2` is small enough to broadcast, but the hint asks for a shuffle
hash join. The aggregate
+ // shuffles below the join, so the join can get the bloom filter.
+ val query = "select /*+ shuffle_hash(bf2) */ * from (select f5, sum(a5) s
from bf5part " +
+ "group by f5) t join bf2 on t.f5 = bf2.c2 where bf2.a2 = 62"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "5000") {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if the creation side that the
subquery reads is not " +
+ "deterministic") {
+ // The build plan of the DPP filter would be the creation side of the
bloom filter. A bloom
+ // filter would evaluate it again, apart from the join, and could drop
rows (SPARK-60808).
+ // Cleanup drops such a DPP filter.
+ val query = "select * from bf5part join (select * from bf2 where a2 = 36
and " +
+ "cast(reflect('java.lang.Math', 'random') as double) < 2.0) d on
bf5part.f5 = d.c2"
+ withDppTestConf() {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ assert(dynamicPruningFilters(query).isEmpty)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation side is a
non-deterministic pruned " +
+ "side") {
+ // Nothing broadcasts `f`, the deterministic build plan of the DPP filter
on `d.k`, so that
+ // filter is dropped. `d` is too small to take a bloom filter, so `f`
would take one, built
+ // from `d`, which is not deterministic (SPARK-60808).
+ withTable("ndd", "ndf") {
+ spark.range(40).selectExpr("id as k", "id as
w").write.partitionBy("k").saveAsTable("ndd")
+ spark.range(40000).selectExpr("id % 40 as k", "id as
v").write.saveAsTable("ndf")
+ sql("analyze table ndd compute statistics")
+ sql("analyze table ndf compute statistics")
+ val nddSize =
spark.table("ndd").queryExecution.optimizedPlan.stats.sizeInBytes
+ val query = "select count(*) from (select k from ndd " +
+ "where cast(reflect('java.lang.Math', 'random') as double) < 0.5) d " +
+ "join ndf f on d.k = f.k where f.v >= 0"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key ->
+ (nddSize + 1).toString,
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if the key of the dropped dpp
filter can fail") {
+ // On a partition column, the bloom filter would become a partition
filter, which evaluates
+ // the key on every partition before the data filters (SPARK-60809). An
alias hides the key only
+ // at the join, since pushdown rewrites the bloom filter through it. And
`coalesce` cannot be
+ // null, so no inferred `isnotnull` evaluates the key on every partition
first.
+ // Through a union, every branch counts, and without DPP each gets a bloom
filter. A narrowing
+ // cast can fail too, also over an up-cast or under the normalization of a
floating point key.
+ Seq(
+ "select * from bf5part join bf2 on bf5part.f5 + 1 = bf2.c2 where bf2.a2
= 36" -> 1,
+ "select * from (select coalesce(f5 + 1, 0) as k, a5 from bf5part) p " +
+ "join bf2 on p.k = bf2.c2 where bf2.a2 = 36" -> 1,
+ "select * from (select f5 k, a5 v from bf5part union all " +
+ "select coalesce(f5 + 1, 0) k, a5 v from bf5filtered) u " +
+ "join bf2 on u.k = bf2.c2 where bf2.a2 = 36" -> 2,
+ "select * from bf5part join bf2 on cast(bf5part.f5 as tinyint) = bf2.c2
" +
+ "where bf2.a2 = 36" -> 1,
+ "select * from bf5part join bf2 on cast(cast(bf5part.f5 as float) as
int) = bf2.c2 " +
+ "where bf2.a2 = 36" -> 1,
+ "select * from bf5part join (select cast(c2 as double) k, a2 from bf2) d
" +
+ "on cast(bf5part.f5 as tinyint) = d.k where d.a2 = 36" -> 1
+ ).foreach { case (query, numBloomFilters) =>
+ withDppTestConf() {
+ assertBloomFilterWithoutDpp(query, numBloomFilters)
+ assert(bloomFilterKeys(query).isEmpty)
+ val filters = dynamicPruningFilters(query)
+ assert(filters.nonEmpty && filters.forall(_ == Literal.TrueLiteral),
filters)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter in an in subquery whose dpp
filter reuses a " +
+ "broadcast outside it") {
+ // The optimizer runs on the subquery on its own, where the broadcast of
`x` is not visible.
+ // The DPP filter on `p.f5` reuses it once the subquery is joined with the
rest of the query.
+ val query = "select /*+ broadcast(x) */ * from bf3 join (select c2 from
bf2 where a2 = 62) x " +
+ "on bf3.c3 = x.c2 where bf3.a3 in (select p.a5 from bf5part p join
(select c2 from bf2 " +
+ "where a2 = 62) d on p.f5 = d.c2)"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "500") {
+ assert(!bloomFilterKeyNames(query).contains("f5"))
+ assert(dynamicPruningFilters(query) ==
Seq(classOf[SubqueryBroadcastExec]))
+ // Without the hint, nothing broadcasts `x`, and `p.f5` gets the bloom
filter.
+ val withoutHint = query.replace("/*+ broadcast(x) */ ", "")
+ assert(bloomFilterKeyNames(withoutHint).contains("f5"))
+ assert(dynamicPruningFilters(withoutHint) == Seq(Literal.TrueLiteral))
+ if (!conf.adaptiveExecutionEnabled) sql(withoutHint).collect()
+ }
+ }
+
+ test("SPARK-60702: add bloom filter for a key with a dropped dpp filter
under a numeric " +
+ "up-cast") {
+ // Type coercion casts `f5` to `bigint`, which cannot fail.
+ val query = "select * from bf5part join (select cast(c2 as bigint) k, a2
from bf2) d " +
+ "on bf5part.f5 = d.k where d.a2 = 36"
+ withDppTestConf() {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+
+ test("SPARK-60702: add bloom filter for a key with a dropped dpp filter of a
floating point " +
+ "type") {
+ // `NormalizeFloatingNumbers` wraps the `double` join keys, which cannot
fail, also over a
+ // `coalesce`.
+ Seq("bf5part.f5", "coalesce(cast(bf5part.f5 as double), 0D)").foreach {
key =>
+ val query = "select * from bf5part join (select cast(c2 as double) k, a2
from bf2) d " +
+ s"on $key = d.k where d.a2 = 36"
+ withDppTestConf() {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+ }
+
+ test("SPARK-60702: add bloom filter whose creation filter has a coalesce,
which cannot fail") {
+ val query = "select * from bf5part join bf2 on bf5part.f5 = bf2.c2 " +
+ "where coalesce(bf2.a2, 0) = 36"
+ withDppTestConf() {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+
+ test("SPARK-60702: check each cast in the creation side against its own
child") {
+ // Type coercion puts an up-cast into the `coalesce`. The first query
casts the `coalesce` down,
+ // which can fail, and the second casts it up. Neither may fail to
optimize.
+ val narrow = "select * from bf5part join bf2 on bf5part.f5 = bf2.c2 " +
+ "where cast(coalesce(bf2.a2, 0L) as int) = 36"
+ val wide = "select * from bf5part join bf2 on bf5part.f5 = bf2.c2 " +
+ "where coalesce(bf2.a2, 0L) > 35.5D"
+ // A deeply nested filter takes a time linear in its size.
+ val nested = (1 to 28).foldLeft("b2 + 1") { (acc, k) =>
+ s"if(d2 > $k, $acc, cast(b2 as decimal(${10 + k}, 0)))"
+ }
+ withDppTestConf() {
+ assertBloomFilterWithoutDpp(narrow)
+ assert(bloomFilterKeys(narrow).isEmpty)
+ assertRewroteWithBloomFilterForDroppedDpp(wide)
+ assert(bloomFilterKeys("select * from bf5part join bf2 on bf5part.f5 =
bf2.c2 " +
+ s"where bf2.a2 = 36 and $nested > 0").isEmpty)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation side reads a scan
with a pushed " +
+ "top-n") {
+ // The source picks the rows of a pushed top-N, so a second read can pick
other rows. So `p.f5`
+ // gets no bloom filter. `x.k` gets one, from `bf5part`, which the
inferred `f5 > 1` filters.
+ withTempView("tn") {
+
spark.read.format(classOf[StorageFilterTestSource].getName).option("push",
"topN").load()
+ .createOrReplaceTempView("tn")
+ val query = "select * from bf5part p join (select * from (select * from
tn order by v " +
+ "limit 100) where k > 1) x on p.f5 = x.k"
+ withDppTestConf(SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ withSQLConf(SQLConf.DYNAMIC_PARTITION_PRUNING_ENABLED.key -> "false") {
+ assert(bloomFilterKeyNames(query) == Seq("f5"))
+ }
+ assert(bloomFilterKeyNames(query) == Seq("k"))
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation side reads a file
metadata column") {
+ // `_metadata.file_block_start` depends on how the scan splits its files,
which the bloom
+ // filter's subquery can do differently from the join.
+ val query = "select * from bf5part p join bf2 d on p.f5 = d.c2 " +
+ "where d.a2 = 36 and d._metadata.file_block_start = 0"
+ withDppTestConf(SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter for a key with a dropped dpp
filter under a date to " +
+ "timestamp cast") {
+ // `Cast.canUpCast` takes a date to a timestamp, which can overflow past
the year 294247.
+ withTable("fdt", "tsd") {
+ sql("create table fdt (v int, d date) using parquet partitioned by (d)")
+ sql("insert into fdt select cast(id as int), date'2020-01-01' from
range(100)")
+ sql("create table tsd (ts timestamp, flag int) using parquet")
+ sql("insert into tsd values (timestamp'2020-01-01 00:00:00', 1), " +
+ "(timestamp'2021-01-01 00:00:00', 0)")
+ val query = "select * from fdt join tsd on fdt.d = tsd.ts where tsd.flag
= 1"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter for a key with a dropped dpp
filter under a " +
+ "decimal to bigint cast") {
+ // `9999999999999999999` overflows `BIGINT`, so the cast is no up-cast
(SPARK-60943). The
+ // query casts it only on rows with `v > 0`, which that partition does not
have.
+ withTable("fd") {
+ sql("create table fd (v int, d decimal(19,0)) using parquet partitioned
by (d)")
+ sql("insert into fd select cast(id as int) + 1, 36 from range(100)")
+ sql("insert into fd select -cast(id as int), cast('9999999999999999999'
as decimal(19,0)) " +
+ "from range(100)")
+ val query = "select * from fd join bf2 on cast(fd.d as bigint) = bf2.c2
" +
+ "where fd.v > 0 and bf2.a2 = 36"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ sql(query).collect()
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose key can fail in a union
branch the lineage " +
+ "does not reach") {
+ // The lineage of `u.k` stops at the inner union of the second branch, but
pushdown moves the
+ // bloom filter into both inner branches, where it would cast `p` on every
partition. Each
+ // row of `p = 'abc'` has `v <= 0`, so the query itself never casts
`'abc'`.
+ withTable("sp") {
+ sql("create table sp (v int, p string) using parquet partitioned by (p)")
+ sql("insert into sp select cast(id as int) + 1, '36' from range(100)")
+ sql("insert into sp select -cast(id as int), 'abc' from range(100)")
+ val query = "select * from (select f5 as k, a5 as v from bf5part union
all " +
+ "select coalesce(cast(x as int), 0) as k, sum(v) as v from (select p
as x, v from sp " +
+ "where v > 0 union all select p, v from sp where v > 0) group by x) u
" +
+ "join bf2 on u.k = bf2.c2 where bf2.a2 = 36"
+ withDppTestConf() {
+ // Without DPP, pushdown puts a bloom filter into each of the three
branches.
+ assertBloomFilterWithoutDpp(query, 3)
+ assert(bloomFilterKeys(query).isEmpty)
+ sql(query).collect()
+ }
+ }
+ }
+
+ test("SPARK-60702: add bloom filter whose creation side has a
non-deterministic column that " +
+ "the bloom filter does not read") {
+ // The bloom filter's subquery reads only `c2` of `d`, so `src` does not
change its keys. The
+ // DPP filter is not deterministic, so cleanup removes it.
+ val query = "select * from bf5part p join (select c2, input_file_name()
src from bf2 " +
+ "where a2 = 36) d on p.f5 = d.c2"
+ withDppTestConf() {
+ assert(bloomFilterKeyNames(query) == Seq("f5"))
+ val expected = withSQLConf(SQLConf.RUNTIME_BLOOM_FILTER_ENABLED.key ->
"false") {
+ sql(query).collect()
+ }
+ checkAnswer(sql(query), expected)
+ }
+ }
+
+ test("SPARK-60702: add bloom filter from a cache only if spark can tell that
scanning it " +
+ "again gives the same rows") {
+ // A cache that is not materialized yet can compute a partition twice,
once for the join and
+ // once for the bloom filter's subquery. So can a cache whose plan Spark
cannot show to be
+ // repeatable. `bf2c` would give the same rows again, but Spark can tell
only once it is
+ // materialized.
+ def query(cache: String): String =
+ s"select * from bf5part p join $cache d on p.f5 = d.c2 where d.a2 = 36"
+ withTempView("bf2c", "bf2r") {
+ withCache("bf2c", "bf2r") {
+ spark.table("bf2").createOrReplaceTempView("bf2c")
+ spark.catalog.cacheTable("bf2c")
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query("bf2c"))
+ assert(bloomFilterKeys(query("bf2c")).isEmpty)
+ spark.table("bf2c").count()
+ assert(bloomFilterKeyNames(query("bf2c")) == Seq("f5"))
+ spark.table("bf2").where("cast(reflect('java.lang.Math', 'random')
as double) < 2.0")
+ .createOrReplaceTempView("bf2r")
+ spark.catalog.cacheTable("bf2r")
+ spark.table("bf2r").count()
+ assertBloomFilterWithoutDpp(query("bf2r"))
+ assert(bloomFilterKeys(query("bf2r")).isEmpty)
+ }
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter for a key with a dropped dpp
filter of a collated " +
+ "string type") {
+ // The bloom filter hashes the bytes of the key, so `'abc'` and `'ABC'`
would not match.
+ withSQLConf("spark.sql.catalog.testcat" ->
classOf[InMemoryTableCatalog].getName) {
+ withTable("testcat.cf", "testcat.cd") {
+ sql("create table testcat.cf (k string collate utf8_lcase, v int)
partitioned by (k)")
+ sql("insert into testcat.cf values ('abc', 1)")
+ sql("create table testcat.cd (k string collate utf8_lcase, flag int)")
+ sql("insert into testcat.cd values ('ABC', 1), ('zzz', 0)")
+ val query = "select v from testcat.cf where k in (select k from
testcat.cd where flag = 1)"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "0") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ checkAnswer(sql(query), Row(1))
+ }
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation side reads a cte")
{
+ // The reference to `d` hides that its plan is not deterministic. With
exchange reuse off, the
+ // bloom filter's subquery would draw a second sample of `d`.
+ withTable("cdim", "cfact") {
+ spark.range(200).selectExpr("cast(id as int) k", "cast(id % 2 as int) x")
+ .write.saveAsTable("cdim")
+ spark.range(20000).selectExpr("cast(id % 20 as int) k", "id v")
+ .write.partitionBy("k").saveAsTable("cfact")
+ sql("analyze table cdim compute statistics")
+ sql("analyze table cfact compute statistics")
+ val query = "with d as (select k, x from cdim " +
+ "where cast(reflect('java.lang.Math', 'random') as double) < 0.5) " +
+ "select count(*), count_if(t.k is null) from d left join (select k,
sum(v) s from cfact " +
+ "group by k) t on d.k = t.k where d.x = 1 union all select count(*), 0
from d"
+ withDppTestConf(SQLConf.EXCHANGE_REUSE_ENABLED.key -> "false",
+ SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+ assertBloomFilterWithoutDpp(query)
+ assert(bloomFilterKeys(query).isEmpty)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose subquery can fail on
partitions that the join " +
+ "does not read") {
+ // The bloom filter's subquery reads its creation side as it is when this
rule runs. A DPP
+ // filter on `c.q` that physical planning keeps prunes `rc` only in the
join. It reuses the
+ // broadcast of `d` in an upper join, and pushdown moves it into `rc` only
later. Or it reuses
+ // the broadcast of `g` in another branch of a union, which the subquery
does not see. So the
+ // subquery would read `q = 99` too, and cast `'abc'`, in its key or in
its filter.
+ withTable("ra", "rc", "rd", "rx") {
+ sql("create table ra (v int, x int, b int, a int) using parquet
partitioned by (a)")
+ sql("insert into ra select cast(id as int), 1, 10, 1 from range(2000)")
+ sql("insert into ra select cast(id as int), 1, 20, 2 from range(2000)")
+ sql("create table rc (s string, flag int, t int, q int) using parquet
partitioned by (q)")
+ sql("insert into rc values ('1', 1, 1, 10), ('2', 1, 2, 20), ('abc', 1,
3, 99)")
+ sql("create table rd (k int, dflag int) using parquet")
+ sql("insert into rd values (10, 1), (20, 1)")
+ sql("create table rx (k1 int, k2 int) using parquet")
+ sql("insert into rx values (1, 10), (2, 20)")
+ val confs = Seq(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+ SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key
-> "1")
+ val castInKey = "select /*+ broadcast(d) */ f.v from ra f join rc c " +
+ "on f.a = cast(c.s as int) join rd d on c.q = d.k " +
+ "where f.x = 1 and c.flag = 1 and d.dflag = 1"
+ val castInFilter = "select /*+ broadcast(d) */ f.v from ra f join rc c
on f.a = c.t " +
+ "join rd d on c.q = d.k where f.x = 1 and c.flag = 1 and cast(c.s as
int) > 0 " +
+ "and d.dflag = 1"
+ withDppTestConf(confs: _*) {
+ // Without DPP, `f.a` and `c.q` get a bloom filter.
+ assertBloomFilterWithoutDpp(castInKey, 2)
+ assert(bloomFilterKeys(castInKey).isEmpty)
+ assertBloomFilterWithoutDpp(castInFilter, 2)
+ // The bloom filter goes to `c.t` instead, from `ra`, where nothing
can fail.
+ assertRewroteWithBloomFilter(castInFilter)
+ assert(bloomFilterKeyNames(castInFilter) == Seq("t"))
+ // So it does with each of these filters, which can fail too.
+ Seq("(c.t > 5 or cast(c.s as int) > 0)", "not (cast(c.s as int) in (1,
2))",
+ "cast(c.s as int) is null", "cast(c.s as int) is not null",
+ s"cast(c.s as int) in (${(1 to 11).mkString(", ")})",
+ "cast(cast(c.s as int) as boolean)"
+ ).foreach { condition =>
+ val query = castInFilter.replace("cast(c.s as int) > 0", condition)
+ assert(bloomFilterKeyNames(query) == Seq("t"), query)
+ }
+ }
+ val query = "select f.v from ra f join rc c on f.a = cast(c.s as int)
and f.b = c.q " +
+ "where f.x = 1 and c.flag = 1 union all select /*+ broadcast(g) */ g.v
from rx " +
+ "join (select * from ra where x = 1) g on rx.k1 = g.a and rx.k2 = g.b"
+ withDppTestConf(confs :+ (SQLConf.ADAPTIVE_EXECUTION_ENABLED.key ->
"false"): _*) {
+ assertBloomFilterWithoutDpp(query, 2)
+ assert(bloomFilterKeys(query).isEmpty)
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation key can fail on
rows that another " +
+ "bloom filter drops") {
+ // The bloom filter on `f.y` drops the row `'abc'` before the join casts
it, but only in the
+ // join. The bloom filter on `c.q` would read all of `sf` in its subquery.
+ withTable("sf", "sc") {
+ sql("create table sf (s string, x int, y int) using parquet")
+ sql("insert into sf values ('10', 1, 1), ('20', 1, 2), ('abc', 1, 999)")
+ sql("create table sc (y int, flag int, q int) using parquet partitioned
by (q)")
+ sql("insert into sc values (1, 1, 10), (2, 1, 20)")
+ val query = "select f.s, c.q from sf f join sc c on f.y = c.y and
cast(f.s as int) = c.q " +
+ "where f.x = 1 and c.flag = 1"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1") {
+ assertBloomFilterWithoutDpp(query, 2)
+ assert(bloomFilterKeyNames(query) == Seq("y"))
+ checkAnswer(sql(query), Seq(Row("10", 10), Row("20", 20)))
+ }
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter whose creation side keeps a dpp
filter that runs as " +
+ "its own subquery") {
+ // `rc2` is large and `ra2` small, so with `reuseBroadcastOnly` off the
DPP filter on `c.q` has
+ // a benefit, and the one on `f.a` does not. The first would run as its
own subquery in the
+ // bloom filter's subquery, though cleanup can drop it from the join. So
`f.a` gets no bloom
+ // filter. `c.t` gets one, from `ra2`.
+ withTable("ra2", "rc2") {
+ sql("create table ra2 (v int, x int, b int, a int) using parquet
partitioned by (a)")
+ sql("insert into ra2 select cast(id as int), 1, 10 + 10 * cast(id % 2 as
int), " +
+ "1 + cast(id % 2 as int) from range(100)")
+ sql("create table rc2 (t int, flag int, pad int, q int) using parquet
partitioned by (q)")
+ sql("insert into rc2 select 1 + cast(id % 2 as int), 1, cast(id as int),
" +
+ "10 + 10 * cast(id % 3 as int) from range(20000)")
+ sql("analyze table ra2 compute statistics")
+ sql("analyze table rc2 compute statistics")
+ val query = "select f.v from ra2 f join rc2 c on f.a = c.t and f.b = c.q
" +
+ "where f.x = 1 and c.flag = 1"
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1",
+ SQLConf.DYNAMIC_PARTITION_PRUNING_REUSE_BROADCAST_ONLY.key ->
"false") {
+ assertRewroteWithBloomFilter(query)
+ assert(bloomFilterKeyNames(query) == Seq("t"))
+ }
+ // With `reuseBroadcastOnly` on, both DPP filters are dropped. The one
on `c.q` is `true` in
+ // the bloom filter's subquery, so `f.a` gets the bloom filter, and so
does `f.b`.
+ withDppTestConf(SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "1") {
+ assertRewroteWithBloomFilter(query, 2)
+ assert(bloomFilterKeyNames(query).toSet == Set("a", "b"))
+ }
+ }
+ }
+
+ test("SPARK-60702: add bloom filter if the broadcast join builds the side
the dpp filter " +
+ "prunes") {
+ val query = "select /*+ broadcast(t) */ * from " +
+ "(select f5, sum(a5) from bf5part group by f5) t join bf2 on t.f5 =
bf2.c2 " +
+ "where bf2.a2 = 62"
+ withDppTestConf() {
+ assertRewroteWithBloomFilterForDroppedDpp(query)
+ }
+ }
+
+ test("SPARK-60702: do not add bloom filter if the dpp filter can project its
values from " +
+ "a broadcast") {
+ def query(broadcast: String): String = "select * from bf5part join " +
+ s"(select /*+ broadcast($broadcast) */ bf3.f3 from bf2 join bf3 on
bf2.c2 = bf3.c3 " +
+ "where bf3.a3 = 62) t on bf5part.f5 = t.f3"
+ val projection =
SQLConf.DYNAMIC_PARTITION_PRUNING_BROADCAST_PROJECTION_ENABLED.key -> "true"
+ withDppTestConf(
+ SQLConf.DYNAMIC_PARTITION_PRUNING_BROADCAST_PROJECTION_ENABLED.key ->
"false") {
+ assertRewroteWithBloomFilter(query("bf3"))
+ }
+ // Nothing can broadcast `bf3`, so there is no broadcast to project from.
+ withDppTestConf(projection, SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key ->
"-1") {
+ assertRewroteWithBloomFilterForDroppedDpp(query("bf2"))
+ }
+ withDppTestConf(projection, SQLConf.ADAPTIVE_EXECUTION_ENABLED.key ->
"false") {
+ assert(bloomFilterKeys(query("bf3")).isEmpty)
+ assert(dynamicPruningFilters(query("bf3")) ==
+ Seq(classOf[ProjectedBroadcastValueSubqueryExec]))
+ }
+ // AQE plans the projected filter as `true`, see SPARK-60703.
+ withDppTestConf(projection, SQLConf.ADAPTIVE_EXECUTION_ENABLED.key ->
"true") {
+ assert(bloomFilterKeys(query("bf3")).isEmpty)
+ assert(dynamicPruningFilters(query("bf3")) == Seq(Literal.TrueLiteral))
}
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
index 45491255334f..8d60c80549d5 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/connector/KeyGroupedPartitioningSuite.scala
@@ -25,7 +25,7 @@ import org.apache.spark.{SparkConf, SparkException}
import org.apache.spark.rdd.SortedMergeCoalescedRDD
import org.apache.spark.sql.{AnalysisException, DataFrame, ExplainSuiteHelper,
QueryTest, Row}
import org.apache.spark.sql.catalyst.InternalRow
-import org.apache.spark.sql.catalyst.expressions.{Ascending,
AttributeReference, Descending, DynamicPruningExpression, ExprId, Literal,
TransformExpression}
+import org.apache.spark.sql.catalyst.expressions.{Ascending,
AttributeReference, BloomFilterMightContain, Descending,
DynamicPruningExpression, ExprId, Literal, TransformExpression}
import org.apache.spark.sql.catalyst.expressions.aggregate.Complete
import org.apache.spark.sql.catalyst.plans.{Cross, ExistenceJoin, Inner,
JoinType, LeftAnti, LeftSemi, LeftSingle, RightOuter}
import org.apache.spark.sql.catalyst.plans.physical
@@ -11626,6 +11626,41 @@ class KeyGroupedPartitioningCatalystRuntimeFilterSuite
after {
catalog.clearTables()
+ catalog.clearFunctions()
+ }
+
+ test("SPARK-60702: a storage-partitioned join gets the bloom filter that its
dropped dpp " +
+ "filter used to keep out") {
+ // The scan offers the source column of `bucket(4, id)` for runtime
filtering, so
+ // `PartitionPruning` adds a DPP filter on `f.id`. Nothing can broadcast
its build plan, so
+ // physical planning plans it as `true`. The key gets a bloom filter
instead, and the join
+ // still shuffles neither side.
+ catalog.createFunction(Identifier.of(Array.empty,
UnboundBucketFunction.name()),
+ UnboundBucketFunction)
+ createTable("fact", Array(Column.create("id", LongType),
Column.create("v", IntegerType)),
+ Array(bucket(4, "id")))
+ sql("INSERT INTO testcat.ns.fact VALUES " +
+ (0 until 16).map(i => s"($i, ${i * 10})").mkString(", "))
+ createTable("dim", Array(Column.create("id", LongType),
Column.create("flag", IntegerType)),
+ Array(bucket(4, "id")))
+ sql("INSERT INTO testcat.ns.dim VALUES (1, 1), (2, 0), (3, 1), (5, 1), (6,
0), (9, 1)")
+ Seq(true, false).foreach { adaptive =>
+ withSQLConf(
+ SQLConf.AUTO_BROADCASTJOIN_THRESHOLD.key -> "-1",
+
SQLConf.RUNTIME_BLOOM_FILTER_APPLICATION_SIDE_SCAN_SIZE_THRESHOLD.key -> "0",
+ SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> adaptive.toString) {
+ val df = sql("SELECT f.id, f.v FROM testcat.ns.fact f JOIN
testcat.ns.dim d " +
+ "ON f.id = d.id WHERE d.flag = 1")
+ checkAnswer(df, Seq(Row(1, 10), Row(3, 30), Row(5, 50), Row(9, 90)))
+ val plan = df.queryExecution.executedPlan
+ val runtimeFilters = collectScans(plan)
+ .filter(_.table.name().endsWith("fact"))
+ .flatMap(_.runtimeFilters)
+
assert(runtimeFilters.exists(_.exists(_.isInstanceOf[BloomFilterMightContain])),
plan)
+
assert(runtimeFilters.contains(DynamicPruningExpression(Literal.TrueLiteral)),
plan)
+ assert(collect(plan) { case s: ShuffleExchangeLike => s }.isEmpty,
plan)
+ }
+ }
}
}
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterPushDownSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterPushDownSuite.scala
index f1a4c7661158..182c019f69fc 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterPushDownSuite.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterPushDownSuite.scala
@@ -352,10 +352,8 @@ class StorageFilterPushDownSuite
test("a scan with a column named like the checked column gets the filter,
and runtime " +
s"filtering still resolves that column (AQE $aqe)") {
// Runtime filtering resolves the declared filter attributes by name,
which the checked column
- // the scan appends would make ambiguous. Dynamic partition pruning on
`k` would take the
- // bloom's place.
- withBloomJoin(SQLConf.DYNAMIC_PARTITION_PRUNING_ENABLED.key -> "false",
- SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString) {
+ // the scan appends would make ambiguous.
+ withBloomJoin(SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString) {
val plan = runBloomJoin(
"columnar" -> "true", "valueName" -> checkedName, "runtimeFiltering"
-> "v2")
val scan = scanOf(plan)
@@ -370,9 +368,7 @@ class StorageFilterPushDownSuite
}
test(s"a bloom the scan fully evaluates through runtime filtering is not
offered (AQE $aqe)") {
- // Dynamic partition pruning on `k` would take the bloom's place.
- withBloomJoin(SQLConf.DYNAMIC_PARTITION_PRUNING_ENABLED.key -> "false",
- SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString) {
+ withBloomJoin(SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> aqe.toString) {
val plan = runBloomJoin("columnar" -> "true", "runtimeFiltering" ->
"catalyst")
val scan = scanOf(plan)
assert(scan.runtimeFilters.exists(_.exists(_.isInstanceOf[BloomFilterMightContain]))
&&
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterTestSource.scala
b/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterTestSource.scala
index d17f6a84f1c1..0804320269ea 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterTestSource.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/connector/StorageFilterTestSource.scala
@@ -126,7 +126,7 @@ object StorageFilterTestSource {
val filterReferences = mutable.Set.empty[Seq[String]]
val filterIdentities = mutable.Set.empty[Int]
- /** How many times a runtime-filtering scan was handed filters. */
+ /** How many times a runtime-filtering scan was handed a filter on a column.
*/
val runtimeFilterCalls = new AtomicInteger(0)
def reset(): Unit = {
@@ -234,7 +234,9 @@ class StorageFilterTestScanBuilder(tableSchema: StructType,
options: CaseInsensi
override def filterAttributes(): Array[NamedReference] =
readSchema().fieldNames.map(FieldReference.column).toArray[NamedReference]
override def filter(predicates: Array[Predicate]): Unit =
- StorageFilterTestSource.runtimeFilterCalls.incrementAndGet()
+ if (predicates.exists(_.references().nonEmpty)) {
+ StorageFilterTestSource.runtimeFilterCalls.incrementAndGet()
+ }
override def supportsIterativePushdown(): Boolean = true
}
case "catalyst" => new StorageFilterTestScan(schema, factory, declared,
numPartitions)
@@ -242,7 +244,9 @@ class StorageFilterTestScanBuilder(tableSchema: StructType,
options: CaseInsensi
override def filterAttributes(): Array[NamedReference] =
Array(FieldReference.column("k"))
override def fullyPushedFilterAttributes(): Array[NamedReference] =
filterAttributes()
override def filter(expressions: Array[Expression]): Unit =
- StorageFilterTestSource.runtimeFilterCalls.incrementAndGet()
+ if (expressions.exists(_.references.nonEmpty)) {
+ StorageFilterTestSource.runtimeFilterCalls.incrementAndGet()
+ }
}
case _ => new StorageFilterTestScan(schema, factory, declared,
numPartitions)
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]