This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 61c817f4cc [feature](syntax) support SELECT * EXCEPT (#13844)
61c817f4cc is described below
commit 61c817f4cc30b2aab0465ac844b9c9323b27abc2
Author: qiye <[email protected]>
AuthorDate: Tue Nov 1 19:41:25 2022 +0800
[feature](syntax) support SELECT * EXCEPT (#13844)
* [feature](syntax) support SELECT * EXCEPT: add regression test
---
docs/en/docs/data-table/basic-usage.md | 18 ++++++
docs/zh-CN/docs/data-table/basic-usage.md | 18 ++++++
fe/fe-core/src/main/cup/sql_parser.cup | 7 +++
.../java/org/apache/doris/analysis/SelectList.java | 12 ++++
.../java/org/apache/doris/analysis/SelectStmt.java | 72 ++++++++++++++--------
.../org/apache/doris/analysis/SelectStmtTest.java | 9 +++
.../suites/correctness/test_select_except.groovy | 69 +++++++++++++++++++++
7 files changed, 181 insertions(+), 24 deletions(-)
diff --git a/docs/en/docs/data-table/basic-usage.md
b/docs/en/docs/data-table/basic-usage.md
index 069fceaa12..3f05aa5e1f 100644
--- a/docs/en/docs/data-table/basic-usage.md
+++ b/docs/en/docs/data-table/basic-usage.md
@@ -376,6 +376,24 @@ MySQL> SELECT * FROM table1 ORDER BY citycode;
5 rows in set (0.01 sec)
```
+### SELECT * EXCEPT
+
+A `SELECT * EXCEPT` statement specifies the names of one or more columns to
exclude from the result. All matching column names are omitted from the output.
+
+```sql
+MySQL> SELECT * except (username, citycode) FROM table1;
++--------+------+
+| siteid | pv |
++--------+------+
+| 2 | 2 |
+| 5 | 3 |
+| 3 | 2 |
++--------+------+
+3 rows in set (0.01 sec)
+```
+
+**Note**: `SELECT * EXCEPT` does not exclude columns that do not have names.
+
### Join Query
Query example::
diff --git a/docs/zh-CN/docs/data-table/basic-usage.md
b/docs/zh-CN/docs/data-table/basic-usage.md
index 4a108e4e3e..37141b9b6c 100644
--- a/docs/zh-CN/docs/data-table/basic-usage.md
+++ b/docs/zh-CN/docs/data-table/basic-usage.md
@@ -401,6 +401,24 @@ mysql> SELECT * FROM table1 ORDER BY citycode;
5 rows in set (0.01 sec)
```
+### SELECT * EXCEPT
+
+`SELECT * EXCEPT` 语句指定要从结果中排除的一个或多个列的名称。输出中将忽略所有匹配的列名称。
+
+```sql
+MySQL> SELECT * except (username, citycode) FROM table1;
++--------+------+
+| siteid | pv |
++--------+------+
+| 2 | 2 |
+| 5 | 3 |
+| 3 | 2 |
++--------+------+
+3 rows in set (0.01 sec)
+```
+
+**注意**：`SELECT * EXCEPT` 不会排除没有名称的列。
+
### Join 查询
查询示例:
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index 0590f1bc62..c9c6260c60 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -891,6 +891,7 @@ precedence left KW_LIKE, KW_REGEXP;
precedence left EQUAL, LESSTHAN, GREATERTHAN;
precedence left ADD, SUBTRACT;
precedence left AT, STAR, DIVIDE, MOD, KW_DIV;
+precedence left KW_EXCEPT;
precedence left BITAND, BITOR, BITXOR;
precedence left KW_PIPE;
precedence left BITNOT;
@@ -4759,6 +4760,12 @@ select_list ::=
list.addItem(SelectListItem.createStarItem(null));
RESULT = list;
:}
+ | STAR KW_EXCEPT LPAREN select_sublist: list RPAREN
+ {:
+ SelectList res = new SelectList(list);
+ list.setIsExcept(true);
+ RESULT = list;
+ :}
;
select_sublist ::=
diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectList.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectList.java
index ee950a032e..e7da518af8 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectList.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/SelectList.java
@@ -36,6 +36,7 @@ public class SelectList {
private static final String SET_VAR_KEY = "set_var";
private boolean isDistinct;
+ private boolean isExcept;
private Map<String, String> optHints;
private List<OrderByElement> orderByElements;
@@ -53,15 +54,18 @@ public class SelectList {
items.add(item.clone());
}
isDistinct = other.isDistinct;
+ isExcept = other.isExcept;
}
public SelectList() {
items = Lists.newArrayList();
this.isDistinct = false;
+ this.isExcept = false;
}
public SelectList(List<SelectListItem> items, boolean isDistinct) {
this.isDistinct = isDistinct;
+ this.isExcept = false;
this.items = items;
}
@@ -81,6 +85,14 @@ public class SelectList {
isDistinct = value;
}
+ public boolean isExcept() {
+ return isExcept;
+ }
+
+ public void setIsExcept(boolean except) {
+ isExcept = except;
+ }
+
public Map<String, String> getOptHints() {
return optHints;
}
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 193ff8a8e8..f522dcd898 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
@@ -414,33 +414,57 @@ public class SelectStmt extends QueryStmt {
registerIsNotEmptyPredicates(analyzer);
}
// populate selectListExprs, aliasSMap, groupingSmap and colNames
- for (SelectListItem item : selectList.getItems()) {
- if (item.isStar()) {
- TableName tblName = item.getTblName();
- if (tblName == null) {
- expandStar(analyzer);
- } else {
- expandStar(analyzer, tblName);
- }
+ if (selectList.isExcept()) {
+ List<SelectListItem> items = selectList.getItems();
+ TableName tblName = items.get(0).getTblName();
+ if (tblName == null) {
+ expandStar(analyzer);
} else {
- // Analyze the resultExpr before generating a label to ensure
enforcement
- // of expr child and depth limits (toColumn() label may call
toSql()).
- item.getExpr().analyze(analyzer);
- if (!(item.getExpr() instanceof CaseExpr)
- &&
item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
- throw new AnalysisException("Subquery is not supported in
the select list.");
+ expandStar(analyzer, tblName);
+ }
+
+ // get excepted cols
+ ArrayList<String> exceptCols = new ArrayList<>();
+ for (SelectListItem item : items) {
+ Expr expr = item.getExpr();
+ if (!(item.getExpr() instanceof SlotRef)) {
+ throw new AnalysisException("`SELECT * EXCEPT` only
supports column name.");
}
- Expr expr = rewriteQueryExprByMvColumnExpr(item.getExpr(),
analyzer);
- resultExprs.add(expr);
- SlotRef aliasRef = new SlotRef(null, item.toColumnLabel());
- Expr existingAliasExpr = aliasSMap.get(aliasRef);
- if (existingAliasExpr != null &&
!existingAliasExpr.equals(item.getExpr())) {
- // If we have already seen this alias, it refers to more
than one column and
- // therefore is ambiguous.
- ambiguousAliasList.add(aliasRef);
+ exceptCols.add(expr.toColumnLabel());
+ }
+ // remove excepted columns
+ resultExprs.removeIf(expr ->
exceptCols.contains(expr.toColumnLabel()));
+ colLabels.removeIf(exceptCols::contains);
+
+ } else {
+ for (SelectListItem item : selectList.getItems()) {
+ if (item.isStar()) {
+ TableName tblName = item.getTblName();
+ if (tblName == null) {
+ expandStar(analyzer);
+ } else {
+ expandStar(analyzer, tblName);
+ }
+ } else {
+ // Analyze the resultExpr before generating a label to
ensure enforcement
+ // of expr child and depth limits (toColumn() label may
call toSql()).
+ item.getExpr().analyze(analyzer);
+ if (!(item.getExpr() instanceof CaseExpr)
+ &&
item.getExpr().contains(Predicates.instanceOf(Subquery.class))) {
+ throw new AnalysisException("Subquery is not supported
in the select list.");
+ }
+ Expr expr = rewriteQueryExprByMvColumnExpr(item.getExpr(),
analyzer);
+ resultExprs.add(expr);
+ SlotRef aliasRef = new SlotRef(null, item.toColumnLabel());
+ Expr existingAliasExpr = aliasSMap.get(aliasRef);
+ if (existingAliasExpr != null &&
!existingAliasExpr.equals(item.getExpr())) {
+ // If we have already seen this alias, it refers to
more than one column and
+ // therefore is ambiguous.
+ ambiguousAliasList.add(aliasRef);
+ }
+ aliasSMap.put(aliasRef, item.getExpr().clone());
+ colLabels.add(item.toColumnLabel());
}
- aliasSMap.put(aliasRef, item.getExpr().clone());
- colLabels.add(item.toColumnLabel());
}
}
if (groupByClause != null && groupByClause.isGroupByExtension()) {
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 ecb81927ab..dc66b2ea6a 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
@@ -937,4 +937,13 @@ public class SelectStmtTest {
Set<Long> sampleTabletIds16 = ((OlapScanNode)
planner16.getScanNodes().get(0)).getSampleTabletIds();
Assert.assertEquals(1, sampleTabletIds16.size());
}
+
+ @Test
+ public void testSelectExcept() throws Exception {
+ ConnectContext ctx = UtFrameUtils.createDefaultCtx();
+ String sql = "SELECT * EXCEPT (siteid) FROM db1.table1";
+ SelectStmt stmt = (SelectStmt) UtFrameUtils.parseAndAnalyzeStmt(sql,
ctx);
+ Assert.assertFalse(stmt.getColLabels().contains("siteid"));
+ Assert.assertEquals(stmt.resultExprs.size(), 3);
+ }
}
diff --git a/regression-test/suites/correctness/test_select_except.groovy
b/regression-test/suites/correctness/test_select_except.groovy
new file mode 100644
index 0000000000..ddcb0ade6d
--- /dev/null
+++ b/regression-test/suites/correctness/test_select_except.groovy
@@ -0,0 +1,69 @@
+// 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_select_except") {
+ sql """ DROP TABLE IF EXISTS tbl_select_except """
+ sql """
+ CREATE TABLE tbl_select_except (
+ siteid INT DEFAULT '10',
+ citycode SMALLINT,
+ username VARCHAR(32) DEFAULT '',
+ pv BIGINT SUM DEFAULT '0'
+ ) ENGINE=OLAP
+ AGGREGATE KEY(siteid, citycode, username)
+ DISTRIBUTED BY HASH(siteid) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+ sql """
+ insert into tbl_select_except values(1,1,'jim',2)
+ """
+ sql """
+ insert into tbl_select_except values(2,1,'grace',3)
+ """
+ sql """
+ insert into tbl_select_except values(3,2,'tom',4)
+ """
+
+ List<List<Object>> results = sql "select * except (siteid, citycode) from
tbl_select_except order by username"
+
+ assertEquals(results.size(), 3)
+ assertEquals(results[0].size(), 2)
+ assertEquals(results[1].size(), 2)
+ assertEquals(results[2].size(), 2)
+
+ assertEquals(results[0][0], 'grace')
+ assertEquals(results[1][0], 'jim')
+ assertEquals(results[2][0], 'tom')
+ assertEquals(results[0][1], 3)
+ assertEquals(results[1][1], 2)
+ assertEquals(results[2][1], 4)
+
+ try {
+ test {
+ sql """
+ select * except (concat(username, 's')) from tbl_select_except
order by username
+ """
+ exception "errCode = 2, detailMessage = `SELECT * EXCEPT` only
supports column name."
+ }
+ } finally {
+ sql "drop table if exists tbl_select_except"
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
