You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2021/09/26 03:45:19 UTC
[incubator-doris] branch master updated: [Bug] When using view,
make toSql method generates the final sql (#6736)
This is an automated email from the ASF dual-hosted git repository.
morningman pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-doris.git
The following commit(s) were added to refs/heads/master by this push:
new 11ec38d [Bug] When using view, make toSql method generates the final sql (#6736)
11ec38d is described below
commit 11ec38dd6fd9f86632d83c47bd9d8bc05db69a2b
Author: Zeno Yang <co...@qq.com>
AuthorDate: Sun Sep 26 11:44:23 2021 +0800
[Bug] When using view, make toSql method generates the final sql (#6736)
1. Fix the problem that the WITH statement cannot be printed when `UNION` is included in SQL
2. In the `toSql` method, convert the normal VIEW into the final statement
3. Replace `selectStmt.originSql` with `selectStmt.toSql`
---
.../org/apache/doris/analysis/InlineViewRef.java | 19 +++--
.../apache/doris/analysis/SetOperationStmt.java | 4 ++
.../java/org/apache/doris/qe/cache/SqlCache.java | 6 +-
.../org/apache/doris/analysis/SelectStmtTest.java | 84 ++++++++++++++++++++++
4 files changed, 105 insertions(+), 8 deletions(-)
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/InlineViewRef.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/InlineViewRef.java
index a498e36..dfc22cb 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/InlineViewRef.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/InlineViewRef.java
@@ -424,14 +424,23 @@ public class InlineViewRef extends TableRef {
String aliasSql = null;
String alias = getExplicitAlias();
if (alias != null) aliasSql = ToSqlUtils.getIdentSql(alias);
+
+ StringBuilder sb = new StringBuilder();
if (view != null) {
- // TODO(zc):
- // return view_.toSql() + (aliasSql == null ? "" : " " + aliasSql);
- return name.toSql() + (aliasSql == null ? "" : " " + aliasSql);
+ // When it is a local view, it does not need to be expanded into a statement,
+ // because toSql function already contains `with` statement
+ if (view.isLocalView()) {
+ return name.toSql() + (aliasSql == null ? "" : " " + aliasSql);
+ }
+
+ sb.append("(")
+ .append(view.getInlineViewDef())
+ .append(")")
+ .append(aliasSql == null ? "" : " " + aliasSql);
+ return sb.toString();
}
- StringBuilder sb = new StringBuilder()
- .append("(")
+ sb.append("(")
.append(queryStmt.toSql())
.append(") ")
.append(aliasSql);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SetOperationStmt.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/SetOperationStmt.java
index 875b2d8..f735315 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SetOperationStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SetOperationStmt.java
@@ -598,6 +598,10 @@ public class SetOperationStmt extends QueryStmt {
return toSqlString;
}
StringBuilder strBuilder = new StringBuilder();
+ if (withClause_ != null) {
+ strBuilder.append(withClause_.toSql());
+ strBuilder.append(" ");
+ }
Preconditions.checkState(operands.size() > 0);
strBuilder.append(operands.get(0).getQueryStmt().toSql());
for (int i = 1; i < operands.size() - 1; ++i) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/cache/SqlCache.java b/fe/fe-core/src/main/java/org/apache/doris/qe/cache/SqlCache.java
index 2f6cd3f..9e87439 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/cache/SqlCache.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/cache/SqlCache.java
@@ -41,7 +41,7 @@ public class SqlCache extends Cache {
public InternalService.PFetchCacheResult getCacheData(Status status) {
InternalService.PFetchCacheRequest request = InternalService.PFetchCacheRequest.newBuilder()
- .setSqlKey(CacheProxy.getMd5(selectStmt.getOrigStmt().originStmt))
+ .setSqlKey(CacheProxy.getMd5(selectStmt.toSql()))
.addParams(InternalService.PCacheParam.newBuilder()
.setPartitionKey(latestTable.latestPartitionId)
.setLastVersion(latestTable.latestVersion)
@@ -74,8 +74,8 @@ public class SqlCache extends Cache {
}
InternalService.PUpdateCacheRequest updateRequest =
- rowBatchBuilder.buildSqlUpdateRequest(selectStmt.getOrigStmt().originStmt,
- latestTable.latestPartitionId, latestTable.latestVersion, latestTable.latestTime);
+ rowBatchBuilder.buildSqlUpdateRequest(selectStmt.toSql(), latestTable.latestPartitionId,
+ latestTable.latestVersion, latestTable.latestTime);
if (updateRequest.getValuesCount() > 0) {
CacheBeProxy proxy = new CacheBeProxy();
Status status = new Status();
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 018adad..434c556 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
@@ -715,4 +715,88 @@ public class SelectStmtTest {
System.out.println(e.getMessage());
}
}
+
+ @Test
+ public void testWithUnionToSql() throws Exception {
+ ConnectContext ctx = UtFrameUtils.createDefaultCtx();
+ String sql1 =
+ "select \n" +
+ " t.k1 \n" +
+ "from (\n" +
+ " with \n" +
+ " v1 as (select t1.k1 from db1.tbl1 t1),\n" +
+ " v2 as (select t2.k1 from db1.tbl1 t2)\n" +
+ " select v1.k1 as k1 from v1\n" +
+ " union\n" +
+ " select v2.k1 as k1 from v2\n" +
+ ") t";
+ SelectStmt stmt1 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql1, ctx);
+ stmt1.rewriteExprs(new Analyzer(ctx.getCatalog(), ctx).getExprRewriter());
+ Assert.assertTrue(stmt1.toSql().equals("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) " +
+ "SELECT `v1`.`k1` AS `k1` FROM `v1` UNION SELECT `v2`.`k1` AS `k1` FROM `v2`) t"));
+
+ String sql2 =
+ "with\n" +
+ " v1 as (select t1.k1 from db1.tbl1 t1),\n" +
+ " v2 as (select t2.k1 from db1.tbl1 t2)\n" +
+ "select\n" +
+ " t.k1\n" +
+ "from (\n" +
+ " select v1.k1 as k1 from v1\n" +
+ " union\n" +
+ " select v2.k1 as k1 from v2\n" +
+ ") t";
+ SelectStmt stmt2 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql2, ctx);
+ stmt2.rewriteExprs(new Analyzer(ctx.getCatalog(), 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)"));
+ }
+
+ @Test
+ public void testViewConvertFinalSql() throws Exception {
+ ConnectContext ctx = UtFrameUtils.createDefaultCtx();
+ dorisAssert.useDatabase("db1");
+ String testView1 = "CREATE VIEW `view1` as (select t1.k1, t1.k2 from db1.tbl1 t1)";
+ dorisAssert.withView(testView1);
+
+ String sql1 = "select * from db1.view1;";
+ SelectStmt stmt1 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql1, ctx);
+ stmt1.rewriteExprs(new Analyzer(ctx.getCatalog(), ctx).getExprRewriter());
+ Assert.assertTrue(stmt1.toSql().contains("FROM " +
+ "(SELECT `t1`.`k1` AS `k1`, `t1`.`k2` AS `k2` FROM `default_cluster:db1`.`tbl1` t1)"));
+
+ String sql2 =
+ "select \n" +
+ " t.k1 \n" +
+ "from (\n" +
+ " select v1.k1 from db1.view1 v1\n" +
+ ") t";
+ SelectStmt stmt2 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql2, ctx);
+ stmt2.rewriteExprs(new Analyzer(ctx.getCatalog(), ctx).getExprRewriter());
+ Assert.assertTrue(stmt2.toSql().contains("FROM " +
+ "(SELECT `t1`.`k1` AS `k1`, `t1`.`k2` AS `k2` FROM `default_cluster:db1`.`tbl1` t1) v1"));
+
+ // WITH statement contains view, that is, localView contains normal view
+ String sql3 =
+ "with\n" +
+ " v1 as (select t1.k1 from db1.view1 t1),\n" +
+ " v2 as (select t2.k1 from db1.view1 t2)\n" +
+ "select\n" +
+ " t.k1\n" +
+ "from (\n" +
+ " select v1.k1 as k1 from v1\n" +
+ " union\n" +
+ " select v2.k1 as k1 from v2\n" +
+ ") t";
+ SelectStmt stmt3 = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql3, ctx);
+ stmt3.rewriteExprs(new Analyzer(ctx.getCatalog(), ctx).getExprRewriter());
+ System.out.println(stmt3.toSql());
+ Assert.assertTrue(stmt3.toSql().contains("WITH " +
+ "v1 AS (SELECT `t1`.`k1` AS `k1` FROM (SELECT `t1`.`k1` AS `k1`, `t1`.`k2` AS `k2` FROM `default_cluster:db1`.`tbl1` t1) t1)," +
+ "v2 AS (SELECT `t2`.`k1` AS `k1` FROM (SELECT `t1`.`k1` AS `k1`, `t1`.`k2` AS `k2` FROM `default_cluster:db1`.`tbl1` t1) t2)"));
+
+ dorisAssert.dropView("view1");
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org