This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new 8af2618fc22 Parse pivot segment and extract column in sql binder
(#28433)
8af2618fc22 is described below
commit 8af2618fc22803735aa8e04c2543f6856ba57600
Author: ZhangCheng <[email protected]>
AuthorDate: Thu Sep 14 19:07:37 2023 +0800
Parse pivot segment and extract column in sql binder (#28433)
* Parse pivot segment and extract column in sql binder
* Parse pivot segment and extract column in sql binder
* Parse pivot segment and extract column in sql binder
---
.../expression/impl/ColumnSegmentBinder.java | 18 ++++++++++
.../from/impl/SubqueryTableSegmentBinder.java | 9 +++++
.../statement/SQLStatementBinderContext.java | 2 ++
.../src/main/antlr4/imports/oracle/DMLStatement.g4 | 8 +++--
.../statement/type/OracleDMLStatementVisitor.java | 29 ++++++++++++++-
.../sql/common/segment/generic/PivotSegment.java | 41 ++++++++++++++++++++++
.../segment/generic/table/SimpleTableSegment.java | 13 +++++++
.../generic/table/SubqueryTableSegment.java | 13 +++++++
8 files changed, 130 insertions(+), 3 deletions(-)
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/expression/impl/ColumnSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/expression/impl/ColumnSegmentBinder.java
index 2f44a93b03d..faeb0bca37d 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/expression/impl/ColumnSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/expression/impl/ColumnSegmentBinder.java
@@ -152,11 +152,26 @@ public final class ColumnSegmentBinder {
result = findInputColumnSegmentByVariables(segment,
statementBinderContext.getVariableNames()).orElse(null);
isFindInputColumn = result != null;
}
+ if (!isFindInputColumn) {
+ result = findInputColumnSegmentByPivotColumns(segment,
statementBinderContext.getPivotColumnNames()).orElse(null);
+ isFindInputColumn = result != null;
+ }
ShardingSpherePreconditions.checkState(isFindInputColumn ||
containsFunctionTable(tableBinderContexts, outerTableBinderContexts.values()),
() -> new UnknownColumnException(segment.getExpression(),
SEGMENT_TYPE_MESSAGES.getOrDefault(parentSegmentType,
UNKNOWN_SEGMENT_TYPE_MESSAGE)));
return Optional.ofNullable(result);
}
+ private static Optional<ColumnSegment>
findInputColumnSegmentByPivotColumns(final ColumnSegment segment, final
Collection<String> pivotColumnNames) {
+ if (pivotColumnNames.isEmpty()) {
+ return Optional.empty();
+ }
+ if
(pivotColumnNames.contains(segment.getIdentifier().getValue().toLowerCase())) {
+ ColumnSegment result = new ColumnSegment(0, 0,
segment.getIdentifier());
+ return Optional.of(result);
+ }
+ return Optional.empty();
+ }
+
private static Optional<ProjectionSegment>
findInputColumnSegmentFromOuterTable(final ColumnSegment segment, final
Map<String, TableSegmentBinderContext> outerTableBinderContexts) {
ListIterator<TableSegmentBinderContext> listIterator = new
ArrayList<>(outerTableBinderContexts.values()).listIterator(outerTableBinderContexts.size());
while (listIterator.hasPrevious()) {
@@ -180,6 +195,9 @@ public final class ColumnSegmentBinder {
}
private static Optional<ColumnSegment>
findInputColumnSegmentByVariables(final ColumnSegment segment, final
Collection<String> variableNames) {
+ if (variableNames.isEmpty()) {
+ return Optional.empty();
+ }
if
(variableNames.contains(segment.getIdentifier().getValue().toLowerCase())) {
ColumnSegment result = new ColumnSegment(0, 0,
segment.getIdentifier());
result.setVariable(true);
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/from/impl/SubqueryTableSegmentBinder.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/from/impl/SubqueryTableSegmentBinder.java
index 74bb8196f6e..e82fab4b026 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/from/impl/SubqueryTableSegmentBinder.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/segment/from/impl/SubqueryTableSegmentBinder.java
@@ -62,6 +62,7 @@ public final class SubqueryTableSegmentBinder {
SubquerySegment boundedSubquerySegment = new
SubquerySegment(segment.getSubquery().getStartIndex(),
segment.getSubquery().getStopIndex(), boundedSelect);
boundedSubquerySegment.setSubqueryType(segment.getSubquery().getSubqueryType());
SubqueryTableSegment result = new
SubqueryTableSegment(boundedSubquerySegment);
+ fillPivotColumnNamesInBinderContext(segment, statementBinderContext);
segment.getAliasSegment().ifPresent(result::setAlias);
IdentifierValue subqueryTableName =
segment.getAliasSegment().map(AliasSegment::getIdentifier).orElseGet(() -> new
IdentifierValue(""));
tableBinderContexts.put(subqueryTableName.getValue().toLowerCase(),
@@ -69,6 +70,14 @@ public final class SubqueryTableSegmentBinder {
return result;
}
+ private static void fillPivotColumnNamesInBinderContext(final
SubqueryTableSegment segment, final SQLStatementBinderContext
statementBinderContext) {
+ segment.getPivot().ifPresent(optional -> {
+ for (ColumnSegment each : optional.getPivotInColumns()) {
+
statementBinderContext.getPivotColumnNames().add(each.getIdentifier().getValue().toLowerCase());
+ }
+ });
+ }
+
private static Collection<ProjectionSegment>
createSubqueryProjections(final Collection<ProjectionSegment> projections,
final IdentifierValue subqueryTableName) {
Collection<ProjectionSegment> result = new LinkedList<>();
for (ProjectionSegment each : projections) {
diff --git
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/SQLStatementBinderContext.java
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/SQLStatementBinderContext.java
index 21c5ed6ccf6..34dafe75eb5 100644
---
a/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/SQLStatementBinderContext.java
+++
b/infra/binder/src/main/java/org/apache/shardingsphere/infra/binder/statement/SQLStatementBinderContext.java
@@ -50,4 +50,6 @@ public final class SQLStatementBinderContext {
private final Collection<ProjectionSegment> joinTableProjectionSegments =
new LinkedList<>();
private final Map<String, TableSegmentBinderContext>
externalTableBinderContexts = new CaseInsensitiveMap<>();
+
+ private final Collection<String> pivotColumnNames = new HashSet<>();
}
diff --git
a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
index f8444a02303..6b850168f86 100644
--- a/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
+++ b/parser/sql/dialect/oracle/src/main/antlr4/imports/oracle/DMLStatement.g4
@@ -513,7 +513,7 @@ modifyExternalTableProperties
pivotClause
: PIVOT XML?
- LP_ aggregationFunctionName LP_ expr RP_ (AS? alias)? (COMMA_
aggregationFunctionName LP_ expr RP_ (AS? alias)?)* pivotForClause
pivotInClause RP_
+ LP_ aggregationFunction (AS? alias)? (COMMA_ aggregationFunction (AS?
alias)?)* pivotForClause pivotInClause RP_
;
pivotForClause
@@ -521,11 +521,15 @@ pivotForClause
;
pivotInClause
- : IN LP_ ((expr | exprList) (AS? alias)? (COMMA_ (expr | exprList) (AS?
alias)?)*
+ : IN LP_ (pivotInClauseExpr (COMMA_ pivotInClauseExpr)*
| selectSubquery
| ANY (COMMA_ ANY)*) RP_
;
+pivotInClauseExpr
+ : (expr | exprList) (AS? alias)?
+ ;
+
unpivotClause
: UNPIVOT ((INCLUDE | EXCLUDE) NULLS)? LP_ (columnName | columnNames)
pivotForClause unpivotInClause RP_
;
diff --git
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
index ae687051fb3..08ce6ff45d3 100644
---
a/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
+++
b/parser/sql/dialect/oracle/src/main/java/org/apache/shardingsphere/sql/parser/oracle/visitor/statement/type/OracleDMLStatementVisitor.java
@@ -72,6 +72,7 @@ import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.MultiT
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.OrderByClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.OuterJoinClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.ParenthesisSelectSubqueryContext;
+import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.PivotClauseContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.QueryBlockContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.QueryNameContext;
import
org.apache.shardingsphere.sql.parser.autogen.OracleStatementParser.QueryTableExprClauseContext;
@@ -154,6 +155,7 @@ import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasAvai
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.ModelSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.PivotSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.WithSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.CollectionTableSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.FunctionTableSegment;
@@ -420,6 +422,21 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
return result;
}
+ @Override
+ public ASTNode visitPivotClause(final PivotClauseContext ctx) {
+ ColumnSegment pivotForColumn = (ColumnSegment)
visitColumnName(ctx.pivotForClause().columnName());
+ Collection<ColumnSegment> pivotInColumns = new LinkedList<>();
+ if (null != ctx.pivotInClause()) {
+ ctx.pivotInClause().pivotInClauseExpr().forEach(each -> {
+ ExpressionSegment expr = (ExpressionSegment)
visit(each.expr());
+ String columnName = null != each.alias() && null !=
each.alias().identifier() ? each.alias().identifier().IDENTIFIER_().getText() :
expr.getText();
+ ColumnSegment columnSegment = new
ColumnSegment(each.getStart().getStartIndex(), each.getStop().getStopIndex(),
new IdentifierValue(columnName));
+ pivotInColumns.add(columnSegment);
+ });
+ }
+ return new PivotSegment(ctx.getStart().getStartIndex(),
ctx.getStop().getStopIndex(), pivotForColumn, pivotInColumns);
+ }
+
@Override
public ASTNode visitDmlTableClause(final DmlTableClauseContext ctx) {
return visit(ctx.tableName());
@@ -1016,7 +1033,17 @@ public final class OracleDMLStatementVisitor extends
OracleStatementVisitor impl
@Override
public ASTNode visitQueryTableExprClause(final QueryTableExprClauseContext
ctx) {
- return visit(ctx.queryTableExpr());
+ ASTNode result = visit(ctx.queryTableExpr());
+ if (null != ctx.pivotClause()) {
+ PivotSegment pivotClause = (PivotSegment) visit(ctx.pivotClause());
+ if (result instanceof SubqueryTableSegment) {
+ ((SubqueryTableSegment) result).setPivot(pivotClause);
+ }
+ if (result instanceof SimpleTableSegment) {
+ ((SimpleTableSegment) result).setPivot(pivotClause);
+ }
+ }
+ return result;
}
@Override
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/PivotSegment.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/PivotSegment.java
new file mode 100644
index 00000000000..d2bbfc70948
--- /dev/null
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/PivotSegment.java
@@ -0,0 +1,41 @@
+/*
+ * 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.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.common.segment.generic;
+
+import lombok.Getter;
+import lombok.RequiredArgsConstructor;
+import org.apache.shardingsphere.sql.parser.sql.common.segment.SQLSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.column.ColumnSegment;
+
+import java.util.Collection;
+
+/**
+ * Pivot segment.
+ */
+@RequiredArgsConstructor
+@Getter
+public final class PivotSegment implements SQLSegment {
+
+ private final int startIndex;
+
+ private final int stopIndex;
+
+ private final ColumnSegment pivotForColumn;
+
+ private final Collection<ColumnSegment> pivotInColumns;
+}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SimpleTableSegment.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SimpleTableSegment.java
index ff8fc744d19..88358eaeae0 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SimpleTableSegment.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SimpleTableSegment.java
@@ -23,6 +23,7 @@ import lombok.Setter;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerAvailable;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.OwnerSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.PivotSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import java.util.Optional;
@@ -42,6 +43,9 @@ public final class SimpleTableSegment implements
TableSegment, OwnerAvailable {
@Setter
private AliasSegment alias;
+ @Setter
+ private PivotSegment pivot;
+
@Override
public int getStartIndex() {
if (null == owner) {
@@ -78,4 +82,13 @@ public final class SimpleTableSegment implements
TableSegment, OwnerAvailable {
public Optional<AliasSegment> getAliasSegment() {
return Optional.ofNullable(alias);
}
+
+ /**
+ * Get pivot segment.
+ *
+ * @return pivot segment
+ */
+ public Optional<PivotSegment> getPivot() {
+ return Optional.ofNullable(pivot);
+ }
}
diff --git
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SubqueryTableSegment.java
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SubqueryTableSegment.java
index 310bdfd3497..f562992bf1b 100644
---
a/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SubqueryTableSegment.java
+++
b/parser/sql/statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/common/segment/generic/table/SubqueryTableSegment.java
@@ -22,6 +22,7 @@ import lombok.RequiredArgsConstructor;
import lombok.Setter;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.dml.expr.subquery.SubquerySegment;
import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.AliasSegment;
+import
org.apache.shardingsphere.sql.parser.sql.common.segment.generic.PivotSegment;
import
org.apache.shardingsphere.sql.parser.sql.common.value.identifier.IdentifierValue;
import java.util.Optional;
@@ -38,6 +39,9 @@ public final class SubqueryTableSegment implements
TableSegment {
@Setter
private AliasSegment alias;
+ @Setter
+ private PivotSegment pivot;
+
@Override
public Optional<String> getAliasName() {
return null == alias ? Optional.empty() :
Optional.ofNullable(alias.getIdentifier().getValue());
@@ -48,6 +52,15 @@ public final class SubqueryTableSegment implements
TableSegment {
return Optional.ofNullable(alias).map(AliasSegment::getIdentifier);
}
+ /**
+ * Get pivot segment.
+ *
+ * @return pivot segment
+ */
+ public Optional<PivotSegment> getPivot() {
+ return Optional.ofNullable(pivot);
+ }
+
@Override
public int getStartIndex() {
return subquery.getStartIndex();
