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/07/26 04:30:18 UTC
[doris] branch master updated: [feature](nereids) Add subquery expression and In expression and TPCH… (#11129)
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 58795771f2 [feature](nereids) Add subquery expression and In expression and TPCH… (#11129)
58795771f2 is described below
commit 58795771f2b5e68ce55cf2dfd7c0f637943c2916
Author: zhengshiJ <32...@users.noreply.github.com>
AuthorDate: Tue Jul 26 12:30:12 2022 +0800
[feature](nereids) Add subquery expression and In expression and TPCH… (#11129)
1. Increase the expression of subquery and in.
2. Added tpch creation table and sql query, including original sql query and query rewritten by doris
3. Adjust the position of checkAnalyze
4. add exists subquery
---
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 4 +
.../doris/nereids/parser/LogicalPlanBuilder.java | 27 +
.../trees/expressions/{Not.java => Exists.java} | 50 +-
.../nereids/trees/expressions/InSubquery.java | 95 ++
.../doris/nereids/trees/expressions/Not.java | 7 +-
.../nereids/trees/expressions/SubqueryExpr.java | 127 +++
.../expressions/visitor/ExpressionVisitor.java | 15 +
.../apache/doris/nereids/ssb/AnalyzeSSBTest.java | 47 -
.../org/apache/doris/nereids/ssb/SSBTestBase.java | 4 +-
.../AnalyzeCheckTestBase.java} | 76 +-
.../apache/doris/nereids/tpch/AnalyzeTPCHTest.java | 191 ++++
.../SSBTestBase.java => tpch/TPCHTestBase.java} | 12 +-
.../org/apache/doris/nereids/tpch/TPCHUtils.java | 1127 ++++++++++++++++++++
.../trees/expressions/ExpressionParserTest.java | 30 +
.../nereids/trees/expressions/SubqueryTest.java | 62 ++
15 files changed, 1724 insertions(+), 150 deletions(-)
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4 b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index 5b254c8074..c3086488cc 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -64,6 +64,7 @@ queryTerm
queryPrimary
: querySpecification #queryPrimaryDefault
| TABLE multipartIdentifier #table
+ | LEFT_PAREN query RIGHT_PAREN #subquery
;
querySpecification
@@ -171,6 +172,7 @@ expression
booleanExpression
: NOT booleanExpression #logicalNot
+ | EXISTS LEFT_PAREN query RIGHT_PAREN #exist
| valueExpression predicate? #predicated
| left=booleanExpression operator=AND right=booleanExpression #logicalBinary
| left=booleanExpression operator=OR right=booleanExpression #logicalBinary
@@ -179,6 +181,8 @@ booleanExpression
predicate
: NOT? kind=BETWEEN lower=valueExpression AND upper=valueExpression
| NOT? kind=(LIKE | REGEXP) pattern=valueExpression
+ | NOT? kind=IN LEFT_PAREN expression (COMMA expression)* RIGHT_PAREN
+ | NOT? kind=IN LEFT_PAREN query RIGHT_PAREN
;
valueExpression
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 bc3f5cf222..2dcad23211 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
@@ -26,6 +26,7 @@ import org.apache.doris.nereids.DorisParser.BooleanLiteralContext;
import org.apache.doris.nereids.DorisParser.ColumnReferenceContext;
import org.apache.doris.nereids.DorisParser.ComparisonContext;
import org.apache.doris.nereids.DorisParser.DereferenceContext;
+import org.apache.doris.nereids.DorisParser.ExistContext;
import org.apache.doris.nereids.DorisParser.FromClauseContext;
import org.apache.doris.nereids.DorisParser.IdentifierListContext;
import org.apache.doris.nereids.DorisParser.IdentifierSeqContext;
@@ -52,6 +53,7 @@ import org.apache.doris.nereids.DorisParser.SingleStatementContext;
import org.apache.doris.nereids.DorisParser.SortItemContext;
import org.apache.doris.nereids.DorisParser.StarContext;
import org.apache.doris.nereids.DorisParser.StringLiteralContext;
+import org.apache.doris.nereids.DorisParser.SubqueryExpressionContext;
import org.apache.doris.nereids.DorisParser.TableNameContext;
import org.apache.doris.nereids.DorisParser.WhereClauseContext;
import org.apache.doris.nereids.DorisParserBaseVisitor;
@@ -69,9 +71,11 @@ import org.apache.doris.nereids.trees.expressions.BooleanLiteral;
import org.apache.doris.nereids.trees.expressions.CaseWhen;
import org.apache.doris.nereids.trees.expressions.Divide;
import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.GreaterThan;
import org.apache.doris.nereids.trees.expressions.GreaterThanEqual;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
import org.apache.doris.nereids.trees.expressions.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.LessThan;
import org.apache.doris.nereids.trees.expressions.LessThanEqual;
@@ -86,6 +90,7 @@ import org.apache.doris.nereids.trees.expressions.NullSafeEqual;
import org.apache.doris.nereids.trees.expressions.Or;
import org.apache.doris.nereids.trees.expressions.Regexp;
import org.apache.doris.nereids.trees.expressions.StringLiteral;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.expressions.Subtract;
import org.apache.doris.nereids.trees.expressions.WhenClause;
import org.apache.doris.nereids.trees.plans.JoinType;
@@ -704,6 +709,18 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
getExpression(ctx.pattern)
);
break;
+ case DorisParser.IN:
+ if (ctx.query() == null) {
+ //TODO: InPredicate
+ outExpression = null;
+ throw new IllegalStateException("Unsupported predicate type: " + ctx.kind.getText());
+ } else {
+ outExpression = new InSubquery(
+ valueExpression,
+ typedVisit(ctx.query())
+ );
+ }
+ break;
default:
throw new IllegalStateException("Unsupported predicate type: " + ctx.kind.getText());
}
@@ -724,4 +741,14 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
return namedExpressions;
});
}
+
+ @Override
+ public Expression visitSubqueryExpression(SubqueryExpressionContext subqueryExprCtx) {
+ return ParserUtils.withOrigin(subqueryExprCtx, () -> new SubqueryExpr(typedVisit(subqueryExprCtx.query())));
+ }
+
+ @Override
+ public Expression visitExist(ExistContext context) {
+ return ParserUtils.withOrigin(context, () -> new Exists(typedVisit(context.query())));
+ }
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
similarity index 61%
copy from fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java
copy to fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
index 5cfae089e7..1371e27054 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Exists.java
@@ -19,6 +19,9 @@ package org.apache.doris.nereids.trees.expressions;
import org.apache.doris.nereids.exceptions.UnboundException;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.types.BooleanType;
+import org.apache.doris.nereids.types.DataType;
import com.google.common.base.Preconditions;
@@ -26,22 +29,38 @@ import java.util.List;
import java.util.Objects;
/**
- * Not expression: not a.
+ * Exists subquery expression.
*/
-public class Not extends Expression implements UnaryExpression {
+public class Exists extends SubqueryExpr {
- public Not(Expression child) {
- super(child);
+ public Exists(LogicalPlan subquery) {
+ super(Objects.requireNonNull(subquery, "subquery can not be null"));
}
@Override
- public boolean nullable() throws UnboundException {
- return child().nullable();
+ public DataType getDataType() throws UnboundException {
+ return BooleanType.INSTANCE;
}
@Override
+ public String toSql() {
+ return "EXISTS (SUBQUERY) " + super.toSql();
+ }
+
+ @Override
+ public String toString() {
+ return "EXISTS (SUBQUERY) " + super.toString();
+ }
+
public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
- return visitor.visitNot(this, context);
+ return visitor.visitExistsSubquery(this, context);
+ }
+
+ @Override
+ public Expression withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ Preconditions.checkArgument(children.get(0) instanceof SubqueryExpr);
+ return new Exists(((SubqueryExpr) children.get(0)).getQueryPlan());
}
@Override
@@ -52,23 +71,12 @@ public class Not extends Expression implements UnaryExpression {
if (o == null || getClass() != o.getClass()) {
return false;
}
- Not other = (Not) o;
- return Objects.equals(child(), other.child());
+ Exists exists = (Exists) o;
+ return Objects.equals(this.queryPlan, exists.getQueryPlan());
}
@Override
public int hashCode() {
- return child().hashCode();
- }
-
- @Override
- public String toString() {
- return "( not " + child() + ")";
- }
-
- @Override
- public Not withChildren(List<Expression> children) {
- Preconditions.checkArgument(children.size() == 1);
- return new Not(children.get(0));
+ return Objects.hash(this.queryPlan);
}
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java
new file mode 100644
index 0000000000..697fec564e
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/InSubquery.java
@@ -0,0 +1,95 @@
+// 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.exceptions.UnboundException;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.types.BooleanType;
+import org.apache.doris.nereids.types.DataType;
+
+import com.google.common.base.Preconditions;
+
+import java.util.List;
+import java.util.Objects;
+
+/**
+ * In predicate expression.
+ */
+public class InSubquery extends SubqueryExpr implements BinaryExpression {
+ private Expression compareExpr;
+
+ public InSubquery(Expression compareExpression, LogicalPlan subquery) {
+ super(Objects.requireNonNull(subquery, "subquery can not be null"));
+ this.compareExpr = compareExpression;
+ }
+
+ @Override
+ public DataType getDataType() throws UnboundException {
+ return BooleanType.INSTANCE;
+ }
+
+ @Override
+ public boolean nullable() throws UnboundException {
+ return super.nullable() || this.compareExpr.nullable();
+ }
+
+ @Override
+ public String toSql() {
+ return this.compareExpr.toSql() + "IN (SUBQUERY) " + super.toSql();
+ }
+
+ @Override
+ public String toString() {
+ return this.compareExpr + "IN (SUBQUERY) " + super.toString();
+ }
+
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitInSubquery(this, context);
+ }
+
+ public Expression getCompareExpr() {
+ return this.compareExpr;
+ }
+
+ @Override
+ public Expression withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 2);
+ Preconditions.checkArgument(children.get(0) instanceof Expression);
+ Preconditions.checkArgument(children.get(1) instanceof SubqueryExpr);
+ return new InSubquery(children.get(0), ((SubqueryExpr) children.get(1)).getQueryPlan());
+ }
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ InSubquery inSubquery = (InSubquery) o;
+ return Objects.equals(this.compareExpr, inSubquery.getCompareExpr())
+ && Objects.equals(this.queryPlan, inSubquery.getQueryPlan());
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(this.compareExpr, this.queryPlan);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java
index 5cfae089e7..80cef67b33 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/Not.java
@@ -63,7 +63,12 @@ public class Not extends Expression implements UnaryExpression {
@Override
public String toString() {
- return "( not " + child() + ")";
+ return "( not " + child().toString() + ")";
+ }
+
+ @Override
+ public String toSql() {
+ return "( not " + child().toSql() + ")";
}
@Override
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
new file mode 100644
index 0000000000..f987a1a2bf
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/SubqueryExpr.java
@@ -0,0 +1,127 @@
+// 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.exceptions.UnboundException;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.nereids.types.DataType;
+
+import com.google.common.base.Preconditions;
+
+import java.util.List;
+import java.util.Objects;
+
+/**
+ * Subquery Expression.
+ */
+public class SubqueryExpr extends Expression {
+ protected LogicalPlan queryPlan;
+
+ public SubqueryExpr(LogicalPlan subquery) {
+ this.queryPlan = Objects.requireNonNull(subquery, "subquery can not be null");
+ }
+
+ @Override
+ public DataType getDataType() throws UnboundException {
+ // TODO:
+ // Returns the data type of the row on a single line
+ // For multiple lines, struct type is returned, in the form of splicing,
+ // but it seems that struct type is not currently supported
+ throw new UnboundException("not support");
+ }
+
+ @Override
+ public boolean nullable() throws UnboundException {
+ // TODO:
+ // Any child is nullable, the whole is nullable
+ throw new UnboundException("not support");
+ }
+
+ @Override
+ public String toSql() {
+ return "(" + queryPlan.toString() + ")";
+ }
+
+ @Override
+ public String toString() {
+ return "(" + queryPlan.toString() + ")";
+ }
+
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitSubqueryExpr(this, context);
+ }
+
+ public LogicalPlan getQueryPlan() {
+ return queryPlan;
+ }
+
+ @Override
+ public Expression withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return children.get(0);
+ }
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ SubqueryExpr other = (SubqueryExpr) o;
+ return checkEquals(queryPlan, other.queryPlan);
+ }
+
+ /**
+ * Compare whether all logical nodes under query are the same.
+ * @param i original query.
+ * @param o compared query.
+ * @return equal ? true : false;
+ */
+ private boolean checkEquals(Object i, Object o) {
+ if (!(i instanceof LogicalPlan) || !(o instanceof LogicalPlan)) {
+ return false;
+ }
+ LogicalPlan other = (LogicalPlan) o;
+ LogicalPlan input = (LogicalPlan) i;
+ if (other.children().size() != input.children().size()) {
+ return false;
+ }
+ boolean equal;
+ for (int j = 0; j < input.children().size(); j++) {
+ LogicalPlan childInput = (LogicalPlan) input.child(j);
+ LogicalPlan childOther = (LogicalPlan) other.child(j);
+ equal = Objects.equals(childInput, childOther);
+ if (!equal) {
+ return false;
+ }
+ if (childInput.children().size() != childOther.children().size()) {
+ return false;
+ }
+ checkEquals(childInput, childOther);
+ }
+ return true;
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(queryPlan);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
index e884dc9cec..95ae89512a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ExpressionVisitor.java
@@ -33,9 +33,11 @@ import org.apache.doris.nereids.trees.expressions.CompoundPredicate;
import org.apache.doris.nereids.trees.expressions.Divide;
import org.apache.doris.nereids.trees.expressions.DoubleLiteral;
import org.apache.doris.nereids.trees.expressions.EqualTo;
+import org.apache.doris.nereids.trees.expressions.Exists;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.GreaterThan;
import org.apache.doris.nereids.trees.expressions.GreaterThanEqual;
+import org.apache.doris.nereids.trees.expressions.InSubquery;
import org.apache.doris.nereids.trees.expressions.IntegerLiteral;
import org.apache.doris.nereids.trees.expressions.LessThan;
import org.apache.doris.nereids.trees.expressions.LessThanEqual;
@@ -53,6 +55,7 @@ import org.apache.doris.nereids.trees.expressions.Slot;
import org.apache.doris.nereids.trees.expressions.SlotReference;
import org.apache.doris.nereids.trees.expressions.StringLiteral;
import org.apache.doris.nereids.trees.expressions.StringRegexPredicate;
+import org.apache.doris.nereids.trees.expressions.SubqueryExpr;
import org.apache.doris.nereids.trees.expressions.Subtract;
import org.apache.doris.nereids.trees.expressions.WhenClause;
import org.apache.doris.nereids.trees.expressions.functions.AggregateFunction;
@@ -205,6 +208,18 @@ public abstract class ExpressionVisitor<R, C> {
return visit(caseWhen, context);
}
+ public R visitInSubquery(InSubquery in, C context) {
+ return visitSubqueryExpr(in, context);
+ }
+
+ public R visitExistsSubquery(Exists exists, C context) {
+ return visitSubqueryExpr(exists, context);
+ }
+
+ public R visitSubqueryExpr(SubqueryExpr subqueryExpr, C context) {
+ return visit(subqueryExpr, context);
+ }
+
/* ********************************************************************************************
* Unbound expressions
* ********************************************************************************************/
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java
index 3b5c639e8f..6065f2159f 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java
@@ -17,17 +17,8 @@
package org.apache.doris.nereids.ssb;
-import org.apache.doris.nereids.analyzer.Unbound;
-import org.apache.doris.nereids.rules.rewrite.logical.TestAnalyzer;
-import org.apache.doris.nereids.trees.expressions.Expression;
-import org.apache.doris.nereids.trees.plans.Plan;
-import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
-
-import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
-import java.util.List;
-
public class AnalyzeSSBTest extends SSBTestBase {
/**
* TODO: check bound plan and expression details.
@@ -96,42 +87,4 @@ public class AnalyzeSSBTest extends SSBTestBase {
public void q4_3() {
checkAnalyze(SSBUtils.Q4_3);
}
-
- private void checkAnalyze(String sql) {
- LogicalPlan analyzed = new TestAnalyzer(connectContext).analyze(sql);
- Assertions.assertTrue(checkBound(analyzed));
- }
-
- /**
- * PlanNode and its expressions are all bound.
- */
- private boolean checkBound(LogicalPlan plan) {
- if (plan instanceof Unbound) {
- return false;
- }
-
- List<Plan> children = plan.children();
- for (Plan child : children) {
- if (!checkBound((LogicalPlan) child)) {
- return false;
- }
- }
-
- List<Expression> expressions = plan.getExpressions();
- return expressions.stream().allMatch(this::checkExpressionBound);
- }
-
- private boolean checkExpressionBound(Expression expr) {
- if (expr instanceof Unbound) {
- return false;
- }
-
- List<Expression> children = expr.children();
- for (Expression child : children) {
- if (!checkExpressionBound(child)) {
- return false;
- }
- }
- return true;
- }
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java
index 140ba64897..87a620e0e1 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java
@@ -17,9 +17,9 @@
package org.apache.doris.nereids.ssb;
-import org.apache.doris.utframe.TestWithFeService;
+import org.apache.doris.nereids.tpch.AnalyzeCheckTestBase;
-public abstract class SSBTestBase extends TestWithFeService {
+public abstract class SSBTestBase extends AnalyzeCheckTestBase {
@Override
protected void runBeforeAll() throws Exception {
createDatabase("test");
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeCheckTestBase.java
similarity index 63%
copy from fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java
copy to fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeCheckTestBase.java
index 3b5c639e8f..099bce18ea 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/AnalyzeSSBTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeCheckTestBase.java
@@ -15,89 +15,21 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.nereids.ssb;
+package org.apache.doris.nereids.tpch;
import org.apache.doris.nereids.analyzer.Unbound;
import org.apache.doris.nereids.rules.rewrite.logical.TestAnalyzer;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.plans.Plan;
import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+import org.apache.doris.utframe.TestWithFeService;
import org.junit.jupiter.api.Assertions;
-import org.junit.jupiter.api.Test;
import java.util.List;
-public class AnalyzeSSBTest extends SSBTestBase {
- /**
- * TODO: check bound plan and expression details.
- */
- @Test
- public void q1_1() {
- checkAnalyze(SSBUtils.Q1_1);
- }
-
- @Test
- public void q1_2() {
- checkAnalyze(SSBUtils.Q1_2);
- }
-
- @Test
- public void q1_3() {
- checkAnalyze(SSBUtils.Q1_3);
- }
-
- @Test
- public void q2_1() {
- checkAnalyze(SSBUtils.Q2_1);
- }
-
- @Test
- public void q2_2() {
- checkAnalyze(SSBUtils.Q2_2);
- }
-
- @Test
- public void q2_3() {
- checkAnalyze(SSBUtils.Q2_3);
- }
-
- @Test
- public void q3_1() {
- checkAnalyze(SSBUtils.Q3_1);
- }
-
- @Test
- public void q3_2() {
- checkAnalyze(SSBUtils.Q3_2);
- }
-
- @Test
- public void q3_3() {
- checkAnalyze(SSBUtils.Q3_3);
- }
-
- @Test
- public void q3_4() {
- checkAnalyze(SSBUtils.Q3_4);
- }
-
- @Test
- public void q4_1() {
- checkAnalyze(SSBUtils.Q4_1);
- }
-
- @Test
- public void q4_2() {
- checkAnalyze(SSBUtils.Q4_2);
- }
-
- @Test
- public void q4_3() {
- checkAnalyze(SSBUtils.Q4_3);
- }
-
- private void checkAnalyze(String sql) {
+public abstract class AnalyzeCheckTestBase extends TestWithFeService {
+ protected void checkAnalyze(String sql) {
LogicalPlan analyzed = new TestAnalyzer(connectContext).analyze(sql);
Assertions.assertTrue(checkBound(analyzed));
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeTPCHTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeTPCHTest.java
new file mode 100644
index 0000000000..d4185d67d7
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/AnalyzeTPCHTest.java
@@ -0,0 +1,191 @@
+// 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.tpch;
+
+/**
+ * There are still many functions that have not been implemented,
+ * so the tpch cannot be fully parsed, and the interface is only reserved here.
+ * When the related functions of tpch are supported, the comments will be deleted and the analyze of tpch will be verified.
+ */
+public class AnalyzeTPCHTest extends TPCHTestBase {
+ /*
+ @Test
+ public void q1() {
+ checkAnalyze(TPCHUtils.Q1);
+ }
+
+ @Test
+ public void q2() {
+ checkAnalyze(TPCHUtils.Q2);
+ }
+
+ @Test
+ public void q2_rewrite() {
+ checkAnalyze(TPCHUtils.Q2_rewrite);
+ }
+
+ @Test
+ public void q3() {
+ checkAnalyze(TPCHUtils.Q3);
+ }
+
+ @Test
+ public void q3_rewrite() {
+ checkAnalyze(TPCHUtils.Q3_rewrite);
+ }
+
+ @Test
+ public void q4() {
+ checkAnalyze(TPCHUtils.Q4);
+ }
+
+ @Test
+ public void q4_rewrite() {
+ checkAnalyze(TPCHUtils.Q4_rewrite);
+ }
+
+ @Test
+ public void q5() {
+ checkAnalyze(TPCHUtils.Q5);
+ }
+
+ @Test
+ public void q6() {
+ checkAnalyze(TPCHUtils.Q6);
+ }
+
+ @Test
+ public void q7() {
+ checkAnalyze(TPCHUtils.Q7);
+ }
+
+ @Test
+ public void q8() {
+ checkAnalyze(TPCHUtils.Q8);
+ }
+
+ @Test
+ public void q9() {
+ checkAnalyze(TPCHUtils.Q9);
+ }
+
+ @Test
+ public void q10() {
+ checkAnalyze(TPCHUtils.Q10);
+ }
+
+ @Test
+ public void q11() {
+ checkAnalyze(TPCHUtils.Q11);
+ }
+
+ @Test
+ public void q12() {
+ checkAnalyze(TPCHUtils.Q12);
+ }
+
+ @Test
+ public void q12_rewrite() {
+ checkAnalyze(TPCHUtils.Q12_rewrite);
+ }
+
+ @Test
+ public void q13() {
+ checkAnalyze(TPCHUtils.Q13);
+ }
+
+ @Test
+ public void q14() {
+ checkAnalyze(TPCHUtils.Q14);
+ }
+
+ @Test
+ public void q14_rewrite() {
+ checkAnalyze(TPCHUtils.Q14_rewrite);
+ }
+
+ @Test
+ public void q15() {
+ checkAnalyze(TPCHUtils.Q15);
+ }
+
+ @Test
+ public void q15_rewrite() {
+ checkAnalyze(TPCHUtils.Q15_rewrite);
+ }
+
+ @Test
+ public void q16() {
+ checkAnalyze(TPCHUtils.Q16);
+ }
+
+ @Test
+ public void q17() {
+ checkAnalyze(TPCHUtils.Q17);
+ }
+
+ @Test
+ public void q17_rewrite() {
+ checkAnalyze(TPCHUtils.Q17_rewrite);
+ }
+
+ @Test
+ public void q18() {
+ checkAnalyze(TPCHUtils.Q18);
+ }
+
+ @Test
+ public void q18_rewrite() {
+ checkAnalyze(TPCHUtils.Q18_rewrite);
+ }
+
+ @Test
+ public void q19() {
+ checkAnalyze(TPCHUtils.Q19);
+ }
+
+ @Test
+ public void q20() {
+ checkAnalyze(TPCHUtils.Q20);
+ }
+
+ @Test
+ public void q20_rewrite() {
+ checkAnalyze(TPCHUtils.Q20_rewrite);
+ }
+
+ @Test
+ public void q21() {
+ checkAnalyze(TPCHUtils.Q21);
+ }
+
+ @Test
+ public void q21_rewrite() {
+ checkAnalyze(TPCHUtils.Q21_rewrite);
+ }
+
+ @Test
+ public void q22() {
+ checkAnalyze(TPCHUtils.Q22);
+ }
+
+ @Test
+ public void q22_rewrite() {
+ checkAnalyze(TPCHUtils.Q22_rewrite);
+ }*/
+}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHTestBase.java
similarity index 75%
copy from fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java
copy to fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHTestBase.java
index 140ba64897..64df88841d 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/ssb/SSBTestBase.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHTestBase.java
@@ -15,15 +15,13 @@
// specific language governing permissions and limitations
// under the License.
-package org.apache.doris.nereids.ssb;
+package org.apache.doris.nereids.tpch;
-import org.apache.doris.utframe.TestWithFeService;
-
-public abstract class SSBTestBase extends TestWithFeService {
+public abstract class TPCHTestBase extends AnalyzeCheckTestBase {
@Override
protected void runBeforeAll() throws Exception {
- createDatabase("test");
- connectContext.setDatabase("default_cluster:test");
- SSBUtils.createTables(this);
+ createDatabase("tpch");
+ connectContext.setDatabase("default_cluster:tpch");
+ TPCHUtils.createTables(this);
}
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHUtils.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHUtils.java
new file mode 100644
index 0000000000..5bc571cf97
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/tpch/TPCHUtils.java
@@ -0,0 +1,1127 @@
+// 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.tpch;
+
+import org.apache.doris.utframe.TestWithFeService;
+
+public class TPCHUtils {
+
+ public static final String Q1 = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=false, enable_projection=false) */\n"
+ + " l_returnflag,\n"
+ + " l_linestatus,\n"
+ + " sum(l_quantity) as sum_qty,\n"
+ + " sum(l_extendedprice) as sum_base_price,\n"
+ + " sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,\n"
+ + " sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,\n"
+ + " avg(l_quantity) as avg_qty,\n"
+ + " avg(l_extendedprice) as avg_price,\n"
+ + " avg(l_discount) as avg_disc,\n"
+ + " count(*) as count_order\n"
+ + "from\n"
+ + " lineitem\n"
+ + "where\n"
+ + " l_shipdate <= date '1998-12-01' - interval '90' day\n"
+ + "group by\n"
+ + " l_returnflag,\n"
+ + " l_linestatus\n"
+ + "order by\n"
+ + " l_returnflag,\n"
+ + " l_linestatus;";
+
+ public static final String Q2 = "select\n"
+ + " s_acctbal,\n"
+ + " s_name,\n"
+ + " n_name,\n"
+ + " p_partkey,\n"
+ + " p_mfgr,\n"
+ + " s_address,\n"
+ + " s_phone,\n"
+ + " s_comment\n"
+ + "from\n"
+ + " part,\n"
+ + " supplier,\n"
+ + " partsupp,\n"
+ + " nation,\n"
+ + " region\n"
+ + "where\n"
+ + " p_partkey = ps_partkey\n"
+ + " and s_suppkey = ps_suppkey\n"
+ + " and p_size = 15\n"
+ + " and p_type like '%BRASS'\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_regionkey = r_regionkey\n"
+ + " and r_name = 'EUROPE'\n"
+ + " and ps_supplycost = (\n"
+ + " select\n"
+ + " min(ps_supplycost)\n"
+ + " from\n"
+ + " partsupp,\n"
+ + " supplier,\n"
+ + " nation,\n"
+ + " region\n"
+ + " where\n"
+ + " p_partkey = ps_partkey\n"
+ + " and s_suppkey = ps_suppkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_regionkey = r_regionkey\n"
+ + " and r_name = 'EUROPE'\n"
+ + " )\n"
+ + "order by\n"
+ + " s_acctbal desc,\n"
+ + " n_name,\n"
+ + " s_name,\n"
+ + " p_partkey\n"
+ + "limit 100;";
+
+ public static final String Q2_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */\n"
+ + " s_acctbal,\n"
+ + " s_name,\n"
+ + " n_name,\n"
+ + " p_partkey,\n"
+ + " p_mfgr,\n"
+ + " s_address,\n"
+ + " s_phone,\n"
+ + " s_comment\n"
+ + "from\n"
+ + "partsupp,\n"
+ + "(\n"
+ + " select ps_partkey, min(ps_supplycost) as ps_s from\n"
+ + " partsupp, supplier, nation, region\n"
+ + " where s_suppkey = ps_suppkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_regionkey = r_regionkey\n"
+ + " and r_name = 'EUROPE'\n"
+ + " group by ps_partkey\n"
+ + ") t1,\n"
+ + "supplier,\n"
+ + "part,\n"
+ + "nation,\n"
+ + "region\n"
+ + "where p_partkey = t1.ps_partkey\n"
+ + " and p_partkey = partsupp.ps_partkey\n"
+ + " and s_suppkey = ps_suppkey\n"
+ + " and p_size = 15\n"
+ + " and p_type like '%BRASS'\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_regionkey = r_regionkey\n"
+ + " and r_name = 'EUROPE'\n"
+ + " and ps_supplycost = t1.ps_s\n"
+ + "order by\n"
+ + " s_acctbal desc,\n"
+ + " n_name,\n"
+ + " s_name,\n"
+ + " p_partkey\n"
+ + "limit 100;";
+
+ public static String Q3 = "select\n"
+ + " l_orderkey,\n"
+ + " sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
+ + " o_orderdate,\n"
+ + " o_shippriority\n"
+ + "from\n"
+ + " customer,\n"
+ + " orders,\n"
+ + " lineitem\n"
+ + "where\n"
+ + " c_mktsegment = 'BUILDING'\n"
+ + " and c_custkey = o_custkey\n"
+ + " and l_orderkey = o_orderkey\n"
+ + " and o_orderdate < date '1995-03-15'\n"
+ + " and l_shipdate > date '1995-03-15'\n"
+ + "group by\n"
+ + " l_orderkey,\n"
+ + " o_orderdate,\n"
+ + " o_shippriority\n"
+ + "order by\n"
+ + " revenue desc,\n"
+ + " o_orderdate\n"
+ + "limit 10;";
+
+ public static String Q3_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */\n"
+ + " l_orderkey,\n"
+ + " sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
+ + " o_orderdate,\n"
+ + " o_shippriority\n"
+ + "from\n"
+ + " (\n"
+ + " select l_orderkey, l_extendedprice, l_discount, o_orderdate, o_shippriority, o_custkey from\n"
+ + " lineitem join orders\n"
+ + " where l_orderkey = o_orderkey\n"
+ + " and o_orderdate < date '1995-03-15'\n"
+ + " and l_shipdate > date '1995-03-15'\n"
+ + " ) t1 join customer c \n"
+ + " on c.c_custkey = t1.o_custkey\n"
+ + " where c_mktsegment = 'BUILDING'\n"
+ + "group by\n"
+ + " l_orderkey,\n"
+ + " o_orderdate,\n"
+ + " o_shippriority\n"
+ + "order by\n"
+ + " revenue desc,\n"
+ + " o_orderdate\n"
+ + "limit 10;";
+
+ public static String Q4 = "select\n"
+ + " o_orderpriority,\n"
+ + " count(*) as order_count\n"
+ + "from\n"
+ + " orders\n"
+ + "where\n"
+ + " o_orderdate >= date '1993-07-01'\n"
+ + " and o_orderdate < date '1993-10-01'\n"
+ + " and exists (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " lineitem\n"
+ + " where\n"
+ + " l_orderkey = o_orderkey\n"
+ + " and l_commitdate < l_receiptdate\n"
+ + " )\n"
+ + "group by\n"
+ + " o_orderpriority\n"
+ + "order by\n"
+ + " o_orderpriority;";
+
+ public static String Q4_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=false, enable_projection=true) */\n"
+ + " o_orderpriority,\n"
+ + " count(*) as order_count\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " lineitem\n"
+ + " where l_commitdate < l_receiptdate\n"
+ + " ) t1\n"
+ + " right semi join orders\n"
+ + " on t1.l_orderkey = o_orderkey\n"
+ + "where\n"
+ + " o_orderdate >= date '1993-07-01'\n"
+ + " and o_orderdate < date '1993-07-01' + interval '3' month\n"
+ + "group by\n"
+ + " o_orderpriority\n"
+ + "order by\n"
+ + " o_orderpriority;";
+
+ public static String Q5 = "select\n"
+ + " n_name,\n"
+ + " sum(l_extendedprice * (1 - l_discount)) as revenue\n"
+ + "from\n"
+ + " customer,\n"
+ + " orders,\n"
+ + " lineitem,\n"
+ + " supplier,\n"
+ + " nation,\n"
+ + " region\n"
+ + "where\n"
+ + " c_custkey = o_custkey\n"
+ + " and l_orderkey = o_orderkey\n"
+ + " and l_suppkey = s_suppkey\n"
+ + " and c_nationkey = s_nationkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_regionkey = r_regionkey\n"
+ + " and r_name = 'ASIA'\n"
+ + " and o_orderdate >= date '1994-01-01'\n"
+ + " and o_orderdate < date '1995-01-01'\n"
+ + "group by\n"
+ + " n_name\n"
+ + "order by\n"
+ + " revenue desc;";
+
+ public static String Q6 = "select\n"
+ + " sum(l_extendedprice * l_discount) as revenue\n"
+ + "from\n"
+ + " lineitem\n"
+ + "where\n"
+ + " l_shipdate >= date '1994-01-01'\n"
+ + " and l_shipdate < date '1995-01-01'\n"
+ + " and l_discount between 0.06 - 0.01 and 0.06 + 0.01\n"
+ + " and l_quantity < 24;";
+
+ public static String Q7 = "select\n"
+ + " supp_nation,\n"
+ + " cust_nation,\n"
+ + " l_year,\n"
+ + " sum(volume) as revenue\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " n1.n_name as supp_nation,\n"
+ + " n2.n_name as cust_nation,\n"
+ + " extract(year from l_shipdate) as l_year,\n"
+ + " l_extendedprice * (1 - l_discount) as volume\n"
+ + " from\n"
+ + " supplier,\n"
+ + " lineitem,\n"
+ + " orders,\n"
+ + " customer,\n"
+ + " nation n1,\n"
+ + " nation n2\n"
+ + " where\n"
+ + " s_suppkey = l_suppkey\n"
+ + " and o_orderkey = l_orderkey\n"
+ + " and c_custkey = o_custkey\n"
+ + " and s_nationkey = n1.n_nationkey\n"
+ + " and c_nationkey = n2.n_nationkey\n"
+ + " and (\n"
+ + " (n1.n_name = 'FRANCE' and n2.n_name = 'GERMANY')\n"
+ + " or (n1.n_name = 'GERMANY' and n2.n_name = 'FRANCE')\n"
+ + " )\n"
+ + " and l_shipdate between date '1995-01-01' and date '1996-12-31'\n"
+ + " ) as shipping\n"
+ + "group by\n"
+ + " supp_nation,\n"
+ + " cust_nation,\n"
+ + " l_year\n"
+ + "order by\n"
+ + " supp_nation,\n"
+ + " cust_nation,\n"
+ + " l_year;";
+
+ public static final String Q8 = "select\n"
+ + " o_year,\n"
+ + " sum(case\n"
+ + " when nation = 'BRAZIL' then volume\n"
+ + " else 0\n"
+ + " end) / sum(volume) as mkt_share\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " extract(year from o_orderdate) as o_year,\n"
+ + " l_extendedprice * (1 - l_discount) as volume,\n"
+ + " n2.n_name as nation\n"
+ + " from\n"
+ + " part,\n"
+ + " supplier,\n"
+ + " lineitem,\n"
+ + " orders,\n"
+ + " customer,\n"
+ + " nation n1,\n"
+ + " nation n2,\n"
+ + " region\n"
+ + " where\n"
+ + " p_partkey = l_partkey\n"
+ + " and s_suppkey = l_suppkey\n"
+ + " and l_orderkey = o_orderkey\n"
+ + " and o_custkey = c_custkey\n"
+ + " and c_nationkey = n1.n_nationkey\n"
+ + " and n1.n_regionkey = r_regionkey\n"
+ + " and r_name = 'AMERICA'\n"
+ + " and s_nationkey = n2.n_nationkey\n"
+ + " and o_orderdate between date '1995-01-01' and date '1996-12-31'\n"
+ + " and p_type = 'ECONOMY ANODIZED STEEL'\n"
+ + " ) as all_nations\n"
+ + "group by\n"
+ + " o_year\n"
+ + "order by\n"
+ + " o_year;";
+
+ public static final String Q9 = "select\n"
+ + " nation,\n"
+ + " o_year,\n"
+ + " sum(amount) as sum_profit\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " n_name as nation,\n"
+ + " extract(year from o_orderdate) as o_year,\n"
+ + " l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount\n"
+ + " from\n"
+ + " part,\n"
+ + " supplier,\n"
+ + " lineitem,\n"
+ + " partsupp,\n"
+ + " orders,\n"
+ + " nation\n"
+ + " where\n"
+ + " s_suppkey = l_suppkey\n"
+ + " and ps_suppkey = l_suppkey\n"
+ + " and ps_partkey = l_partkey\n"
+ + " and p_partkey = l_partkey\n"
+ + " and o_orderkey = l_orderkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and p_name like '%green%'\n"
+ + " ) as profit\n"
+ + "group by\n"
+ + " nation,\n"
+ + " o_year\n"
+ + "order by\n"
+ + " nation,\n"
+ + " o_year desc;";
+
+ public static final String Q10 = "select\n"
+ + " c_custkey,\n"
+ + " c_name,\n"
+ + " sum(l_extendedprice * (1 - l_discount)) as revenue,\n"
+ + " c_acctbal,\n"
+ + " n_name,\n"
+ + " c_address,\n"
+ + " c_phone,\n"
+ + " c_comment\n"
+ + "from\n"
+ + " customer,\n"
+ + " orders,\n"
+ + " lineitem,\n"
+ + " nation\n"
+ + "where\n"
+ + " c_custkey = o_custkey\n"
+ + " and l_orderkey = o_orderkey\n"
+ + " and o_orderdate >= date '1993-10-01'\n"
+ + " and o_orderdate < date '1994-01-01'\n"
+ + " and l_returnflag = 'R'\n"
+ + " and c_nationkey = n_nationkey\n"
+ + "group by\n"
+ + " c_custkey,\n"
+ + " c_name,\n"
+ + " c_acctbal,\n"
+ + " c_phone,\n"
+ + " n_name,\n"
+ + " c_address,\n"
+ + " c_comment\n"
+ + "order by\n"
+ + " revenue desc\n"
+ + "limit 20;";
+
+ public static final String Q11 = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " ps_partkey,\n"
+ + " sum(ps_supplycost * ps_availqty) as value\n"
+ + "from\n"
+ + " partsupp,\n"
+ + " supplier,\n"
+ + " nation\n"
+ + "where\n"
+ + " ps_suppkey = s_suppkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_name = 'GERMANY'\n"
+ + "group by\n"
+ + " ps_partkey having\n"
+ + " sum(ps_supplycost * ps_availqty) > (\n"
+ + " select\n"
+ + " sum(ps_supplycost * ps_availqty) * 0.0001000000\n"
+ + " from\n"
+ + " partsupp,\n"
+ + " supplier,\n"
+ + " nation\n"
+ + " where\n"
+ + " ps_suppkey = s_suppkey\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_name = 'GERMANY'\n"
+ + " )\n"
+ + "order by\n"
+ + " value desc;";
+ public static final String Q12 = "select\n"
+ + " l_shipmode,\n"
+ + " sum(case\n"
+ + " when o_orderpriority = '1-URGENT'\n"
+ + " or o_orderpriority = '2-HIGH'\n"
+ + " then 1\n"
+ + " else 0\n"
+ + " end) as high_line_count,\n"
+ + " sum(case\n"
+ + " when o_orderpriority <> '1-URGENT'\n"
+ + " and o_orderpriority <> '2-HIGH'\n"
+ + " then 1\n"
+ + " else 0\n"
+ + " end) as low_line_count\n"
+ + "from\n"
+ + " orders,\n"
+ + " lineitem\n"
+ + "where\n"
+ + " o_orderkey = l_orderkey\n"
+ + " and l_shipmode in ('MAIL', 'SHIP')\n"
+ + " and l_commitdate < l_receiptdate\n"
+ + " and l_shipdate < l_commitdate\n"
+ + " and l_receiptdate >= date '1994-01-01'\n"
+ + " and l_receiptdate < date '1995-01-01'\n"
+ + "group by\n"
+ + " l_shipmode\n"
+ + "order by\n"
+ + " l_shipmode;";
+
+ public static final String Q12_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " l_shipmode,\n"
+ + " sum(case\n"
+ + " when o_orderpriority = '1-URGENT'\n"
+ + " or o_orderpriority = '2-HIGH'\n"
+ + " then 1\n"
+ + " else 0\n"
+ + " end) as high_line_count,\n"
+ + " sum(case\n"
+ + " when o_orderpriority <> '1-URGENT'\n"
+ + " and o_orderpriority <> '2-HIGH'\n"
+ + " then 1\n"
+ + " else 0\n"
+ + " end) as low_line_count\n"
+ + "from\n"
+ + " orders,\n"
+ + " lineitem\n"
+ + "where\n"
+ + " o_orderkey = l_orderkey\n"
+ + " and l_shipmode in ('MAIL', 'SHIP')\n"
+ + " and l_commitdate < l_receiptdate\n"
+ + " and l_shipdate < l_commitdate\n"
+ + " and l_receiptdate >= date '1994-01-01'\n"
+ + " and l_receiptdate < date '1994-01-01' + interval '1' year\n"
+ + "group by\n"
+ + " l_shipmode\n"
+ + "order by\n"
+ + " l_shipmode;";
+
+ public static final String Q13 = "select\n"
+ + " c_count,\n"
+ + " count(*) as custdist\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " c_custkey,\n"
+ + " count(o_orderkey) c_count\n"
+ + " from\n"
+ + " customer left outer join orders on\n"
+ + " c_custkey = o_custkey\n"
+ + " and o_comment not like '%special%requests%'\n"
+ + " group by\n"
+ + " c_custkey\n"
+ + " ) as c_orders\n"
+ + "group by\n"
+ + " c_count\n"
+ + "order by\n"
+ + " custdist desc,\n"
+ + " c_count desc;";
+
+ public static final String Q14 = "select\n"
+ + " 100.00 * sum(case\n"
+ + " when p_type like 'PROMO%'\n"
+ + " then l_extendedprice * (1 - l_discount)\n"
+ + " else 0\n"
+ + " end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n"
+ + "from\n"
+ + " lineitem,\n"
+ + " part\n"
+ + "where\n"
+ + " l_partkey = p_partkey\n"
+ + " and l_shipdate >= date '1995-09-01'\n"
+ + " and l_shipdate < date '1995-10-01';";
+
+ public static final String Q14_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " 100.00 * sum(case\n"
+ + " when p_type like 'PROMO%'\n"
+ + " then l_extendedprice * (1 - l_discount)\n"
+ + " else 0\n"
+ + " end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue\n"
+ + "from\n"
+ + " part,\n"
+ + " lineitem\n"
+ + "where\n"
+ + " l_partkey = p_partkey\n"
+ + " and l_shipdate >= date '1995-09-01'\n"
+ + " and l_shipdate < date '1995-09-01' + interval '1' month;\n";
+
+ public static final String Q15 = "with revenue as (\n"
+ + "\tselect\n"
+ + "\t\tl_suppkey as supplier_no,\n"
+ + "\t\tsum(l_extendedprice * (1 - l_discount)) as total_revenue\n"
+ + "\tfrom\n"
+ + "\t\tlineitem\n"
+ + "\twhere\n"
+ + "\t\tl_shipdate >= date '1996-01-01'\n"
+ + "\t\tand l_shipdate < date '1996-04-01'\n"
+ + "\tgroup by\n"
+ + "\t\tl_suppkey)\n"
+ + "select\n"
+ + "\ts_suppkey,\n"
+ + "\ts_name,\n"
+ + "\ts_address,\n"
+ + "\ts_phone,\n"
+ + "\ttotal_revenue\n"
+ + "from\n"
+ + "\tsupplier,\n"
+ + "\trevenue\n"
+ + "where\n"
+ + "\ts_suppkey = supplier_no\n"
+ + "\tand total_revenue = (\n"
+ + "\t\tselect\n"
+ + "\t\t\tmax(total_revenue)\n"
+ + "\t\tfrom\n"
+ + "\t\t\trevenue\n"
+ + "\t)\n"
+ + "order by\n"
+ + "\ts_suppkey;";
+
+ public static final String Q15_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " s_suppkey,\n"
+ + " s_name,\n"
+ + " s_address,\n"
+ + " s_phone,\n"
+ + " total_revenue\n"
+ + "from\n"
+ + " supplier,\n"
+ + " revenue0\n"
+ + "where\n"
+ + " s_suppkey = supplier_no\n"
+ + " and total_revenue = (\n"
+ + " select\n"
+ + " max(total_revenue)\n"
+ + " from\n"
+ + " revenue0\n"
+ + " )\n"
+ + "order by\n"
+ + " s_suppkey;";
+
+ public static final String Q16 = "select\n"
+ + " p_brand,\n"
+ + " p_type,\n"
+ + " p_size,\n"
+ + " count(distinct ps_suppkey) as supplier_cnt\n"
+ + "from\n"
+ + " partsupp,\n"
+ + " part\n"
+ + "where\n"
+ + " p_partkey = ps_partkey\n"
+ + " and p_brand <> 'Brand#45'\n"
+ + " and p_type not like 'MEDIUM POLISHED%'\n"
+ + " and p_size in (49, 14, 23, 45, 19, 3, 36, 9)\n"
+ + " and ps_suppkey not in (\n"
+ + " select\n"
+ + " s_suppkey\n"
+ + " from\n"
+ + " supplier\n"
+ + " where\n"
+ + " s_comment like '%Customer%Complaints%'\n"
+ + " )\n"
+ + "group by\n"
+ + " p_brand,\n"
+ + " p_type,\n"
+ + " p_size\n"
+ + "order by\n"
+ + " supplier_cnt desc,\n"
+ + " p_brand,\n"
+ + " p_type,\n"
+ + " p_size;";
+
+ public static final String Q17 = "select\n"
+ + " sum(l_extendedprice) / 7.0 as avg_yearly\n"
+ + "from\n"
+ + " lineitem,\n"
+ + " part\n"
+ + "where\n"
+ + " p_partkey = l_partkey\n"
+ + " and p_brand = 'Brand#23'\n"
+ + " and p_container = 'MED BOX'\n"
+ + " and l_quantity < (\n"
+ + " select\n"
+ + " 0.2 * avg(l_quantity)\n"
+ + " from\n"
+ + " lineitem\n"
+ + " where\n"
+ + " l_partkey = p_partkey\n"
+ + " );";
+
+ public static final String Q17_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=1, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " sum(l_extendedprice) / 7.0 as avg_yearly\n"
+ + "from\n"
+ + " lineitem join [broadcast]\n"
+ + " part p1 on p1.p_partkey = l_partkey\n"
+ + "where\n"
+ + " p1.p_brand = 'Brand#23'\n"
+ + " and p1.p_container = 'MED BOX'\n"
+ + " and l_quantity < (\n"
+ + " select\n"
+ + " 0.2 * avg(l_quantity)\n"
+ + " from\n"
+ + " lineitem join [broadcast]\n"
+ + " part p2 on p2.p_partkey = l_partkey\n"
+ + " where\n"
+ + " l_partkey = p1.p_partkey\n"
+ + " and p2.p_brand = 'Brand#23'\n"
+ + " and p2.p_container = 'MED BOX'\n"
+ + " );";
+
+ public static final String Q18 = "select\n"
+ + " c_name,\n"
+ + " c_custkey,\n"
+ + " o_orderkey,\n"
+ + " o_orderdate,\n"
+ + " o_totalprice,\n"
+ + " sum(l_quantity)\n"
+ + "from\n"
+ + " customer,\n"
+ + " orders,\n"
+ + " lineitem\n"
+ + "where\n"
+ + " o_orderkey in (\n"
+ + " select\n"
+ + " l_orderkey\n"
+ + " from\n"
+ + " lineitem\n"
+ + " group by\n"
+ + " l_orderkey having\n"
+ + " sum(l_quantity) > 300\n"
+ + " )\n"
+ + " and c_custkey = o_custkey\n"
+ + " and o_orderkey = l_orderkey\n"
+ + "group by\n"
+ + " c_name,\n"
+ + " c_custkey,\n"
+ + " o_orderkey,\n"
+ + " o_orderdate,\n"
+ + " o_totalprice\n"
+ + "order by\n"
+ + " o_totalprice desc,\n"
+ + " o_orderdate\n"
+ + "limit 100;";
+
+ public static final String Q18_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " c_name,\n"
+ + " c_custkey,\n"
+ + " t3.o_orderkey,\n"
+ + " t3.o_orderdate,\n"
+ + " t3.o_totalprice,\n"
+ + " sum(t3.l_quantity)\n"
+ + "from\n"
+ + "customer join\n"
+ + "(\n"
+ + " select * from\n"
+ + " lineitem join\n"
+ + " (\n"
+ + " select * from\n"
+ + " orders left semi join\n"
+ + " (\n"
+ + " select\n"
+ + " l_orderkey\n"
+ + " from\n"
+ + " lineitem\n"
+ + " group by\n"
+ + " l_orderkey having sum(l_quantity) > 300\n"
+ + " ) t1\n"
+ + " on o_orderkey = t1.l_orderkey\n"
+ + " ) t2\n"
+ + " on t2.o_orderkey = l_orderkey\n"
+ + ") t3\n"
+ + "on c_custkey = t3.o_custkey\n"
+ + "group by\n"
+ + " c_name,\n"
+ + " c_custkey,\n"
+ + " t3.o_orderkey,\n"
+ + " t3.o_orderdate,\n"
+ + " t3.o_totalprice\n"
+ + "order by\n"
+ + " t3.o_totalprice desc,\n"
+ + " t3.o_orderdate\n"
+ + "limit 100;";
+
+ public static final String Q19 = "select\n"
+ + " sum(l_extendedprice* (1 - l_discount)) as revenue\n"
+ + "from\n"
+ + " lineitem,\n"
+ + " part\n"
+ + "where\n"
+ + " (\n"
+ + " p_partkey = l_partkey\n"
+ + " and p_brand = 'Brand#12'\n"
+ + " and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')\n"
+ + " and l_quantity >= 1 and l_quantity <= 1 + 10\n"
+ + " and p_size between 1 and 5\n"
+ + " and l_shipmode in ('AIR', 'AIR REG')\n"
+ + " and l_shipinstruct = 'DELIVER IN PERSON'\n"
+ + " )\n"
+ + " or\n"
+ + " (\n"
+ + " p_partkey = l_partkey\n"
+ + " and p_brand = 'Brand#23'\n"
+ + " and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')\n"
+ + " and l_quantity >= 10 and l_quantity <= 10 + 10\n"
+ + " and p_size between 1 and 10\n"
+ + " and l_shipmode in ('AIR', 'AIR REG')\n"
+ + " and l_shipinstruct = 'DELIVER IN PERSON'\n"
+ + " )\n"
+ + " or\n"
+ + " (\n"
+ + " p_partkey = l_partkey\n"
+ + " and p_brand = 'Brand#34'\n"
+ + " and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')\n"
+ + " and l_quantity >= 20 and l_quantity <= 20 + 10\n"
+ + " and p_size between 1 and 15\n"
+ + " and l_shipmode in ('AIR', 'AIR REG')\n"
+ + " and l_shipinstruct = 'DELIVER IN PERSON'\n"
+ + " );";
+
+ public static final String Q20 = "select\n"
+ + " s_name,\n"
+ + " s_address\n"
+ + "from\n"
+ + " supplier,\n"
+ + " nation\n"
+ + "where\n"
+ + " s_suppkey in (\n"
+ + " select\n"
+ + " ps_suppkey\n"
+ + " from\n"
+ + " partsupp\n"
+ + " where\n"
+ + " ps_partkey in (\n"
+ + " select\n"
+ + " p_partkey\n"
+ + " from\n"
+ + " part\n"
+ + " where\n"
+ + " p_name like 'forest%'\n"
+ + " )\n"
+ + " and ps_availqty > (\n"
+ + " select\n"
+ + " 0.5 * sum(l_quantity)\n"
+ + " from\n"
+ + " lineitem\n"
+ + " where\n"
+ + " l_partkey = ps_partkey\n"
+ + " and l_suppkey = ps_suppkey\n"
+ + " and l_shipdate >= date '1994-01-01'\n"
+ + " and l_shipdate < date '1995-01-01'\n"
+ + " )\n"
+ + " )\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_name = 'CANADA'\n"
+ + "order by\n"
+ + " s_name;";
+
+ public static final String Q20_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + "s_name, s_address from\n"
+ + "supplier left semi join\n"
+ + "(\n"
+ + " select * from\n"
+ + " (\n"
+ + " select l_partkey,l_suppkey, 0.5 * sum(l_quantity) as l_q\n"
+ + " from lineitem\n"
+ + " where l_shipdate >= date '1994-01-01'\n"
+ + " and l_shipdate < date '1994-01-01' + interval '1' year\n"
+ + " group by l_partkey,l_suppkey\n"
+ + " ) t2 join\n"
+ + " (\n"
+ + " select ps_partkey, ps_suppkey, ps_availqty\n"
+ + " from partsupp left semi join part\n"
+ + " on ps_partkey = p_partkey and p_name like 'forest%'\n"
+ + " ) t1\n"
+ + " on t2.l_partkey = t1.ps_partkey and t2.l_suppkey = t1.ps_suppkey\n"
+ + " and t1.ps_availqty > t2.l_q\n"
+ + ") t3\n"
+ + "on s_suppkey = t3.ps_suppkey\n"
+ + "join nation\n"
+ + "where s_nationkey = n_nationkey\n"
+ + " and n_name = 'CANADA'\n"
+ + "order by s_name;";
+
+ public static final String Q21 = "select\n"
+ + " s_name,\n"
+ + " count(*) as numwait\n"
+ + "from\n"
+ + " supplier,\n"
+ + " lineitem l1,\n"
+ + " orders,\n"
+ + " nation\n"
+ + "where\n"
+ + " s_suppkey = l1.l_suppkey\n"
+ + " and o_orderkey = l1.l_orderkey\n"
+ + " and o_orderstatus = 'F'\n"
+ + " and l1.l_receiptdate > l1.l_commitdate\n"
+ + " and exists (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " lineitem l2\n"
+ + " where\n"
+ + " l2.l_orderkey = l1.l_orderkey\n"
+ + " and l2.l_suppkey <> l1.l_suppkey\n"
+ + " )\n"
+ + " and not exists (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " lineitem l3\n"
+ + " where\n"
+ + " l3.l_orderkey = l1.l_orderkey\n"
+ + " and l3.l_suppkey <> l1.l_suppkey\n"
+ + " and l3.l_receiptdate > l3.l_commitdate\n"
+ + " )\n"
+ + " and s_nationkey = n_nationkey\n"
+ + " and n_name = 'SAUDI ARABIA'\n"
+ + "group by\n"
+ + " s_name\n"
+ + "order by\n"
+ + " numwait desc,\n"
+ + " s_name\n"
+ + "limit 100;";
+
+ public static final String Q21_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=true, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + "s_name, count(*) as numwait\n"
+ + "from orders join\n"
+ + "(\n"
+ + " select * from\n"
+ + " lineitem l2 right semi join\n"
+ + " (\n"
+ + " select * from\n"
+ + " lineitem l3 right anti join\n"
+ + " (\n"
+ + " select * from\n"
+ + " lineitem l1 join\n"
+ + " (\n"
+ + " select * from\n"
+ + " supplier join nation\n"
+ + " where s_nationkey = n_nationkey\n"
+ + " and n_name = 'SAUDI ARABIA'\n"
+ + " ) t1\n"
+ + " where t1.s_suppkey = l1.l_suppkey and l1.l_receiptdate > l1.l_commitdate\n"
+ + " ) t2\n"
+ + " on l3.l_orderkey = t2.l_orderkey and l3.l_suppkey <> t2.l_suppkey and l3.l_receiptdate > l3.l_commitdate\n"
+ + " ) t3\n"
+ + " on l2.l_orderkey = t3.l_orderkey and l2.l_suppkey <> t3.l_suppkey\n"
+ + ") t4\n"
+ + "on o_orderkey = t4.l_orderkey and o_orderstatus = 'F'\n"
+ + "group by\n"
+ + " t4.s_name\n"
+ + "order by\n"
+ + " numwait desc,\n"
+ + " t4.s_name\n"
+ + "limit 100;";
+
+ public static final String Q22 = "select\n"
+ + " cntrycode,\n"
+ + " count(*) as numcust,\n"
+ + " sum(c_acctbal) as totacctbal\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " substring(c_phone from 1 for 2) as cntrycode,\n"
+ + " c_acctbal\n"
+ + " from\n"
+ + " customer\n"
+ + " where\n"
+ + " substring(c_phone from 1 for 2) in\n"
+ + " ('13', '31', '23', '29', '30', '18', '17')\n"
+ + " and c_acctbal > (\n"
+ + " select\n"
+ + " avg(c_acctbal)\n"
+ + " from\n"
+ + " customer\n"
+ + " where\n"
+ + " c_acctbal > 0.00\n"
+ + " and substring(c_phone from 1 for 2) in\n"
+ + " ('13', '31', '23', '29', '30', '18', '17')\n"
+ + " )\n"
+ + " and not exists (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " orders\n"
+ + " where\n"
+ + " o_custkey = c_custkey\n"
+ + " )\n"
+ + " ) as custsale\n"
+ + "group by\n"
+ + " cntrycode\n"
+ + "order by\n"
+ + " cntrycode;";
+
+ public static final String Q22_rewrite = "select /*+SET_VAR(exec_mem_limit=8589934592, parallel_fragment_exec_instance_num=8, enable_vectorized_engine=true, batch_size=4096, disable_join_reorder=false, enable_cost_based_join_reorder=true, enable_projection=true) */\n"
+ + " cntrycode,\n"
+ + " count(*) as numcust,\n"
+ + " sum(c_acctbal) as totacctbal\n"
+ + "from\n"
+ + " (\n"
+ + " select\n"
+ + " substring(c_phone, 1, 2) as cntrycode,\n"
+ + " c_acctbal\n"
+ + " from\n"
+ + " customer\n"
+ + " where\n"
+ + " substring(c_phone, 1, 2) in\n"
+ + " ('13', '31', '23', '29', '30', '18', '17')\n"
+ + " and c_acctbal > (\n"
+ + " select\n"
+ + " avg(c_acctbal)\n"
+ + " from\n"
+ + " customer\n"
+ + " where\n"
+ + " c_acctbal > 0.00\n"
+ + " and substring(c_phone, 1, 2) in\n"
+ + " ('13', '31', '23', '29', '30', '18', '17')\n"
+ + " )\n"
+ + " and not exists (\n"
+ + " select\n"
+ + " *\n"
+ + " from\n"
+ + " orders\n"
+ + " where\n"
+ + " o_custkey = c_custkey\n"
+ + " )\n"
+ + " ) as custsale\n"
+ + "group by\n"
+ + " cntrycode\n"
+ + "order by\n"
+ + " cntrycode;";
+
+ public static void createTables(TestWithFeService service) throws Exception {
+ service.createTable("CREATE TABLE lineitem (\n"
+ + " l_shipdate DATE NOT NULL,\n"
+ + " l_orderkey bigint NOT NULL,\n"
+ + " l_linenumber int not null,\n"
+ + " l_partkey int NOT NULL,\n"
+ + " l_suppkey int not null,\n"
+ + " l_quantity decimal(15, 2) NOT NULL,\n"
+ + " l_extendedprice decimal(15, 2) NOT NULL,\n"
+ + " l_discount decimal(15, 2) NOT NULL,\n"
+ + " l_tax decimal(15, 2) NOT NULL,\n"
+ + " l_returnflag VARCHAR(1) NOT NULL,\n"
+ + " l_linestatus VARCHAR(1) NOT NULL,\n"
+ + " l_commitdate DATE NOT NULL,\n"
+ + " l_receiptdate DATE NOT NULL,\n"
+ + " l_shipinstruct VARCHAR(25) NOT NULL,\n"
+ + " l_shipmode VARCHAR(10) NOT NULL,\n"
+ + " l_comment VARCHAR(44) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`l_shipdate`, `l_orderkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`l_orderkey`) BUCKETS 96\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\",\n"
+ + " \"colocate_with\" = \"lineitem_orders\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE orders (\n"
+ + " o_orderkey bigint NOT NULL,\n"
+ + " o_orderdate DATE NOT NULL,\n"
+ + " o_custkey int NOT NULL,\n"
+ + " o_orderstatus VARCHAR(1) NOT NULL,\n"
+ + " o_totalprice decimal(15, 2) NOT NULL,\n"
+ + " o_orderpriority VARCHAR(15) NOT NULL,\n"
+ + " o_clerk VARCHAR(15) NOT NULL,\n"
+ + " o_shippriority int NOT NULL,\n"
+ + " o_comment VARCHAR(79) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`o_orderkey`, `o_orderdate`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`o_orderkey`) BUCKETS 96\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\",\n"
+ + " \"colocate_with\" = \"lineitem_orders\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE partsupp (\n"
+ + " ps_partkey int NOT NULL,\n"
+ + " ps_suppkey int NOT NULL,\n"
+ + " ps_availqty int NOT NULL,\n"
+ + " ps_supplycost decimal(15, 2) NOT NULL,\n"
+ + " ps_comment VARCHAR(199) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`ps_partkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`ps_partkey`) BUCKETS 24\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\",\n"
+ + " \"colocate_with\" = \"part_partsupp\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE part (\n"
+ + " p_partkey int NOT NULL,\n"
+ + " p_name VARCHAR(55) NOT NULL,\n"
+ + " p_mfgr VARCHAR(25) NOT NULL,\n"
+ + " p_brand VARCHAR(10) NOT NULL,\n"
+ + " p_type VARCHAR(25) NOT NULL,\n"
+ + " p_size int NOT NULL,\n"
+ + " p_container VARCHAR(10) NOT NULL,\n"
+ + " p_retailprice decimal(15, 2) NOT NULL,\n"
+ + " p_comment VARCHAR(23) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`p_partkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`p_partkey`) BUCKETS 24\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\",\n"
+ + " \"colocate_with\" = \"part_partsupp\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE customer (\n"
+ + " c_custkey int NOT NULL,\n"
+ + " c_name VARCHAR(25) NOT NULL,\n"
+ + " c_address VARCHAR(40) NOT NULL,\n"
+ + " c_nationkey int NOT NULL,\n"
+ + " c_phone VARCHAR(15) NOT NULL,\n"
+ + " c_acctbal decimal(15, 2) NOT NULL,\n"
+ + " c_mktsegment VARCHAR(10) NOT NULL,\n"
+ + " c_comment VARCHAR(117) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`c_custkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 24\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE supplier (\n"
+ + " s_suppkey int NOT NULL,\n"
+ + " s_name VARCHAR(25) NOT NULL,\n"
+ + " s_address VARCHAR(40) NOT NULL,\n"
+ + " s_nationkey int NOT NULL,\n"
+ + " s_phone VARCHAR(15) NOT NULL,\n"
+ + " s_acctbal decimal(15, 2) NOT NULL,\n"
+ + " s_comment VARCHAR(101) NOT NULL\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`s_suppkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`s_suppkey`) BUCKETS 12\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE `nation` (\n"
+ + " `n_nationkey` int(11) NOT NULL,\n"
+ + " `n_name` varchar(25) NOT NULL,\n"
+ + " `n_regionkey` int(11) NOT NULL,\n"
+ + " `n_comment` varchar(152) NULL\n"
+ + ") ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`N_NATIONKEY`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`N_NATIONKEY`) BUCKETS 1\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ");");
+
+ service.createTable("CREATE TABLE region (\n"
+ + " r_regionkey int NOT NULL,\n"
+ + " r_name VARCHAR(25) NOT NULL,\n"
+ + " r_comment VARCHAR(152)\n"
+ + ")ENGINE=OLAP\n"
+ + "DUPLICATE KEY(`r_regionkey`)\n"
+ + "COMMENT \"OLAP\"\n"
+ + "DISTRIBUTED BY HASH(`r_regionkey`) BUCKETS 1\n"
+ + "PROPERTIES (\n"
+ + " \"replication_num\" = \"1\"\n"
+ + ");");
+
+ service.createView("create view revenue0 (supplier_no, total_revenue) as\n"
+ + "select\n"
+ + " l_suppkey,\n"
+ + " sum(l_extendedprice * (1 - l_discount))\n"
+ + "from\n"
+ + " lineitem\n"
+ + "where\n"
+ + " l_shipdate >= date '1996-01-01'\n"
+ + " and l_shipdate < date '1996-01-01' + interval '3' month\n"
+ + "group by\n"
+ + " l_suppkey;");
+ }
+}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ExpressionParserTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ExpressionParserTest.java
index 7b238fa2de..fafee04bc6 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ExpressionParserTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/ExpressionParserTest.java
@@ -146,4 +146,34 @@ public class ExpressionParserTest {
String caseWhen2 = "select case when a = 1 then 2 else 3 end from test";
assertSql(caseWhen2);
}
+
+ @Test
+ public void testInSubquery() throws Exception {
+ String in = "select * from test where a in (select * from test1 where a = 0)";
+ assertSql(in);
+
+ String inExpr = "a in (select * from test where b = 1)";
+ assertExpr(inExpr);
+
+ String notIn = "select * from test where a not in (select * from test1 where a = 0)";
+ assertSql(notIn);
+
+ String notInExpr = "a not in (select * from test where b = 1)";
+ assertExpr(notInExpr);
+ }
+
+ @Test
+ public void testExist() throws Exception {
+ String exist = "select * from test where exists (select * from test where a = 1)";
+ assertSql(exist);
+
+ String existExpr = "exists (select * from test where b = 1)";
+ assertExpr(existExpr);
+
+ String notExist = "select * from test where not exists (select * from test where a = 1)";
+ assertSql(notExist);
+
+ String notExistExpr = "not exists (select * from test where b = 1)";
+ assertExpr(notExistExpr);
+ }
}
diff --git a/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java
new file mode 100644
index 0000000000..5a8f071684
--- /dev/null
+++ b/fe/fe-core/src/test/java/org/apache/doris/nereids/trees/expressions/SubqueryTest.java
@@ -0,0 +1,62 @@
+// 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.parser.NereidsParser;
+import org.apache.doris.nereids.tpch.AnalyzeCheckTestBase;
+import org.apache.doris.nereids.trees.plans.logical.LogicalPlan;
+
+import org.junit.jupiter.api.Test;
+
+public class SubqueryTest extends AnalyzeCheckTestBase {
+ @Override
+ protected void runBeforeAll() throws Exception {
+ createDatabase("test");
+ connectContext.setDatabase("default_cluster:test");
+ String t0 = "create table t0("
+ + "id int, \n"
+ + "k1 int, \n"
+ + "k2 int, \n"
+ + "v1 int, \n"
+ + "v2 int)\n"
+ + "distributed by hash(k1) buckets 1\n"
+ + "properties('replication_num' = '1');";
+
+ String t1 = "create table t1("
+ + "id int, \n"
+ + "k1 int, \n"
+ + "v2 int)\n"
+ + "distributed by hash(k1) buckets 1\n"
+ + "properties('replication_num' = '1');";
+ createTables(t0, t1);
+ }
+
+ @Test
+ public void test() {
+ String sql = "select t0.k1\n"
+ + "from t0\n"
+ + "where t0.k2 in\n"
+ + " (select id\n"
+ + " from t1\n"
+ + " where t0.k2=t1.k1)";
+ NereidsParser parser = new NereidsParser();
+ LogicalPlan parsed = parser.parseSingle(sql);
+ assert parsed != null;
+ //checkAnalyze(sql);
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org