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

cloud-fan pushed a commit to branch branch-4.x
in repository https://gitbox.apache.org/repos/asf/spark.git


The following commit(s) were added to refs/heads/branch-4.x by this push:
     new 6c1d199bc0fa [SPARK-58411][SQL] Preserve ORDER BY in LIMIT 
decorrelation when correlation references only the outer table
6c1d199bc0fa is described below

commit 6c1d199bc0fa12107328c0911cd10a48db4df87d
Author: Avery <[email protected]>
AuthorDate: Tue Aug 4 14:29:59 2026 +0800

    [SPARK-58411][SQL] Preserve ORDER BY in LIMIT decorrelation when 
correlation references only the outer table
    
    ### What changes were proposed in this pull request?
    
    In `DecorrelateInnerQuery`, the `Limit` case peels the `Sort` off the 
subquery and saves its ordering. When the correlated predicate references only 
outer columns, decorrelation produces no partitioning key 
(`partitionFields.isEmpty`) and that branch re-emits the limit as `Limit(k, 
newChild)` / `Limit(k, Offset(m, newChild))` without re-applying the saved 
ordering, turning `ORDER BY ... LIMIT k` into an arbitrary `LIMIT k`.
    
    This PR re-applies the saved ordering as a global `Sort` below the limit in 
the `partitionFields.isEmpty` branch (covering both `LIMIT` and `LIMIT ... 
OFFSET`), so ordered limits stay order-preserving and deterministic. A new 
internal flag 
`spark.sql.optimizer.decorrelateLimitOffsetLegacyIncorrectOrderHandling.enabled`
 (default false) can restore the legacy behavior.
    
    The standalone `Offset` case (no `LIMIT`) is not affected: it decorrelates 
the original input with the `Sort` retained, so its ordering is already 
preserved.
    
    ### Why are the changes needed?
    
    The dropped `ORDER BY` makes an ordered-limit subquery return a 
non-deterministic row. For example:
    
    ```
    SELECT t1a, t1b FROM t1
    WHERE t1c = (SELECT t2c FROM t2 WHERE t1b < t1d ORDER BY t2c LIMIT 1);
    ```
    
    With distinct `t2c` values `{12, 16, NULL}` and Spark's default `ASC NULLS 
FIRST`, the correct ordered `LIMIT 1` returns `NULL`, so the row is correctly 
excluded. With the bug, an arbitrary row is picked, which can incorrectly emit 
a row and varies by physical execution order.
    
    ### ground truth verification
    <img width="2642" height="1754" alt="image" 
src="https://github.com/user-attachments/assets/69d7f45d-5d3a-4bef-a159-21de1c800b58";
 />
    Result from Postgresql is [val1a |  16]. This is because the default 
ordering in Postgresql is asc with NULL last. But in Spark sql, it is asc with 
null first.
    
    ### Does this PR introduce any user-facing change?
    
    Yes. Correlated scalar/predicate subqueries of the form `ORDER BY ... 
