You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/04/16 17:58:01 UTC
(datafusion) branch main updated: feat: add a config to disable subquery_sort_elimination (#21614)
This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 7731130861 feat: add a config to disable subquery_sort_elimination (#21614)
7731130861 is described below
commit 773113086191b87b5e21b2c0fa64af91c2a19298
Author: Huaijin <ha...@gmail.com>
AuthorDate: Fri Apr 17 01:55:46 2026 +0800
feat: add a config to disable subquery_sort_elimination (#21614)
## Which issue does this PR close?
- Closes #15886
## Rationale for this change
- see
https://github.com/apache/datafusion/issues/15886#issuecomment-3691327978
## What changes are included in this PR?
- Added a new optimizer config option:
`datafusion.sql_parser.enable_subquery_sort_elimination`
- Added a `SessionConfig` builder method:
`with_enable_subquery_sort_elimination(...)`
- Updated SQL relation planning so subquery/CTE `ORDER BY` elimination
only happens when this config is enabled
- Kept the current behavior as the default to avoid changing existing
behavior unexpectedly
## Are these changes tested?
Added SQL integration tests that verify:
- subquery `ORDER BY` is removed by default
- subquery `ORDER BY` is preserved when
`enable_subquery_sort_elimination` is set to `false`
- add slt test case
## Are there any user-facing changes?
no
---
datafusion/common/src/config.rs | 9 +++
datafusion/execution/src/config.rs | 9 +++
datafusion/sql/src/relation/mod.rs | 18 +++++-
datafusion/sql/tests/sql_integration.rs | 69 +++++++++++++++++++++-
.../sqllogictest/test_files/information_schema.slt | 2 +
.../sqllogictest/test_files/subquery_sort.slt | 19 ++++++
docs/source/user-guide/configs.md | 1 +
7 files changed, 123 insertions(+), 4 deletions(-)
diff --git a/datafusion/common/src/config.rs b/datafusion/common/src/config.rs
index 7195af3f96..85361ef5e1 100644
--- a/datafusion/common/src/config.rs
+++ b/datafusion/common/src/config.rs
@@ -311,6 +311,15 @@ config_namespace! {
/// By default, `nulls_max` is used to follow Postgres's behavior.
/// postgres rule: <https://www.postgresql.org/docs/current/queries-order.html>
pub default_null_ordering: String, default = "nulls_max".to_string()
+
+ /// When set to true, DataFusion may remove `ORDER BY` clauses from
+ /// subqueries or CTEs during SQL planning when their ordering cannot
+ /// affect the result, such as when no `LIMIT` or other
+ /// order-sensitive operator depends on them.
+ ///
+ /// Disable this option to preserve explicit subquery ordering in the
+ /// planned query.
+ pub enable_subquery_sort_elimination: bool, default = true
}
}
diff --git a/datafusion/execution/src/config.rs b/datafusion/execution/src/config.rs
index 854d239236..6f60711631 100644
--- a/datafusion/execution/src/config.rs
+++ b/datafusion/execution/src/config.rs
@@ -431,6 +431,15 @@ impl SessionConfig {
self
}
+ /// Enables or disables elimination of `ORDER BY` clauses in subqueries
+ /// when they are not required by order-sensitive operators.
+ pub fn with_enable_subquery_sort_elimination(mut self, enabled: bool) -> Self {
+ self.options_mut()
+ .sql_parser
+ .enable_subquery_sort_elimination = enabled;
+ self
+ }
+
/// Set the size of [`sort_spill_reservation_bytes`] to control
/// memory pre-reservation
///
diff --git a/datafusion/sql/src/relation/mod.rs b/datafusion/sql/src/relation/mod.rs
index 14ccab8703..1665f096ba 100644
--- a/datafusion/sql/src/relation/mod.rs
+++ b/datafusion/sql/src/relation/mod.rs
@@ -97,7 +97,14 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
}
};
- let optimized_plan = optimize_subquery_sort(planned_relation.plan)?.data;
+ let optimized_plan = optimize_subquery_sort(
+ planned_relation.plan,
+ self.context_provider
+ .options()
+ .sql_parser
+ .enable_subquery_sort_elimination,
+ )?
+ .data;
if let Some(alias) = planned_relation.alias {
self.apply_table_alias(optimized_plan, alias)
} else {
@@ -357,7 +364,14 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
}
}
-fn optimize_subquery_sort(plan: LogicalPlan) -> Result<Transformed<LogicalPlan>> {
+fn optimize_subquery_sort(
+ plan: LogicalPlan,
+ enable_subquery_sort_elimination: bool,
+) -> Result<Transformed<LogicalPlan>> {
+ if !enable_subquery_sort_elimination {
+ return Ok(Transformed::no(plan));
+ }
+
// When initializing subqueries, we examine sort options since they might be unnecessary.
// They are only important if the subquery result is affected by the ORDER BY statement,
// which can happen when we have:
diff --git a/datafusion/sql/tests/sql_integration.rs b/datafusion/sql/tests/sql_integration.rs
index 574468f4b1..e0fc5422d6 100644
--- a/datafusion/sql/tests/sql_integration.rs
+++ b/datafusion/sql/tests/sql_integration.rs
@@ -3478,6 +3478,13 @@ fn logical_plan(sql: &str) -> Result<LogicalPlan> {
logical_plan_with_options(sql, ParserOptions::default())
}
+fn logical_plan_with_config(
+ sql: &str,
+ config_options: datafusion_common::config::ConfigOptions,
+) -> Result<LogicalPlan> {
+ logical_plan_with_config_and_options(sql, config_options, ParserOptions::default())
+}
+
fn logical_plan_with_options(sql: &str, options: ParserOptions) -> Result<LogicalPlan> {
let dialect = &GenericDialect {};
logical_plan_with_dialect_and_options(sql, dialect, options)
@@ -3497,7 +3504,25 @@ fn logical_plan_with_dialect_and_options(
dialect: &dyn Dialect,
options: ParserOptions,
) -> Result<LogicalPlan> {
- let state = MockSessionState::default()
+ let state = mock_session_state();
+
+ logical_plan_from_state(sql, dialect, options, state)
+}
+
+fn logical_plan_with_config_and_options(
+ sql: &str,
+ config_options: datafusion_common::config::ConfigOptions,
+ options: ParserOptions,
+) -> Result<LogicalPlan> {
+ let dialect = &GenericDialect {};
+ let mut state = mock_session_state();
+ state.config_options = config_options;
+
+ logical_plan_from_state(sql, dialect, options, state)
+}
+
+fn mock_session_state() -> MockSessionState {
+ MockSessionState::default()
.with_scalar_function(Arc::new(unicode::character_length().as_ref().clone()))
.with_scalar_function(Arc::new(string::concat().as_ref().clone()))
.with_scalar_function(Arc::new(make_udf(
@@ -3534,8 +3559,15 @@ fn logical_plan_with_dialect_and_options(
.with_aggregate_function(grouping_udaf())
.with_window_function(rank_udwf())
.with_window_function(row_number_udwf())
- .with_expr_planner(Arc::new(CoreFunctionPlanner::default()));
+ .with_expr_planner(Arc::new(CoreFunctionPlanner::default()))
+}
+fn logical_plan_from_state(
+ sql: &str,
+ dialect: &dyn Dialect,
+ options: ParserOptions,
+ state: MockSessionState,
+) -> Result<LogicalPlan> {
let context = MockContextProvider { state };
let planner = SqlToRel::new_with_options(&context, options);
let result = DFParser::parse_sql_with_dialect(sql, dialect);
@@ -3808,6 +3840,39 @@ fn in_subquery_uncorrelated() {
);
}
+#[test]
+fn subquery_order_by_is_eliminated_by_default() {
+ let sql = "SELECT x.* FROM (SELECT id FROM person ORDER BY id) x";
+ let plan = logical_plan(sql).unwrap();
+ assert_snapshot!(
+ plan.display_indent_schema().to_string(),
+ @r"
+ Projection: x.id [id:UInt32]
+ SubqueryAlias: x [id:UInt32]
+ Projection: person.id [id:UInt32]
+ TableScan: person [id:UInt32, first_name:Utf8, last_name:Utf8, age:Int32, state:Utf8, salary:Float64, birth_date:Timestamp(ns), 😀:Int32]
+ "
+ );
+}
+
+#[test]
+fn subquery_order_by_can_be_preserved() {
+ let sql = "SELECT x.* FROM (SELECT id FROM person ORDER BY id) x";
+ let mut config_options = datafusion_common::config::ConfigOptions::new();
+ config_options.sql_parser.enable_subquery_sort_elimination = false;
+ let plan = logical_plan_with_config(sql, config_options).unwrap();
+ assert_snapshot!(
+ plan.display_indent_schema().to_string(),
+ @r"
+ Projection: x.id [id:UInt32]
+ SubqueryAlias: x [id:UInt32]
+ Sort: person.id ASC NULLS LAST [id:UInt32]
+ Projection: person.id [id:UInt32]
+ TableScan: person [id:UInt32, first_name:Utf8, last_name:Utf8, age:Int32, state:Utf8, salary:Float64, birth_date:Timestamp(ns), 😀:Int32]
+ "
+ );
+}
+
#[test]
fn not_in_subquery_correlated() {
let sql = "SELECT id FROM person p WHERE id NOT IN \
diff --git a/datafusion/sqllogictest/test_files/information_schema.slt b/datafusion/sqllogictest/test_files/information_schema.slt
index c6a9bfc24c..b04c78bd27 100644
--- a/datafusion/sqllogictest/test_files/information_schema.slt
+++ b/datafusion/sqllogictest/test_files/information_schema.slt
@@ -343,6 +343,7 @@ datafusion.sql_parser.default_null_ordering nulls_max
datafusion.sql_parser.dialect generic
datafusion.sql_parser.enable_ident_normalization true
datafusion.sql_parser.enable_options_value_normalization false
+datafusion.sql_parser.enable_subquery_sort_elimination true
datafusion.sql_parser.map_string_types_to_utf8view true
datafusion.sql_parser.parse_float_as_decimal false
datafusion.sql_parser.recursion_limit 50
@@ -489,6 +490,7 @@ datafusion.sql_parser.default_null_ordering nulls_max Specifies the default null
datafusion.sql_parser.dialect generic Configure the SQL dialect used by DataFusion's parser; supported values include: Generic, MySQL, PostgreSQL, Hive, SQLite, Snowflake, Redshift, MsSQL, ClickHouse, BigQuery, Ansi, DuckDB and Databricks.
datafusion.sql_parser.enable_ident_normalization true When set to true, SQL parser will normalize ident (convert ident to lowercase when not quoted)
datafusion.sql_parser.enable_options_value_normalization false When set to true, SQL parser will normalize options value (convert value to lowercase). Note that this option is ignored and will be removed in the future. All case-insensitive values are normalized automatically.
+datafusion.sql_parser.enable_subquery_sort_elimination true When set to true, DataFusion may remove `ORDER BY` clauses from subqueries or CTEs during SQL planning when their ordering cannot affect the result, such as when no `LIMIT` or other order-sensitive operator depends on them. Disable this option to preserve explicit subquery ordering in the planned query.
datafusion.sql_parser.map_string_types_to_utf8view true If true, string types (VARCHAR, CHAR, Text, and String) are mapped to `Utf8View` during SQL planning. If false, they are mapped to `Utf8`. Default is true.
datafusion.sql_parser.parse_float_as_decimal false When set to true, SQL parser will parse float as decimal type
datafusion.sql_parser.recursion_limit 50 Specifies the recursion depth limit when parsing complex SQL Queries
diff --git a/datafusion/sqllogictest/test_files/subquery_sort.slt b/datafusion/sqllogictest/test_files/subquery_sort.slt
index dd474f3fa1..6df93a3daa 100644
--- a/datafusion/sqllogictest/test_files/subquery_sort.slt
+++ b/datafusion/sqllogictest/test_files/subquery_sort.slt
@@ -51,6 +51,25 @@ EXPLAIN SELECT c1 FROM (SELECT c1 FROM sink_table ORDER BY c2)
logical_plan TableScan: sink_table projection=[c1]
physical_plan DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/testing/data/csv/aggregate_test_100.csv]]}, projection=[c1], file_type=csv, has_header=true
+statement ok
+SET datafusion.sql_parser.enable_subquery_sort_elimination = false;
+
+query TT
+EXPLAIN SELECT c1 FROM (SELECT c1 FROM sink_table ORDER BY c2) AS ttt
+----
+logical_plan
+01)SubqueryAlias: ttt
+02)--Projection: sink_table.c1
+03)----Sort: sink_table.c2 ASC NULLS LAST
+04)------TableScan: sink_table projection=[c1, c2]
+physical_plan
+01)ProjectionExec: expr=[c1@0 as c1]
+02)--SortExec: expr=[c2@1 ASC NULLS LAST], preserve_partitioning=[false]
+03)----DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/testing/data/csv/aggregate_test_100.csv]]}, projection=[c1, c2], file_type=csv, has_header=true
+
+statement ok
+RESET datafusion.sql_parser.enable_subquery_sort_elimination;
+
# Do not remove ordering when it's with limit
diff --git a/docs/source/user-guide/configs.md b/docs/source/user-guide/configs.md
index 16088c892b..49c9eea29e 100644
--- a/docs/source/user-guide/configs.md
+++ b/docs/source/user-guide/configs.md
@@ -191,6 +191,7 @@ The following configuration settings are available:
| datafusion.sql_parser.collect_spans | false | When set to true, the source locations relative to the original SQL query (i.e. [`Span`](https://docs.rs/sqlparser/latest/sqlparser/tokenizer/struct.Span.html)) will be collected and recorded in the logical plan nodes. [...]
| datafusion.sql_parser.recursion_limit | 50 | Specifies the recursion depth limit when parsing complex SQL Queries [...]
| datafusion.sql_parser.default_null_ordering | nulls_max | Specifies the default null ordering for query results. There are 4 options: - `nulls_max`: Nulls appear last in ascending order. - `nulls_min`: Nulls appear first in ascending order. - `nulls_first`: Nulls always be first in any order. - `nulls_last`: Nulls always be last in any order. By default, `nulls_max` is used to follow Postgres's behavior. postgres rule: <https://www.postgresql [...]
+| datafusion.sql_parser.enable_subquery_sort_elimination | true | When set to true, DataFusion may remove `ORDER BY` clauses from subqueries or CTEs during SQL planning when their ordering cannot affect the result, such as when no `LIMIT` or other order-sensitive operator depends on them. Disable this option to preserve explicit subquery ordering in the planned query. [...]
| datafusion.format.safe | true | If set to `true` any formatting errors will be written to the output instead of being converted into a [`std::fmt::Error`] [...]
| datafusion.format.null | | Format string for nulls [...]
| datafusion.format.date_format | %Y-%m-%d | Date format for date arrays [...]
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org