This is an automated email from the ASF dual-hosted git repository.
xuyang 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 646c2aaca7f [chore](show) support statement to show views from table
(#32358)
646c2aaca7f is described below
commit 646c2aaca7f45fc2578b477851ba9d25665f1318
Author: xy720 <[email protected]>
AuthorDate: Wed Mar 27 15:01:57 2024 +0800
[chore](show) support statement to show views from table (#32358)
MySQL [test]> show views;
+----------------+
| Tables_in_test |
+----------------+
| t1_view |
| t2_view |
+----------------+
2 rows in set (0.00 sec)
MySQL [test]> show views like '%t1%';
+----------------+
| Tables_in_test |
+----------------+
| t1_view |
+----------------+
1 row in set (0.01 sec)
MySQL [test]> show views where create_time > '2024-03-18';
+----------------+
| Tables_in_test |
+----------------+
| t2_view |
+----------------+
1 row in set (0.02 sec)
---
.../sql-reference/Show-Statements/SHOW-VIEWS.md | 78 ++++++++++++++++++++++
.../sql-reference/Show-Statements/SHOW-VIEWS.md | 78 ++++++++++++++++++++++
fe/fe-core/src/main/cup/sql_parser.cup | 14 ++++
.../org/apache/doris/analysis/ShowTableStmt.java | 34 +++++++++-
.../java/org/apache/doris/qe/ShowExecutor.java | 3 +
fe/fe-core/src/main/jflex/sql_scanner.flex | 1 +
.../apache/doris/analysis/ShowTableStmtTest.java | 23 +++++++
.../java/org/apache/doris/qe/ShowExecutorTest.java | 10 +++
8 files changed, 238 insertions(+), 3 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
new file mode 100644
index 00000000000..5622be47fa3
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
@@ -0,0 +1,78 @@
+---
+{
+ "title": "SHOW-VIEWS",
+ "language": "en"
+}
+---
+
+<!--
+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.
+-->
+
+## SHOW-VIEWS
+
+### Name
+
+SHOW VIEWS
+
+### Description
+
+This statement is used to display all logical views under the current db
+
+grammar:
+
+```sql
+SHOW [FULL] VIEWS [LIKE]
+````
+
+illustrate:
+
+1. LIKE: Fuzzy query can be performed according to the table name
+
+### Example
+
+ 1. Desplay all views under DB
+
+ ```sql
+ MySQL [test]> show views;
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ | t2_view |
+ +----------------+
+ 2 rows in set (0.00 sec)
+ ```
+
+2. Fuzzy query by view name
+
+ ```sql
+ MySQL [test]> show views like '%t1%';
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ +----------------+
+ 1 row in set (0.01 sec)
+ ```
+
+### Keywords
+
+ SHOW, VIEWS
+
+### Best Practice
diff --git
a/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
new file mode 100644
index 00000000000..41262dba693
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-reference/Show-Statements/SHOW-VIEWS.md
@@ -0,0 +1,78 @@
+---
+{
+ "title": "SHOW-VIEWS",
+ "language": "zh-CN"
+}
+---
+
+<!--
+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.
+-->
+
+## SHOW-VIEWS
+
+### Name
+
+SHOW VIEWS
+
+### Description
+
+该语句用于展示当前 db 下所有的 logical view
+
+语法：
+
+```sql
+SHOW [FULL] VIEWS [LIKE] | [WHERE where_condition]
+```
+
+说明:
+
+1. LIKE：可按照表名进行模糊查询
+
+### Example
+
+ 1. 查看DB下所有逻辑视图
+
+ ```sql
+ MySQL [test]> show views;
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ | t2_view |
+ +----------------+
+ 2 rows in set (0.00 sec)
+ ```
+
+2. 按照VIEW名进行模糊查询
+
+ ```sql
+ MySQL [test]> show views like '%t1%';
+ +----------------+
+ | Tables_in_test |
+ +----------------+
+ | t1_view |
+ +----------------+
+ 1 row in set (0.01 sec)
+ ```
+
+### Keywords
+
+ SHOW, VIEWS
+
+### Best Practice
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index d18f00146b9..9738b361c6b 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -45,6 +45,7 @@ import org.apache.doris.catalog.ArrayType;
import org.apache.doris.catalog.MapType;
import org.apache.doris.catalog.StructField;
import org.apache.doris.catalog.StructType;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.catalog.View;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.FeConstants;
@@ -659,6 +660,7 @@ terminal String
KW_VERBOSE,
KW_VERSION,
KW_VIEW,
+ KW_VIEWS,
KW_WARNINGS,
KW_WEEK,
KW_WHEN,
@@ -3992,6 +3994,16 @@ show_param ::=
{:
RESULT = new ShowTableStmt(db, ctl, parser.isVerbose, parser.wild,
parser.where);
:}
+ /* show views */
+ | opt_full KW_VIEWS opt_db:db opt_wild_where
+ {:
+ RESULT = new ShowTableStmt(db, null, parser.isVerbose, TableType.VIEW,
parser.wild, parser.where);
+ :}
+ /* show views */
+ | opt_full KW_VIEWS from_or_in ident:ctl DOT ident:db opt_wild_where
+ {:
+ RESULT = new ShowTableStmt(db, ctl, parser.isVerbose, TableType.VIEW,
parser.wild, parser.where);
+ :}
/* show table id */
| KW_TABLE INTEGER_LITERAL:tableId
{:
@@ -8080,6 +8092,8 @@ keyword ::=
{: RESULT = id; :}
| KW_VIEW:id
{: RESULT = id; :}
+ | KW_VIEWS:id
+ {: RESULT = id; :}
| KW_WARNINGS:id
{: RESULT = id; :}
| KW_WORK:id
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
index 3dc3fa24aae..f3e12f1aa92 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/ShowTableStmt.java
@@ -20,6 +20,7 @@ package org.apache.doris.analysis;
import org.apache.doris.catalog.Column;
import org.apache.doris.catalog.InfoSchemaDb;
import org.apache.doris.catalog.ScalarType;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.cluster.ClusterNamespace;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.ErrorCode;
@@ -40,8 +41,9 @@ public class ShowTableStmt extends ShowStmt {
private static final String INVERTED_INDEX_STORAGE_FORMAT_COL =
"Inverted_index_storage_format";
private String db;
private String catalog;
- private boolean isVerbose;
- private String pattern;
+ private final boolean isVerbose;
+ private TableType type;
+ private final String pattern;
private Expr where;
private SelectStmt selectStmt;
@@ -61,6 +63,12 @@ public class ShowTableStmt extends ShowStmt {
this.catalog = catalog;
}
+ public ShowTableStmt(String db, String catalog, boolean isVerbose,
TableType type, String pattern,
+ Expr where) {
+ this(db, catalog, isVerbose, pattern, where);
+ this.type = type;
+ }
+
public String getDb() {
return db;
}
@@ -73,6 +81,10 @@ public class ShowTableStmt extends ShowStmt {
return isVerbose;
}
+ public TableType getType() {
+ return type;
+ }
+
public String getPattern() {
return pattern;
}
@@ -120,6 +132,11 @@ public class ShowTableStmt extends ShowStmt {
selectList.addItem(item);
aliasMap.put(new SlotRef(null, TYPE_COL),
item.getExpr().clone(null));
}
+ if (type != null) {
+ BinaryPredicate viewFilter = new
BinaryPredicate(BinaryPredicate.Operator.EQ,
+ new SlotRef(tablesTableName, "ENGINE"), new
StringLiteral(type.toEngineName()));
+ where = CompoundPredicate.createConjunction(viewFilter, where);
+ }
where = where.substitute(aliasMap);
selectStmt = new SelectStmt(selectList,
new FromClause(Lists.newArrayList(new
TableRef(tablesTableName, null))),
@@ -137,7 +154,18 @@ public class ShowTableStmt extends ShowStmt {
if (isVerbose) {
sb.append(" FULL");
}
- sb.append(" TABLES");
+ if (type != null) {
+ switch (type) {
+ // todo(only show views from now)
+ case VIEW:
+ sb.append(" VIEWS");
+ break;
+ default:
+ sb.append(" TABLES");
+ }
+ } else {
+ sb.append(" TABLES");
+ }
if (!Strings.isNullOrEmpty(db)) {
if (!Strings.isNullOrEmpty(catalog)) {
sb.append(" FROM ").append(catalog);
diff --git a/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
b/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
index 61beeb57ce6..6a763769afc 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/qe/ShowExecutor.java
@@ -909,6 +909,9 @@ public class ShowExecutor {
if
(tbl.getName().startsWith(FeConstants.TEMP_MATERIZLIZE_DVIEW_PREFIX)) {
continue;
}
+ if (showTableStmt.getType() != null && tbl.getType() !=
showTableStmt.getType()) {
+ continue;
+ }
if (matcher != null && !matcher.match(tbl.getName())) {
continue;
}
diff --git a/fe/fe-core/src/main/jflex/sql_scanner.flex
b/fe/fe-core/src/main/jflex/sql_scanner.flex
index cd496a9fff3..b931bda9e92 100644
--- a/fe/fe-core/src/main/jflex/sql_scanner.flex
+++ b/fe/fe-core/src/main/jflex/sql_scanner.flex
@@ -505,6 +505,7 @@ import org.apache.doris.qe.SqlModeHelper;
keywordMap.put("verbose", new Integer(SqlParserSymbols.KW_VERBOSE));
keywordMap.put("version", new Integer(SqlParserSymbols.KW_VERSION));
keywordMap.put("view", new Integer(SqlParserSymbols.KW_VIEW));
+ keywordMap.put("views", new Integer(SqlParserSymbols.KW_VIEWS));
keywordMap.put("warnings", new Integer(SqlParserSymbols.KW_WARNINGS));
keywordMap.put("week", new Integer(SqlParserSymbols.KW_WEEK));
keywordMap.put("when", new Integer(SqlParserSymbols.KW_WHEN));
diff --git
a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
index cd72b41bafe..5c0015fd03a 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/analysis/ShowTableStmtTest.java
@@ -17,6 +17,7 @@
package org.apache.doris.analysis;
+import org.apache.doris.catalog.TableIf.TableType;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.mysql.privilege.AccessControllerManager;
import org.apache.doris.mysql.privilege.MockedAuth;
@@ -68,6 +69,28 @@ public class ShowTableStmtTest {
Assert.assertEquals("Table_type",
stmt.getMetaData().getColumn(1).getName());
}
+ @Test
+ public void testShowViews() throws AnalysisException {
+ ShowTableStmt stmt = new ShowTableStmt("", null, false, TableType.VIEW,
+ null, null);
+ stmt.analyze(analyzer);
+ Assert.assertEquals("SHOW VIEWS FROM internal.testDb",
stmt.toString());
+ Assert.assertEquals("testDb", stmt.getDb());
+ Assert.assertEquals(TableType.VIEW, stmt.getType());
+ Assert.assertFalse(stmt.isVerbose());
+ Assert.assertEquals(1, stmt.getMetaData().getColumnCount());
+ Assert.assertEquals("Tables_in_testDb",
stmt.getMetaData().getColumn(0).getName());
+
+ stmt = new ShowTableStmt("abc", null, true, TableType.VIEW, "bcd",
null);
+ stmt.analyze(analyzer);
+ Assert.assertEquals("bcd", stmt.getPattern());
+ Assert.assertEquals("SHOW FULL VIEWS FROM internal.abc LIKE 'bcd'",
stmt.toString());
+ Assert.assertEquals(4, stmt.getMetaData().getColumnCount());
+ Assert.assertEquals("Tables_in_abc",
stmt.getMetaData().getColumn(0).getName());
+ Assert.assertEquals("Table_type",
stmt.getMetaData().getColumn(1).getName());
+ Assert.assertEquals(TableType.VIEW, stmt.getType());
+ }
+
@Test
public void testNoDb() {
ShowTableStmt stmt = new ShowTableStmt("", null, false, null);
diff --git a/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
b/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
index 114159142dc..5b8b374ad5a 100644
--- a/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
+++ b/fe/fe-core/src/test/java/org/apache/doris/qe/ShowExecutorTest.java
@@ -324,6 +324,16 @@ public class ShowExecutorTest {
Assert.assertFalse(resultSet.next());
}
+ @Test
+ public void testShowViews() throws AnalysisException {
+ ShowTableStmt stmt = new ShowTableStmt("testDb", null, false,
TableType.VIEW,
+ null, null);
+ ShowExecutor executor = new ShowExecutor(ctx, stmt);
+ ShowResultSet resultSet = executor.execute();
+
+ Assert.assertFalse(resultSet.next());
+ }
+
@Test
public void testShowTableFromCatalog() throws AnalysisException {
ShowTableStmt stmt = new ShowTableStmt("testDb", "internal", false,
null);
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
