You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by ur...@apache.org on 2026/09/10 13:12:39 UTC

(spark) branch master updated: [SPARK-59370][SQL] Improve the EXPLAIN output of BIN BY plan nodes

This is an automated email from the ASF dual-hosted git repository.

uros-b 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 7fbf7c191589 [SPARK-59370][SQL] Improve the EXPLAIN output of BIN BY plan nodes
7fbf7c191589 is described below

commit 7fbf7c191589f0f62dd1d498e13b698b4dce46b9
Author: Nikolina Vraneš <ni...@databricks.com>
AuthorDate: Thu Sep 10 13:12:26 2026 +0000

    [SPARK-59370][SQL] Improve the EXPLAIN output of BIN BY plan nodes
    
    ### What changes were proposed in this pull request?
    Override `stringArgs` on the `BinBy` logical node and the `BinByExec` physical node so `EXPLAIN` renders their arguments readably:
    - `BIN WIDTH` and `ALIGN TO` show as a day-time interval and a timestamp instead of raw microseconds.
    - The attribute lists are labeled: `range`, `distribute`, `scaledDistribute`, `appends`, `zone`.
    
    Rendered plan before:
    ```
    BinBy 300000000, ts_start#12: timestamp, ts_end#13: timestamp, 1704067200000000, [value#14], [value#20], [bin_start#21, bin_end#22, bin_distribute_ratio#23], UTC
    ```
    After:
    ```
    BinBy range=[ts_start#12: timestamp, ts_end#13: timestamp], binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:00:00, distribute=[value#14: double], scaledDistribute=[value#20: double], appends=[bin_start#21: timestamp, bin_end#22: timestamp, bin_distribute_ratio#23: double], zone=UTC
    ```
    
    ### Why are the changes needed?
    The default rendering prints the constructor fields positionally: the bin width and origin as raw microseconds, and the attribute lists without labels. The microsecond values are harder to read, and the `distribute` inputs share names with their `scaledDistribute` outputs, so the unlabeled lists look like duplicates.
    
    ### Does this PR introduce _any_ user-facing change?
    Yes, the `EXPLAIN` output for the BIN BY node is changed (query results are unchanged).
    However, BIN BY is a new operator, gated by `spark.sql.binByRelationOperator.enabled` (off by default), and it has not been released yet.
    
    ### How was this patch tested?
    Regenerated the `bin-by.sql` analyzer golden and confirmed the output in `SQLQueryTestSuite`.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    Generated-by: Claude Code
    
    Closes #58660 from vranes/bin-by-explain-stringargs.
    
    Authored-by: Nikolina Vraneš <ni...@databricks.com>
    Signed-off-by: Uros Bojanic <22...@users.noreply.github.com>
---
 .../plans/logical/basicLogicalOperators.scala      | 46 +++++++++++++++++++
 .../org/apache/spark/sql/execution/BinByExec.scala | 13 ++++++
 .../sql-tests/analyzer-results/bin-by.sql.out      |  6 +--
 .../scala/org/apache/spark/sql/BinBySuite.scala    | 52 ++++++++++++++++++++++
 4 files changed, 114 insertions(+), 3 deletions(-)

diff --git a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
index 1223ee6ea541..0b70b973aec6 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/plans/logical/basicLogicalOperators.scala
@@ -1876,12 +1876,58 @@ case class BinBy(
   override def producedAttributes: AttributeSet =
     AttributeSet(scaledDistributeColumns ++ appendedAttributes)
 
+  override protected def stringArgs: Iterator[Any] = {
+    BinBy.explainStringArgs(
+      rangeStart = rangeStart,
+      rangeEnd = rangeEnd,
+      binWidthMicros = binWidthMicros,
+      originMicros = originMicros,
+      distributeColumns = distributeColumns,
+      scaledDistributeColumns = scaledDistributeColumns,
+      appendedAttributes = appendedAttributes,
+      timeZoneId = timeZoneId)
+  }
+
   final override val nodePatterns: Seq[TreePattern] = Seq(BIN_BY)
 
   override protected def withNewChildInternal(newChild: LogicalPlan): BinBy =
     copy(child = newChild)
 }
 
