This is an automated email from the ASF dual-hosted git repository.
panxiaolei 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 5569b3dc2d3 [Bug](sort) fix wrong result coz push down sort with null
last (#51472)
5569b3dc2d3 is described below
commit 5569b3dc2d39a21d16c57b4241cb08c6d42e4714
Author: Pxl <[email protected]>
AuthorDate: Thu Jun 5 10:27:52 2025 +0800
[Bug](sort) fix wrong result coz push down sort with null last (#51472)
### What problem does this PR solve?
```sql
drop table if exists d_table;
create table d_table (
k1 int null,
k2 int not null,
k3 bigint null,
k4 varchar(100) null
)
duplicate key (k1,k2,k3)
distributed BY hash(k1) buckets 3
properties("replication_num" = "1");
insert into d_table select 1,1,1,'a';
insert into d_table select 2,2,2,'b';
insert into d_table select 3,-3,null,'c';
insert into d_table select 3,3,null,'c';
insert into d_table select null,3,null,'c';
select * from d_table order by k1 nulls last limit 1;
mysql> select * from d_table order by k1 nulls last limit 1;
+------+------+------+------+
| k1 | k2 | k3 | k4 |
+------+------+------+------+
| 2 | 2 | 2 | b |
+------+------+------+------+
1 row in set (0.01 sec)
mysql> select * from d_table order by k1 nulls last limit 2;
+------+------+------+------+
| k1 | k2 | k3 | k4 |
+------+------+------+------+
| 1 | 1 | 1 | a |
| 2 | 2 | 2 | b |
+------+------+------+------+
2 rows in set (0.02 sec)
```
---
.../glue/translator/PhysicalPlanTranslator.java | 6 +-
.../org/apache/doris/planner/OlapScanNode.java | 6 +-
.../test_orderby_limit/test_orderby_limit.out | Bin 0 -> 1191 bytes
.../test_orderby_limit/test_orderby_limit.groovy | 64 +++++++++++++++++++++
4 files changed, 70 insertions(+), 6 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
index 52dffcf30d3..fc57a62ef88 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/PhysicalPlanTranslator.java
@@ -2960,9 +2960,9 @@ public class PhysicalPlanTranslator extends
DefaultPlanVisitor<PlanFragment, Pla
if (sortExpr instanceof SlotRef) {
SlotRef slotRef = (SlotRef) sortExpr;
if (sortColumn.equals(slotRef.getColumn())) {
- // ORDER BY DESC NULLS FIRST can not be optimized to only
read file tail,
- // since NULLS is at file head but data is at tail
- if (sortColumn.isAllowNull() && nullsFirsts.get(i) &&
!isAscOrders.get(i)) {
+ // [ORDER BY DESC NULLS FIRST] or [ORDER BY ASC NULLS
LAST] can not be optimized
+ // to only read file tail, since NULLS is at file head but
data is at tail
+ if (sortColumn.isAllowNull() && nullsFirsts.get(i) !=
isAscOrders.get(i)) {
return false;
}
} else {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java
b/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java
index 9fc80b1563a..641a1c992a8 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/planner/OlapScanNode.java
@@ -1281,9 +1281,9 @@ public class OlapScanNode extends ScanNode {
if (sortExpr instanceof SlotRef) {
SlotRef slotRef = (SlotRef) sortExpr;
if (tableKey.equals(slotRef.getColumn())) {
- // ORDER BY DESC NULLS FIRST can not be optimized to only
read file tail,
- // since NULLS is at file head but data is at tail
- if (tableKey.isAllowNull() && nullsFirsts.get(i) &&
!isAscOrders.get(i)) {
+ // [ORDER BY DESC NULLS FIRST] or [ORDER BY ASC NULLS
LAST] can not be optimized
+ // to only read file tail, since NULLS is at file head but
data is at tail
+ if (tableKey.isAllowNull() && (nullsFirsts.get(i) !=
isAscOrders.get(i))) {
return false;
}
} else {
diff --git
a/regression-test/data/query_p0/limit/test_orderby_limit/test_orderby_limit.out
b/regression-test/data/query_p0/limit/test_orderby_limit/test_orderby_limit.out
new file mode 100644
index 00000000000..b70bcf4aabd
Binary files /dev/null and
b/regression-test/data/query_p0/limit/test_orderby_limit/test_orderby_limit.out
differ
diff --git
a/regression-test/suites/query_p0/limit/test_orderby_limit/test_orderby_limit.groovy
b/regression-test/suites/query_p0/limit/test_orderby_limit/test_orderby_limit.groovy
new file mode 100644
index 00000000000..82f7688d0e5
--- /dev/null
+++
b/regression-test/suites/query_p0/limit/test_orderby_limit/test_orderby_limit.groovy
@@ -0,0 +1,64 @@
+// 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("test_orderby_limit") {
+ sql "drop table if exists d_table;"
+ sql """
+ create table d_table (
+ k1 int null,
+ k2 int not null,
+ k3 bigint null,
+ k4 varchar(100) null
+ )
+ duplicate key (k1,k2,k3)
+ distributed BY hash(k1) buckets 3
+ properties("replication_num" = "1");
+ """
+ sql "insert into d_table select 1,1,1,'a';"
+ sql "insert into d_table select 2,2,2,'b';"
+ sql "insert into d_table select -3,-3,null,'c';"
+ sql "insert into d_table select 3,3,null,'c';"
+ sql "insert into d_table select null,3,null,'c';"
+
+ qt_select "select * from d_table order by k1 desc nulls last limit 1;"
+ qt_select "select * from d_table order by k1 desc nulls last limit 2;"
+ qt_select "select * from d_table order by k1 desc nulls last limit 3;"
+ qt_select "select * from d_table order by k1 desc nulls last limit 4;"
+ qt_select "select * from d_table order by k1 desc nulls last limit 5;"
+ qt_select "select * from d_table order by k1 desc nulls last limit 6;"
+
+ qt_select "select * from d_table order by k1 desc nulls first limit 1;"
+ qt_select "select * from d_table order by k1 desc nulls first limit 2;"
+ qt_select "select * from d_table order by k1 desc nulls first limit 3;"
+ qt_select "select * from d_table order by k1 desc nulls first limit 4;"
+ qt_select "select * from d_table order by k1 desc nulls first limit 5;"
+ qt_select "select * from d_table order by k1 desc nulls first limit 6;"
+
+ qt_select "select * from d_table order by k1 asc nulls last limit 1;"
+ qt_select "select * from d_table order by k1 asc nulls last limit 2;"
+ qt_select "select * from d_table order by k1 asc nulls last limit 3;"
+ qt_select "select * from d_table order by k1 asc nulls last limit 4;"
+ qt_select "select * from d_table order by k1 asc nulls last limit 5;"
+ qt_select "select * from d_table order by k1 asc nulls last limit 6;"
+
+ qt_select "select * from d_table order by k1 asc nulls first limit 1;"
+ qt_select "select * from d_table order by k1 asc nulls first limit 2;"
+ qt_select "select * from d_table order by k1 asc nulls first limit 3;"
+ qt_select "select * from d_table order by k1 asc nulls first limit 4;"
+ qt_select "select * from d_table order by k1 asc nulls first limit 5;"
+ qt_select "select * from d_table order by k1 asc nulls first limit 6;"
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
