This is an automated email from the ASF dual-hosted git repository.
mihaibudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new a7f3290b7f [CALCITE-7028] Implement SQL pipe syntax
a7f3290b7f is described below
commit a7f3290b7fe2660fb9386a4fcdf2e624ae52ed63
Author: Gian Merlino <[email protected]>
AuthorDate: Thu Oct 22 13:26:00 2026 -0700
[CALCITE-7028] Implement SQL pipe syntax
This patch adds FROM and |> WHERE from SQL pipe syntax. The syntax is
implemented in the core parser, and is available only in certain
conformances.
The approach is to keep the operators in the parse tree through
validation, ultimately converting them to relational operators in
SqlToRelConverter. This approach enables us to adhere to pipe syntax
rules, especially the rules around table alias visibility and sort
ordering retention. It also enables us to write pipe-syntax-aware
error messages.
To simplify validation and conversion, each pipe operator is associated
with a surrogate SELECT that acts as its stand-in. The pipe operator
itself is used for pipe-specific validations, and retaining the correct
scopes and collations.
---
core/src/main/codegen/templates/Parser.jj | 157 +++++++++++-
.../apache/calcite/runtime/CalciteResource.java | 12 +
.../java/org/apache/calcite/sql/SqlFromQuery.java | 103 ++++++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 39 ++-
.../java/org/apache/calcite/sql/SqlPipeCall.java | 106 +++++++++
.../java/org/apache/calcite/sql/SqlPipeInput.java | 92 +++++++
.../java/org/apache/calcite/sql/SqlPipeQuery.java | 78 ++++++
.../java/org/apache/calcite/sql/SqlPipeWhere.java | 88 +++++++
.../org/apache/calcite/sql/SqlSelectOperator.java | 12 +-
.../main/java/org/apache/calcite/sql/SqlUtil.java | 21 ++
.../org/apache/calcite/sql/advise/SqlAdvisor.java | 13 +-
.../sql/fun/SqlMultisetQueryConstructor.java | 10 +-
.../org/apache/calcite/sql/validate/ListScope.java | 11 +
.../calcite/sql/validate/PipeInputNamespace.java | 42 ++++
.../apache/calcite/sql/validate/PipeNamespace.java | 139 +++++++++++
.../sql/validate/SqlAbstractConformance.java | 4 +
.../calcite/sql/validate/SqlConformance.java | 16 ++
.../calcite/sql/validate/SqlConformanceEnum.java | 11 +
.../sql/validate/SqlDelegatingConformance.java | 4 +
.../calcite/sql/validate/SqlValidatorImpl.java | 125 +++++++++-
.../sql/validate/implicit/TypeCoercionImpl.java | 20 +-
.../apache/calcite/sql2rel/SqlToRelConverter.java | 173 +++++++++++++-
.../calcite/runtime/CalciteResource.properties | 4 +
.../apache/calcite/sql/test/SqlAdvisorTest.java | 12 +
.../apache/calcite/test/SqlToRelConverterTest.java | 98 ++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 265 +++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 193 +++++++++++++++
core/src/test/resources/pipe_iq_googlesql.py | 78 ++++++
.../src/test/resources/pipe_iq_googlesql_setup.sql | 42 ++++
core/src/test/resources/sql/pipe.iq | 157 ++++++++++++
site/_docs/reference.md | 24 ++
.../apache/calcite/sql/parser/SqlParserTest.java | 230 ++++++++++++++++++
32 files changed, 2340 insertions(+), 39 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 6317b9c088..2c776169ef 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -55,6 +55,7 @@ import org.apache.calcite.sql.SqlDynamicParam;
import org.apache.calcite.sql.SqlExplain;
import org.apache.calcite.sql.SqlExplainFormat;
import org.apache.calcite.sql.SqlExplainLevel;
+import org.apache.calcite.sql.SqlFromQuery;
import org.apache.calcite.sql.SqlFunction;
import org.apache.calcite.sql.SqlFunctionCategory;
import org.apache.calcite.sql.SqlHint;
@@ -84,6 +85,7 @@ import org.apache.calcite.sql.SqlNodeList;
import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOrderBy;
+import org.apache.calcite.sql.SqlPipeWhere;
import org.apache.calcite.sql.SqlPivot;
import org.apache.calcite.sql.SqlPostfixOperator;
import org.apache.calcite.sql.SqlPrefixOperator;
@@ -689,6 +691,7 @@ JAVACODE boolean matchesPrefix(int[] seq, int[][] prefixes)
SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext) :
{
+ final boolean with = getToken(1).kind == WITH;
SqlNode e;
final List<Object> list = new ArrayList<Object>();
}
@@ -700,13 +703,16 @@ SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext)
:
LOOKAHEAD(2)
e = Query(exprContext)
e = OrderByLimitOpt(e)
+ e = PipeOpt(e, with)
{ return e; }
|
e = TableRef1(ExprContext.ACCEPT_QUERY_OR_JOIN)
( e = JoinTable(e) )*
{ list.add(e); }
( AddSetOpQuery(list, exprContext) )*
- { return SqlParserUtil.toTree(list); }
+ { e = SqlParserUtil.toTree(list); }
+ e = PipeOpt(e, false)
+ { return e; }
)
}
@@ -743,11 +749,13 @@ SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext)
:
*/
SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
{
+ final boolean with = getToken(1).kind == WITH;
SqlNode e;
}
{
e = QueryOrExpr(exprContext)
e = OrderByLimitOpt(e)
+ e = PipeOpt(e, with)
{ return e; }
}
@@ -899,7 +907,7 @@ void LimitClause(Span s, SqlNode[] offsetFetch) :
}
/**
- * Parses a leaf in a query expression (SELECT, VALUES or TABLE).
+ * Parses a leaf in a query expression (SELECT, VALUES, TABLE or FROM).
*/
SqlNode LeafQuery(ExprContext exprContext) :
{
@@ -915,6 +923,138 @@ SqlNode LeafQuery(ExprContext exprContext) :
e = TableConstructor() { return e; }
|
e = ExplicitTable(getPos()) { return e; }
+|
+ e = FromQuery() { return e; }
+}
+
+/**
+ * Parses a standalone FROM clause. Only valid under conformance levels
+ * that allow pipe syntax.
+ */
+SqlNode FromQuery() :
+{
+ final SqlNode fromClause;
+ final Span s;
+}
+{
+ <FROM> {
+ s = span();
+ if (!this.conformance.isPipeSyntaxAllowed()) {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.pipeSyntaxNotAllowed());
+ }
+ }
+ fromClause = FromClause()
+ // Report helpful error messages for common mistakes. (Generally, FROM
+ // must be followed by pipe operators.)
+ [
+ LOOKAHEAD(1)
+ (
+ <WHERE> {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.pipeClauseNotAllowed("WHERE", "|> WHERE"));
+ }
+ |
+ <ORDER> {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryClauseNotAllowed("ORDER BY"));
+ }
+ |
+ <LIMIT> {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryClauseNotAllowed("LIMIT"));
+ }
+ |
+ <OFFSET> {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryClauseNotAllowed("OFFSET"));
+ }
+ |
+ <FETCH> {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryClauseNotAllowed("FETCH"));
+ }
+ |
+ ( <UNION> | <INTERSECT> | <EXCEPT> | <SET_MINUS> ) {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryInSetOpNotAllowed());
+ }
+ )
+ ]
+ { return new SqlFromQuery(s.end(this), fromClause); }
+}
+
+/**
+ * Parses zero or more pipe operators that follow the query {@code e}.
+ *
+ * <p>The flag {@code with} signifies whether {@code e} is a WITH that is not
+ * in parentheses, or an ORDER BY of such a WITH. If so, the operators are
+ * applied to the body of the WITH, so that they can reference its CTEs.
+ */
+SqlNode PipeOpt(SqlNode e, boolean with) :
+{
+ SqlWith w = null;
+}
+{
+ {
+ if (with && getToken(1).kind == PIPE) {
+ if (e instanceof SqlOrderBy) {
+ final SqlOrderBy orderBy = (SqlOrderBy) e;
+ w = (SqlWith) orderBy.query;
+ orderBy.setOperand(0, w.body);
+ } else {
+ w = (SqlWith) e;
+ e = w.body;
+ }
+ }
+ }
+ ( e = PipeOperator(e) )*
+ {
+ if (w != null) {
+ w.setOperand(1, e);
+ return w;
+ }
+ return e;
+ }
+}
+
+/**
+ * Parses one pipe operation.
+ */
+SqlNode PipeOperator(SqlNode e) :
+{
+ final Span s;
+}
+{
+ <PIPE> {
+ s = span();
+ if (!this.conformance.isPipeSyntaxAllowed()) {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.pipeSyntaxNotAllowed());
+ }
+ if (!e.isA(SqlKind.QUERY)) {
+ // A pipe operator applies only to a query.
+ checkNonQueryExpression(ExprContext.ACCEPT_QUERY);
+ }
+ }
+ (
+ e = PipeWhere(s, e)
+ )
+ { return e; }
+}
+
+/**
+ * Parses a {@code |> WHERE} operation.
+ */
+SqlNode PipeWhere(Span s, SqlNode e) :
+{
+ final SqlNode condition;
+}
+{
+ <WHERE> condition = Expression(ExprContext.ACCEPT_SUB_QUERY)
+ {
+ return new SqlPipeWhere(s.end(this), e, condition);
+ }
}
/**
@@ -3982,6 +4122,18 @@ void AddSetOpQuery(List<Object> list, ExprContext
exprContext) :
pos = getPos();
checkQueryExpression(exprContext);
}
+ [
+ // Deliberately require parentheses, as GoogleSQL does.
+ LOOKAHEAD(1)
+ <FROM> {
+ if (!this.conformance.isPipeSyntaxAllowed()) {
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.pipeSyntaxNotAllowed());
+ }
+ throw SqlUtil.newContextException(getPos(),
+ RESOURCE.fromQueryInSetOpNotAllowed());
+ }
+ ]
e = LeafQueryOrExpr(ExprContext.ACCEPT_QUERY) {
list.add(new SqlParserUtil.ToTreeListItem(op, pos));
list.add(e);
@@ -9677,6 +9829,7 @@ void NonReservedKeyWord2of3() :
| < QUOTE: "'" >
| < DOUBLE_QUOTE: "\"" >
| < VERTICAL_BAR: "|" >
+| < PIPE: "|>" >
| < CARET: "^" >
| < AMPERSAND: "&" >
| < LEFTSHIFT: "<<" >
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index d7ceb5bbe2..2ce43a1419 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -51,6 +51,18 @@ public interface CalciteResource {
@BaseMessage("VALUE is not allowed under the current SQL conformance level")
ExInst<CalciteException> valueNotAllowed();
+ @BaseMessage("Pipe syntax is not allowed under the current SQL conformance
level")
+ ExInst<CalciteException> pipeSyntaxNotAllowed();
+
+ @BaseMessage("{0} clause is not allowed after a FROM query; use ''{1}''")
+ ExInst<CalciteException> pipeClauseNotAllowed(String a0, String a1);
+
+ @BaseMessage("{0} clause is not allowed after a FROM query")
+ ExInst<CalciteException> fromQueryClauseNotAllowed(String a0);
+
+ @BaseMessage("A FROM query must be in parentheses to be an operand of UNION,
INTERSECT or EXCEPT")
+ ExInst<CalciteException> fromQueryInSetOpNotAllowed();
+
@BaseMessage("Illegal {0} literal ''{1}'': {2}")
ExInst<CalciteException> illegalLiteral(String a0, String a1, String a2);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlFromQuery.java
b/core/src/main/java/org/apache/calcite/sql/SqlFromQuery.java
new file mode 100644
index 0000000000..f4c232aba3
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlFromQuery.java
@@ -0,0 +1,103 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlConformance;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
+
+import static com.google.common.base.Preconditions.checkArgument;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Parse tree node that represents a standalone {@code FROM} query in SQL pipe
+ * syntax; for example, {@code FROM emp}.
+ *
+ * @see SqlConformance#isPipeSyntaxAllowed()
+ */
+public class SqlFromQuery extends SqlPipeQuery {
+ public static final SqlSpecialOperator OPERATOR = new Operator() {
+ @Override public SqlCall createCall(@Nullable SqlLiteral functionQualifier,
+ SqlParserPos pos, @Nullable SqlNode... operands) {
+ checkArgument(operands.length == 1);
+ return new SqlFromQuery(pos, requireNonNull(operands[0], "from"));
+ }
+ };
+
+ private SqlNode from;
+
+ //~ Constructors -----------------------------------------------------------
+
+ public SqlFromQuery(SqlParserPos pos, SqlNode from) {
+ super(pos);
+ this.from = from;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public SqlKind getKind() {
+ return SqlKind.FROM_QUERY;
+ }
+
+ @Override public SqlSelect surrogateSelect() {
+ return surrogate(null, SqlNodeList.SINGLETON_STAR, from);
+ }
+
+ @Override public SqlOperator getOperator() {
+ return OPERATOR;
+ }
+
+ @Override public List<SqlNode> getOperandList() {
+ return ImmutableList.of(from);
+ }
+
+ @Override public void setOperand(int i, @Nullable SqlNode operand) {
+ if (i != 0) {
+ throw new AssertionError(i);
+ }
+ from = requireNonNull(operand, "operand");
+ }
+
+ /** Definition of the standalone {@code FROM} query operator. */
+ private static class Operator extends SqlPipeQuery.Operator {
+ private Operator() {
+ super("FROM", SqlKind.FROM_QUERY);
+ }
+
+ @Override public SqlSyntax getSyntax() {
+ return SqlSyntax.PREFIX;
+ }
+
+ @Override public void unparse(
+ SqlWriter writer,
+ SqlCall call,
+ int leftPrec,
+ int rightPrec) {
+ final SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.SELECT);
+ writer.sep("FROM");
+ SqlUtil.unparseFromClause(writer, ((SqlFromQuery) call).from);
+ writer.endList(frame);
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index e0d2786e68..fd23a131a0 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -16,6 +16,8 @@
*/
package org.apache.calcite.sql;
+import org.apache.calcite.sql.validate.PipeNamespace;
+
import com.google.common.collect.Sets;
import org.apiguardian.api.API;
@@ -593,6 +595,26 @@ public enum SqlKind {
*/
EXPLICIT_TABLE,
+ /**
+ * Standalone {@code FROM}, e.g. {@code FROM t}, in pipe syntax.
+ */
+ FROM_QUERY,
+
+ /**
+ * Call to a pipe operator, e.g. {@code q |> WHERE c}.
+ *
+ * @see SqlPipeCall
+ */
+ PIPE_CALL,
+
+ /**
+ * Input of a pipe operator; that is, the rows of the query it applies to,
+ * in the {@code FROM} clause of its surrogate SELECT.
+ *
+ * @see SqlPipeInput
+ */
+ PIPE_INPUT,
+
/**
* Scalar query; that is, a sub-query used in an expression context, and
* returning one row and one column.
@@ -1441,6 +1463,13 @@ public enum SqlKind {
CREATE_TYPE, DROP_TYPE,
SET_OPTION, OTHER_DDL);
+ /**
+ * Category consisting of the queries written in pipe syntax. These are
+ * the kinds whose namespace is a {@link PipeNamespace}.
+ */
+ public static final EnumSet<SqlKind> PIPE_QUERY =
+ EnumSet.of(FROM_QUERY, PIPE_CALL);
+
/**
* Category consisting of query node types.
*
@@ -1450,12 +1479,16 @@ public enum SqlKind {
* {@link #INTERSECT},
* {@link #UNION},
* {@link #VALUES},
+ * {@link #WITH},
* {@link #ORDER_BY},
- * {@link #EXPLICIT_TABLE}.
+ * {@link #EXPLICIT_TABLE},
+ * {@link #FROM_QUERY},
+ * {@link #PIPE_CALL},
+ * {@link #PIPE_INPUT}.
*/
public static final EnumSet<SqlKind> QUERY =
EnumSet.of(SELECT, UNION, INTERSECT, EXCEPT, VALUES, WITH, ORDER_BY,
- EXPLICIT_TABLE);
+ EXPLICIT_TABLE, FROM_QUERY, PIPE_CALL, PIPE_INPUT);
/**
* Category consisting of all expression operators.
@@ -1493,7 +1526,7 @@ public enum SqlKind {
TIMESTAMP_ADD, TIMESTAMP_DIFF, TIMESTAMP_SUB,
EXTRACT, INTERVAL,
LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY,
- ORDER_BY_ALL,
+ ORDER_BY_ALL, FROM_QUERY, PIPE_CALL, PIPE_INPUT,
NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE,
VALUES, WITH, WITH_ITEM, CYCLE, ITEM, SKIP_TO_FIRST,
SKIP_TO_LAST,
JSON_VALUE_EXPRESSION, UNNEST),
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlPipeCall.java
b/core/src/main/java/org/apache/calcite/sql/SqlPipeCall.java
new file mode 100644
index 0000000000..b2ac6c519a
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlPipeCall.java
@@ -0,0 +1,106 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlConformance;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Base class for parse tree nodes that represent a call to a pipe operator.
+ *
+ * @see SqlConformance#isPipeSyntaxAllowed()
+ * @see SqlKind#PIPE_CALL
+ */
+public abstract class SqlPipeCall extends SqlPipeQuery {
+ public static final String PIPE = "|>";
+
+ protected SqlNode query;
+
+ //~ Constructors -----------------------------------------------------------
+
+ protected SqlPipeCall(SqlParserPos pos, SqlNode query) {
+ super(pos);
+ this.query = requireNonNull(query, "query");
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ /** Creates a surrogate SELECT with the given select list, and whose FROM
+ * clause is a {@link SqlPipeInput} of this pipe operator call. */
+ protected SqlSelect surrogate(@Nullable SqlNodeList keywordList,
+ SqlNodeList selectList) {
+ return surrogate(keywordList, selectList, SqlPipeInput.of(this));
+ }
+
+ /** Returns the query that this pipe operator applies to. */
+ public SqlNode getQuery() {
+ return query;
+ }
+
+ @Override public void setOperand(int i, @Nullable SqlNode operand) {
+ if (i != 0) {
+ throw new AssertionError(i);
+ }
+ query = requireNonNull(operand, "operand");
+ }
+
+ @Override public final SqlKind getKind() {
+ return SqlKind.PIPE_CALL;
+ }
+
+ /** Definition of a pipe operator. Unparses the input query, then the
+ * operator's keyword, then a tail supplied by the sub-class. */
+ public abstract static class Operator extends SqlPipeQuery.Operator {
+ protected Operator(String name) {
+ super(name, SqlKind.PIPE_CALL);
+ }
+
+ @Override public SqlSyntax getSyntax() {
+ return SqlSyntax.POSTFIX;
+ }
+
+ @Override public final void unparse(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ final SqlPipeCall pipe = (SqlPipeCall) call;
+ // An ORDER_BY frame starts each operator on a new line, unindented.
+ final SqlWriter.Frame frame =
+ writer.startList(SqlWriter.FrameTypeEnum.ORDER_BY);
+ final SqlNode query = pipe.query;
+ if (query instanceof SqlWith
+ || query instanceof SqlOrderBy
+ && ((SqlOrderBy) query).query instanceof SqlWith) {
+ // Without parentheses, the operator would be re-parsed into the WITH.
+ final SqlWriter.Frame frame1 = writer.startList("(", ")");
+ query.unparse(writer, 0, 0);
+ writer.endList(frame1);
+ } else {
+ query.unparse(writer, 0, 0);
+ }
+ writer.sep(getName());
+ unparseTail(writer, pipe);
+ writer.endList(frame);
+ }
+
+ /** Unparses whatever follows the operator's keyword. */
+ protected abstract void unparseTail(SqlWriter writer,
+ SqlPipeCall call);
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlPipeInput.java
b/core/src/main/java/org/apache/calcite/sql/SqlPipeInput.java
new file mode 100644
index 0000000000..d55908f507
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlPipeInput.java
@@ -0,0 +1,92 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
+
+import static com.google.common.base.Preconditions.checkArgument;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Parse tree node that represents the input table of a pipe operator.
+ *
+ * <p>The parser does not create it; it occurs only as the {@code FROM} clause
+ * of a surrogate SELECT (see {@link SqlPipeQuery#surrogateSelect()}).
+ *
+ * @see SqlKind#PIPE_INPUT
+ */
+public class SqlPipeInput extends SqlCall {
+ /** Definition of the pipe input. */
+ public static final SqlSpecialOperator OPERATOR =
+ new SqlSpecialOperator("PIPE_INPUT", SqlKind.PIPE_INPUT) {
+ @Override public SqlCall createCall(
+ @Nullable SqlLiteral functionQualifier, SqlParserPos pos,
+ @Nullable SqlNode... operands) {
+ checkArgument(operands.length == 1);
+ return new SqlPipeInput(pos, requireNonNull(operands[0], "input"));
+ }
+
+ @Override public void unparse(SqlWriter writer, SqlCall call,
+ int leftPrec, int rightPrec) {
+ ((SqlPipeInput) call).input.unparse(writer, leftPrec, rightPrec);
+ }
+ };
+
+ private SqlNode input;
+
+ //~ Constructors -----------------------------------------------------------
+
+ private SqlPipeInput(SqlParserPos pos, SqlNode input) {
+ super(pos);
+ this.input = input;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ /** Creates a node that represents the input of the given pipe operator
+ * call. */
+ public static SqlPipeInput of(SqlPipeCall pipe) {
+ return new SqlPipeInput(pipe.getParserPosition(), pipe.getQuery());
+ }
+
+ /** Returns the query that this node stands for. */
+ public SqlNode getInput() {
+ return input;
+ }
+
+ @Override public SqlOperator getOperator() {
+ return OPERATOR;
+ }
+
+ @Override public List<SqlNode> getOperandList() {
+ return ImmutableList.of(input);
+ }
+
+ @Override public void setOperand(int i, @Nullable SqlNode operand) {
+ if (i != 0) {
+ throw new AssertionError(i);
+ }
+ input = requireNonNull(operand, "operand");
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlPipeQuery.java
b/core/src/main/java/org/apache/calcite/sql/SqlPipeQuery.java
new file mode 100644
index 0000000000..2711afb12f
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlPipeQuery.java
@@ -0,0 +1,78 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.util.SqlBasicVisitor;
+import org.apache.calcite.sql.util.SqlVisitor;
+import org.apache.calcite.sql.validate.SqlConformance;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+/**
+ * Base class for parse tree nodes that represent a query in SQL pipe
+ * syntax: {@link SqlFromQuery} or {@link SqlPipeCall}.
+ *
+ * @see SqlConformance#isPipeSyntaxAllowed()
+ * @see SqlKind#PIPE_QUERY
+ */
+public abstract class SqlPipeQuery extends SqlCall {
+ protected SqlPipeQuery(SqlParserPos pos) {
+ super(pos);
+ }
+
+ /**
+ * Creates the surrogate SELECT of this query: a {@code SELECT} that stands
+ * in for the query while it is validated and converted.
+ *
+ * <p>For example, {@code FROM emp |> WHERE c} is a {@link SqlPipeWhere}
+ * whose input is the {@link SqlFromQuery} {@code FROM emp}. The surrogate of
+ * {@code FROM emp} is {@code SELECT * FROM emp}, and the surrogate of the
+ * {@link SqlPipeWhere} is {@code SELECT * FROM input WHERE c}.
+ */
+ public abstract SqlSelect surrogateSelect();
+
+ /** Creates a surrogate SELECT with the given select list and FROM clause,
+ * and no other clauses. */
+ protected SqlSelect surrogate(@Nullable SqlNodeList keywordList,
+ SqlNodeList selectList, @Nullable SqlNode from) {
+ return new SqlSelect(getParserPosition(), keywordList, selectList, from,
+ null, null, null, null, null, null, null, null, null);
+ }
+
+ @Override public void validate(SqlValidator validator,
+ SqlValidatorScope scope) {
+ validator.validateQuery(this, scope, validator.getUnknownType());
+ }
+
+ /** Definition of an operator written in pipe syntax. */
+ protected abstract static class Operator extends SqlSpecialOperator {
+ protected Operator(String name, SqlKind kind) {
+ super(name, kind, 0);
+ }
+
+ @Override public <R> void acceptCall(SqlVisitor<R> visitor, SqlCall call,
+ boolean onlyExpressions, SqlBasicVisitor.ArgHandler<R> argHandler) {
+ if (!onlyExpressions) {
+ // None of the operands of a pipe operator are expressions.
+ super.acceptCall(visitor, call, onlyExpressions, argHandler);
+ }
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlPipeWhere.java
b/core/src/main/java/org/apache/calcite/sql/SqlPipeWhere.java
new file mode 100644
index 0000000000..9aa2302268
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlPipeWhere.java
@@ -0,0 +1,88 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.validate.SqlConformance;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
+
+import static com.google.common.base.Preconditions.checkArgument;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Parse tree node that represents the {@code WHERE} pipe operator in SQL pipe
+ * syntax; for example, {@code FROM emp |> WHERE deptno = 10}.
+ *
+ * @see SqlConformance#isPipeSyntaxAllowed()
+ */
+public class SqlPipeWhere extends SqlPipeCall {
+ public static final SqlSpecialOperator OPERATOR =
+ new Operator(PIPE + " WHERE") {
+ @Override public SqlCall createCall(
+ @Nullable SqlLiteral functionQualifier, SqlParserPos pos,
+ @Nullable SqlNode... operands) {
+ checkArgument(operands.length == 2);
+ return new SqlPipeWhere(pos, requireNonNull(operands[0], "query"),
+ requireNonNull(operands[1], "condition"));
+ }
+
+ @Override protected void unparseTail(SqlWriter writer,
+ SqlPipeCall call) {
+ SqlUtil.unparseConditionClause(writer,
+ ((SqlPipeWhere) call).condition, 0, 0);
+ }
+ };
+
+ private SqlNode condition;
+
+ //~ Constructors -----------------------------------------------------------
+
+ public SqlPipeWhere(SqlParserPos pos, SqlNode query, SqlNode condition) {
+ super(pos, query);
+ this.condition = condition;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public SqlSelect surrogateSelect() {
+ final SqlSelect select = surrogate(null, SqlNodeList.SINGLETON_STAR);
+ select.setWhere(condition);
+ return select;
+ }
+
+ @Override public SqlOperator getOperator() {
+ return OPERATOR;
+ }
+
+ @Override public List<SqlNode> getOperandList() {
+ return ImmutableList.of(query, condition);
+ }
+
+ @Override public void setOperand(int i, @Nullable SqlNode operand) {
+ if (i == 1) {
+ condition = requireNonNull(operand, "operand");
+ } else {
+ super.setOperand(i, operand);
+ }
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
index 1c1a8edfe8..d5ea256e11 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java
@@ -167,17 +167,7 @@ public SqlSelect createCall(
if (select.from != null) {
// Calcite SQL requires FROM but MySQL does not.
writer.sep("FROM");
-
- // for FROM clause, use precedence just below join operator to make
- // sure that an un-joined nested select will be properly
- // parenthesized
- final SqlWriter.Frame fromFrame =
- writer.startList(SqlWriter.FrameTypeEnum.FROM_LIST);
- select.from.unparse(
- writer,
- SqlJoin.COMMA_OPERATOR.getLeftPrec() - 1,
- SqlJoin.COMMA_OPERATOR.getRightPrec() - 1);
- writer.endList(fromFrame);
+ SqlUtil.unparseFromClause(writer, select.from);
}
SqlNode where = select.where;
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
index e1e6e23e4e..fc16a169fd 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlUtil.java
@@ -457,6 +457,22 @@ public static void unparseBinarySyntax(
writer.endList(frame);
}
+ /**
+ * Unparses the item of a {@code FROM} clause using a
+ * {@link SqlWriter.FrameTypeEnum#FROM_LIST} frame.
+ *
+ * @param writer Writer
+ * @param from Item of the FROM clause
+ */
+ public static void unparseFromClause(SqlWriter writer, SqlNode from) {
+ final SqlWriter.Frame fromFrame =
+ writer.startList(SqlWriter.FrameTypeEnum.FROM_LIST);
+ from.unparse(writer,
+ SqlJoin.COMMA_OPERATOR.getLeftPrec() - 1,
+ SqlJoin.COMMA_OPERATOR.getRightPrec() - 1);
+ writer.endList(fromFrame);
+ }
+
/**
* Unparses a condition using a
* {@link SqlWriter.FrameTypeEnum#WHERE_LIST} frame, which provides
@@ -904,6 +920,11 @@ public static SqlNode getSelectListItem(SqlNode query, int
i) {
final List<SqlNode> operandList = ((SqlBasicCall)
query).getOperandList();
return getSelectListItem(operandList.get(0), i);
+ case FROM_QUERY:
+ case PIPE_CALL:
+ // A query in pipe syntax has no select list to point into.
+ return query;
+
default:
// Unexpected type of query.
throw Util.needToImplement(query);
diff --git a/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisor.java
b/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisor.java
index c830dc2cd7..627059e23e 100644
--- a/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisor.java
+++ b/core/src/main/java/org/apache/calcite/sql/advise/SqlAdvisor.java
@@ -21,6 +21,7 @@
import org.apache.calcite.runtime.CalciteException;
import org.apache.calcite.sql.SqlIdentifier;
import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPipeCall;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.parser.SqlAbstractParserImpl;
@@ -404,10 +405,14 @@ private static boolean isSelectListItem(SqlNode root,
// symbols such as '<Identifier>'.
if (tokenName.startsWith("\"")
&& tokenName.endsWith("\"")) {
- hintList.add(
- new SqlMonikerImpl(
- tokenName.substring(1, tokenName.length() - 1),
- SqlMonikerType.KEYWORD));
+ final String keyword =
+ tokenName.substring(1, tokenName.length() - 1);
+ if (keyword.equals(SqlPipeCall.PIPE)
+ && !parserConfig.conformance().isPipeSyntaxAllowed()) {
+ // Do not include pipe operators unless pipe syntax is allowed.
+ continue;
+ }
+ hintList.add(new SqlMonikerImpl(keyword, SqlMonikerType.KEYWORD));
}
}
return null;
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlMultisetQueryConstructor.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlMultisetQueryConstructor.java
index 05ed42ca84..eb57c7db83 100644
---
a/core/src/main/java/org/apache/calcite/sql/fun/SqlMultisetQueryConstructor.java
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlMultisetQueryConstructor.java
@@ -20,7 +20,7 @@
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
import org.apache.calcite.sql.SqlKind;
-import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlSpecialOperator;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.OperandTypes;
@@ -83,11 +83,11 @@ protected SqlMultisetQueryConstructor(String name, SqlKind
kind,
SqlValidator validator,
SqlValidatorScope scope,
SqlCall call) {
- SqlSelect subSelect = call.operand(0);
- subSelect.validateExpr(validator, scope);
+ final SqlNode subQuery = call.operand(0);
+ subQuery.validateExpr(validator, scope);
final SqlValidatorNamespace ns =
- requireNonNull(validator.getNamespace(subSelect),
- () -> "namespace is missing for " + subSelect);
+ requireNonNull(validator.getNamespace(subQuery),
+ () -> "namespace is missing for " + subQuery);
final RelDataType rowType = requireNonNull(ns.getRowType(), "rowType");
final SqlCallBinding opBinding = new SqlCallBinding(validator, scope,
call);
return typeTransform.transformType(opBinding, rowType);
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
b/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
index 81a2a070cf..0b48e10f4f 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/ListScope.java
@@ -64,6 +64,17 @@ protected ListScope(SqlValidatorScope parent) {
children.add(new ScopeChild(children.size(), alias, ns, nullable));
}
+ /** Replaces the children of this scope, renumbering them. */
+ void replaceChildren(List<ScopeChild> newChildren) {
+ final List<ScopeChild> list = ImmutableList.copyOf(newChildren);
+ children.clear();
+ for (ScopeChild child : list) {
+ children.add(
+ new ScopeChild(children.size(), child.name, child.namespace,
+ child.nullable));
+ }
+ }
+
/**
* Returns an immutable list of child namespaces.
*
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/PipeInputNamespace.java
b/core/src/main/java/org/apache/calcite/sql/validate/PipeInputNamespace.java
new file mode 100644
index 0000000000..129d9ce14d
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/validate/PipeInputNamespace.java
@@ -0,0 +1,42 @@
+/*
+ * 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.calcite.sql.validate;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPipeInput;
+
+/**
+ * Namespace of a {@link SqlPipeInput}.
+ */
+class PipeInputNamespace extends AbstractNamespace {
+ private final SqlPipeInput node;
+
+ PipeInputNamespace(SqlValidatorImpl validator, SqlPipeInput node,
+ SqlNode enclosingNode) {
+ super(validator, enclosingNode);
+ this.node = node;
+ }
+
+ @Override public SqlNode getNode() {
+ return node;
+ }
+
+ @Override protected RelDataType validateImpl(RelDataType targetRowType) {
+ return validator.getNamespaceOrThrow(node.getInput()).getRowType();
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/PipeNamespace.java
b/core/src/main/java/org/apache/calcite/sql/validate/PipeNamespace.java
new file mode 100644
index 0000000000..8125e92ec1
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/validate/PipeNamespace.java
@@ -0,0 +1,139 @@
+/*
+ * 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.calcite.sql.validate;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlPipeCall;
+import org.apache.calcite.sql.SqlPipeQuery;
+import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.sql.SqlUtil;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
+
+import static java.util.Objects.requireNonNull;
+
+/**
+ * Namespace for a query written in SQL pipe syntax.
+ */
+public class PipeNamespace extends AbstractNamespace {
+ private final SqlPipeQuery node;
+ private final SqlSelect surrogateSelect;
+ /** Scope that encloses the pipeline. */
+ private final SqlValidatorScope scope;
+
+ /** Scope children that an operator applied to this one can see. Computed
+ * on demand, once this namespace has been validated. */
+ private @Nullable List<ScopeChild> outChildren;
+
+ //~ Constructors -----------------------------------------------------------
+
+ PipeNamespace(SqlValidatorImpl validator, SqlPipeQuery node,
+ SqlSelect surrogateSelect, SqlValidatorScope scope,
+ SqlNode enclosingNode) {
+ super(validator, enclosingNode);
+ this.node = node;
+ this.surrogateSelect = surrogateSelect;
+ this.scope = scope;
+ }
+
+ //~ Methods ----------------------------------------------------------------
+
+ @Override public SqlNode getNode() {
+ return node;
+ }
+
+ /** Returns the surrogate SELECT of this namespace's query. */
+ public SqlSelect getSurrogateSelect() {
+ return surrogateSelect;
+ }
+
+ @Override protected RelDataType validateImpl(RelDataType targetRowType) {
+ if (node instanceof SqlPipeCall) {
+ // Scopes are built during registration, but which of the input's
+ // tables this operator can see (and so which aliases resolve) is known
+ // only once the input is validated. Replace the scope's children here
+ // so this operator's expressions see the correct table aliases.
+ final SqlNode input = ((SqlPipeCall) node).getQuery();
+ validator.validateQuery(input, scope, validator.unknownType);
+ final SelectScope selectScope =
+ (SelectScope) validator.getFromScope(surrogateSelect);
+ selectScope.replaceChildren(validator.pipeChildren(input));
+ }
+ validator.validateQuery(surrogateSelect, scope, targetRowType);
+ filterRequirement = getSurrogateNamespace().getFilterRequirement();
+ return getSurrogateNamespace().getRowType();
+ }
+
+ /** Returns the namespace of the surrogate SELECT. */
+ private SqlValidatorNamespace getSurrogateNamespace() {
+ return validator.getNamespaceOrThrow(surrogateSelect);
+ }
+
+ @Override public boolean supportsModality(SqlModality modality) {
+ return getSurrogateNamespace().supportsModality(modality);
+ }
+
+ @Override public SqlMonotonicity getMonotonicity(String columnName) {
+ return getSurrogateNamespace().getMonotonicity(columnName);
+ }
+
+ /** Returns the scope children that an operator applied to this query
+ * can see. */
+ List<ScopeChild> outChildren() {
+ if (outChildren == null) {
+ final SelectScope selectScope =
+ (SelectScope) validator.getFromScope(surrogateSelect);
+ outChildren = passesThrough(selectScope)
+ ? ImmutableList.copyOf(selectScope.children)
+ : validator.singleAnonymousScopeChild(node, this);
+ }
+ return outChildren;
+ }
+
+ /** Returns whether this query's columns are exactly the columns of the
+ * tables its surrogate reads, in order. If so, a pipe operator applied
+ * to this query can see those tables and qualify their columns with the
+ * table aliases. */
+ private boolean passesThrough(SelectScope selectScope) {
+ final List<SqlNode> items =
+ requireNonNull(selectScope.getExpandedSelectList(),
+ "expandedSelectList");
+ int i = 0;
+ for (ScopeChild child : selectScope.children) {
+ for (RelDataTypeField field
+ : child.namespace.getRowType().getFieldList()) {
+ if (i >= items.size()) {
+ return false;
+ }
+ final SqlNode item = SqlUtil.stripAs(items.get(i++));
+ if (!(item instanceof SqlIdentifier)
+ || !((SqlIdentifier) item).names.equals(
+ ImmutableList.of(child.name, field.getName()))) {
+ return false;
+ }
+ }
+ }
+ return i == items.size();
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
index 7734f1b2fe..c67f970d0f 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlAbstractConformance.java
@@ -188,4 +188,8 @@ public abstract class SqlAbstractConformance implements
SqlConformance {
@Override public boolean isCorrelatedAggregateAllowed() {
return SqlConformanceEnum.DEFAULT.isCorrelatedAggregateAllowed();
}
+
+ @Override public boolean isPipeSyntaxAllowed() {
+ return SqlConformanceEnum.DEFAULT.isPipeSyntaxAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
index 0a4041bbde..c483fb6d61 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformance.java
@@ -773,4 +773,20 @@ default boolean isCorrelatedAggregateAllowed() {
default boolean isGroupingSetsStarAllowed() {
return false;
}
+
+ /**
+ * Whether SQL pipe syntax is allowed.
+ *
+ * <p>Pipe SQL is not part of the SQL standard, but is supported by some
+ * databases, notably those using GoogleSQL and Spark SQL.
+ *
+ * <p>Among the built-in conformance levels, true in
+ * {@link SqlConformanceEnum#BABEL},
+ * {@link SqlConformanceEnum#BIG_QUERY},
+ * {@link SqlConformanceEnum#LENIENT};
+ * false otherwise.
+ */
+ default boolean isPipeSyntaxAllowed() {
+ return false;
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
index 072cdc0f68..ef237c6a58 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlConformanceEnum.java
@@ -582,4 +582,15 @@ public enum SqlConformanceEnum implements SqlConformance {
return false;
}
}
+
+ @Override public boolean isPipeSyntaxAllowed() {
+ switch (this) {
+ case BABEL:
+ case BIG_QUERY:
+ case LENIENT:
+ return true;
+ default:
+ return false;
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
index 94ed901bca..4b4975f103 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/SqlDelegatingConformance.java
@@ -197,4 +197,8 @@ protected SqlDelegatingConformance(SqlConformance delegate)
{
@Override public boolean isGroupingSetsStarAllowed() {
return delegate.isGroupingSetsStarAllowed();
}
+
+ @Override public boolean isPipeSyntaxAllowed() {
+ return delegate.isPipeSyntaxAllowed();
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 837d90b1d2..bbfa06cd03 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -76,6 +76,9 @@
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlOverOperator;
+import org.apache.calcite.sql.SqlPipeCall;
+import org.apache.calcite.sql.SqlPipeInput;
+import org.apache.calcite.sql.SqlPipeQuery;
import org.apache.calcite.sql.SqlPivot;
import org.apache.calcite.sql.SqlSampleSpec;
import org.apache.calcite.sql.SqlSelect;
@@ -452,6 +455,10 @@ public SqlConformance getConformance() {
ns = new TableConstructorNamespace(this, call, cursorScope, call);
} else if (query.getKind() == SqlKind.WITH) {
ns = new WithNamespace(this, (SqlWith) call, call);
+ } else if (query.isA(SqlKind.PIPE_QUERY)) {
+ // Should be registered already by registerSubQueries, along with
+ // the surrogates of its operators.
+ ns = getNamespaceOrThrow(query);
} else {
throw Util.unexpected(query.getKind());
}
@@ -3235,6 +3242,8 @@ private SqlNode registerFrom(
case VALUES:
case WITH:
case OTHER_FUNCTION:
+ case FROM_QUERY:
+ case PIPE_CALL:
if (alias == null) {
alias = SqlValidatorUtil.alias(node, nextGeneratedId++);
}
@@ -3247,6 +3256,13 @@ private SqlNode registerFrom(
forceNullable);
return newNode;
+ case PIPE_INPUT:
+ registerNamespace(register ? usingScope : null,
+ SqlValidatorUtil.alias(node, nextGeneratedId++),
+ new PipeInputNamespace(this, (SqlPipeInput) node, enclosingNode),
+ forceNullable);
+ return newNode;
+
case OVER:
if (!shouldAllowOverRelation()) {
throw Util.unexpected(kind);
@@ -3421,6 +3437,12 @@ private void registerQuery(
SqlCall call;
List<SqlNode> operands;
switch (node.getKind()) {
+ case FROM_QUERY:
+ case PIPE_CALL:
+ registerPipe(parentScope, usingScope, (SqlPipeQuery) node, enclosingNode,
+ alias, forceNullable);
+ break;
+
case SELECT:
final SqlSelect select = (SqlSelect) node;
final SelectNamespace selectNs =
@@ -3748,6 +3770,51 @@ private void registerQuery(
}
}
+ /**
+ * Registers a query written in pipe syntax.
+ */
+ private void registerPipe(SqlValidatorScope parentScope,
+ @Nullable SqlValidatorScope usingScope, SqlPipeQuery node,
+ SqlNode enclosingNode, @Nullable String alias, boolean forceNullable) {
+ if (node instanceof SqlPipeCall) {
+ // PipeNamespace decides, when it is validated, what this operator sees
+ // of its input.
+ final SqlNode input = ((SqlPipeCall) node).getQuery();
+ registerQuery(parentScope, null, input, input, null, false);
+ }
+ // Register the pipe query's surrogate SELECT (see
+ // SqlPipeQuery#surrogateSelect) as if it were the query. This is what lets
+ // a pipe query reuse the validation of SELECT.
+ final SqlSelect surrogate = node.surrogateSelect();
+ registerQuery(parentScope, null, surrogate, node, null, false);
+ // Associate this node with the scope created for its surrogate, so it
+ // can be found by e.g. validateInsert.
+ scopes.put(node, getSelectScope(surrogate));
+ registerNamespace(usingScope, alias,
+ new PipeNamespace(this, node, surrogate, parentScope, enclosingNode),
+ forceNullable);
+ }
+
+ /** Returns the scope children that an operator applied to {@code input}
+ * can see. At the time this is called, {@code input} has already been
+ * validated. */
+ List<ScopeChild> pipeChildren(SqlNode input) {
+ final SqlValidatorNamespace ns = getNamespaceOrThrow(input);
+ if (ns instanceof PipeNamespace) {
+ return ((PipeNamespace) ns).outChildren();
+ }
+ return singleAnonymousScopeChild(input, ns);
+ }
+
+ /** Returns a single scope child for {@code node}, under a generated alias,
+ * so that its columns are visible but the table aliases are not. */
+ List<ScopeChild> singleAnonymousScopeChild(SqlNode node,
+ SqlValidatorNamespace ns) {
+ return ImmutableList.of(
+ new ScopeChild(0, SqlValidatorUtil.alias(node, nextGeneratedId++),
+ ns, false));
+ }
+
private void registerSetop(
SqlValidatorScope parentScope,
@Nullable SqlValidatorScope usingScope,
@@ -4792,6 +4859,20 @@ private static void purgeForBypassFields(SqlNode node,
SqlValidatorScope scope,
});
}
+ /** Returns the must-filter requirement of the input of the pipe operator
+ * whose surrogate's namespace is {@code ns}; null if it is not such a
+ * namespace. */
+ private @Nullable FilterRequirement pipeInputRequirement(
+ SelectNamespace ns) {
+ // A surrogate is registered with its pipe operator as enclosing node.
+ final @Nullable SqlNode node = ns.getEnclosingNode();
+ if (!(node instanceof SqlPipeCall)) {
+ return null;
+ }
+ return getNamespaceOrThrow(((SqlPipeCall) node).getQuery())
+ .getFilterRequirement();
+ }
+
private static void toQualifieds(ImmutableBitSet fields,
Set<SqlQualified> qualifiedSet, SelectScope fromScope, ScopeChild child,
List<String> fieldNames) {
@@ -4880,7 +4961,9 @@ private static SqlNode stripDot(SqlNode node) {
private void checkRollUp(@Nullable SqlNode grandParent, @Nullable SqlNode
parent,
@Nullable SqlNode current, SqlValidatorScope scope, @Nullable String
contextClause) {
current = stripAs(current);
- if (current instanceof SqlCall && !(current instanceof SqlSelect)) {
+ if (current instanceof SqlCall
+ && !(current instanceof SqlSelect)
+ && !current.isA(SqlKind.PIPE_QUERY)) {
// Validate OVER separately
checkRollUpInWindow(getWindowInOver(current), scope);
current = stripOver(current);
@@ -5038,6 +5121,16 @@ private void validateModality(SqlNode query) {
default:
break;
}
+ } else if (query.isA(SqlKind.PIPE_QUERY)) {
+ if (query instanceof SqlPipeCall) {
+ // A pipeline is a stream if and only if the query it starts from is.
+ validateModality(((SqlPipeCall) query).getQuery());
+ }
+ // The query's own clauses are its surrogate's, so they follow the
+ // rules for the clauses of a SELECT on a stream.
+ validateModality(
+ ((PipeNamespace) getNamespaceOrThrow(query)).getSurrogateSelect(),
modality,
+ true);
} else if (query.getKind() == SqlKind.WITH) {
// The modality of WITH clause depends on its body
// For example:
@@ -5067,8 +5160,11 @@ private static SqlModality deduceModality(SqlNode query)
{
return select.getModifierNode(SqlSelectKeyword.STREAM) != null
? SqlModality.STREAM
: SqlModality.RELATION;
- } else if (query.getKind() == SqlKind.VALUES) {
+ } else if (query.getKind() == SqlKind.VALUES
+ || query.getKind() == SqlKind.FROM_QUERY) {
return SqlModality.RELATION;
+ } else if (query instanceof SqlPipeCall) {
+ return deduceModality(((SqlPipeCall) query).getQuery());
} else if (query.getKind() == SqlKind.WITH) {
return deduceModality(((SqlWith) query).body);
} else {
@@ -5332,17 +5428,34 @@ private static SqlNode stripOrderByModifiers(SqlNode
orderExpr) {
protected void validateMustFilterRequirements(SqlSelect select,
SelectNamespace ns) {
ns.filterRequirement = FilterRequirement.EMPTY;
+ // The input's tables in a surrogate's scope report the requirements they
+ // started with; the input's own requirement says what remains.
+ final @Nullable FilterRequirement inputRequirement =
+ pipeInputRequirement(ns);
if (select.getFrom() != null) {
final SelectScope fromScope = (SelectScope) getFromScope(select);
final BitSet projectedNonFilteredBypassField = new BitSet();
final Set<SqlQualified> qualifieds = new LinkedHashSet<>();
final Set<SqlQualified> bypassQualifieds = new LinkedHashSet<>();
final Set<SqlQualified> remnantQualifieds = new LinkedHashSet<>();
+ int offset = 0;
for (ScopeChild child : fromScope.children) {
final List<String> fieldNames =
child.namespace.getRowType().getFieldNames();
- final FilterRequirement filterRequirement =
- child.namespace.getFilterRequirement();
+ final FilterRequirement filterRequirement;
+ if (inputRequirement == null) {
+ filterRequirement = child.namespace.getFilterRequirement();
+ } else {
+ // The children's columns are the input's columns, in order (see
+ // PipeNamespace#outChildren), so this child's fields start at
offset.
+ final int end = offset + fieldNames.size();
+ filterRequirement =
+ new FilterRequirement(
+ inputRequirement.filterFields.get(offset,
end).shift(-offset),
+ inputRequirement.bypassFields.get(offset,
end).shift(-offset),
+ inputRequirement.remnantFilterFields);
+ }
+ offset += fieldNames.size();
toQualifieds(filterRequirement.filterFields, qualifieds, fromScope,
child, fieldNames);
toQualifieds(filterRequirement.bypassFields, bypassQualifieds,
@@ -8221,6 +8334,10 @@ public SqlNode extendedExpandGroupBy(SqlNode expr,
return null;
} else if (sqlQuery instanceof SqlOrderBy) {
return getFieldOrigin(((SqlOrderBy) sqlQuery).query, i);
+ } else if (sqlQuery.isA(SqlKind.PIPE_QUERY)) {
+ // A pipe query has the origins of its surrogate SELECT.
+ final PipeNamespace ns = (PipeNamespace) getNamespaceOrThrow(sqlQuery);
+ return getFieldOrigin(ns.getSurrogateSelect(), i);
} else {
return null;
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
index 463216b006..982e405d9f 100644
---
a/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
+++
b/core/src/main/java/org/apache/calcite/sql/validate/implicit/TypeCoercionImpl.java
@@ -40,6 +40,7 @@
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeMappingRule;
import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.PipeNamespace;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
import org.apache.calcite.util.Util;
@@ -118,6 +119,18 @@ public TypeCoercionImpl(RelDataTypeFactory typeFactory,
SqlValidator validator)
case WITH:
SqlNode body = ((SqlWith) query).body;
return rowTypeCoercion(validator.getOverScope(query), body, columnIndex,
targetType);
+ case FROM_QUERY:
+ case PIPE_CALL:
+ // A query in pipe syntax is validated and converted as its surrogate
+ // SELECT, so coerce that.
+ final PipeNamespace pipeNs =
+ (PipeNamespace) requireNonNull(validator.getNamespace(query), "ns");
+ if (!rowTypeCoercion(scope, pipeNs.getSurrogateSelect(), columnIndex,
targetType)) {
+ return false;
+ }
+ updateInferredColumnType(
+ requireNonNull(scope, "scope"), query, columnIndex, targetType);
+ return true;
case UNION:
case INTERSECT:
case EXCEPT:
@@ -681,7 +694,8 @@ private boolean coalesceCoercion(SqlCallBinding
callBinding) {
int colCount = type1.isStruct() ? type1.getFieldCount() : 1;
RelDataType[] argTypes = new RelDataType[2];
argTypes[0] = type1;
- final boolean isSubQuery = node2 instanceof SqlSelect;
+ final boolean isSubQuery =
+ node2 instanceof SqlSelect || node2.isA(SqlKind.PIPE_QUERY);
// For subquery, use the row type directly.
// For collection, use the component type for comparison, not the
collection.
if (isSubQuery) {
@@ -748,7 +762,9 @@ private boolean coalesceCoercion(SqlCallBinding
callBinding) {
if (isSubQuery) {
// Use rowTypeCoercion on the subquery output column,
// consistent with how inOperationCoercion handles the subquery case.
- SqlValidatorScope scope1 = validator.getSelectScope((SqlSelect) node2);
+ SqlValidatorScope scope1 = node2 instanceof SqlSelect
+ ? validator.getSelectScope((SqlSelect) node2)
+ : scope;
RelDataType source = validator.getValidatedNodeType(node2);
RelDataType target = binding.getTypeFactory()
.createTypeWithNullability(desired, source.isNullable() ||
desired.isNullable());
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 49953ced1f..45ffb51997 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -132,6 +132,9 @@
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlOrderBy;
+import org.apache.calcite.sql.SqlPipeCall;
+import org.apache.calcite.sql.SqlPipeInput;
+import org.apache.calcite.sql.SqlPipeQuery;
import org.apache.calcite.sql.SqlPivot;
import org.apache.calcite.sql.SqlSampleSpec;
import org.apache.calcite.sql.SqlSelect;
@@ -165,6 +168,7 @@
import org.apache.calcite.sql.validate.MatchRecognizeScope;
import org.apache.calcite.sql.validate.MeasureScope;
import org.apache.calcite.sql.validate.ParameterScope;
+import org.apache.calcite.sql.validate.PipeNamespace;
import org.apache.calcite.sql.validate.SelectScope;
import org.apache.calcite.sql.validate.SqlLambdaScope;
import org.apache.calcite.sql.validate.SqlMonotonicity;
@@ -639,7 +643,8 @@ public RelRoot convertQuery(
query = validator().validate(query);
}
- RelNode result = convertQueryRecursive(query, top, null).rel;
+ final RelRoot root = convertQueryRecursive(query, top, null);
+ RelNode result = root.rel;
if (top) {
if (unwrapMeasures) {
result = unwrapMeasures(result);
@@ -648,12 +653,9 @@ public RelRoot convertQuery(
result = new LogicalDelta(cluster, result.getTraitSet(), result);
}
}
- RelCollation collation = RelCollations.EMPTY;
- if (!query.isA(SqlKind.DML)) {
- if (isOrdered(query)) {
- collation = requiredCollation(result);
- }
- }
+ final RelCollation collation = query.isA(SqlKind.DML)
+ ? RelCollations.EMPTY
+ : collation(query, root, top);
checkConvertedType(query, result, top && unwrapMeasures);
if (SQL2REL_LOGGER.isDebugEnabled()) {
@@ -706,10 +708,27 @@ private RexNode measureToValue(RexNode e) {
}
private static boolean isStream(SqlNode query) {
+ if (query instanceof SqlPipeCall) {
+ // A pipeline is a stream if the query it starts from is one. If that is
+ // a WITH, look at its body.
+ SqlNode input = ((SqlPipeCall) query).getQuery();
+ while (input instanceof SqlWith) {
+ input = ((SqlWith) input).body;
+ }
+ return isStream(input);
+ }
return query instanceof SqlSelect
&& ((SqlSelect) query).isKeywordPresent(SqlSelectKeyword.STREAM);
}
+ /** Returns whether {@code scope} is the scope of a pipe operator's
+ * surrogate SELECT, whose children are the tables of the operator's input
+ * (see {@link Blackboard#setPipeInput}). */
+ private static boolean readsPipeInput(@Nullable SqlValidatorScope scope) {
+ return scope instanceof SelectScope
+ && ((SelectScope) scope).getNode().getFrom() instanceof SqlPipeInput;
+ }
+
public static boolean isOrdered(SqlNode query) {
switch (query.getKind()) {
case SELECT:
@@ -719,11 +738,28 @@ public static boolean isOrdered(SqlNode query) {
return isOrdered(((SqlWith) query).body);
case ORDER_BY:
return !((SqlOrderBy) query).orderList.isEmpty();
+ case PIPE_CALL:
+ // Each pipe operator keeps its input's order.
+ return isOrdered(((SqlPipeCall) query).getQuery());
default:
return false;
}
}
+ /** Returns the order of the rows of {@code query}, which has been converted
+ * to {@code root}; empty if it is not ordered or its sort was removed. */
+ private RelCollation collation(SqlNode query, RelRoot root, boolean top) {
+ if (!isOrdered(query) || removeSortInSubQuery(top)) {
+ return RelCollations.EMPTY;
+ }
+ // For SELECT, we need to call requiredCollation to track down its
+ // Sort. In other cases (e.g. WITH, pipe syntax) we can use the
+ // root.collation that was built during conversion.
+ return query.getKind() == SqlKind.SELECT
+ ? requiredCollation(root.rel)
+ : root.collation;
+ }
+
private static RelCollation requiredCollation(RelNode r) {
if (r instanceof Sort) {
return ((Sort) r).collation;
@@ -2878,10 +2914,33 @@ protected void convertFrom(
case INTERSECT:
case EXCEPT:
case UNION:
+ case FROM_QUERY:
+ case PIPE_CALL:
final RelNode rel = convertQueryRecursive(from, false, null).project();
bb.setRoot(rel, true);
return;
+ case PIPE_INPUT:
+ // If the "bb.top" flag is set, it means we are converting a pipe
+ // operator call at the top of the statement; therefore, we need to
+ // also convert the input with the top flag set. (This causes it to
+ // retain its ordering, which would otherwise be dropped for a
+ // subquery.)
+ final boolean keepOrder = bb.top;
+ SqlNode pipeInput = ((SqlPipeInput) from).getInput();
+ while (pipeInput instanceof SqlWith) {
+ pipeInput = ((SqlWith) pipeInput).body;
+ }
+ final RelRoot pipeInputRoot =
+ convertQueryRecursive(pipeInput, keepOrder, null);
+ if (keepOrder) {
+ bb.setPipeInput(pipeInputRoot.rel,
+ collation(pipeInput, pipeInputRoot, true));
+ } else {
+ bb.setPipeInput(pipeInputRoot.project(), RelCollations.EMPTY);
+ }
+ return;
+
case VALUES:
convertValuesImpl(bb, (SqlCall) from, null);
if (fieldNames != null) {
@@ -3614,6 +3673,21 @@ protected RelNode createAsofJoin(
r.accept(
new CorrelationFieldMappingShuttle(rexBuilder,
correlNames.get(0),
bb.root().getRowType(), fieldMapping));
+ } else if (readsPipeInput(bb.scope)) {
+ // The rel of a pipe operator's input may have trailing columns for
+ // sort keys (see convertFrom), which the correlation variable lacks.
+ // Use the CorrelationFieldMappingShuttle to change the type of the
+ // variable to the rel's row type.
+ final RelDataType rowType = bb.root().getRowType();
+ final Map<Pair<CorrelationId, Integer>, Integer> identityMapping =
+ new HashMap<>();
+ for (int i = 0; i < rowType.getFieldCount(); i++) {
+ identityMapping.put(Pair.of(correlNames.get(0), i), i);
+ }
+ r =
+ r.accept(
+ new CorrelationFieldMappingShuttle(rexBuilder,
correlNames.get(0),
+ rowType, identityMapping));
}
return new CorrelationUse(correlNames.get(0), requiredColumns.build(), r);
@@ -4454,6 +4528,9 @@ protected RelRoot convertQueryRecursive(SqlNode query,
boolean top,
return convertWith((SqlWith) query, top);
case VALUES:
return RelRoot.of(convertValues((SqlCall) query, targetRowType), kind);
+ case FROM_QUERY:
+ case PIPE_CALL:
+ return convertPipeQuery((SqlPipeQuery) query, top);
default:
throw new AssertionError("not a query: " + query);
}
@@ -4487,6 +4564,29 @@ private RelNode createUnion(SqlCall call,
.build();
}
+ /**
+ * Converts a query written in pipe syntax.
+ */
+ private RelRoot convertPipeQuery(SqlPipeQuery query, boolean top) {
+ // Convert the query's surrogate SELECT (see
+ // SqlPipeQuery#surrogateSelect), in the scopes that the validator gave
+ // the surrogate.
+ final PipeNamespace ns = getNamespace(query);
+ final SqlSelect select = ns.getSurrogateSelect();
+ final SqlValidatorScope selectScope = validator().getWhereScope(select);
+ final MeasureScope measureScope =
+ (MeasureScope) validator().getMeasureScope(select);
+ final Blackboard bb = createBlackboard(selectScope, null, top);
+ bb.pipeQuery = query;
+ final Blackboard measureBb = new MeasureBlackboard(measureScope, bb);
+ convertSelectImpl(bb, measureBb, select);
+ final RelDataType rowType = validator().getValidatedNodeType(query);
+ final RelRoot root = RelRoot.of(bb.root(), rowType, query.getKind());
+ return top
+ ? root.withCollation(bb.pipeCollation)
+ : RelRoot.of(root.project(), rowType, query.getKind());
+ }
+
/**
* Converts a set operation (UNION, INTERSECT, MINUS) into relational
* expressions.
@@ -5394,6 +5494,26 @@ private void convertNonAggregateSelectList(
fieldNames.add(deriveAlias(expr, aliases, i));
}
+ // Project extra fields for the sort keys of a pipe operator's input, if
+ // its order is to be kept (see convertFrom) but the operator does not
+ // output them.
+ if (!bb.pipeInputCollation.getFieldCollations().isEmpty()) {
+ final List<RelFieldCollation> fieldCollations = new ArrayList<>();
+ for (RelFieldCollation fieldCollation
+ : bb.pipeInputCollation.getFieldCollations()) {
+ final int key = fieldCollation.getFieldIndex();
+ final RexInputRef ref = rexBuilder.makeInputRef(bb.root(), key);
+ int target = exprs.indexOf(ref);
+ if (target < 0) {
+ target = exprs.size();
+ exprs.add(ref);
+ fieldNames.add(bb.root().getRowType().getFieldNames().get(key));
+ }
+ fieldCollations.add(fieldCollation.withFieldIndex(target));
+ }
+ bb.pipeCollation = RelCollations.of(fieldCollations);
+ }
+
final List<String> uniqueFieldNames =
SqlValidatorUtil.uniquify(fieldNames,
catalogReader.nameMatcher().isCaseSensitive());
@@ -5414,6 +5534,11 @@ private void convertNonAggregateSelectList(
.projectNamed(project1.getProjects(), uniqueFieldNames, true,
ImmutableSet.of(p.id))
.build();
+ } else if (bb.pipeQuery != null
+ && RexUtil.isIdentity(exprs, bb.root().getRowType())
+ && uniqueFieldNames.equals(bb.root().getRowType().getFieldNames())) {
+ // No need for an identity Project for each step of a pipeline.
+ r = bb.root();
} else {
r = relBuilder.push(bb.root()).projectNamed(exprs, uniqueFieldNames,
true).build();
}
@@ -5659,6 +5784,18 @@ protected class Blackboard implements SqlRexContext,
SqlVisitor<RexNode>,
public final SqlValidatorScope scope;
private final @Nullable Map<String, RexNode> nameToNodeMap;
public @Nullable RelNode root;
+
+ /** Query written in pipe syntax whose surrogate SELECT this blackboard
+ * converts, or null. */
+ @Nullable SqlPipeQuery pipeQuery;
+
+ /** Order of the rows of a pipe operator's input, in the input's field
+ * numbering. */
+ RelCollation pipeInputCollation = RelCollations.EMPTY;
+
+ /** Order of the rows of the pipe operator that this blackboard converts,
+ * in the field numbering of {@link #root}. */
+ RelCollation pipeCollation = RelCollations.EMPTY;
private @Nullable List<RelNode> inputs;
private final Map<CorrelationId, RexFieldAccess> mapCorrelateToRex =
new HashMap<>();
@@ -5983,7 +6120,18 @@ void setRoot(List<RelNode> inputs) {
} else {
final LookupContext rels =
new LookupContext(this, inputs, systemFieldList.size());
- node = lookup(resolve.path.steps().get(0).i, rels);
+ final int i = resolve.path.steps().get(0).i;
+ if (readsPipeInput(ancestorScope)) {
+ // The scope's children are the input's tables.
+ int offset = rels.findRel(0).getValue();
+ for (int j = 0; j < i; j++) {
+ offset += ((ListScope) requireNonNull(ancestorScope,
"ancestorScope"))
+ .getChildren().get(j).getRowType().getFieldCount();
+ }
+ node = rexBuilder.makeRangeReference(rowType, offset, false);
+ } else {
+ node = lookup(i, rels);
+ }
}
return Pair.of(node, (e, fieldName) -> {
final RelDataTypeField field =
@@ -6037,6 +6185,15 @@ void setRoot(List<RelNode> inputs) {
}
}
+ /** Sets the root to the rel of a pipe operator's input. The scope's
+ * children are then the input's tables, which all read that one rel; see
+ * {@link #lookupExp}. The rel may also have trailing columns that hold
+ * sort keys of its {@code collation}. */
+ void setPipeInput(RelNode input, RelCollation collation) {
+ setRoot(input, true);
+ pipeInputCollation = collation;
+ }
+
/**
* Creates an expression with which to reference the expression whose
* offset in its from-list is {@code offset}.
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index 4ef7f50d1d..7e5fff8557 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -25,6 +25,10 @@ LimitStartCountOrAllNotAllowed=''LIMIT start, {0}'' is not
allowed under the cur
OffsetLimitNotAllowed=''OFFSET start LIMIT count'' is not allowed under the
current SQL conformance level
ApplyNotAllowed=APPLY operator is not allowed under the current SQL
conformance level
ValueNotAllowed=VALUE is not allowed under the current SQL conformance level
+PipeSyntaxNotAllowed=Pipe syntax is not allowed under the current SQL
conformance level
+PipeClauseNotAllowed={0} clause is not allowed after a FROM query; use ''{1}''
+FromQueryClauseNotAllowed={0} clause is not allowed after a FROM query
+FromQueryInSetOpNotAllowed=A FROM query must be in parentheses to be an
operand of UNION, INTERSECT or EXCEPT
IllegalLiteral=Illegal {0} literal ''{1}'': {2}
IdentifierTooLong=Length of identifier ''{0}'' must be less than or equal to
{1,number,#} characters
BadFormat=not in format ''{0}''
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 01488fcc4b..3b086d50ff 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -22,6 +22,7 @@
import org.apache.calcite.sql.advise.SqlSimpleParser;
import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.StringAndPos;
+import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql.validate.SqlMoniker;
import org.apache.calcite.sql.validate.SqlMonikerType;
import org.apache.calcite.test.SqlValidatorFixture;
@@ -504,6 +505,17 @@ protected List<String> getJoinKeywords() {
f.withSql(sql).assertComplete(QUANTIFIERS, EXPR_KEYWORDS); // join
}
+ @Test void testPipeKeywordNeedsConformance() {
+ final String sql = "select * from dummy join sales.emp ^";
+ final List<String> pipeKeywords =
+ Collections.singletonList("KEYWORD(|>)");
+ fixture().withSql(sql)
+ .assertComplete(JOIN_KEYWORDS);
+ ((Fixture) fixture().withConformance(SqlConformanceEnum.LENIENT))
+ .withSql(sql)
+ .assertComplete(JOIN_KEYWORDS, pipeKeywords);
+ }
+
@Test void testJoinKeywords() {
// variety of keywords possible
List<String> list = getJoinKeywords();
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 06f991c803..f93370a6b1 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -85,6 +85,7 @@
import static org.hamcrest.MatcherAssert.assertThat;
import static org.hamcrest.Matchers.containsString;
import static org.hamcrest.Matchers.hasSize;
+import static org.hamcrest.Matchers.hasToString;
import static org.junit.jupiter.api.Assertions.assertThrows;
/**
@@ -1771,6 +1772,13 @@ private void checkCallTypesConsistent(String sql) {
sql(sql).withExpand(false).ok();
}
+ @Test void testWithInsideWhereInOrderBy() {
+ final String sql = "select * from emp\n"
+ + "where deptno in (\n"
+ + " with w as (select * from dept) select deptno from w order by
deptno)";
+ sql(sql).ok();
+ }
+
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-365";>[CALCITE-365]
* AssertionError while translating query with WITH and correlated
@@ -1924,6 +1932,96 @@ private void checkCallTypesConsistent(String sql) {
sql("table emp").ok();
}
+ @Test void testFromQuery() {
+ sql("from emp").withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Tests that a pipeline over a streaming query is still streaming. */
+ @Test void testPipeWhereOverStream() {
+ sql("select stream * from orders |> where productid = 1")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** As {@link #testPipeWhereOverStream()}, but the pipeline starts from a
+ * WITH whose body is the streaming query. */
+ @Test void testPipeWhereOverStreamingWith() {
+ sql("(with d as (select * from dept)\n"
+ + " select stream * from orders) |> where productid = 1")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ @Test void testPipeWhere() {
+ sql("from emp |> where deptno = 10")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Tests that {@code |> WHERE} keeps the order of its input. The input's
+ * sort key, which is not one of its columns, stays as a hidden column. */
+ @Test void testPipeWhereKeepsOrder() {
+ final SqlToRelFixture f =
+ sql("select ename, deptno from emp order by sal desc\n"
+ + "|> where deptno = 10")
+ .withConformance(SqlConformanceEnum.LENIENT);
+ f.ok();
+ assertThat(f.toRoot().collation, hasToString("[2 DESC]"));
+ }
+
+ /** Tests a correlated sub-query in a {@code |> WHERE} whose input has a
+ * hidden column for its sort key. */
+ @Test void testPipeWhereCorrelatedHiddenSortKey() {
+ sql("select ename, deptno as dno from emp order by sal + 1 desc\n"
+ + "|> where exists (select 1 from dept as d where d.deptno = dno)")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .withDecorrelate(false).ok();
+ }
+
+ /** Tests a pipe query used as an expression sub-query, correlated on a
+ * table alias of the enclosing pipeline. */
+ @Test void testPipeQueryAsCorrelatedSubQuery() {
+ sql("from emp as e\n"
+ + "|> where exists (from dept |> where deptno = e.deptno)")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Tests that implicit type coercion casts the column of a pipe query that
+ * is an {@code IN} sub-query, as it would for a SELECT. The plan shows the
+ * cast as {@code CAST($0):BIGINT NOT NULL} in the sub-query. */
+ @Test void testPipeWhereInSubQueryCoercion() {
+ sql("select * from emp\n"
+ + "where cast(deptno as bigint) in\n"
+ + " (select deptno from dept |> where deptno > 0)")
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .withExpand(false)
+ .ok();
+ }
+
+ @Test void testPipeWhereWrapsSelect() {
+ sql("select ename, sal from emp |> where sal > 100")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ @Test void testPipeWhereAfterUnion() {
+ sql("select deptno from emp union all select deptno from dept\n"
+ + "|> where deptno = 10")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ @Test void testPipeWhereAfterWith() {
+ // The |> WHERE applies to the body of the WITH, above its ORDER BY.
+ sql("with d as (select * from emp)\n"
+ + "select * from d order by empno\n"
+ + "|> where deptno = 10")
+ .withConformance(SqlConformanceEnum.LENIENT).ok();
+ }
+
+ /** Tests a pipe query as the argument of a {@code CURSOR}. */
+ @Test void testPipeWhereAsCursor() {
+ final String sql = "select * from table(dedup("
+ + "cursor(from emp |> where deptno = 10), 'ENAME'))";
+ sql(sql).withConformance(SqlConformanceEnum.LENIENT)
+ .withDecorrelate(false).ok();
+ }
+
@Test void testCollectionTable() {
sql("select * from table(ramp(3))").ok();
}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index ba5d9b02de..7f94e4e7e6 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -10646,6 +10646,219 @@ void
testOrderByAliasPreservesValidatedExpressions(String query) {
+ "ORDER BY `EXPR$0`.`EMPNO`");
}
+ /** Tests that a pipe operator's expressions are checked for rolled-up
+ * columns, as the clauses of the {@code SELECT} they stand for are. */
+ @Test void testPipeRolledUpColumn() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ // |> WHERE rejects a rolled-up column of the enclosing query as a
+ // standard sub-query does.
+ sql("select empno from emp_r as e\n"
+ + "where exists (from dept |> where deptno = ^e.slackingmin^)")
+ .withConformance(lenient)
+ .fails("Rolled up column 'SLACKINGMIN' is not allowed in EQUALS");
+ // A column that is not rolled up is fine.
+ sql("select empno from emp_r as e\n"
+ + "where exists (from dept |> where deptno = e.deptno)")
+ .withConformance(lenient)
+ .ok();
+ }
+
+ /** Tests that a pipeline is checked for stream/relation modality. */
+ @Test void testPipeModality() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ // "FROM orders" is "SELECT * FROM orders", which cannot read a stream.
+ sql("from ^orders^")
+ .withConformance(lenient)
+ .fails("Cannot convert stream 'ORDERS' to relation");
+ // A pipeline read as a relation cannot read a stream.
+ sql("select * from (select stream * from orders\n"
+ + "^|> where productid = 1^)")
+ .withConformance(lenient)
+ .fails("Cannot convert stream 'EXPR\\$0' to relation");
+ }
+
+ @Test void testPipeSyntaxType() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("from emp")
+ .withConformance(lenient)
+ .type(EMP_RECORD_TYPE);
+ sql("from emp |> where deptno = 10")
+ .withConformance(lenient)
+ .type(EMP_RECORD_TYPE);
+ sql("select ename, sal from emp |> where sal > 100")
+ .withConformance(lenient)
+ .type("RecordType(VARCHAR(20) NOT NULL ENAME, INTEGER NOT NULL SAL)"
+ + " NOT NULL");
+ }
+
+ @Test void testPipeSyntaxScope() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ // |> WHERE cannot see table aliases in SELECT.
+ sql("select * from emp as e |> where ^e^.deptno = 10")
+ .withConformance(lenient)
+ .fails("Table 'E' not found");
+ // Table aliases in FROM stay visible through a chain of |> WHERE.
+ sql("from emp as e |> where e.deptno = 10 |> where e.sal > 1000")
+ .withConformance(lenient)
+ .ok();
+ // |> WHERE can see both CTEs and table aliases in FROM.
+ sql("with e as (select * from emp)\n"
+ + "from e as x |> where x.deptno in (select deptno from e)")
+ .withConformance(lenient)
+ .ok();
+ sql("with e as (select * from emp)\n"
+ + "select * from e order by empno |> where deptno in (select deptno
from e)")
+ .withConformance(lenient)
+ .ok();
+ // |> WHERE cannot see CTEs of a WITH in parentheses.
+ sql("(with e as (select * from emp) select * from emp)\n"
+ + "|> where deptno in (select deptno from ^e^)")
+ .withConformance(lenient)
+ .fails("Object 'E' not found");
+ sql("(with e as (select * from emp) select * from emp) order by empno\n"
+ + "|> where deptno in (select deptno from ^e^)")
+ .withConformance(lenient)
+ .fails("Object 'E' not found");
+ sql("(with dept as (select 1 as x from emp) select * from emp)\n"
+ + "|> where deptno in (select dept.deptno from dept)")
+ .withConformance(lenient)
+ .ok();
+ }
+
+ /** Tests a pipe query used as an expression sub-query: the operand of
+ * {@code EXISTS} or {@code IN}, or a scalar sub-query. Such a query is
+ * validated in a scope of its own, so nothing that walks the enclosing
+ * query's expressions may descend into it. */
+ @Test void testPipeQueryAsSubQuery() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("select ename, (select name from dept |> where deptno = 10) as dn\n"
+ + "from emp")
+ .withConformance(lenient)
+ .type("RecordType(VARCHAR(20) NOT NULL ENAME, VARCHAR(10) DN)"
+ + " NOT NULL");
+ // An unknown column inside the sub-query is still reported, and the
+ // sub-query's columns are not visible outside it.
+ sql("select * from emp\n"
+ + "where exists (from dept |> where ^nonexistent^ = 1)")
+ .withConformance(lenient)
+ .fails("Column 'NONEXISTENT' not found in any table");
+ sql("select * from emp where exists (from dept) and ^name^ = 'x'")
+ .withConformance(lenient)
+ .fails("Column 'NAME' not found in any table");
+ // The usual arity rules apply; DEPT has two columns.
+ sql("select * from emp where ^deptno in (from dept^)")
+ .withConformance(lenient)
+ .fails("Values passed to IN operator must have compatible types");
+ sql("select ^(from dept |> where deptno = 10)^ from emp")
+ .withConformance(lenient)
+ .fails("Cannot apply '\\$SCALAR_QUERY' to arguments of type.*");
+ // A set operation whose first query is in pipe syntax reports a type
+ // mismatch at that query, which has no select list to point into.
+ sql("^(select slacker from emp |> where slacker)^\n"
+ + "union all select hiredate from emp")
+ .withConformance(lenient)
+ .fails("Type mismatch in column 1 of UNION ALL");
+ }
+
+ /** Tests that implicit type coercion applies to a query in pipe syntax as
+ * it does to the equivalent SELECT. */
+ @Test void testPipeQueryTypeCoercion() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("(from dept) union all select name, deptno from dept")
+ .withConformance(lenient)
+ .type("RecordType(VARCHAR NOT NULL DEPTNO,"
+ + " VARCHAR NOT NULL NAME) NOT NULL");
+ sql("select 'a' as x union all (select deptno from dept |> where true)")
+ .withConformance(lenient)
+ .type("RecordType(VARCHAR NOT NULL X) NOT NULL");
+ }
+
+ /** Tests a query in pipe syntax as the operand of the {@code ARRAY} and
+ * {@code MAP} query constructors. */
+ @Test void testPipeQueryInArrayAndMap() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("select array(select ename from emp |> where deptno = 10)\n"
+ + "from dept")
+ .withConformance(lenient)
+ .columnType("VARCHAR(20) NOT NULL ARRAY NOT NULL");
+ sql("select map(from dept |> where deptno = 10) from emp")
+ .withConformance(lenient)
+ .columnType("(INTEGER NOT NULL, VARCHAR(10) NOT NULL) MAP NOT NULL");
+ }
+
+ @Test void testPipeQueryAsInsertSource() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ // The validator looks up the scope of an INSERT source that is not a
+ // SELECT, so a pipe query must have one.
+ sql("insert into emp from emp |> where deptno = 10")
+ .withConformance(lenient)
+ .ok();
+ sql("insert into emp (from emp |> where deptno = 10)")
+ .withConformance(lenient)
+ .ok();
+ }
+
+ @Test void testPipeWhereBaseQueries() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("table emp |> where deptno = 10")
+ .withConformance(lenient)
+ .type(EMP_RECORD_TYPE);
+ // The validator rewrites the sub-query's ORDER BY into a different node,
+ // which replaces the FROM query's operand.
+ sql("from (select * from emp order by empno)")
+ .withConformance(lenient)
+ .type(EMP_RECORD_TYPE);
+ }
+
+ @Test void testPipeValidationErrors() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("from ^nonexistent^ |> where x = 1")
+ .withConformance(lenient)
+ .fails("Object 'NONEXISTENT' not found");
+ sql("from emp |> where deptno = 10 |> where ^nonexistent^ = 1")
+ .withConformance(lenient)
+ .fails("Column 'NONEXISTENT' not found in any table");
+ sql("from emp |> where ^empno^")
+ .withConformance(lenient)
+ .fails("WHERE clause must be a condition");
+ sql("from emp |> where ^count(*)^ > 1")
+ .withConformance(lenient)
+ .fails("Aggregate expression is illegal in WHERE clause");
+ sql("from emp |> where ^rank() over (order by sal)^ = 1")
+ .withConformance(lenient)
+ .fails("Windowed aggregate expression is illegal in WHERE clause");
+ sql("from emp, dept |> where ^deptno^ = 10")
+ .withConformance(lenient)
+ .fails("Column 'DEPTNO' is ambiguous");
+ sql("select ename from emp |> where ^empno^ > 1")
+ .withConformance(lenient)
+ .fails("Column 'EMPNO' not found in any table");
+ sql("from emp as e |> where e.^nonexistent^ = 1")
+ .withConformance(lenient)
+ .fails("Column 'NONEXISTENT' not found in table 'E'");
+ }
+
+ /** Tests pipe syntax under a conformance level that requires a
+ * {@code FROM} clause, which an operator's surrogate SELECT has:
+ * a {@link org.apache.calcite.sql.SqlPipeInput}. */
+ @Test void testPipeUnderFromRequiredConformance() {
+ final SqlConformance fromRequired =
+ new SqlDelegatingConformance(SqlConformanceEnum.STRICT_2003) {
+ @Override public boolean isPipeSyntaxAllowed() {
+ return true;
+ }
+ };
+ assertThat(fromRequired.isFromRequired(), is(true));
+ sql("from dept |> where deptno = 10")
+ .withConformance(fromRequired)
+ .type("RecordType(INTEGER NOT NULL DEPTNO, VARCHAR(10) NOT NULL NAME)"
+ + " NOT NULL");
+ // A SELECT that the user writes without a FROM clause is still rejected.
+ sql("^select 1^")
+ .withConformance(fromRequired)
+ .fails("SELECT must have a FROM clause");
+ }
+
@Test void testCollectionTable() {
sql("select * from table(ramp(3))")
.type("RecordType(INTEGER NOT NULL I) NOT NULL");
@@ -11464,6 +11677,22 @@ void
testOrderByAliasPreservesValidatedExpressions(String query) {
.assertFieldOrigin(is("{CATALOG.SALES.DEPT_NESTED.SKILL.DESC}"));
}
+ /** Tests that a query written in pipe syntax reports the origins of its
+ * columns, as the {@code SELECT} it stands for does. */
+ @Test void testPipeFieldOrigin() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ // "FROM dept" is "SELECT * FROM dept", and has its origins.
+ sql("from dept")
+ .withConformance(lenient)
+ .assertFieldOrigin(
+ is("{CATALOG.SALES.DEPT.DEPTNO, CATALOG.SALES.DEPT.NAME}"));
+ // |> WHERE keeps the origins of its input's columns.
+ sql("from dept |> where deptno = 10")
+ .withConformance(lenient)
+ .assertFieldOrigin(
+ is("{CATALOG.SALES.DEPT.DEPTNO, CATALOG.SALES.DEPT.NAME}"));
+ }
+
@Test void testBrackets() {
final SqlValidatorFixture s = fixture().withQuoting(Quoting.BRACKET);
s.withSql("select [e].EMPNO from [EMP] as [e]")
@@ -15495,6 +15724,42 @@ private SqlCall checkArrayOperandType(String sql,
String expectedArrayType,
.fails(missingFilters("JOB"));
}
+ @Test void testMustFilterColumnsInPipeQuery() {
+ final SqlValidatorFixture fixture = fixture()
+ .withConformance(SqlConformanceEnum.LENIENT)
+ .withOperatorTable(operatorTableFor(SqlLibrary.BIG_QUERY))
+ .withCatalogReader(MustFilterMockCatalogReader::create);
+
+ // "FROM emp" is "SELECT * FROM emp", and requires the same filters.
+ fixture.withSql("^from emp^")
+ .fails(missingFilters("EMPNO", "JOB"));
+
+ // A |> WHERE defuses them, exactly as a WHERE clause does.
+ fixture.withSql("from emp\n"
+ + "|> where empno = 1 and job = 'doctor'")
+ .ok();
+ fixture.withSql("from emp\n"
+ + "^|> where empno = 1^")
+ .fails(missingFilters("JOB"));
+
+ // With two tables in the FROM clause, each contributes the fields at its
+ // own offset in the input table.
+ fixture.withSql("from emp, dept\n"
+ + "|> where empno = 1 and job = 'doctor' and name = 'x'")
+ .ok();
+ fixture.withSql("from emp, dept\n"
+ + "^|> where empno = 1 and job = 'doctor'^")
+ .fails(missingFilters("NAME"));
+
+ // A pipeline in a sub-query is defused by the enclosing query, and
+ // enforced at the top if it is not.
+ fixture.withSql("select * from (from emp |> where sal > 1000)\n"
+ + "where empno = 1 and job = 'doctor'")
+ .ok();
+ fixture.withSql("^select * from (from emp |> where sal > 1000)^")
+ .fails(missingFilters("EMPNO", "JOB"));
+ }
+
/** Returns a message that the particular columns are not filtered. */
private static String missingFilters(String... args) {
return "SQL statement did not contain filters on the following fields: \\["
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 22aeace682..a881a357a4 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2764,6 +2764,16 @@ from emp]]>
<![CDATA[
LogicalProject(F=[*(/INT(CASE(>=(11045000:INTERVAL HOUR TO SECOND, 0),
11045000:INTERVAL HOUR TO SECOND, -(11045000:INTERVAL HOUR TO SECOND,
3599999:INTERVAL HOUR TO SECOND)), 3600000), 3600000)],
C=[*(/INT(CASE(>=(11045000:INTERVAL HOUR TO SECOND, 0), +(11045000:INTERVAL
HOUR TO SECOND, 3599999:INTERVAL HOUR TO SECOND), 11045000:INTERVAL HOUR TO
SECOND), 3600000), 3600000)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testFromQuery">
+ <Resource name="sql">
+ <![CDATA[from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -7813,6 +7823,172 @@ group by deptno]]>
LogicalAggregate(group=[{0}], EXPR$1=[PERCENTILE_DISC($1) WITHIN GROUP ([2])])
LogicalProject(DEPTNO=[$7], $f1=[0.25:DECIMAL(3, 2)], EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeQueryAsCorrelatedSubQuery">
+ <Resource name="sql">
+ <![CDATA[from emp as e
+|> where exists (from dept |> where deptno = e.deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($9):INTEGER],
$f1=[CAST($10):BOOLEAN])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(DEPTNO=[$0], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhere">
+ <Resource name="sql">
+ <![CDATA[from emp |> where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereAfterUnion">
+ <Resource name="sql">
+ <![CDATA[select deptno from emp union all select deptno from dept
+|> where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalFilter(condition=[=($0, 10)])
+ LogicalUnion(all=[true])
+ LogicalProject(DEPTNO=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereAfterWith">
+ <Resource name="sql">
+ <![CDATA[with d as (select * from emp)
+select * from d order by empno
+|> where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalFilter(condition=[=($7, 10)])
+ LogicalSort(sort0=[$0], dir0=[ASC])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereAsCursor">
+ <Resource name="sql">
+ <![CDATA[select * from table(dedup(cursor(from emp |> where deptno =
10), 'ENAME'))]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NAME=[$0])
+ LogicalTableFunctionScan(invocation=[DEDUP(CAST($0):CURSOR NOT NULL,
'ENAME')], rowType=[RecordType(VARCHAR(1024) NAME)])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereCorrelatedHiddenSortKey">
+ <Resource name="sql">
+ <![CDATA[select ename, deptno as dno from emp order by sal + 1 desc
+|> where exists (select 1 from dept as d where d.deptno = dno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$0], DNO=[$1])
+ LogicalProject(ENAME=[$0], DNO=[$1], EXPR$2=[$2])
+ LogicalFilter(condition=[IS NOT NULL($3)])
+ LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])
+ LogicalSort(sort0=[$2], dir0=[DESC])
+ LogicalProject(ENAME=[$1], DNO=[$7], EXPR$2=[+($5, 1)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], agg#0=[MIN($0)])
+ LogicalProject($f0=[true])
+ LogicalFilter(condition=[=($0, $cor0.DNO)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereInSubQueryCoercion">
+ <Resource name="sql">
+ <![CDATA[select * from emp
+where cast(deptno as bigint) in
+ (select deptno from dept |> where deptno > 0)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalFilter(condition=[IN(CAST($7):BIGINT NOT NULL, {
+LogicalProject(DEPTNO=[CAST($0):BIGINT NOT NULL])
+ LogicalFilter(condition=[>($0, 0)])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereKeepsOrder">
+ <Resource name="sql">
+ <![CDATA[select ename, deptno from emp order by sal desc
+|> where deptno = 10]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(ENAME=[$0], DEPTNO=[$1])
+ LogicalFilter(condition=[=($1, 10)])
+ LogicalSort(sort0=[$2], dir0=[DESC])
+ LogicalProject(ENAME=[$1], DEPTNO=[$7], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereOverStream">
+ <Resource name="sql">
+ <![CDATA[select stream * from orders |> where productid = 1]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalDelta
+ LogicalFilter(condition=[=($1, 1)])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereOverStreamingWith">
+ <Resource name="sql">
+ <![CDATA[(with d as (select * from dept)
+ select stream * from orders) |> where productid = 1]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalDelta
+ LogicalFilter(condition=[=($1, 1)])
+ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1], ORDERID=[$2])
+ LogicalTableScan(table=[[CATALOG, SALES, ORDERS]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testPipeWhereWrapsSelect">
+ <Resource name="sql">
+ <![CDATA[select ename, sal from emp |> where sal > 100]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalFilter(condition=[>($1, 100)])
+ LogicalProject(ENAME=[$1], SAL=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -11442,6 +11618,23 @@ LogicalFilter(condition=[<=($0, $cor1.DEPTNO)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
})], variablesSet=[[$cor1]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testWithInsideWhereInOrderBy">
+ <Resource name="sql">
+ <![CDATA[select * from emp
+where deptno in (
+ with w as (select * from dept) select deptno from w order by deptno)]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+ LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/pipe_iq_googlesql.py
b/core/src/test/resources/pipe_iq_googlesql.py
new file mode 100755
index 0000000000..21bc7844e1
--- /dev/null
+++ b/core/src/test/resources/pipe_iq_googlesql.py
@@ -0,0 +1,78 @@
+#!/usr/bin/env python3
+#
+# 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.
+#
+
+# Checks the '!ok' and '!error' queries of an IQ file (such as sql/pipe.iq)
+# against GoogleSQL's execute_query binary, from a release of
+# https://github.com/google/googlesql. SETUP_SQL runs before each query. As in
+# Quidem, rows are compared in order only if the query's last ORDER BY is not
+# in parentheses.
+#
+# Usage: pipe_iq_googlesql.py <EXECUTE_QUERY> <SETUP_SQL> <IQ_FILENAME>
+#
+# ex: pipe_iq_googlesql.py ./execute_query_linux pipe_iq_googlesql_setup.sql \
+# sql/pipe.iq
+
+import decimal
+import re
+import subprocess
+import sys
+
+
+def table(text, sep, ordered):
+ """Parses a table, upper-casing its header, and normalizing NULLs and
+ numbers so that GoogleSQL's 2450 matches Quidem's 2450.00."""
+ def value(v):
+ try:
+ return str(decimal.Decimal(v).normalize())
+ except decimal.InvalidOperation:
+ return '' if v == 'NULL' else v
+ # Each row (and the header) is a line that starts with the separator, such
+ # as '| a | b |'; other lines, such as '+---+---+', are borders.
+ rows = [[value(v.strip()) for v in line.strip()[1:-1].split(sep)]
+ for line in text.split('\n') if line.startswith(sep)]
+ return [[h.upper() for h in row] for row in rows[:1]] \
+ + (rows[1:] if ordered else sorted(rows[1:]))
+
+
+binary, setup, iq = sys.argv[1:]
+setup, iq = open(setup).read(), open(iq).read()
+failed = False
+# Each match is a query (a line that is not blank, a comment or a command,
+# through a line ending in ';'), its expected output, and the command after it.
+for m in re.finditer(r'^(?![#!\s])(.*?);\n(.*?)^(!\S+)', iq, re.M | re.S):
+ sql, expected, command = m.groups()
+ if command not in ('!ok', '!error'):
+ continue
+ # Run the query on GoogleSQL, after the statements that create the tables.
+ out = subprocess.run([binary, '--enabled_language_features=MAXIMUM',
+ '--mode=execute', '-'], input=f'{setup}\n{sql};',
+ capture_output=True, text=True)
+ out = out.stdout + out.stderr
+ # Compare in order only if Quidem would; a port of
+ # Quidem.isProbablyDeterministic.
+ _, order_by, tail = sql.upper().rpartition('ORDER BY')
+ ordered = order_by and tail.count(')') <= tail.count('(')
+ # GoogleSQL prints tables with box-drawing characters; Quidem uses '|'.
+ ok = 'ERROR:' in out if command == '!error' \
+ else table(out, '│', ordered) == table(expected, '|', ordered)
+ print('PASS' if ok else 'FAIL', 'line', iq.count('\n', 0, m.start()) + 1,
+ sql.split('\n')[0])
+ if not ok:
+ print(expected + out)
+ failed = True
+sys.exit(failed)
diff --git a/core/src/test/resources/pipe_iq_googlesql_setup.sql
b/core/src/test/resources/pipe_iq_googlesql_setup.sql
new file mode 100644
index 0000000000..0430c2b147
--- /dev/null
+++ b/core/src/test/resources/pipe_iq_googlesql_setup.sql
@@ -0,0 +1,42 @@
+# 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.
+#
+# Creates EMP and DEPT in GoogleSQL, with the same rows as the scott schema,
+# for pipe_iq_googlesql.py.
+CREATE TEMP TABLE emp AS SELECT * FROM UNNEST([
+ STRUCT(7369 AS empno, 'SMITH' AS ename, 'CLERK' AS job,
+ CAST(7902 AS INT64) AS mgr, DATE '1980-12-17' AS hiredate,
+ CAST(800.00 AS NUMERIC) AS sal, CAST(NULL AS NUMERIC) AS comm,
+ 20 AS deptno),
+ (7499, 'ALLEN', 'SALESMAN', 7698, DATE '1981-02-20', 1600.00, 300.00, 30),
+ (7521, 'WARD', 'SALESMAN', 7698, DATE '1981-02-22', 1250.00, 500.00, 30),
+ (7566, 'JONES', 'MANAGER', 7839, DATE '1981-02-04', 2975.00, NULL, 20),
+ (7654, 'MARTIN', 'SALESMAN', 7698, DATE '1981-09-28', 1250.00, 1400.00, 30),
+ (7698, 'BLAKE', 'MANAGER', 7839, DATE '1981-01-05', 2850.00, NULL, 30),
+ (7782, 'CLARK', 'MANAGER', 7839, DATE '1981-06-09', 2450.00, NULL, 10),
+ (7788, 'SCOTT', 'ANALYST', 7566, DATE '1987-04-19', 3000.00, NULL, 20),
+ (7839, 'KING', 'PRESIDENT', NULL, DATE '1981-11-17', 5000.00, NULL, 10),
+ (7844, 'TURNER', 'SALESMAN', 7698, DATE '1981-09-08', 1500.00, 0.00, 30),
+ (7876, 'ADAMS', 'CLERK', 7788, DATE '1987-05-23', 1100.00, NULL, 20),
+ (7900, 'JAMES', 'CLERK', 7698, DATE '1981-12-03', 950.00, NULL, 30),
+ (7902, 'FORD', 'ANALYST', 7566, DATE '1981-12-03', 3000.00, NULL, 20),
+ (7934, 'MILLER', 'CLERK', 7782, DATE '1982-01-23', 1300.00, NULL, 10)
+]);
+CREATE TEMP TABLE dept AS SELECT * FROM UNNEST([
+ STRUCT(10 AS deptno, 'ACCOUNTING' AS dname, 'NEW YORK' AS loc),
+ (20, 'RESEARCH', 'DALLAS'),
+ (30, 'SALES', 'CHICAGO'),
+ (40, 'OPERATIONS', 'BOSTON')
+]);
diff --git a/core/src/test/resources/sql/pipe.iq
b/core/src/test/resources/sql/pipe.iq
new file mode 100644
index 0000000000..4de1c4996d
--- /dev/null
+++ b/core/src/test/resources/sql/pipe.iq
@@ -0,0 +1,157 @@
+# pipe.iq - Tests for SQL pipe syntax
+#
+# 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.
+#
+# Pipe syntax is only allowed under conformance levels that support it;
+# scott-lenient uses LENIENT conformance.
+#
+# The expected results were checked against GoogleSQL's reference
+# implementation. To repeat the check, visit
+# https://github.com/google/googlesql to download GoogleSQL. Then,
+# from core/src/test/resources, run:
+# ./pipe_iq_googlesql.py <path>/execute_query_linux \
+# pipe_iq_googlesql_setup.sql sql/pipe.iq
+!use scott-lenient
+!set outputformat mysql
+
+from dept;
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+from emp |> where deptno = 10;
++-------+--------+-----------+------+------------+---------+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+------+--------+
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+------+--------+
+(3 rows)
+
+!ok
+
+select empno, ename, sal from emp |> where sal > 2500;
++-------+-------+---------+
+| EMPNO | ENAME | SAL |
++-------+-------+---------+
+| 7566 | JONES | 2975.00 |
+| 7698 | BLAKE | 2850.00 |
+| 7788 | SCOTT | 3000.00 |
+| 7839 | KING | 5000.00 |
+| 7902 | FORD | 3000.00 |
++-------+-------+---------+
+(5 rows)
+
+!ok
+
+select ename from (from emp |> where deptno = 10) where sal > 1500;
++-------+
+| ENAME |
++-------+
+| CLARK |
+| KING |
++-------+
+(2 rows)
+
+!ok
+
+# |> WHERE keeps the order of its input, even if the input's sort key is not
+# one of its columns.
+select ename from emp where deptno = 20 order by sal desc, empno
+|> where ename <> 'SMITH';
++-------+
+| ENAME |
++-------+
+| SCOTT |
+| FORD |
+| JONES |
+| ADAMS |
++-------+
+(4 rows)
+
+!ok
+
+# A correlated sub-query in an operator whose input has a hidden column for a
+# sort key that is an expression.
+select ename, deptno as dno from emp order by sal + 1 desc, empno
+|> where exists (select 1 from dept as d where d.deptno = dno and d.loc =
'DALLAS');
++-------+-----+
+| ENAME | DNO |
++-------+-----+
+| SCOTT | 20 |
+| FORD | 20 |
+| JONES | 20 |
+| ADAMS | 20 |
+| SMITH | 20 |
++-------+-----+
+(5 rows)
+
+!ok
+
+# After a join with USING, which merges columns, the operators see the columns
+# of the FROM clause.
+from emp JOIN dept USING (deptno) |> WHERE dname = 'ACCOUNTING' |> WHERE sal >
2000;
++--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
+| DEPTNO | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM |
DNAME | LOC |
++--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
+| 10 | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | |
ACCOUNTING | NEW YORK |
+| 10 | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | |
ACCOUNTING | NEW YORK |
++--------+-------+-------+-----------+------+------------+---------+------+------------+----------+
+(2 rows)
+
+!ok
+
+# After a join whose tables have columns of the same name, pipe operators still
+# see the table aliases, and can qualify the columns with the table aliases.
+select ename, dname from (
+from emp as e join dept as d on e.deptno = d.deptno
+|> where d.deptno = 10 |> where e.sal > 2000);
++-------+------------+
+| ENAME | DNAME |
++-------+------------+
+| CLARK | ACCOUNTING |
+| KING | ACCOUNTING |
++-------+------------+
+(2 rows)
+
+!ok
+
+# Implicit type coercion applies to a pipe query as it does to a SELECT.
+# Here, the operands of = SOME are INTEGER and TINYINT.
+select ename from emp
+where deptno + 0 = some (select deptno from dept |> where deptno < 20)
+order by ename;
++--------+
+| ENAME |
++--------+
+| CLARK |
+| KING |
+| MILLER |
++--------+
+(3 rows)
+
+!ok
+
+# End pipe.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 889645ae17..4d3fa1dfae 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -195,6 +195,11 @@ ## Grammar
[ LIMIT [ start, ] { count | ALL } ]
[ OFFSET { start | expression } { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ count | '(' expression ')' ] { ROW | ROWS }
ONLY ]
+ [ pipeOperator ]*
+ | FROM tableExpression [ pipeOperator ]*
+
+pipeOperator:
+ '|>' WHERE booleanExpression
withItem:
name
@@ -490,6 +495,25 @@ ## Grammar
but is not standard SQL and is only allowed in certain
[conformance levels]({{ site.apiRoot
}}/org/apache/calcite/sql/validate/SqlConformance.html#isFromRequired--).
+*pipeOperator* and a standalone `FROM` are only allowed in certain
+[conformance levels]({{ site.apiRoot
}}/org/apache/calcite/sql/validate/SqlConformance.html#isPipeSyntaxAllowed--).
+A standalone `FROM` is a query, equivalent to `SELECT *` over that `FROM`
+clause. Any query may be followed by pipe operators, each of which takes the
+query before it as its input table and produces a new table. Pipe syntax is
+based on
+[GoogleSQL's pipe
syntax](https://github.com/google/googlesql/blob/master/docs/pipe-syntax.md).
+
+A pipe operator sees only the columns of its input table (and, in a sub-query,
+those of enclosing queries). Table aliases in a standalone `FROM` remain
+visible to the operators that follow it.
+
+For example:
+
+{% highlight sql %}
+FROM emp AS e
+|> WHERE e.deptno = 10
+{% endhighlight %}
+
MINUS is equivalent to EXCEPT,
but is not standard SQL and is only allowed in certain
[conformance levels]({{ site.apiRoot
}}/org/apache/calcite/sql/validate/SqlConformance.html#isMinusAllowed--).
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 952a2c97cf..cdcd5b2766 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -4948,6 +4948,236 @@ void checkPeriodPredicate(Checker checker) {
.fails("(?s)Encountered \"select\".*");
}
+ // Tests for SQL pipe syntax.
+
+ @Test void testFromQuery() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("from emp")
+ .withConformance(lenient)
+ .ok("FROM `EMP`");
+ sql("from emp join dept using (deptno)")
+ .withConformance(lenient)
+ .ok("FROM `EMP`\n"
+ + "INNER JOIN `DEPT` USING (`DEPTNO`)");
+ sql("from (from emp)")
+ .withConformance(lenient)
+ .ok("FROM (FROM `EMP`)");
+ }
+
+ @Test void testFromQueryOrderedAndSetOp() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("(from emp) order by empno")
+ .withConformance(lenient)
+ .ok("(FROM `EMP`)\n"
+ + "ORDER BY `EMPNO`");
+ sql("(from emp) union all (from dept)")
+ .withConformance(lenient)
+ .ok("(FROM `EMP`)\n"
+ + "UNION ALL\n"
+ + "(FROM `DEPT`)");
+ final String error = "A FROM query must be in parentheses to be an"
+ + " operand of UNION, INTERSECT or EXCEPT";
+ sql("from emp ^union^ all (from dept)")
+ .withConformance(lenient)
+ .fails(error);
+ sql("select * from emp except ^from^ dept")
+ .withConformance(lenient)
+ .fails(error);
+ }
+
+ @Test void testFromQueryConformance() {
+ final String error =
+ "Pipe syntax is not allowed under the current SQL conformance level";
+ sql("^from^ emp").fails(error);
+ sql("select * from emp ^|>^ where deptno = 10").fails(error);
+ sql("select * from emp union ^from^ dept").fails(error);
+ }
+
+ @Test void testPipeWhere() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("from emp |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("from emp |> where deptno = 10 |> where ename <> 'X'")
+ .withConformance(lenient)
+ .ok("FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)\n"
+ + "|> WHERE (`ENAME` <> 'X')");
+ }
+
+ @Test void testPipeWhereAfterQuery() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("select * from emp |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("values (1, 2) |> where true")
+ .withConformance(lenient)
+ .ok("VALUES (ROW(1, 2))\n"
+ + "|> WHERE TRUE");
+ sql("table emp |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("(TABLE `EMP`)\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("select 1 union all select 2 |> where true")
+ .withConformance(lenient)
+ .ok("SELECT 1\n"
+ + "UNION ALL\n"
+ + "SELECT 2\n"
+ + "|> WHERE TRUE");
+ sql("select * from emp order by empno limit 5 |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM `EMP`\n"
+ + "ORDER BY `EMPNO`\n"
+ + "FETCH NEXT 5 ROWS ONLY\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("with e as (select * from emp) select * from e |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("WITH `E` AS (SELECT *\n"
+ + "FROM `EMP`) (SELECT *\n"
+ + "FROM `E`\n"
+ + "|> WHERE (`DEPTNO` = 10))");
+ sql("(with e as (select * from emp) select * from e) |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("(WITH `E` AS (SELECT *\n"
+ + "FROM `EMP`) SELECT *\n"
+ + "FROM `E`)\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("with e as (select * from emp) select * from e order by empno\n"
+ + "|> where deptno = 10")
+ .withConformance(lenient)
+ .ok("WITH `E` AS (SELECT *\n"
+ + "FROM `EMP`) (SELECT *\n"
+ + "FROM `E`\n"
+ + "ORDER BY `EMPNO`\n"
+ + "|> WHERE (`DEPTNO` = 10))");
+ sql("(with e as (select * from emp) select * from e) order by empno\n"
+ + "|> where deptno = 10")
+ .withConformance(lenient)
+ .ok("(WITH `E` AS (SELECT *\n"
+ + "FROM `EMP`) SELECT *\n"
+ + "FROM `E`\n"
+ + "ORDER BY `EMPNO`)\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ }
+
+ @Test void testPipeQueryAsSubQuery() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("select * from (from emp |> where deptno = 10)")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM (FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10))");
+ sql("select * from emp where deptno in (from dept |> where name = 'a')")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM `EMP`\n"
+ + "WHERE (`DEPTNO` IN (FROM `DEPT`\n"
+ + "|> WHERE (`NAME` = 'a')))");
+ sql("select * from emp where exists (from dept)")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM `EMP`\n"
+ + "WHERE (EXISTS (FROM `DEPT`))");
+ sql("select (from dept |> where deptno = 10) from emp")
+ .withConformance(lenient)
+ .ok("SELECT (FROM `DEPT`\n"
+ + "|> WHERE (`DEPTNO` = 10))\n"
+ + "FROM `EMP`");
+ sql("insert into emp2 from emp |> where deptno = 10")
+ .withConformance(lenient)
+ .ok("INSERT INTO `EMP2`\n"
+ + "FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)");
+ sql("with e as (from emp |> where deptno = 10)\n"
+ + "from e |> where ename = 'X'")
+ .withConformance(lenient)
+ .ok("WITH `E` AS (FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)) (FROM `E`\n"
+ + "|> WHERE (`ENAME` = 'X'))");
+ sql("from emp |> where deptno in (select deptno from dept)")
+ .withConformance(lenient)
+ .ok("FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` IN (SELECT `DEPTNO`\n"
+ + "FROM `DEPT`))");
+ }
+
+ /** Tests pipe operators after a parenthesized query, in a context that is
+ * itself in parentheses, such as an item in a FROM clause. */
+ @Test void testPipeAfterParenthesizedQuery() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("select * from ((select 1 as x) |> where x = 1)")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM (SELECT 1 AS `X`\n"
+ + "|> WHERE (`X` = 1))");
+ sql("select * from ((from emp) |> where deptno = 10) as e")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM (FROM `EMP`\n"
+ + "|> WHERE (`DEPTNO` = 10)) AS `E`");
+ sql("select * from ((select 1 as x) union all (select 2) |> where x = 1)")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM (SELECT 1 AS `X`\n"
+ + "UNION ALL\n"
+ + "SELECT 2\n"
+ + "|> WHERE (`X` = 1))");
+ sql("select * from emp,\n"
+ + "lateral ((select deptno from dept) |> where deptno = emp.deptno)")
+ .withConformance(lenient)
+ .ok("SELECT *\n"
+ + "FROM `EMP`,\n"
+ + "LATERAL(SELECT `DEPTNO`\n"
+ + "FROM `DEPT`\n"
+ + "|> WHERE (`DEPTNO` = `EMP`.`DEPTNO`))");
+ sql("with t as ((select 1 as x) |> where x = 1) select * from t")
+ .withConformance(lenient)
+ .ok("WITH `T` AS (SELECT 1 AS `X`\n"
+ + "|> WHERE (`X` = 1)) SELECT *\n"
+ + "FROM `T`");
+ // The input of a pipe operator must be a query.
+ sql("select * from ((emp join dept using (deptno)) ^|>^ where true)")
+ .withConformance(lenient)
+ .fails("Non-query expression encountered in illegal context");
+ }
+
+ @Test void testPipeSyntaxIllegal() {
+ final SqlConformanceEnum lenient = SqlConformanceEnum.LENIENT;
+ sql("1 + 2 ^|>^ where true")
+ .withConformance(lenient)
+ .fails("Non-query expression encountered in illegal context");
+ sql("^|>^ where empno > 1")
+ .withConformance(lenient)
+ .fails("(?s)Encountered \"\\|>\" at .*");
+ sql("from emp |> ^match_recognize^ (partition by deptno)")
+ .withConformance(lenient)
+ .fails("(?s)Encountered \"match_recognize\" at .*"
+ + "Was expecting.*\"WHERE\".*");
+ sql("from emp |> where deptno = 10 ^order^ by empno")
+ .withConformance(lenient)
+ .fails("(?s)Encountered \"order\" at .*");
+ sql("from emp ^where^ deptno = 10")
+ .withConformance(lenient)
+ .fails("WHERE clause is not allowed after a FROM query; "
+ + "use '\\|> WHERE'");
+ sql("from emp ^order^ by empno")
+ .withConformance(lenient)
+ .fails("ORDER BY clause is not allowed after a FROM query");
+ sql("from emp ^limit^ 5")
+ .withConformance(lenient)
+ .fails("LIMIT clause is not allowed after a FROM query");
+ sql("from emp ^offset^ 5")
+ .withConformance(lenient)
+ .fails("OFFSET clause is not allowed after a FROM query");
+ sql("from emp ^fetch^ next 5 rows only")
+ .withConformance(lenient)
+ .fails("FETCH clause is not allowed after a FROM query");
+ }
+
@Test void testCollectionTable() {
sql("select * from table(ramp(3, 4))")
.ok("SELECT *\n"