+object BinBy {
+
+  // Builds the `stringArgs` for EXPLAIN. LTZ formats `alignTo` in the captured zone and appends
+  // `zone=`, NTZ formats in UTC and omits it.
+  private[sql] def explainStringArgs(
+      rangeStart: Attribute,
+      rangeEnd: Attribute,
+      binWidthMicros: Long,
+      originMicros: Long,
+      distributeColumns: Seq[Attribute],
+      scaledDistributeColumns: Seq[Attribute],
+      appendedAttributes: Seq[Attribute],
+      timeZoneId: Option[String]): Iterator[Any] = {
+    val maxFields = SQLConf.get.maxToStringFields
+    val fmt = TimestampFormatter.getFractionFormatter(
+      DateTimeUtils.getZoneId(timeZoneId.getOrElse("UTC")))
+
+    def refs(attrs: Seq[Attribute]): String = {
+      truncatedString(attrs.map(_.simpleString(maxFields)), "[", ", ", "]", maxFields)
+    }
+
+    Iterator(
+      s"range=[${rangeStart.simpleString(maxFields)}, ${rangeEnd.simpleString(maxFields)}]",
+      "binWidth=" + IntervalUtils.toDayTimeIntervalString(
+        binWidthMicros, IntervalStringStyles.ANSI_STYLE,
+        DayTimeIntervalType.DAY, DayTimeIntervalType.SECOND),
+      s"alignTo=${fmt.format(originMicros)}",
+      s"distribute=${refs(distributeColumns)}",
+      s"scaledDistribute=${refs(scaledDistributeColumns)}",
+      s"appends=${refs(appendedAttributes)}") ++
+      timeZoneId.map(z => s"zone=$z")
+  }
+}
+
 /**
  * A logical plan node for creating a logical limit, which is split into two separate logical nodes:
  * a [[LocalLimit]], which is a partition local limit, followed by a [[GlobalLimit]].
diff --git a/sql/core/src/main/scala/org/apache/spark/sql/execution/BinByExec.scala b/sql/core/src/main/scala/org/apache/spark/sql/execution/BinByExec.scala
index d44092ac2c9e..cc35971aa898 100644
--- a/sql/core/src/main/scala/org/apache/spark/sql/execution/BinByExec.scala
+++ b/sql/core/src/main/scala/org/apache/spark/sql/execution/BinByExec.scala
@@ -23,6 +23,7 @@ import org.apache.spark.SparkException
 import org.apache.spark.rdd.RDD
 import org.apache.spark.sql.catalyst.InternalRow
 import org.apache.spark.sql.catalyst.expressions.{Attribute, AttributeMap, AttributeSet, BindReferences, BoundReference, Cast, Expression, GenericInternalRow, JoinedRow, Literal, Multiply, NamedExpression, UnsafeProjection}
+import org.apache.spark.sql.catalyst.plans.logical.BinBy
 import org.apache.spark.sql.catalyst.util.{DateTimeUtils, TimestampFormatter}
 import org.apache.spark.sql.errors.QueryExecutionErrors
 import org.apache.spark.sql.execution.metric.{SQLMetric, SQLMetrics}
@@ -66,6 +67,18 @@ case class BinByExec(
   override def producedAttributes: AttributeSet =
     AttributeSet(scaledDistributeColumns ++ appendedAttributes)
 
+  override protected def stringArgs: Iterator[Any] = {
+    BinBy.explainStringArgs(
+      rangeStart = rangeStart,
+      rangeEnd = rangeEnd,
+      binWidthMicros = binWidthMicros,
+      originMicros = originMicros,
+      distributeColumns = distributeColumns,
+      scaledDistributeColumns = scaledDistributeColumns,
+      appendedAttributes = appendedAttributes,
+      timeZoneId = timeZoneId)
+  }
+
   // The trait keeps a child partitioning only when its keys are in the output: a partitioning keyed
   // on a pass-through column survives; one keyed on a scaled DISTRIBUTE column (fresh ExprId) does
   // not, because that column is absent from the output.
diff --git a/sql/core/src/test/resources/sql-tests/analyzer-results/bin-by.sql.out b/sql/core/src/test/resources/sql-tests/analyzer-results/bin-by.sql.out
index 91f951d8a403..e426a5d1bac5 100644
--- a/sql/core/src/test/resources/sql-tests/analyzer-results/bin-by.sql.out
+++ b/sql/core/src/test/resources/sql-tests/analyzer-results/bin-by.sql.out
@@ -36,7 +36,7 @@ SELECT * FROM metrics BIN BY (
 )
 -- !query analysis
 Project [ts_start#x, ts_end#x, value#x, bin_start#x, bin_end#x, bin_distribute_ratio#x]
-+- BinBy 300000000, ts_start#x: timestamp, ts_end#x: timestamp, 1704067200000000, [value#x], [value#x], [bin_start#x, bin_end#x, bin_distribute_ratio#x], UTC
++- BinBy range=[ts_start#x: timestamp, ts_end#x: timestamp], binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:00:00, distribute=[value#x: double], scaledDistribute=[value#x: double], appends=[bin_start#x: timestamp, bin_end#x: timestamp, bin_distribute_ratio#x: double], zone=UTC
    +- SubqueryAlias metrics
       +- View (`metrics`, [ts_start#x, ts_end#x, value#x])
          +- Project [cast(ts_start#x as timestamp) AS ts_start#x, cast(ts_end#x as timestamp) AS ts_end#x, cast(value#x as double) AS value#x]
@@ -58,7 +58,7 @@ ORDER BY bin_start
 -- !query analysis
 Sort [bin_start#x ASC NULLS FIRST], true
 +- Aggregate [bin_start#x], [bin_start#x, sum(value#x) AS total#x]
-   +- BinBy 300000000, ts_start#x: timestamp, ts_end#x: timestamp, 1704067200000000, [value#x], [value#x], [bin_start#x, bin_end#x, bin_distribute_ratio#x], UTC
+   +- BinBy range=[ts_start#x: timestamp, ts_end#x: timestamp], binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:00:00, distribute=[value#x: double], scaledDistribute=[value#x: double], appends=[bin_start#x: timestamp, bin_end#x: timestamp, bin_distribute_ratio#x: double], zone=UTC
       +- SubqueryAlias metrics
          +- View (`metrics`, [ts_start#x, ts_end#x, value#x])
             +- Project [cast(ts_start#x as timestamp) AS ts_start#x, cast(ts_end#x as timestamp) AS ts_end#x, cast(value#x as double) AS value#x]
@@ -82,7 +82,7 @@ BIN BY (
 )
 -- !query analysis
 Project [ts_start#x, ts_end#x, value#x, window_start#x, window_end#x, frac#x]
-+- BinBy 300000000, ts_start#x: timestamp, ts_end#x: timestamp, 1704067260000000, [value#x], [value#x], [window_start#x, window_end#x, frac#x], UTC
++- BinBy range=[ts_start#x: timestamp, ts_end#x: timestamp], binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:01:00, distribute=[value#x: double], scaledDistribute=[value#x: double], appends=[window_start#x: timestamp, window_end#x: timestamp, frac#x: double], zone=UTC
    +- SubqueryAlias t
       +- LocalRelation [ts_start#x, ts_end#x, value#x]
 
diff --git a/sql/core/src/test/scala/org/apache/spark/sql/BinBySuite.scala b/sql/core/src/test/scala/org/apache/spark/sql/BinBySuite.scala
index 9a4001503619..fe5af74d4d2a 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/BinBySuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/BinBySuite.scala
@@ -21,6 +21,7 @@ import java.sql.Timestamp
 import java.time.{LocalDateTime, ZoneId, ZoneOffset}
 
 import org.apache.spark.SparkThrowable
+import org.apache.spark.sql.execution.BinByExec
 import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.test.SharedSparkSession
 
@@ -34,6 +35,15 @@ class BinBySuite extends QueryTest with SharedSparkSession {
   private def ratio(overlapMicros: Long, totalMicros: Long): Double =
     overlapMicros.toDouble / totalMicros.toDouble
 
+  private def binByExecArgs(sqlText: String): String = {
+    val plan = spark.sql(sqlText).queryExecution.executedPlan
+    val binByExec = plan.collectFirst {
+      case b: BinByExec => b
+    }.getOrElse(fail(s"No BinByExec in plan:\n$plan"))
+    // exprIds are non-deterministic; normalize them to #x as the golden files do.
+    binByExec.simpleString(SQLConf.get.maxToStringFields).replaceAll("#\\d+", "#x")
+  }
+
   test("BIN BY is rejected when the operator is disabled") {
     withSQLConf(SQLConf.BIN_BY_ENABLED.key -> "false") {
       // Disabled, BIN BY is rejected at analysis with UNSUPPORTED_FEATURE.BIN_BY. spark.sql eagerly
@@ -567,4 +577,46 @@ class BinBySuite extends QueryTest with SharedSparkSession {
       }
     }
   }
+
+  test("EXPLAIN for LTZ BIN BY") {
+    withSQLConf(
+        SQLConf.BIN_BY_ENABLED.key -> "true",
+        SQLConf.SESSION_LOCAL_TIMEZONE.key -> "America/Los_Angeles",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+      val rendered = binByExecArgs(
+        """SELECT * FROM VALUES
+          |  (TIMESTAMP '2024-01-01 00:00:00', TIMESTAMP '2024-01-01 00:05:00', 100.0D)
+          |  AS t(ts_start, ts_end, value)
+          |BIN BY (
+          |  RANGE ts_start TO ts_end BIN WIDTH INTERVAL '5' MINUTE
+          |  ALIGN TO TIMESTAMP '2024-01-01 00:00:00' DISTRIBUTE UNIFORM (value))""".stripMargin)
+      assert(rendered ==
+        "BinBy range=[ts_start#x: timestamp, ts_end#x: timestamp], " +
+          "binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:00:00, " +
+          "distribute=[value#x: double], scaledDistribute=[value#x: double], " +
+          "appends=[bin_start#x: timestamp, bin_end#x: timestamp, " +
+          "bin_distribute_ratio#x: double], zone=America/Los_Angeles")
+    }
+  }
+
+  test("EXPLAIN for TIMESTAMP_NTZ BIN BY") {
+    withSQLConf(
+        SQLConf.BIN_BY_ENABLED.key -> "true",
+        SQLConf.ADAPTIVE_EXECUTION_ENABLED.key -> "false") {
+      val rendered = binByExecArgs(
+        """SELECT * FROM VALUES
+          |  (TIMESTAMP_NTZ '2024-01-01 00:00:00', TIMESTAMP_NTZ '2024-01-01 00:05:00', 100.0D)
+          |  AS t(ts_start, ts_end, value)
+          |BIN BY (
+          |  RANGE ts_start TO ts_end BIN WIDTH INTERVAL '5' MINUTE
+          |  ALIGN TO TIMESTAMP_NTZ '2024-01-01 00:00:00'
+          |  DISTRIBUTE UNIFORM (value))""".stripMargin)
+      assert(rendered ==
+        "BinBy range=[ts_start#x: timestamp_ntz, ts_end#x: timestamp_ntz], " +
+          "binWidth=INTERVAL '0 00:05:00' DAY TO SECOND, alignTo=2024-01-01 00:00:00, " +
+          "distribute=[value#x: double], scaledDistribute=[value#x: double], " +
+          "appends=[bin_start#x: timestamp_ntz, bin_end#x: timestamp_ntz, " +
+          "bin_distribute_ratio#x: double]")
+    }
+  }
 }


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org