This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.1 by this push:
new 339168083d4 branch-4.1: [fix](fe) Reject TopN in correlated scalar
subquery #64251 (#64351)
339168083d4 is described below
commit 339168083d43d363f476e57f02dc6708a6b89029
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Thu Jun 11 14:44:46 2026 +0800
branch-4.1: [fix](fe) Reject TopN in correlated scalar subquery #64251
(#64351)
Cherry-picked from #64251
Co-authored-by: morrySnow <[email protected]>
---
.../nereids/rules/analysis/SubExprAnalyzer.java | 12 ++++++++++--
.../nereids/rules/analysis/AnalyzeSubQueryTest.java | 21 +++++++++++++++++++++
.../subquery/correlated_scalar_subquery.groovy | 16 +++++++++++++++-
.../nereids_syntax_p0/sub_query_correlated.groovy | 9 ++++++---
4 files changed, 52 insertions(+), 6 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
index 3db749ab5ed..81cd3ee38ea 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/SubExprAnalyzer.java
@@ -138,8 +138,16 @@ class SubExprAnalyzer<T> extends
DefaultExpressionRewriter<T> {
boolean limitOneIsEliminated = false;
if (isCorrelated) {
if (analyzedSubqueryPlan instanceof LogicalLimit) {
- LogicalLimit limit = (LogicalLimit) analyzedSubqueryPlan;
- if (limit.getOffset() == 0 && limit.getLimit() == 1) {
+ Plan child = ((LogicalLimit<?>) analyzedSubqueryPlan).child();
+ LogicalLimit<?> limit = (LogicalLimit<?>) analyzedSubqueryPlan;
+ // after analysis, if project not contains sort key,
FILL_UP_SORT_PROJECT will add a project upper sort
+ // so we must find sort under project here.
+ while (child instanceof LogicalProject) {
+ child = ((LogicalProject<?>) child).child();
+ }
+ // order by c1 limit 1 is not acceptable
+ if (!(child instanceof LogicalSort)
+ && limit.getOffset() == 0 && limit.getLimit() == 1) {
// skip useless limit node
analyzedResult = new AnalyzedResult((LogicalPlan)
analyzedSubqueryPlan.child(0),
analyzedResult.correlatedSlots);
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubQueryTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubQueryTest.java
index f73ee3425af..83cf64031f6 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubQueryTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/rules/analysis/AnalyzeSubQueryTest.java
@@ -19,6 +19,7 @@ package org.apache.doris.nereids.rules.analysis;
import org.apache.doris.nereids.NereidsPlanner;
import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.glue.translator.PhysicalPlanTranslator;
import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
import org.apache.doris.nereids.parser.NereidsParser;
@@ -246,6 +247,26 @@ public class AnalyzeSubQueryTest extends TestWithFeService
implements MemoPatter
}
}
+ @Test
+ public void testCorrelatedScalarSubqueryWithTopNProject() {
+ String sql = "select T1.id from T1 where T1.score > "
+ + "(select T2.score + 1 from T2 where T2.id = T1.id order by
T2.score limit 1)";
+
+ AnalysisException exception =
Assertions.assertThrows(AnalysisException.class,
+ () -> PlanChecker.from(connectContext).analyze(sql));
+ Assertions.assertTrue(exception.getMessage().contains("limit is not
supported in correlated subquery"));
+ }
+
+ @Test
+ public void testCorrelatedScalarSubqueryWithTopN() {
+ String sql = "select T1.id from T1 where T1.score > "
+ + "(select T2.score from T2 where T2.id = T1.id order by
T2.score limit 1)";
+
+ AnalysisException exception =
Assertions.assertThrows(AnalysisException.class,
+ () -> PlanChecker.from(connectContext).analyze(sql));
+ Assertions.assertTrue(exception.getMessage().contains("limit is not
supported in correlated subquery"));
+ }
+
private void checkScalarSubquerySlotNullable(String sql, boolean
outputNullable) {
Plan root = PlanChecker.from(connectContext)
.analyze(sql)
diff --git
a/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
b/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
index fb68ab71abe..fce723d277b 100644
---
a/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
+++
b/regression-test/suites/nereids_p0/subquery/correlated_scalar_subquery.groovy
@@ -139,6 +139,20 @@ suite("correlated_scalar_subquery") {
exception "limit is not supported in correlated subquery"
}
+ test {
+ sql """
+ select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select c2 from correlated_scalar_t2 where
correlated_scalar_t1.c1 = correlated_scalar_t2.c1 order by c2 limit 1);
+ """
+ exception "limit is not supported in correlated subquery"
+ }
+
+ test {
+ sql """
+ select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select c2 + 1 from correlated_scalar_t2 where
correlated_scalar_t1.c1 = correlated_scalar_t2.c1 order by c2 limit 1);
+ """
+ exception "limit is not supported in correlated subquery"
+ }
+
test {
sql """
select c1 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select e1 from (select k1 from (select 1 k1 ) as t
where correlated_scalar_t1.c1 = k1 ) tt lateral view explode_numbers(5) tmp1 as
e1 order by e1);
@@ -248,4 +262,4 @@ suite("correlated_scalar_subquery") {
qt_select_agg_project2 """select c2 from correlated_scalar_t1 where
correlated_scalar_t1.c2 = (select if(sum(c1) is null, 2, 100) from
correlated_scalar_t2 where correlated_scalar_t1.c1 = correlated_scalar_t2.c1)
order by c2;"""
qt_select_2_aggs """select c2 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select count(c1) - min(c1) from correlated_scalar_t2
where correlated_scalar_t1.c1 = correlated_scalar_t2.c1) order by c2;"""
qt_select_3_aggs """select c2 from correlated_scalar_t1 where
correlated_scalar_t1.c2 > (select if(sum(c1) is null, count(c1), max(c2)) from
correlated_scalar_t2 where correlated_scalar_t1.c1 = correlated_scalar_t2.c1)
order by c2;"""
-}
\ No newline at end of file
+}
diff --git
a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
index 80409c6b564..a7961e66e34 100644
--- a/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
+++ b/regression-test/suites/nereids_syntax_p0/sub_query_correlated.groovy
@@ -383,9 +383,12 @@ suite ("sub_query_correlated") {
"""
//----------subquery with order and limit----------
- order_qt_scalar_subquery_with_order_and_limit """
- select * from sub_query_correlated_subquery1 where
sub_query_correlated_subquery1.k1 > (select
sum(sub_query_correlated_subquery3.k3) a from sub_query_correlated_subquery3
where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2
order by a limit 1);
- """
+ test {
+ sql """
+ select * from sub_query_correlated_subquery1 where
sub_query_correlated_subquery1.k1 > (select
sum(sub_query_correlated_subquery3.k3) a from sub_query_correlated_subquery3
where sub_query_correlated_subquery3.v2 = sub_query_correlated_subquery1.k2
order by a limit 1);
+ """
+ exception """limit is not supported in correlated subquery"""
+ }
//---------subquery with Disjunctions-------------
order_qt_scalar_subquery_with_disjunctions """
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
