This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 1b4cd24b360 [opt](Nereids) support where, group by, having, order by
clause without from clause in query statement (#27006)
1b4cd24b360 is described below
commit 1b4cd24b360e225c6621b69eb55db36d6e1d1ab8
Author: seawinde <[email protected]>
AuthorDate: Mon Nov 27 12:05:14 2023 +0800
[opt](Nereids) support where, group by, having, order by clause without
from clause in query statement (#27006)
Support where, group by, having, order by clause without from clause in
query statement.
For example as following:
SELECT 1 AS a, COUNT(), SUM(2), AVG(1), RANK() OVER() AS w_rank
WHERE 1 = 1
GROUP BY a, w_rank
HAVING COUNT() IN (1, 2) AND w_rank = 1
ORDER BY a;
this will return result:
| a |count(*)|sum(2)|avg(1)|w_rank|
+----+--------+------+------+------+
| 1 | 1| 2| 1.0| 1|
For another example as following:
select 1 c1, 2 union (select "hell0", "") order by c1
the second column datatype will be varchar(65533), 65533 is the default
varchar length.
this will return result:
|c1 | 2 |
+------+---+
|1 | 2 |
|hell0 | |
---
.../main/java/org/apache/doris/catalog/ScalarType.java | 8 +++++++-
.../doris/nereids/parser/LogicalPlanBuilder.java | 18 +++++++++---------
.../doris/nereids/rules/analysis/BindExpression.java | 6 ++++++
.../select_no_from/sql/withGroupOrderHaving.out | 10 ++++++++++
.../nereids_p0/select_no_from/sql/withWhereClause.out | 7 +++++++
.../nereids_p0/select_no_from/sql/withWhereFalse.out | 7 +++++++
.../select_no_from/sql/withGroupOrderHaving.out | 10 ++++++++++
.../query_p0/select_no_from/sql/withWhereClause.out | 7 +++++++
.../query_p0/select_no_from/sql/withWhereFalse.out | 7 +++++++
.../conditional_functions/test_if_cast.out | 2 +-
.../select_no_from/sql/withGroupOrderHaving.sql | 10 +++++++---
.../nereids_p0/select_no_from/sql/withWhereClause.sql | 5 ++---
.../nereids_p0/select_no_from/sql/withWhereFalse.sql | 5 ++---
.../suites/nereids_syntax_p0/bind_priority.groovy | 2 +-
.../select_no_from/sql/withGroupOrderHaving.sql | 10 +++++++---
.../query_p0/select_no_from/sql/withWhereClause.sql | 5 ++---
.../query_p0/select_no_from/sql/withWhereFalse.sql | 5 ++---
17 files changed, 94 insertions(+), 30 deletions(-)
diff --git
a/fe/fe-common/src/main/java/org/apache/doris/catalog/ScalarType.java
b/fe/fe-common/src/main/java/org/apache/doris/catalog/ScalarType.java
index 79e9d4b4ab8..a4972d9ced7 100644
--- a/fe/fe-common/src/main/java/org/apache/doris/catalog/ScalarType.java
+++ b/fe/fe-common/src/main/java/org/apache/doris/catalog/ScalarType.java
@@ -1060,7 +1060,13 @@ public class ScalarType extends Type {
if (t1.type == PrimitiveType.STRING || t2.type ==
PrimitiveType.STRING) {
return createStringType();
}
- return createVarcharType(Math.max(t1.len, t2.len));
+ int minLength = Math.min(t1.len, t2.len);
+ if (minLength < 0) {
+ // If < 0 which means max length, use firstly
+ return createVarcharType(minLength);
+ }
+ int length = Math.max(t1.len, t2.len);
+ return createVarcharType(length == 0 ? MAX_VARCHAR_LENGTH :
length);
}
if (((t1.isDecimalV3() || t1.isDecimalV2()) && (t2.isDateV2() ||
t2.isDate()))
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 a1118525b8a..9a8fe4bba27 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
@@ -992,22 +992,22 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
return ParserUtils.withOrigin(ctx, () -> {
SelectClauseContext selectCtx = ctx.selectClause();
LogicalPlan selectPlan;
+ LogicalPlan relation;
if (ctx.fromClause() == null) {
SelectColumnClauseContext columnCtx =
selectCtx.selectColumnClause();
if (columnCtx.EXCEPT() != null) {
throw new ParseException("select-except cannot be used in
one row relation", selectCtx);
}
- selectPlan = withOneRowRelation(columnCtx);
+ relation = withOneRowRelation(columnCtx);
} else {
- LogicalPlan relation = visitFromClause(ctx.fromClause());
- selectPlan = withSelectQuerySpecification(
- ctx, relation,
- selectCtx,
- Optional.ofNullable(ctx.whereClause()),
- Optional.ofNullable(ctx.aggClause()),
- Optional.ofNullable(ctx.havingClause())
- );
+ relation = visitFromClause(ctx.fromClause());
}
+ selectPlan = withSelectQuerySpecification(
+ ctx, relation,
+ selectCtx,
+ Optional.ofNullable(ctx.whereClause()),
+ Optional.ofNullable(ctx.aggClause()),
+ Optional.ofNullable(ctx.havingClause()));
selectPlan = withQueryOrganization(selectPlan,
ctx.queryOrganization());
return withSelectHint(selectPlan, selectCtx.selectHint());
});
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
index 9f267cd4712..dd967984c3e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/BindExpression.java
@@ -446,6 +446,12 @@ public class BindExpression implements AnalysisRuleFactory
{
LogicalCTEAnchor<Plan, Plan> cteAnchor = sort.child();
return bindSort(sort, cteAnchor, ctx.cascadesContext);
})
+ ), RuleType.BINDING_SORT_SLOT.build(
+ logicalSort(logicalOneRowRelation()).thenApply(ctx -> {
+ LogicalSort<LogicalOneRowRelation> sort = ctx.root;
+ LogicalOneRowRelation oneRowRelation = sort.child();
+ return bindSort(sort, oneRowRelation, ctx.cascadesContext);
+ })
),
RuleType.BINDING_SORT_SET_OPERATION_SLOT.build(
logicalSort(logicalSetOperation()).thenApply(ctx -> {
diff --git
a/regression-test/data/nereids_p0/select_no_from/sql/withGroupOrderHaving.out
b/regression-test/data/nereids_p0/select_no_from/sql/withGroupOrderHaving.out
new file mode 100644
index 00000000000..636cae7e3f0
--- /dev/null
+++
b/regression-test/data/nereids_p0/select_no_from/sql/withGroupOrderHaving.out
@@ -0,0 +1,10 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withGroupOrderHaving --
+0
+
+-- !withGroupOrderHaving_2 --
+10 3
+
+-- !withGroupOrderHaving_3 --
+1 1 2 1.0 1
+
diff --git
a/regression-test/data/nereids_p0/select_no_from/sql/withWhereClause.out
b/regression-test/data/nereids_p0/select_no_from/sql/withWhereClause.out
new file mode 100644
index 00000000000..0b6d028a244
--- /dev/null
+++ b/regression-test/data/nereids_p0/select_no_from/sql/withWhereClause.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withWhereClause --
+0
+
+-- !withWhereClause_2 --
+1
+
diff --git
a/regression-test/data/nereids_p0/select_no_from/sql/withWhereFalse.out
b/regression-test/data/nereids_p0/select_no_from/sql/withWhereFalse.out
new file mode 100644
index 00000000000..bd030d90fae
--- /dev/null
+++ b/regression-test/data/nereids_p0/select_no_from/sql/withWhereFalse.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withWhereFalse --
+0
+
+-- !withWhereFalse_2 --
+0 1
+
diff --git
a/regression-test/data/query_p0/select_no_from/sql/withGroupOrderHaving.out
b/regression-test/data/query_p0/select_no_from/sql/withGroupOrderHaving.out
new file mode 100644
index 00000000000..636cae7e3f0
--- /dev/null
+++ b/regression-test/data/query_p0/select_no_from/sql/withGroupOrderHaving.out
@@ -0,0 +1,10 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withGroupOrderHaving --
+0
+
+-- !withGroupOrderHaving_2 --
+10 3
+
+-- !withGroupOrderHaving_3 --
+1 1 2 1.0 1
+
diff --git
a/regression-test/data/query_p0/select_no_from/sql/withWhereClause.out
b/regression-test/data/query_p0/select_no_from/sql/withWhereClause.out
new file mode 100644
index 00000000000..0b6d028a244
--- /dev/null
+++ b/regression-test/data/query_p0/select_no_from/sql/withWhereClause.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withWhereClause --
+0
+
+-- !withWhereClause_2 --
+1
+
diff --git
a/regression-test/data/query_p0/select_no_from/sql/withWhereFalse.out
b/regression-test/data/query_p0/select_no_from/sql/withWhereFalse.out
new file mode 100644
index 00000000000..bd030d90fae
--- /dev/null
+++ b/regression-test/data/query_p0/select_no_from/sql/withWhereFalse.out
@@ -0,0 +1,7 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !withWhereFalse --
+0
+
+-- !withWhereFalse_2 --
+0 1
+
diff --git
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_if_cast.out
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_if_cast.out
index cf64c4da4ac..1fb288a7636 100644
---
a/regression-test/data/query_p0/sql_functions/conditional_functions/test_if_cast.out
+++
b/regression-test/data/query_p0/sql_functions/conditional_functions/test_if_cast.out
@@ -15,5 +15,5 @@
["1970-01-01"]
-- !select --
-[null]
+[NULL]
diff --git
a/regression-test/suites/nereids_p0/select_no_from/sql/withGroupOrderHaving.sql
b/regression-test/suites/nereids_p0/select_no_from/sql/withGroupOrderHaving.sql
index 679d8220402..868e42d59ab 100644
---
a/regression-test/suites/nereids_p0/select_no_from/sql/withGroupOrderHaving.sql
+++
b/regression-test/suites/nereids_p0/select_no_from/sql/withGroupOrderHaving.sql
@@ -1,4 +1,8 @@
-/*
-- database: presto; groups: no_from
-SELECT MIN(10), 3 as col1 GROUP BY 2 HAVING 6 > 5 ORDER BY 1
-*/
+SET enable_nereids_planner = TRUE;
+SELECT MIN(10), 3 as col1 GROUP BY 2 HAVING 6 > 5 ORDER BY 1;
+SELECT 1 AS a, COUNT(*), SUM(2), AVG(1), RANK() OVER() AS w_rank
+WHERE 1 = 1
+GROUP BY a, w_rank
+HAVING COUNT(*) IN (1, 2) AND w_rank = 1
+ORDER BY a;
\ No newline at end of file
diff --git
a/regression-test/suites/nereids_p0/select_no_from/sql/withWhereClause.sql
b/regression-test/suites/nereids_p0/select_no_from/sql/withWhereClause.sql
index 3798fa8d56e..12b501a5599 100644
--- a/regression-test/suites/nereids_p0/select_no_from/sql/withWhereClause.sql
+++ b/regression-test/suites/nereids_p0/select_no_from/sql/withWhereClause.sql
@@ -1,4 +1,3 @@
-/*
-- database: presto; groups: no_from
-SELECT 1 WHERE TRUE AND 2=2
-*/
+SET enable_nereids_planner = TRUE;
+SELECT 1 WHERE TRUE AND 2=2;
diff --git
a/regression-test/suites/nereids_p0/select_no_from/sql/withWhereFalse.sql
b/regression-test/suites/nereids_p0/select_no_from/sql/withWhereFalse.sql
index c4096c2811a..3ffe3058851 100644
--- a/regression-test/suites/nereids_p0/select_no_from/sql/withWhereFalse.sql
+++ b/regression-test/suites/nereids_p0/select_no_from/sql/withWhereFalse.sql
@@ -1,4 +1,3 @@
-/*
-- database: presto; groups: no_from
-SELECT COUNT(*), 1 WHERE FALSE
-*/
+SET enable_nereids_planner = TRUE;
+SELECT COUNT(*), 1 WHERE FALSE;
diff --git a/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
b/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
index 536dcddf06c..072587ed28e 100644
--- a/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
+++ b/regression-test/suites/nereids_syntax_p0/bind_priority.groovy
@@ -116,7 +116,7 @@ suite("bind_priority") {
test{
sql "SELECT a,2 as a FROM (SELECT '1' as a) b HAVING a=1"
- exception "a is ambiguous: a#0, a#1."
+ exception "a is ambiguous: a#1, a#2."
}
sql "drop table if exists duplicate_slot";
diff --git
a/regression-test/suites/query_p0/select_no_from/sql/withGroupOrderHaving.sql
b/regression-test/suites/query_p0/select_no_from/sql/withGroupOrderHaving.sql
index 679d8220402..868e42d59ab 100644
---
a/regression-test/suites/query_p0/select_no_from/sql/withGroupOrderHaving.sql
+++
b/regression-test/suites/query_p0/select_no_from/sql/withGroupOrderHaving.sql
@@ -1,4 +1,8 @@
-/*
-- database: presto; groups: no_from
-SELECT MIN(10), 3 as col1 GROUP BY 2 HAVING 6 > 5 ORDER BY 1
-*/
+SET enable_nereids_planner = TRUE;
+SELECT MIN(10), 3 as col1 GROUP BY 2 HAVING 6 > 5 ORDER BY 1;
+SELECT 1 AS a, COUNT(*), SUM(2), AVG(1), RANK() OVER() AS w_rank
+WHERE 1 = 1
+GROUP BY a, w_rank
+HAVING COUNT(*) IN (1, 2) AND w_rank = 1
+ORDER BY a;
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/select_no_from/sql/withWhereClause.sql
b/regression-test/suites/query_p0/select_no_from/sql/withWhereClause.sql
index 3798fa8d56e..2f413308dfe 100644
--- a/regression-test/suites/query_p0/select_no_from/sql/withWhereClause.sql
+++ b/regression-test/suites/query_p0/select_no_from/sql/withWhereClause.sql
@@ -1,4 +1,3 @@
-/*
-- database: presto; groups: no_from
-SELECT 1 WHERE TRUE AND 2=2
-*/
+SET enable_nereids_planner = TRUE;
+SELECT 1 WHERE TRUE AND 2=2;
\ No newline at end of file
diff --git
a/regression-test/suites/query_p0/select_no_from/sql/withWhereFalse.sql
b/regression-test/suites/query_p0/select_no_from/sql/withWhereFalse.sql
index c4096c2811a..774532dc0d2 100644
--- a/regression-test/suites/query_p0/select_no_from/sql/withWhereFalse.sql
+++ b/regression-test/suites/query_p0/select_no_from/sql/withWhereFalse.sql
@@ -1,4 +1,3 @@
-/*
-- database: presto; groups: no_from
-SELECT COUNT(*), 1 WHERE FALSE
-*/
+SET enable_nereids_planner = TRUE;
+SELECT COUNT(*), 1 WHERE FALSE;
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