LIMIT` whose correlation references only the outer table now return correct, 
deterministic results. Results that previously depended on the dropped ordering 
may change. The legacy behavior can be restored via the config above.
    
    ### How was this patch tested?
    
    New unit tests in `DecorrelateInnerQuerySuite` covering default ordering, 
explicit `ASC NULLS LAST`, `LIMIT ... OFFSET`, and the legacy-flag path. 
Golden-file results for `scalar-subquery-predicate.sql` are updated accordingly.
    
    ### Was this patch authored or co-authored using generative AI tooling?
    
    Yes, drafted with assistance from Claude.
    
    Closes #57614 from AveryQi115/SPARK-58411.
    
    Authored-by: Avery <[email protected]>
    Signed-off-by: Wenchen Fan <[email protected]>
    (cherry picked from commit 31e1a7f6346b2606f76bb3acf52ba73ecfd852a6)
    Signed-off-by: Wenchen Fan <[email protected]>
---
 .../catalyst/optimizer/DecorrelateInnerQuery.scala | 16 ++++-
 .../org/apache/spark/sql/internal/SQLConf.scala    | 13 ++++
 .../optimizer/DecorrelateInnerQuerySuite.scala     | 78 ++++++++++++++++++++++
 .../scalar-subquery-predicate.sql.out              |  4 +-
 4 files changed, 107 insertions(+), 4 deletions(-)

diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
index c6a7940b93aa..91d881fbcfbd 100644
--- 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
+++ 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuery.scala
@@ -725,9 +725,21 @@ object DecorrelateInnerQuery extends PredicateHelper {
             if (partitionFields.isEmpty) {
               // Underlying subquery has no predicates connecting inner and 
outer query.
               // In this case, limit can be computed over the inner query 
directly.
+              // The ORDER BY was peeled off the Sort above; re-apply it as a 
global Sort below
+              // the limit so that ORDER BY ... LIMIT (and ORDER BY ... LIMIT 
... OFFSET) is
+              // order-preserving. Otherwise the ordering is dropped and the 
limit returns an
+              // arbitrary (non-deterministic) rows.
+              val orderedChild =
+                if (ordering.nonEmpty && !SQLConf.get.getConf(
+                    
SQLConf.DECORRELATE_LIMIT_OFFSET_LEGACY_INCORRECT_ORDER_HANDLING_ENABLED)) {
+                  Sort(replaceOuterReferences(ordering, outerReferenceMap), 
global = true, newChild)
+                } else {
+                  newChild
+                }
               offsetExpr match {
-                case IntegerLiteral(0) => (Limit(limit, newChild), joinCond, 
outerReferenceMap)
-                case _ => (Limit(limit, Offset(offsetExpr, newChild)), 
joinCond, outerReferenceMap)
+                case IntegerLiteral(0) => (Limit(limit, orderedChild), 
joinCond, outerReferenceMap)
+                case _ =>
+                  (Limit(limit, Offset(offsetExpr, orderedChild)), joinCond, 
outerReferenceMap)
               }
             } else {
               val orderByFields = replaceOuterReferences(ordering, 
outerReferenceMap)
diff --git 
a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala 
b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
index 64d732172960..d060d5f7a7f9 100644
--- a/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
+++ b/sql/catalyst/src/main/scala/org/apache/spark/sql/internal/SQLConf.scala
@@ -6020,6 +6020,19 @@ object SQLConf {
       .booleanConf
       .createWithDefault(true)
 
+  val DECORRELATE_LIMIT_OFFSET_LEGACY_INCORRECT_ORDER_HANDLING_ENABLED =
+    
buildConf("spark.sql.optimizer.decorrelateLimitOffsetLegacyIncorrectOrderHandling.enabled")
+      .internal()
+      .doc("If enabled, revert to the legacy incorrect behavior where the 
ORDER BY of a " +
+        "correlated subquery with LIMIT (and optional OFFSET) whose predicates 
only reference " +
+        "the outer table is dropped during decorrelation, turning ORDER BY ... 
LIMIT ... OFFSET " +
+        "into an arbitrary LIMIT/OFFSET. When disabled (default), the ORDER BY 
is preserved so " +
+        "the result is deterministic.")
+      .version("4.3.0")
+      .withBindingPolicy(ConfigBindingPolicy.SESSION)
+      .booleanConf
+      .createWithDefault(false)
+
   val DECORRELATE_UNION_OR_SET_OP_UNDER_LIMIT_ENABLED =
     buildConf("spark.sql.optimizer.decorrelateUnionOrSetOpUnderLimit.enabled")
       .internal()
diff --git 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
index 1923c7836b23..a451c6961ba8 100644
--- 
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
+++ 
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/optimizer/DecorrelateInnerQuerySuite.scala
@@ -22,6 +22,7 @@ import org.apache.spark.sql.catalyst.dsl.plans._
 import org.apache.spark.sql.catalyst.expressions._
 import org.apache.spark.sql.catalyst.plans._
 import org.apache.spark.sql.catalyst.plans.logical._
+import org.apache.spark.sql.internal.SQLConf
 import org.apache.spark.sql.types.IntegerType
 
 class DecorrelateInnerQuerySuite extends PlanTest {
@@ -736,4 +737,81 @@ class DecorrelateInnerQuerySuite extends PlanTest {
             DomainJoin(Seq(a), testRelation2))))))
     check(outputPlan, joinCond, correctAnswer, Seq(a <=> a))
   }
+
+  test("SPARK-58411: order by is preserved for limit with correlation only on 
outer table") {
+    // The correlated predicate references only outer columns (a, b), so no 
domain join is
+    // needed and the limit is computed directly over the inner query 
(partitionFields.isEmpty
+    // branch). The ORDER BY must be preserved so ORDER BY ... LIMIT stays 
deterministic.
+    val outerPlan = testRelation
+    val innerPlan =
+      Project(Seq(x),
+        Limit(1, Sort(Seq(SortOrder(x, Ascending)), true,
+          Filter(OuterReference(a) < OuterReference(b),
+            testRelation2))))
+    val (outputPlan, joinCond) = DecorrelateInnerQuery(innerPlan, 
outerPlan.select())
+
+    val correctAnswer =
+      Project(Seq(x),
+        Limit(1, Sort(Seq(SortOrder(x, Ascending)), global = true,
+          testRelation2)))
+    check(outputPlan, joinCond, correctAnswer, Seq(a < b))
+  }
+
+  test("SPARK-58411: explicit ORDER BY ASC NULLS LAST is preserved for limit 
with correlation " +
+    "only on outer table") {
+    // An explicitly specified null ordering (NULLS LAST, the opposite of 
ASC's NULLS FIRST
+    // default) must be carried through decorrelation unchanged.
+    val outerPlan = testRelation
+    val nullsLast = SortOrder(x, Ascending, NullsLast, Seq.empty)
+    val innerPlan =
+      Project(Seq(x),
+        Limit(1, Sort(Seq(nullsLast), true,
+          Filter(OuterReference(a) < OuterReference(b),
+            testRelation2))))
+    val (outputPlan, joinCond) = DecorrelateInnerQuery(innerPlan, 
outerPlan.select())
+
+    val correctAnswer =
+      Project(Seq(x),
+        Limit(1, Sort(Seq(nullsLast), global = true,
+          testRelation2)))
+    check(outputPlan, joinCond, correctAnswer, Seq(a < b))
+  }
+
+  test("SPARK-58411: order by is preserved for limit with offset and 
correlation only on " +
+    "outer table") {
+    // Same as above but with an OFFSET between the LIMIT and the ORDER BY. 
The ordering must
+    // be preserved for the LIMIT ... OFFSET case as well.
+    val outerPlan = testRelation
+    val innerPlan =
+      Project(Seq(x),
+        Limit(1, Offset(2, Sort(Seq(SortOrder(x, Ascending)), true,
+          Filter(OuterReference(a) < OuterReference(b),
+            testRelation2)))))
+    val (outputPlan, joinCond) = DecorrelateInnerQuery(innerPlan, 
outerPlan.select())
+
+    val correctAnswer =
+      Project(Seq(x),
+        Limit(1, Offset(2, Sort(Seq(SortOrder(x, Ascending)), global = true,
+          testRelation2))))
+    check(outputPlan, joinCond, correctAnswer, Seq(a < b))
+  }
+
+  test("SPARK-58411: legacy flag restores the incorrect dropped-order 
behavior") {
+    withSQLConf(
+      
SQLConf.DECORRELATE_LIMIT_OFFSET_LEGACY_INCORRECT_ORDER_HANDLING_ENABLED.key -> 
"true") {
+      val outerPlan = testRelation
+      val innerPlan =
+        Project(Seq(x),
+          Limit(1, Sort(Seq(SortOrder(x, Ascending)), true,
+            Filter(OuterReference(a) < OuterReference(b),
+              testRelation2))))
+      val (outputPlan, joinCond) = DecorrelateInnerQuery(innerPlan, 
outerPlan.select())
+
+      // Legacy behavior: the Sort is dropped, leaving an arbitrary 
(non-deterministic) limit.
+      val correctAnswer =
+        Project(Seq(x),
+          Limit(1, testRelation2))
+      check(outputPlan, joinCond, correctAnswer, Seq(a < b))
+    }
+  }
 }
diff --git 
a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
 
b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
index b37fe614e237..9b1322dcb2e8 100644
--- 
a/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
+++ 
b/sql/core/src/test/resources/sql-tests/results/subquery/scalar-subquery/scalar-subquery-predicate.sql.out
@@ -598,7 +598,7 @@ WHERE  t1c = (SELECT t2c
 -- !query schema
 struct<t1a:string,t1b:smallint>
 -- !query output
-val1a  16
+
 
 
 -- !query
@@ -625,7 +625,7 @@ WHERE  t1c = (SELECT DISTINCT t2c
 -- !query schema
 struct<t1a:string,t1b:smallint>
 -- !query output
-val1a  16
+
 
 
 -- !query


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to