This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch branch-1.2-lts
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-1.2-lts by this push:
new 5893bdcb9e [cherry-pick] Fix wrong results when select from view which
has with clause (#15109)
5893bdcb9e is described below
commit 5893bdcb9ed42d3c4870ec0643e77cc80c250b63
Author: Kikyou1997 <[email protected]>
AuthorDate: Thu Dec 22 08:55:42 2022 +0800
[cherry-pick] Fix wrong results when select from view which has with clause
(#15109)
cherry-pick #14747
---
.../java/org/apache/doris/analysis/QueryStmt.java | 8 ++++
.../java/org/apache/doris/analysis/SelectStmt.java | 30 ++++++++-----
.../java/org/apache/doris/analysis/WithClause.java | 2 +-
.../org/apache/doris/analysis/SelectStmtTest.java | 9 ++--
.../org/apache/doris/analysis/SqlModeTest.java | 4 +-
.../org/apache/doris/catalog/CreateViewTest.java | 6 +--
.../correctness/test_view_with_with_clause.out | 5 +++
.../correctness/test_view_with_with_clause.groovy | 51 ++++++++++++++++++++++
8 files changed, 93 insertions(+), 22 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
index ea0d3f8881..9616c1cb47 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/QueryStmt.java
@@ -164,6 +164,8 @@ public abstract class QueryStmt extends StatementBase
implements Queriable {
*/
private Set<TupleId> disableTuplesMVRewriter = Sets.newHashSet();
+ protected boolean toSQLWithSelectList;
+
QueryStmt(ArrayList<OrderByElement> orderByElements, LimitElement
limitElement) {
this.orderByElements = orderByElements;
this.limitElement = limitElement;
@@ -805,4 +807,10 @@ public abstract class QueryStmt extends StatementBase
implements Queriable {
public boolean hasOutFileClause() {
return outFileClause != null;
}
+
+ public String toSqlWithSelectList() {
+ toSQLWithSelectList = true;
+ return toSql();
+ }
+
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
index b715014149..36ea44680a 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectStmt.java
@@ -1843,18 +1843,26 @@ public class SelectStmt extends QueryStmt {
if (selectList.isDistinct()) {
strBuilder.append("DISTINCT ");
}
- for (int i = 0; i < resultExprs.size(); ++i) {
- // strBuilder.append(selectList.getItems().get(i).toSql());
- // strBuilder.append((i + 1 != selectList.getItems().size()) ? ",
" : "");
- if (i != 0) {
- strBuilder.append(", ");
- }
- if (needToSql) {
- strBuilder.append(originalExpr.get(i).toSql());
- } else {
- strBuilder.append(resultExprs.get(i).toSql());
+ ConnectContext ctx = ConnectContext.get();
+ if (ctx == null || ctx.getSessionVariable().internalSession ||
toSQLWithSelectList) {
+ for (int i = 0; i < selectList.getItems().size(); i++) {
+ strBuilder.append(selectList.getItems().get(i).toSql());
+ strBuilder.append((i + 1 != selectList.getItems().size()) ? ",
" : "");
+ }
+ } else {
+ for (int i = 0; i < resultExprs.size(); ++i) {
+ // strBuilder.append(selectList.getItems().get(i).toSql());
+ // strBuilder.append((i + 1 != selectList.getItems().size()) ?
", " : "");
+ if (i != 0) {
+ strBuilder.append(", ");
+ }
+ if (needToSql) {
+ strBuilder.append(originalExpr.get(i).toSql());
+ } else {
+ strBuilder.append(resultExprs.get(i).toSql());
+ }
+ strBuilder.append(" AS
").append(SqlUtils.getIdentSql(colLabels.get(i)));
}
- strBuilder.append(" AS
").append(SqlUtils.getIdentSql(colLabels.get(i)));
}
// From clause
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/WithClause.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/WithClause.java
index fb4fdbec6a..2842685a87 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/WithClause.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/WithClause.java
@@ -145,7 +145,7 @@ public class WithClause implements ParseNode {
aliasSql += "(" + Joiner.on(", ").join(
ToSqlUtils.getIdentSqlList(view.getOriginalColLabels())) + ")";
}
- viewStrings.add(aliasSql + " AS (" + view.getQueryStmt().toSql() +
")");
+ viewStrings.add(aliasSql + " AS (" +
view.getQueryStmt().toSqlWithSelectList() + ")");
}
return "WITH " + Joiner.on(",").join(viewStrings);
}
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
index dc66b2ea6a..7e0137e6bd 100755
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/SelectStmtTest.java
@@ -789,9 +789,8 @@ public class SelectStmtTest {
+ ") t";
SelectStmt stmt1 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql1,
ctx);
stmt1.rewriteExprs(new Analyzer(ctx.getEnv(), ctx).getExprRewriter());
- Assert.assertEquals("SELECT `t`.`k1` AS `k1` "
- + "FROM (WITH v1 AS (SELECT `t1`.`k1` AS `k1` FROM
`default_cluster:db1`.`tbl1` t1),"
- + "v2 AS (SELECT `t2`.`k1` AS `k1` FROM
`default_cluster:db1`.`tbl1` t2) "
+ Assert.assertEquals("SELECT `t`.`k1` AS `k1` FROM (WITH v1 AS (SELECT
`t1`.`k1` "
+ + "FROM `default_cluster:db1`.`tbl1` t1),v2 AS (SELECT
`t2`.`k1` FROM `default_cluster:db1`.`tbl1` t2) "
+ "SELECT `v1`.`k1` AS `k1` FROM `v1` UNION SELECT `v2`.`k1`
AS `k1` FROM `v2`) t", stmt1.toSql());
String sql2 =
@@ -807,8 +806,8 @@ public class SelectStmtTest {
+ ") t";
SelectStmt stmt2 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql2,
ctx);
stmt2.rewriteExprs(new Analyzer(ctx.getEnv(), ctx).getExprRewriter());
- Assert.assertTrue(stmt2.toSql().contains("WITH v1 AS (SELECT `t1`.`k1`
AS `k1` FROM "
- + "`default_cluster:db1`.`tbl1` t1),v2 AS (SELECT `t2`.`k1` AS
`k1` FROM `default_cluster:db1`.`tbl1` t2)"));
+ Assert.assertTrue(stmt2.toSql().contains("WITH v1 AS (SELECT `t1`.`k1`
FROM `default_cluster:db1`.`tbl1` t1),"
+ + "v2 AS (SELECT `t2`.`k1` FROM `default_cluster:db1`.`tbl1`
t2)"));
}
@Test
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/SqlModeTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/SqlModeTest.java
index 019d4d0d90..9e0b8c2a46 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/SqlModeTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/SqlModeTest.java
@@ -41,7 +41,7 @@ public class SqlModeTest {
} catch (Exception e) {
Assert.fail(e.getMessage());
}
- Assert.assertEquals("SELECT FROM `db1`.`tbl1` WHERE `name` = 'BILL
GATES'", selectStmt.toSql());
+ Assert.assertEquals("SELECT * FROM `db1`.`tbl1` WHERE `name` = 'BILL
GATES'", selectStmt.toSql());
parser = new SqlParser(new SqlScanner(new StringReader(stmt),
SqlModeHelper.MODE_DEFAULT));
try {
@@ -49,7 +49,7 @@ public class SqlModeTest {
} catch (Exception e) {
Assert.fail(e.getMessage());
}
- Assert.assertEquals("SELECT FROM `db1`.`tbl1` WHERE `name` = 'BILL
GATES'", selectStmt.toSql());
+ Assert.assertEquals("SELECT * FROM `db1`.`tbl1` WHERE `name` = 'BILL
GATES'", selectStmt.toSql());
}
@Test
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateViewTest.java
b/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateViewTest.java
index fa95e3bb09..1f2e98ea7c 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateViewTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/catalog/CreateViewTest.java
@@ -189,9 +189,9 @@ public class CreateViewTest {
alter1 = (View) db.getTableOrDdlException("alter1");
Assert.assertEquals(
- "WITH test1_cte(w1, w2) "
- + "AS (SELECT `k1` AS `k1`, `k2` AS `k2` FROM
`default_cluster:test`.`tbl1`) "
- + "SELECT `w1` AS `c1`, sum(`w2`) AS `c2` FROM
`test1_cte` WHERE `w1` > 10 GROUP BY `w1` ORDER BY `w1` ASC",
+ "WITH test1_cte(w1, w2) AS (SELECT `k1`, `k2` FROM
`default_cluster:test`.`tbl1`) "
+ + "SELECT `w1` AS `c1`, sum(`w2`) AS `c2` FROM
`test1_cte` WHERE `w1` > 10 GROUP BY `w1` "
+ + "ORDER BY `w1` ASC",
alter1.getInlineViewDef());
}
}
diff --git a/regression-test/data/correctness/test_view_with_with_clause.out
b/regression-test/data/correctness/test_view_with_with_clause.out
new file mode 100644
index 0000000000..354fa4a2f9
--- /dev/null
+++ b/regression-test/data/correctness/test_view_with_with_clause.out
@@ -0,0 +1,5 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql --
+001 3
+002 1
+
diff --git
a/regression-test/suites/correctness/test_view_with_with_clause.groovy
b/regression-test/suites/correctness/test_view_with_with_clause.groovy
new file mode 100644
index 0000000000..2bb072aaee
--- /dev/null
+++ b/regression-test/suites/correctness/test_view_with_with_clause.groovy
@@ -0,0 +1,51 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+suite("test_view_with_with_clause") {
+
+ sql """
+ CREATE TABLE IF NOT EXISTS test_view_with_with_clause (`a` date, `b`
varchar(30) ,`c` varchar(30) )
+ DUPLICATE KEY(`a`)
+ DISTRIBUTED BY HASH(`a`)
+ BUCKETS 10
+ PROPERTIES("replication_allocation" = "tag.location.default:1");
+ """
+
+ sql """insert into test_view_with_with_clause values
('2022-12-01','001','001001');"""
+ sql """insert into test_view_with_with_clause values
('2022-12-02','001','001002');"""
+ sql """insert into test_view_with_with_clause values
('2022-12-01','001','001003');"""
+ sql """insert into test_view_with_with_clause values
('2022-12-01','002','002001');"""
+ sql """insert into test_view_with_with_clause values
('2022-12-02','002','002001');"""
+
+ sql """
+ create view viewtest_test_view_with_with_clause (b,cnt) as
+ with aaa as (
+ select b,count(distinct c) cnt
+ from test_view_with_with_clause
+ group by b
+ order by cnt desc
+ limit 10
+ )
+ select * from aaa;
+ """
+
+ qt_sql """
+ select * from viewtest_test_view_with_with_clause;
+ """
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
