You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by hu...@apache.org on 2022/08/16 11:24:08 UTC
[doris] branch master updated: [Feature](nereids)support view and nested view (#11589)
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 a07e153419 [Feature](nereids)support view and nested view (#11589)
a07e153419 is described below
commit a07e1534194f34c9188fc1cb9f34cacb41a1565c
Author: mch_ucchi <41...@users.noreply.github.com>
AuthorDate: Tue Aug 16 19:24:01 2022 +0800
[Feature](nereids)support view and nested view (#11589)
support view in query
and add a rewrite rule: merge consecutive projects.
the rule can merge relative consecutive projects to one project to improve efficiency
---
.../org/apache/doris/nereids/NereidsPlanner.java | 2 +
.../glue/translator/PhysicalPlanTranslator.java | 8 +
.../batch/MergeConsecutiveProjectJob.java} | 31 ++--
.../nereids/rules/analysis/BindSlotReference.java | 57 ++++----
.../rewrite/logical/MergeConsecutiveProjects.java | 53 ++++++-
.../doris/nereids/trees/expressions/Alias.java | 11 ++
.../expressions/visitor/ExpressionReplacer.java | 3 +-
.../doris/nereids/trees/expressions/ViewTest.java | 162 +++++++++++++++++++++
.../data/nereids_syntax_p0/inpredicate.out | 1 +
regression-test/data/nereids_syntax_p0/view.out | 31 ++++
.../suites/nereids_syntax_p0/view.groovy | 99 +++++++++++++
11 files changed, 409 insertions(+), 49 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
index 303ec02edd..d0dc24cc12 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/NereidsPlanner.java
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.glue.translator.PhysicalPlanTranslator;
import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
import org.apache.doris.nereids.jobs.batch.DisassembleRulesJob;
import org.apache.doris.nereids.jobs.batch.JoinReorderRulesJob;
+import org.apache.doris.nereids.jobs.batch.MergeConsecutiveProjectJob;
import org.apache.doris.nereids.jobs.batch.NormalizeExpressionRulesJob;
import org.apache.doris.nereids.jobs.batch.OptimizeRulesJob;
import org.apache.doris.nereids.jobs.batch.PredicatePushDownRulesJob;
@@ -145,6 +146,7 @@ public class NereidsPlanner extends Planner {
* Logical plan rewrite based on a series of heuristic rules.
*/
private void rewrite() {
+ new MergeConsecutiveProjectJob(cascadesContext).execute();
new NormalizeExpressionRulesJob(cascadesContext).execute();
new JoinReorderRulesJob(cascadesContext).execute();
new PredicatePushDownRulesJob(cascadesContext).execute();
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 2efa6dc6ac..f560adf8eb 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
@@ -30,6 +30,7 @@ import org.apache.doris.catalog.OlapTable;
import org.apache.doris.catalog.Table;
import org.apache.doris.nereids.exceptions.AnalysisException;
import org.apache.doris.nereids.properties.OrderKey;
+import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.EqualTo;
import org.apache.doris.nereids.trees.expressions.ExprId;
import org.apache.doris.nereids.trees.expressions.Expression;
@@ -423,6 +424,13 @@ public class PhysicalPlanTranslator extends DefaultPlanVisitor<PlanFragment, Pla
@Override
public PlanFragment visitPhysicalProject(PhysicalProject<Plan> project, PlanTranslatorContext context) {
PlanFragment inputFragment = project.child(0).accept(this, context);
+
+ // TODO: handle p.child(0) is not NamedExpression.
+ project.getProjects().stream().filter(Alias.class::isInstance).forEach(p -> {
+ SlotRef ref = context.findSlotRef(((NamedExpression) p.child(0)).getExprId());
+ context.addExprIdPair(p.getExprId(), ref);
+ });
+
List<Expr> execExprList = project.getProjects()
.stream()
.map(e -> ExpressionTranslator.translate(e, context))
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/MergeConsecutiveProjectJob.java
similarity index 53%
copy from fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java
copy to fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/MergeConsecutiveProjectJob.java
index 482bb808fe..a7ccd40124 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/jobs/batch/MergeConsecutiveProjectJob.java
@@ -15,26 +15,27 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.nereids.trees.expressions.visitor;
+package org.apache.doris.nereids.jobs.batch;
-import org.apache.doris.nereids.trees.expressions.Expression;
+import org.apache.doris.nereids.CascadesContext;
+import org.apache.doris.nereids.rules.rewrite.logical.MergeConsecutiveProjects;
-import java.util.Map;
+import com.google.common.collect.ImmutableList;
/**
- * replace expr nodes by substitutionMap
+ * Merge consecutive project rules.
*/
-public class ExpressionReplacer
- extends DefaultExpressionRewriter<Map<Expression, Expression>> {
- public static final ExpressionReplacer INSTANCE = new ExpressionReplacer();
+public class MergeConsecutiveProjectJob extends BatchRulesJob {
- @Override
- public Expression visit(Expression expr, Map<Expression, Expression> substitutionMap) {
- if (substitutionMap.containsKey(expr)) {
- return substitutionMap.get(expr);
- } else {
- return super.visit(expr, substitutionMap);
- }
+ /**
+ * Execute the merge consecutive job.
+ * @param ctx planner context for execute job
+ */
+ public MergeConsecutiveProjectJob(CascadesContext ctx) {
+ //TODO: eliminate consecutive projects for view
+ super(ctx);
+ rulesJob.addAll(ImmutableList.of(
+ bottomUpBatch(ImmutableList.of(
+ new MergeConsecutiveProjects()))));
}
}
-
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
index a0a9d531c8..7267d5a22e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindSlotReference.java
@@ -269,38 +269,41 @@ public class BindSlotReference implements AnalysisRuleFactory {
private List<Slot> bindSlot(UnboundSlot unboundSlot, List<Slot> boundSlots) {
return boundSlots.stream().filter(boundSlot -> {
List<String> nameParts = unboundSlot.getNameParts();
- switch (nameParts.size()) {
- case 1:
- // Unbound slot name is `column`
- return nameParts.get(0).equalsIgnoreCase(boundSlot.getName());
- case 2:
- // Unbound slot name is `table`.`column`
- List<String> qualifier = boundSlot.getQualifier();
- String name = boundSlot.getName();
- switch (qualifier.size()) {
- case 2:
- // qualifier is `db`.`table`
- return nameParts.get(0).equalsIgnoreCase(qualifier.get(1))
- && nameParts.get(1).equalsIgnoreCase(name);
- case 1:
- // qualifier is `table`
- return nameParts.get(0).equalsIgnoreCase(qualifier.get(0))
- && nameParts.get(1).equalsIgnoreCase(name);
- case 0:
- // has no qualifiers
- return nameParts.get(1).equalsIgnoreCase(name);
- default:
- throw new AnalysisException("Not supported qualifier: "
- + StringUtils.join(qualifier, "."));
- }
- default:
- throw new AnalysisException("Not supported name: "
- + StringUtils.join(nameParts, "."));
+ if (nameParts.size() == 1) {
+ return nameParts.get(0).equalsIgnoreCase(boundSlot.getName());
+ } else if (nameParts.size() <= 3) {
+ int size = nameParts.size();
+ // if nameParts.size() == 3, nameParts.get(0) is cluster name.
+ return handleNamePartsTwoOrThree(boundSlot, nameParts.subList(size - 2, size));
}
+ //TODO: handle name parts more than three.
+ throw new AnalysisException("Not supported name: "
+ + StringUtils.join(nameParts, "."));
}).collect(Collectors.toList());
}
}
+ private boolean handleNamePartsTwoOrThree(Slot boundSlot, List<String> nameParts) {
+ List<String> qualifier = boundSlot.getQualifier();
+ String name = boundSlot.getName();
+ switch (qualifier.size()) {
+ case 2:
+ // qualifier is `db`.`table`
+ return nameParts.get(0).equalsIgnoreCase(qualifier.get(1))
+ && nameParts.get(1).equalsIgnoreCase(name);
+ case 1:
+ // qualifier is `table`
+ return nameParts.get(0).equalsIgnoreCase(qualifier.get(0))
+ && nameParts.get(1).equalsIgnoreCase(name);
+ case 0:
+ // has no qualifiers
+ return nameParts.get(1).equalsIgnoreCase(name);
+ default:
+ throw new AnalysisException("Not supported qualifier: "
+ + StringUtils.join(qualifier, "."));
+ }
+ }
+
/** BoundStar is used to wrap list of slots for temporary. */
private class BoundStar extends NamedExpression {
public BoundStar(List<Slot> children) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/MergeConsecutiveProjects.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/MergeConsecutiveProjects.java
index 6f1e55ef47..7738ee2772 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/MergeConsecutiveProjects.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/logical/MergeConsecutiveProjects.java
@@ -23,11 +23,13 @@ import org.apache.doris.nereids.rules.rewrite.OneRewriteRuleFactory;
import org.apache.doris.nereids.trees.expressions.Alias;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.NamedExpression;
-import org.apache.doris.nereids.trees.expressions.visitor.ExpressionReplacer;
+import org.apache.doris.nereids.trees.expressions.Slot;
+import org.apache.doris.nereids.trees.expressions.SlotReference;
+import org.apache.doris.nereids.trees.expressions.visitor.DefaultExpressionRewriter;
import org.apache.doris.nereids.trees.plans.GroupPlan;
-import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalProject;
+import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
@@ -50,6 +52,47 @@ import java.util.stream.Collectors;
*/
public class MergeConsecutiveProjects extends OneRewriteRuleFactory {
+
+ private static class ExpressionReplacer extends DefaultExpressionRewriter<Map<Expression, Expression>> {
+ public static final ExpressionReplacer INSTANCE = new ExpressionReplacer();
+
+ /**
+ * case 1:
+ * project(alias(c) as d, alias(x) as y)
+ * |
+ * | ===> project(alias(a) as d, alias(b) as y)
+ * |
+ * project(slotRef(a) as c, slotRef(b) as x)
+ * case 2:
+ * project(slotRef(x.c), slotRef(x.d))
+ * | ===> project(slotRef(a) as x.c, slotRef(b) as x.d)
+ * project(slotRef(a) as c, slotRef(b) as d)
+ * case 3: others
+ */
+ @Override
+ public Expression visit(Expression expr, Map<Expression, Expression> substitutionMap) {
+ if (expr instanceof Alias && expr.child(0) instanceof SlotReference) {
+ // case 1:
+ Expression c = expr.child(0);
+ // Alias doesn't contain qualifier
+ Slot ref = ((SlotReference) c).withQualifier(Collections.emptyList());
+ if (substitutionMap.containsKey(ref)) {
+ return expr.withChildren(substitutionMap.get(ref).children());
+ }
+ } else if (expr instanceof SlotReference) {
+ // case 2:
+ Slot ref = ((SlotReference) expr).withQualifier(Collections.emptyList());
+ if (substitutionMap.containsKey(ref)) {
+ Alias res = (Alias) substitutionMap.get(ref);
+ return (res.child() instanceof SlotReference) ? res : res.child();
+ }
+ } else if (substitutionMap.containsKey(expr)) {
+ return substitutionMap.get(expr).child(0);
+ }
+ return super.visit(expr, substitutionMap);
+ }
+ }
+
@Override
public Rule build() {
return logicalProject(logicalProject()).then(project -> {
@@ -59,14 +102,14 @@ public class MergeConsecutiveProjects extends OneRewriteRuleFactory {
Map<Expression, Expression> childAliasMap = childProjectExpressions.stream()
.filter(e -> e instanceof Alias)
.collect(Collectors.toMap(
- NamedExpression::toSlot, e -> e.child(0))
+ NamedExpression::toSlot, e -> e)
);
projectExpressions = projectExpressions.stream()
- .map(e -> ExpressionReplacer.INSTANCE.visit(e, childAliasMap))
+ .map(e -> MergeConsecutiveProjects.ExpressionReplacer.INSTANCE.visit(e, childAliasMap))
.map(NamedExpression.class::cast)
.collect(Collectors.toList());
- return new LogicalProject(projectExpressions, (Plan) childProject.children().get(0));
+ return new LogicalProject<>(projectExpressions, childProject.children().get(0));
}).toRule(RuleType.MERGE_CONSECUTIVE_PROJECTS);
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
index 4adcb94eeb..453f082fad 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Alias.java
@@ -55,6 +55,13 @@ public class Alias extends NamedExpression implements UnaryExpression {
this.qualifier = ImmutableList.of();
}
+ private Alias(ExprId exprId, Expression child, String name, List<String> qualifier) {
+ super(child);
+ this.exprId = exprId;
+ this.name = name;
+ this.qualifier = qualifier;
+ }
+
@Override
public Slot toSlot() throws UnboundException {
return new SlotReference(exprId, name, child().getDataType(), child().nullable(), qualifier);
@@ -121,6 +128,10 @@ public class Alias extends NamedExpression implements UnaryExpression {
return new Alias(exprId, children.get(0), name);
}
+ public Expression withQualifier(List<String> qualifier) {
+ return new Alias(this.exprId, this.child(0), this.name, qualifier);
+ }
+
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
return visitor.visitAlias(this, context);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java
index 482bb808fe..2caca9e25d 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionReplacer.java
@@ -32,9 +32,8 @@ public class ExpressionReplacer
public Expression visit(Expression expr, Map<Expression, Expression> substitutionMap) {
if (substitutionMap.containsKey(expr)) {
return substitutionMap.get(expr);
- } else {
- return super.visit(expr, substitutionMap);
}
+ return super.visit(expr, substitutionMap);
}
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ViewTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ViewTest.java
new file mode 100644
index 0000000000..3391c14b2d
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ViewTest.java
@@ -0,0 +1,162 @@
+// 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.
+
+package org.apache.doris.nereids.trees.expressions;
+
+import org.apache.doris.nereids.NereidsPlanner;
+import org.apache.doris.nereids.StatementContext;
+import org.apache.doris.nereids.glue.translator.PhysicalPlanTranslator;
+import org.apache.doris.nereids.glue.translator.PlanTranslatorContext;
+import org.apache.doris.nereids.parser.NereidsParser;
+import org.apache.doris.nereids.properties.PhysicalProperties;
+import org.apache.doris.nereids.rules.analysis.EliminateAliasNode;
+import org.apache.doris.nereids.rules.rewrite.logical.MergeConsecutiveProjects;
+import org.apache.doris.nereids.trees.plans.physical.PhysicalPlan;
+import org.apache.doris.nereids.util.MemoTestUtils;
+import org.apache.doris.nereids.util.PatternMatchSupported;
+import org.apache.doris.nereids.util.PlanChecker;
+import org.apache.doris.utframe.TestWithFeService;
+
+import com.google.common.collect.Lists;
+import org.junit.jupiter.api.Test;
+
+import java.util.List;
+
+public class ViewTest extends TestWithFeService implements PatternMatchSupported {
+
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("default_cluster:test");
+ createTables(
+ "CREATE TABLE IF NOT EXISTS T1 (\n"
+ + " ID1 bigint,\n"
+ + " SCORE1 bigint\n"
+ + ")\n"
+ + "DUPLICATE KEY(ID1)\n"
+ + "DISTRIBUTED BY HASH(ID1) BUCKETS 1\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ")\n",
+ "CREATE TABLE IF NOT EXISTS T2 (\n"
+ + " ID2 bigint,\n"
+ + " SCORE2 bigint\n"
+ + ")\n"
+ + "DUPLICATE KEY(ID2)\n"
+ + "DISTRIBUTED BY HASH(ID2) BUCKETS 1\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ")\n",
+ "CREATE TABLE IF NOT EXISTS T3 (\n"
+ + " ID3 bigint,\n"
+ + " SCORE3 bigint\n"
+ + ")\n"
+ + "DUPLICATE KEY(ID3)\n"
+ + "DISTRIBUTED BY HASH(ID3) BUCKETS 1\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ")\n"
+ );
+ createView("CREATE VIEW V1 AS SELECT * FROM T1");
+ createView("CREATE VIEW V2 AS SELECT * FROM T2");
+ createView("CREATE VIEW V3 AS SELECT * FROM T3 JOIN (SELECT * FROM V2) T ON T3.ID3 = T.ID2");
+ }
+
+ @Override
+ protected void runBeforeEach() throws Exception {
+ NamedExpressionUtil.clear();
+ }
+
+ @Test
+ public void testTranslateAllCase() throws Exception {
+
+ List<String> testSql = Lists.newArrayList(
+ "SELECT * FROM V1",
+ "SELECT * FROM V2",
+ "SELECT * FROM V3",
+ "SELECT * FROM T1 JOIN (SELECT * FROM V1) T ON T1.ID1 = T.ID1",
+ "SELECT * FROM T2 JOIN (SELECT * FROM V2) T ON T2.ID2 = T.ID2",
+ "SELECT Y.ID2 FROM (SELECT * FROM V3) Y",
+ "SELECT * FROM (SELECT * FROM V1 JOIN V2 ON V1.ID1 = V2.ID2) X JOIN (SELECT * FROM V1 JOIN V3 ON V1.ID1 = V3.ID2) Y ON X.ID1 = Y.ID3"
+ );
+
+ // check whether they can be translated.
+ for (String sql : testSql) {
+ NamedExpressionUtil.clear();
+ System.out.println("\n\n***** " + sql + " *****\n\n");
+ StatementContext statementContext = MemoTestUtils.createStatementContext(connectContext, sql);
+ PhysicalPlan plan = new NereidsPlanner(statementContext).plan(
+ new NereidsParser().parseSingle(sql),
+ PhysicalProperties.ANY
+ );
+ // Just to check whether translate will throw exception
+ new PhysicalPlanTranslator().translatePlan(plan, new PlanTranslatorContext());
+ }
+ }
+
+ @Test
+ public void testSimpleViewMergeProjects() {
+ PlanChecker.from(connectContext)
+ .analyze("SELECT * FROM V1")
+ .applyTopDown(new EliminateAliasNode())
+ .applyTopDown(new MergeConsecutiveProjects())
+ .matches(
+ logicalProject(
+ logicalOlapScan()
+ )
+ );
+ }
+
+ @Test
+ public void testNestedView() {
+ PlanChecker.from(connectContext)
+ .analyze("SELECT * FROM (SELECT * FROM V1 JOIN V2 ON V1.ID1 = V2.ID2) X JOIN (SELECT * FROM V1 JOIN V3 ON V1.ID1 = V3.ID2) Y ON X.ID1 = Y.ID3")
+ .applyTopDown(new EliminateAliasNode())
+ .applyTopDown(new MergeConsecutiveProjects())
+ .matches(
+ logicalProject(
+ logicalJoin(
+ logicalProject(
+ logicalJoin(
+ logicalProject(
+ logicalOlapScan()
+ ),
+ logicalProject(
+ logicalOlapScan()
+ )
+ )
+ ),
+ logicalProject(
+ logicalJoin(
+ logicalProject(
+ logicalOlapScan()
+ ),
+ logicalProject(
+ logicalJoin(
+ logicalOlapScan(),
+ logicalProject(
+ logicalOlapScan()
+ )
+ )
+ )
+ )
+ )
+ )
+ )
+ );
+ }
+}
diff --git a/regression-test/data/nereids_syntax_p0/inpredicate.out b/regression-test/data/nereids_syntax_p0/inpredicate.out
index 5cad1b30c0..d131768e01 100644
--- a/regression-test/data/nereids_syntax_p0/inpredicate.out
+++ b/regression-test/data/nereids_syntax_p0/inpredicate.out
@@ -16,3 +16,4 @@
15 Supplier#000000015 DF35PepL5saAK INDIA 0 INDIA ASIA 18-687-542-7601
29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342
9 Supplier#000000009 ,gJ6K2MKveYxQT IRAN 6 IRAN MIDDLE EAST 20-338-906-3675
+
diff --git a/regression-test/data/nereids_syntax_p0/view.out b/regression-test/data/nereids_syntax_p0/view.out
new file mode 100644
index 0000000000..64de8a53e6
--- /dev/null
+++ b/regression-test/data/nereids_syntax_p0/view.out
@@ -0,0 +1,31 @@
+-- This file is automatically generated. You should know what you did if you want to edit this
+-- !select_1 --
+1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY
+1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE
+1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING
+
+-- !select_2 --
+1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
+1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
+1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
+
+-- !select_3 --
+1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
+1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
+1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
+
+-- !select_4 --
+1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY 1303 Customer#000001303 fQ Lp,FoozZe1 ETHIOPIA 3 ETHIOPIA AFRICA 15-658-234-7985 MACHINERY
+1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE 1309 Customer#000001309 vQcJGUXPHMH2 5OWs1XUP0kx IRAN 2 IRAN MIDDLE EAST 20-821-905-5952 AUTOMOBILE
+1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING
+
+-- !select_5 --
+1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
+1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
+1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 1309892 1 1303 1432 15 19920517 3-MEDIUM 0 24 2959704 5119906 7 2752524 73992 0 19920619 TRUCK
+1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL 1309892 2 1303 1165 9 19920517 3-MEDIUM 0 21 2404899 5119906 8 2212507 68711 7 19920616 RAIL
+1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
+
+-- !select_6 --
+1455 magenta blush MFGR#1 MFGR#13 MFGR#1324 blue LARGE BRUSHED STEEL 42 SM PACK 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB 29 Supplier#000000029 VVSymB3fbwaN ARGENTINA4 ARGENTINA AMERICA 11-773-203-7342 1312 Customer#000001312 MVsKeqWejff8jQ30 CANADA 9 CANADA AMERICA 13-153-492-9898 BUILDING 1310179 6 1312 1455 29 19921110 3-MEDIUM 0 15 1705830 20506457 10 1535247 68233 8 19930114 FOB
+
diff --git a/regression-test/suites/nereids_syntax_p0/view.groovy b/regression-test/suites/nereids_syntax_p0/view.groovy
new file mode 100644
index 0000000000..29ca5a34f3
--- /dev/null
+++ b/regression-test/suites/nereids_syntax_p0/view.groovy
@@ -0,0 +1,99 @@
+// 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("view") {
+ sql """
+ SET enable_vectorized_engine=true
+ """
+
+ sql """
+ SET enable_nereids_planner=true
+ """
+
+ sql """
+ create view if not exists v1 as
+ select *
+ from customer
+ """
+
+ sql """
+ create view if not exists v2 as
+ select *
+ from lineorder
+ """
+
+ sql """
+ create view if not exists v3 as
+ select *
+ from v1 join (
+ select *
+ from v2
+ ) t
+ on v1.c_custkey = t.lo_custkey;
+ """
+
+ qt_select_1 """
+ select *
+ from v1
+ order by v1.c_custkey
+ """
+
+ qt_select_2 """
+ select *
+ from v2
+ order by v2.lo_custkey
+ """
+
+ qt_select_3 """
+ select *
+ from v3
+ order by v3.c_custkey, v3.lo_orderkey
+ """
+
+ qt_select_4 """
+ select *
+ from customer c join (
+ select *
+ from v1
+ ) t
+ on c.c_custkey = t.c_custkey
+ order by c.c_custkey, t.c_custkey
+ """
+
+ qt_select_5 """
+ select *
+ from lineorder l join (
+ select *
+ from v2
+ ) t
+ on l.lo_custkey = t.lo_custkey
+ order by l.lo_custkey, t.lo_custkey
+ """
+
+ qt_select_6 """
+ select * from (
+ select *
+ from part p
+ join v2 on p.p_partkey = v2.lo_partkey) t1
+ join (
+ select *
+ from supplier s
+ join v3 on s.s_region = v3.c_region) t2
+ on t1.p_partkey = t2.lo_partkey
+ order by t1.lo_custkey, t1.p_partkey, t2.s_suppkey, t2.c_custkey, t2.lo_orderkey
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org