This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 3827209de51 [fix](Nereids) join order is not right after sql parsing
(#28721) (#29194)
3827209de51 is described below
commit 3827209de515f29a5795fa831fca6909eddcbed8
Author: morrySnow <101034200+morrys...@users.noreply.github.com>
AuthorDate: Thu Dec 28 15:45:35 2023 +0800
[fix](Nereids) join order is not right after sql parsing (#28721) (#29194)
pick from master
PR# 28721
commit e51e94fdec02057647803570c15c17ca73cd77cb
for sql
```
t1, t2 join t3
```
we should generate plan like:
```
t1 join (t2 join t3)
```
but we generate:
```
(t1 join t2) join t3
```
to follow legancy planner.
---
.../doris/nereids/parser/LogicalPlanBuilder.java | 3 +-
.../nereids/trees/plans/DeleteCommandTest.java | 25 +-
.../nereids/trees/plans/UpdateCommandTest.java | 30 +-
.../data/nereids_syntax_p0/join_order.out | 463 +++++++++++++++++++++
.../suites/nereids_syntax_p0/join_order.groovy | 83 ++++
5 files changed, 573 insertions(+), 31 deletions(-)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index 447f0c28442..bd0ca3d9b82 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -1805,7 +1805,7 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
LogicalPlan left = inputPlan;
for (RelationContext relation : relations) {
// build left deep join tree
- LogicalPlan right = visitRelation(relation);
+ LogicalPlan right = withJoinRelations(visitRelation(relation),
relation);
left = (left == null) ? right :
new LogicalJoin<>(
JoinType.CROSS_JOIN,
@@ -1815,7 +1815,6 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
Optional.empty(),
left,
right);
- left = withJoinRelations(left, relation);
// TODO: pivot and lateral view
}
return left;
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/DeleteCommandTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/DeleteCommandTest.java
index 34cff095546..23a89ffe041 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/DeleteCommandTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/DeleteCommandTest.java
@@ -73,7 +73,7 @@ public class DeleteCommandTest extends TestWithFeService
implements PlanPatternM
public void testFromClauseDelete() throws AnalysisException {
String sql = "delete from t1 a using src join t2 on src.k1 = t2.k1
where t2.k1 = a.k1";
LogicalPlan parsed = new NereidsParser().parseSingle(sql);
- Assertions.assertTrue(parsed instanceof DeleteCommand);
+ Assertions.assertInstanceOf(DeleteCommand.class, parsed);
DeleteCommand command = ((DeleteCommand) parsed);
LogicalPlan plan = command.completeQueryPlan(connectContext,
command.getLogicalQuery());
PlanChecker.from(connectContext, plan)
@@ -83,19 +83,22 @@ public class DeleteCommandTest extends TestWithFeService
implements PlanPatternM
logicalOlapTableSink(
logicalProject(
logicalJoin(
- logicalJoin(
- logicalProject(
- logicalFilter(
-
logicalOlapScan()
+ logicalProject(
+ logicalJoin(
+ logicalProject(
+
logicalFilter(
+
logicalOlapScan()
+ )
+ ),
+ logicalProject(
+
logicalFilter(
+
logicalOlapScan()
+ )
)
- ),
- logicalProject(
-
logicalOlapScan())
+ )
),
logicalProject(
- logicalFilter(
-
logicalOlapScan()
- )
+ logicalOlapScan()
)
)
)
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/UpdateCommandTest.java
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/UpdateCommandTest.java
index 440d5345f63..9b661bd809a 100644
---
a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/UpdateCommandTest.java
+++
b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/plans/UpdateCommandTest.java
@@ -72,7 +72,7 @@ public class UpdateCommandTest extends TestWithFeService
implements PlanPatternM
public void testSimpleUpdate() throws AnalysisException {
String sql = "update t1 set v1 = v1 + 2, v2 = v1 * 2 where k1 = 3";
LogicalPlan parsed = new NereidsParser().parseSingle(sql);
- Assertions.assertTrue(parsed instanceof UpdateCommand);
+ Assertions.assertInstanceOf(UpdateCommand.class, parsed);
UpdateCommand command = ((UpdateCommand) parsed);
LogicalPlan plan = command.completeQueryPlan(connectContext,
command.getLogicalQuery());
PlanChecker.from(connectContext, plan)
@@ -94,29 +94,23 @@ public class UpdateCommandTest extends TestWithFeService
implements PlanPatternM
String sql = "update t1 a set v1 = t2.v1 + 2, v2 = a.v1 * 2 "
+ "from src join t2 on src.k1 = t2.k1 where t2.k1 = a.k1";
LogicalPlan parsed = new NereidsParser().parseSingle(sql);
- Assertions.assertTrue(parsed instanceof UpdateCommand);
+ Assertions.assertInstanceOf(UpdateCommand.class, parsed);
UpdateCommand command = ((UpdateCommand) parsed);
LogicalPlan plan = command.completeQueryPlan(connectContext,
command.getLogicalQuery());
PlanChecker.from(connectContext, plan)
.analyze(plan)
- .rewrite()
.matches(
- logicalOlapTableSink(
- logicalProject(
+ logicalFilter(
+ logicalJoin(
+ logicalSubQueryAlias(
+ logicalFilter(
+ logicalOlapScan()
+ )
+ ),
logicalJoin(
- logicalJoin(
- logicalProject(
- logicalFilter(
-
logicalOlapScan()
- )
- ),
- logicalProject(
-
logicalOlapScan())
- ),
- logicalProject(
- logicalFilter(
-
logicalOlapScan()
- )
+ logicalOlapScan(),
+ logicalFilter(
+ logicalOlapScan()
)
)
)
diff --git a/regression-test/data/nereids_syntax_p0/join_order.out
b/regression-test/data/nereids_syntax_p0/join_order.out
index b60a65aa0b6..9956671c4f7 100644
--- a/regression-test/data/nereids_syntax_p0/join_order.out
+++ b/regression-test/data/nereids_syntax_p0/join_order.out
@@ -5,3 +5,466 @@
-- !sql2 --
1 1
+-- !test_order_with_both_comma_and_join --
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+0
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+1
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+10
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+11
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+12
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+13
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+15
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+16
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+17
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+18
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+19
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+2
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+20
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+3
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+4
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+6
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+7
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+8
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
+9
\ No newline at end of file
diff --git a/regression-test/suites/nereids_syntax_p0/join_order.groovy
b/regression-test/suites/nereids_syntax_p0/join_order.groovy
index 74f4d7c8016..a7c46a8a9f0 100644
--- a/regression-test/suites/nereids_syntax_p0/join_order.groovy
+++ b/regression-test/suites/nereids_syntax_p0/join_order.groovy
@@ -115,4 +115,87 @@ suite("join_order") {
FROM outerjoin_B_order AS ref_1
INNER JOIN outerjoin_A_order AS ref_7
ON (true) order by ref_7.a2) AS subq_0 order by 1,
2;"""
+
+ sql """
+ drop table if exists table_3_undef_undef;
+ """
+
+ sql """
+ drop table if exists table_21_undef_undef;
+ """
+
+ sql """
+ drop table if exists table_22_undef_undef;
+ """
+
+ sql """
+ create table table_3_undef_undef (
+ `pk` int,
+ `col_int_undef_signed` int ,
+ `col_varchar_10__undef_signed` varchar(10) ,
+ `col_varchar_1024__undef_signed` varchar(1024)
+ )engine=olap
+ distributed by hash(pk) buckets 10
+ properties(
+ 'replication_num' = '1'
+ );
+ """
+
+ sql """
+ insert into table_3_undef_undef values
(0,1,"right","me"),(1,8,'q',"have"),(2,7,'o','e');
+ """
+
+ sql """
+ create table table_21_undef_undef (
+ `pk` int,
+ `col_int_undef_signed` int ,
+ `col_varchar_10__undef_signed` varchar(10) ,
+ `col_varchar_1024__undef_signed` varchar(1024)
+ )engine=olap
+ distributed by hash(pk) buckets 10
+ properties(
+ 'replication_num' = '1'
+ );
+ """
+
+ sql """
+ insert into table_21_undef_undef values
(0,7,'y','b'),(1,null,'j','w'),(2,4,"this","she"),(3,null,'w','r'),(4,1,'i','j'),(5,null,'j','h'),(6,null,'k','h'),(7,null,'o',"when"),(8,null,"out",'n'),(9,8,"out",'h'),(10,null,'c','j'),(11,null,'y','z'),(12,null,'m',"so"),(13,null,"so",'m'),(14,2,"not","and"),(15,0,"about","really"),(16,null,'p',"that's"),(17,4,'z','y'),(18,6,'t','f'),(19,7,'k','w'),(20,9,'a',"for");
+ """
+
+ sql """
+ create table table_22_undef_undef (
+ `pk` int,
+ `col_int_undef_signed` int ,
+ `col_varchar_10__undef_signed` varchar(10) ,
+ `col_varchar_1024__undef_signed` varchar(1024)
+ )
+ engine=olap
+ distributed by hash(pk) buckets 10
+ properties(
+ 'replication_num' = '1'
+ );
+ """
+
+ sql """
+ insert into table_22_undef_undef values
(0,null,"can","why"),(1,null,"had","yeah"),(2,null,"ok",'y'),(3,null,"this",'w'),(4,null,'f',"not"),(5,7,'v',"really"),(6,5,"for",'y'),(7,null,'o',"of"),(8,0,'x','q'),(9,null,"about",'h'),(10,8,"you","this"),(11,null,'i','y'),(12,null,"this","who"),(13,4,"see",'h'),(14,8,"because","him"),(15,1,"good",'r'),(16,6,"know","know"),(17,3,"what",'e'),(18,null,'h',"then"),(19,null,'l','z'),(20,4,'l',"i"),(21,null,'f','q');
+ """
+
+ order_qt_test_order_with_both_comma_and_join """
+ SELECT t1.`pk`
+ FROM table_21_undef_undef AS t1,
+ table_3_undef_undef AS alias1
+ FULL OUTER JOIN table_22_undef_undef AS alias2 ON alias1.`pk` =
alias2.`pk`
+ """
+
+ sql """
+ drop table if exists table_3_undef_undef;
+ """
+
+ sql """
+ drop table if exists table_21_undef_undef;
+ """
+
+ sql """
+ drop table if exists table_22_undef_undef;
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
