This is an automated email from the ASF dual-hosted git repository.
huajianlan pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 5bb7e8789fe [fix](analyze) fix sort with qualify raise unknown column
error (#58577)
5bb7e8789fe is described below
commit 5bb7e8789feb510c7c70c048e25675d8e5a4ae33
Author: yujun <[email protected]>
AuthorDate: Tue Dec 2 11:38:01 2025 +0800
[fix](analyze) fix sort with qualify raise unknown column error (#58577)
For sql with sort and qualify, it may throw exception:
```
MySQL [email protected]:db1> select b from t qualify sum(a) over() > 0 order
by a;
(1105, "errCode = 2, detailMessage = Unknown column 'a' in 'table list' in
SORT clause(line 1, pos 72)")
```
suppose sort's child is C, then sort's input scope = C's output slots
union C's children's slots. (for a sql, order slots scope include not
only the project's scope, but also include project's child's scope).
for the above case, C is qualify Q, C's children is project P, but
qualify Q and project P's output slots are {b}, so sort cann't bind slot
a.
since qualify's output equals qualify's child's output, so we skip
qualify, then we can solve this problem.
relate PR: #40048
---
.../nereids/rules/analysis/BindExpression.java | 5 +
.../data/nereids_syntax_p0/analyze_sort.out | 155 +++++++++++++++++++++
.../suites/nereids_syntax_p0/analyze_sort.groovy | 96 +++++++++++++
3 files changed, 256 insertions(+)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
index 60771004bdd..4020aa12036 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
@@ -1436,6 +1436,10 @@ public class BindExpression implements
AnalysisRuleFactory {
Plan input = sort.child();
List<Slot> childOutput = input.getOutput();
+ if (input instanceof LogicalQualify) {
+ input = input.child(0);
+ }
+
// we should skip distinct project to bind slot in LogicalSort;
// check input.child(0) to avoid process SELECT DISTINCT a FROM t
ORDER BY b by mistake
// NOTICE: SELECT a FROM (SELECT sum(a) AS a FROM t GROUP BY b) v
ORDER BY b will not raise error result
@@ -1446,6 +1450,7 @@ public class BindExpression implements
AnalysisRuleFactory {
|| input.child(0) instanceof LogicalRepeat)) {
input = input.child(0);
}
+
// we should skip LogicalHaving to bind slot in LogicalSort;
if (input instanceof LogicalHaving) {
input = input.child(0);
diff --git a/regression-test/data/nereids_syntax_p0/analyze_sort.out
b/regression-test/data/nereids_syntax_p0/analyze_sort.out
new file mode 100644
index 00000000000..564d6d230a8
--- /dev/null
+++ b/regression-test/data/nereids_syntax_p0/analyze_sort.out
@@ -0,0 +1,155 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sort_1_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalQuickSort[LOCAL_SORT]
+------PhysicalProject
+--------filter((sum((a + b)) OVER() > 0))
+----------PhysicalWindow
+------------PhysicalProject
+--------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_1_result --
+1 1
+1 1
+1 2
+1 2
+1 3
+2 2
+2 2
+2 2
+2 3
+3 3
+3 3
+
+-- !sort_2_shape --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------filter((sum((a + b)) OVER() > 0))
+------------PhysicalWindow
+--------------PhysicalWindow
+----------------PhysicalQuickSort[LOCAL_SORT]
+------------------PhysicalProject
+--------------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_2_result --
+1 1
+1 1
+1 1
+1 1
+1 1
+2 1
+2 1
+2 1
+2 1
+3 1
+3 1
+
+-- !sort_3_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalQuickSort[LOCAL_SORT]
+------hashAgg[GLOBAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------filter((sum((a + b)) OVER() > 0))
+--------------PhysicalWindow
+----------------PhysicalProject
+------------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_3_result --
+1 1
+1 2
+1 3
+2 2
+2 3
+3 3
+
+-- !sort_4_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalQuickSort[LOCAL_SORT]
+------hashAgg[GLOBAL]
+--------hashAgg[LOCAL]
+----------PhysicalProject
+------------filter((sum((a + b)) OVER() > 0))
+--------------PhysicalWindow
+----------------PhysicalProject
+------------------hashAgg[GLOBAL]
+--------------------hashAgg[LOCAL]
+----------------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_4_result --
+1 1 1
+1 2 1
+1 3 1
+2 2 2
+2 3 2
+3 3 3
+
+-- !sort_5_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalQuickSort[LOCAL_SORT]
+------PhysicalProject
+--------filter((sum(sum(a)) OVER() > 0))
+----------PhysicalWindow
+------------hashAgg[GLOBAL]
+--------------hashAgg[LOCAL]
+----------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_5_result --
+1 1
+1 2
+1 3
+2 2
+2 3
+3 3
+
+-- !sort_6_shape --
+PhysicalResultSink
+--PhysicalQuickSort[MERGE_SORT]
+----PhysicalQuickSort[LOCAL_SORT]
+------PhysicalProject
+--------filter((sum(sum(a)) OVER() > 0))
+----------PhysicalWindow
+------------filter((sum(a) > 0))
+--------------hashAgg[GLOBAL]
+----------------hashAgg[LOCAL]
+------------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_6_result --
+1 1 2
+1 2 2
+1 3 1
+2 2 6
+2 3 2
+3 3 6
+
+-- !sort_7_shape --
+PhysicalResultSink
+--PhysicalProject
+----PhysicalQuickSort[MERGE_SORT]
+------PhysicalQuickSort[LOCAL_SORT]
+--------PhysicalProject
+----------filter((sum((a + b)) OVER() > 0))
+------------PhysicalWindow
+--------------PhysicalProject
+----------------PhysicalOlapScan[tbl_analyze_sort]
+
+-- !sort_7_result --
+1
+1
+2
+2
+3
+2
+2
+2
+3
+3
+3
+
diff --git a/regression-test/suites/nereids_syntax_p0/analyze_sort.groovy
b/regression-test/suites/nereids_syntax_p0/analyze_sort.groovy
new file mode 100644
index 00000000000..aa9d0ab7dd6
--- /dev/null
+++ b/regression-test/suites/nereids_syntax_p0/analyze_sort.groovy
@@ -0,0 +1,96 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("analyze_sort") {
+
+ sql """
+ SET ignore_shape_nodes='PhysicalDistribute';
+ SET runtime_filter_mode=OFF;
+ SET disable_join_reorder=true;
+ drop table if exists tbl_analyze_sort force;
+ create table tbl_analyze_sort(a bigint, b bigint)
properties('replication_num' = '1');
+ insert into tbl_analyze_sort values(1, 1), (1, 1), (1, 2), (1, 2), (1,
3), (2, 2), (2, 2), (2, 2), (2, 3), (3, 3), (3, 3);
+ """
+
+ explainAndResult 'sort_1', """
+ select a, b
+ from tbl_analyze_sort
+ qualify sum(a + b) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_2', """
+ select a, rank() over (partition by a)
+ from tbl_analyze_sort
+ qualify sum(a + b) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_3', """
+ select distinct a, b
+ from tbl_analyze_sort
+ qualify sum(a + b) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_4', """
+ select distinct a, b, max(a)
+ from tbl_analyze_sort
+ group by a, b
+ qualify sum(a + b) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_5', """
+ select a, b
+ from tbl_analyze_sort
+ group by a, b
+ qualify sum(sum(a)) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_6', """
+ select a, b, sum(a)
+ from tbl_analyze_sort
+ group by a, b
+ having sum(a) > 0
+ qualify sum(sum(a)) over() > 0
+ order by a, b
+ """
+
+ explainAndResult 'sort_7', """
+ select b
+ from tbl_analyze_sort
+ qualify sum(a + b) over() > 0
+ order by a, b
+ """
+
+ test {
+ sql """
+ select *
+ from (select b from tbl_analyze_sort) t
+ qualify sum(b) over() > 0
+ order by a
+ """
+
+ exception '''Unknown column 'a' in 'table list' in SORT clause'''
+ }
+
+ sql """
+ drop table if exists tbl_analyze_sort force;
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
