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 1a3173d52a [CALCITE-7364] Support the syntax ROW(T.* EXCLUDE cols) for
creating nested ROW values
1a3173d52a is described below
commit 1a3173d52a41683acca60fb09a31ad6ee25e587e
Author: Mihai Budiu <[email protected]>
AuthorDate: Wed Jun 10 16:44:19 2026 -0700
[CALCITE-7364] Support the syntax ROW(T.* EXCLUDE cols) for creating nested
ROW values
Signed-off-by: Mihai Budiu <[email protected]>
---
babel/src/main/codegen/config.fmpp | 1 -
core/src/main/codegen/default_config.fmpp | 1 -
core/src/main/codegen/templates/Parser.jj | 86 ++++++++++++++--------
.../apache/calcite/runtime/CalciteResource.java | 6 ++
.../org/apache/calcite/sql/SqlStarExclude.java | 5 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 75 +++++++++++--------
.../calcite/runtime/CalciteResource.properties | 2 +
.../calcite/sql/parser/CoreSqlParserTest.java | 46 ++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 39 ++++++++++
core/src/test/resources/sql/struct.iq | 51 +++++++++++++
site/_docs/reference.md | 10 ++-
11 files changed, 256 insertions(+), 66 deletions(-)
diff --git a/babel/src/main/codegen/config.fmpp
b/babel/src/main/codegen/config.fmpp
index 001bdf2e10..30c2ce7d65 100644
--- a/babel/src/main/codegen/config.fmpp
+++ b/babel/src/main/codegen/config.fmpp
@@ -617,7 +617,6 @@ data: {
includePosixOperators: true
includeParsingStringLiteralAsArrayLiteral: true
includeIntervalWithoutQualifier: true
- includeStarExclude: true
includeSelectBy: true
}
}
diff --git a/core/src/main/codegen/default_config.fmpp
b/core/src/main/codegen/default_config.fmpp
index 56d17b8279..a2547273cb 100644
--- a/core/src/main/codegen/default_config.fmpp
+++ b/core/src/main/codegen/default_config.fmpp
@@ -460,5 +460,4 @@ parser: {
includeAdditionalDeclarations: false
includeParsingStringLiteralAsArrayLiteral: false
includeIntervalWithoutQualifier: false
- includeStarExclude: false
}
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 0aacdb9ff7..d4b589d6c1 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -2026,7 +2026,6 @@ void AddSelectItem(List<SqlNode> list) :
)
}
-<#if (parser.includeStarExclude!default.parser.includeStarExclude)>
/**
* Parses one unaliased expression in a select list.
*/
@@ -2035,6 +2034,8 @@ SqlNode SelectExpression() :
SqlNode e;
SqlNodeList excludeList;
SqlNodeList replaceList;
+ SqlIdentifier sqlIdentifier;
+ SqlParserPos pos;
}
{
(
@@ -2045,47 +2046,42 @@ SqlNode SelectExpression() :
e = Expression(ExprContext.ACCEPT_SUB_QUERY)
)
(
-<#if (parser.includeStarExclude!default.parser.includeStarExclude)>
excludeList = StarExcludeList() {
if (!(e instanceof SqlIdentifier)) {
throw
SqlUtil.newContextException(excludeList.getParserPosition(),
RESOURCE.selectExcludeRequiresStar());
}
- final SqlIdentifier sqlIdentifier = (SqlIdentifier) e;
+ sqlIdentifier = (SqlIdentifier) e;
if (!sqlIdentifier.isStar()) {
throw
SqlUtil.newContextException(excludeList.getParserPosition(),
RESOURCE.selectExcludeRequiresStar());
}
- final SqlParserPos pos = SqlParserPos.sum(
+ pos = SqlParserPos.sum(
ImmutableList.of(sqlIdentifier.getParserPosition(),
excludeList.getParserPosition()));
return new SqlStarExclude(pos, sqlIdentifier, excludeList);
}
|
-</#if>
-<#if (parser.includeStarExclude!default.parser.includeStarExclude)>
replaceList = StarReplaceList() {
if (!(e instanceof SqlIdentifier)) {
throw
SqlUtil.newContextException(replaceList.getParserPosition(),
RESOURCE.selectReplaceRequiresStar());
}
- final SqlIdentifier sqlIdentifier = (SqlIdentifier) e;
+ sqlIdentifier = (SqlIdentifier) e;
if (!sqlIdentifier.isStar()) {
throw
SqlUtil.newContextException(replaceList.getParserPosition(),
RESOURCE.selectReplaceRequiresStar());
}
- final SqlParserPos pos = SqlParserPos.sum(
+ pos = SqlParserPos.sum(
ImmutableList.of(sqlIdentifier.getParserPosition(),
replaceList.getParserPosition()));
return new SqlStarReplace(pos, sqlIdentifier, replaceList);
}
|
-</#if>
{ return e; }
)
}
-<#if (parser.includeStarExclude!default.parser.includeStarExclude)>
SqlNodeList StarExcludeList() :
{
final Span s;
@@ -2106,9 +2102,7 @@ SqlNodeList StarExcludeList() :
return new SqlNodeList(list, s.end(this));
}
}
-</#if>
-<#if (parser.includeStarExclude!default.parser.includeStarExclude)>
SqlNodeList StarReplaceList() :
{
final Span s;
@@ -2134,25 +2128,6 @@ SqlNodeList StarReplaceList() :
return new SqlNodeList(list, s.end(this));
}
}
-</#if>
-<#else>
-/**
- * Parses one unaliased expression in a select list.
- */
-SqlNode SelectExpression() :
-{
- SqlNode e;
-}
-{
- <STAR> {
- return SqlIdentifier.star(getPos());
- }
-|
- e = Expression(ExprContext.ACCEPT_SUB_QUERY) {
- return e;
- }
-}
-</#if>
SqlLiteral Natural() :
{
@@ -4548,12 +4523,42 @@ SqlCall PercentileFunctionCall() :
}
+/**
+ * Parses an EXCLUDE or EXCEPT clause following a star inside a ROW
constructor,
+ * e.g. {@code ROW(* EXCLUDE(col1, col2))} or {@code ROW(t.* EXCEPT(t.col))}.
+ *
+ * @param starIdentifier the star (e.g. "*" or "t.*") that precedes the
EXCLUDE clause
+ */
+SqlNode RowStarExclude(SqlIdentifier starIdentifier) :
+{
+ SqlIdentifier id; // current column identifier being parsed
+ final List<SqlNode> list = new ArrayList<SqlNode>();
+ Span s;
+}
+{
+ (<EXCLUDE> | <EXCEPT>)
+ <LPAREN> { s = span(); }
+ id = CompoundIdentifier() { list.add(id); }
+ (
+ <COMMA> id = CompoundIdentifier() { list.add(id); }
+ )*
+ <RPAREN> {
+ final SqlNodeList excludeList = new SqlNodeList(list, s.end(this));
+ return new SqlStarExclude(
+ SqlParserPos.sum(ImmutableList.of(
+ starIdentifier.getParserPosition(),
+ excludeList.getParserPosition())),
+ starIdentifier, excludeList);
+ }
+}
+
/**
* Parses an atomic row expression.
*/
SqlNode AtomicRowExpression() :
{
final SqlNode e;
+ SqlNode rowStar;
}
{
(
@@ -4584,10 +4589,27 @@ SqlNode AtomicRowExpression() :
|
e = ContextVariable()
|
+ // Parses "t.*" or "t.* EXCLUDE (col, ...)" inside a ROW constructor
e = CompoundIdentifier()
+ (
+ LOOKAHEAD({ allowRowValueStar()
+ && (getToken(1).kind == EXCLUDE || getToken(1).kind == EXCEPT)
})
+ rowStar = RowStarExclude((SqlIdentifier) e) { return rowStar; }
+ |
+ {}
+ )
|
+ // Parses "*" or "* EXCLUDE (col, ...)" inside a ROW constructor
LOOKAHEAD({ allowRowValueStar() })
- <STAR> { return SqlIdentifier.star(getPos()); }
+ <STAR> {
+ final SqlIdentifier starId = SqlIdentifier.star(getPos());
+ }
+ (
+ LOOKAHEAD((<EXCLUDE> | <EXCEPT>))
+ rowStar = RowStarExclude(starId) { return rowStar; }
+ |
+ { return starId; }
+ )
|
e = NewSpecification()
|
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 a9cb02d065..ea60315ae6 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -816,9 +816,15 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("SELECT * EXCLUDE/EXCEPT list contains unknown column(s): {0}")
ExInst<SqlValidatorException>
selectStarExcludeListContainsUnknownColumns(String columns);
+ @BaseMessage("ROW(* EXCLUDE/EXCEPT list) contains unknown column(s): {0}")
+ ExInst<SqlValidatorException>
rowStarExcludeListContainsUnknownColumns(String columns);
+
@BaseMessage("SELECT * EXCLUDE/EXCEPT list cannot exclude all columns")
ExInst<SqlValidatorException> selectStarExcludeCannotExcludeAllColumns();
+ @BaseMessage("ROW(* EXCLUDE/EXCEPT list) cannot exclude all columns")
+ ExInst<SqlValidatorException> rowStarExcludeCannotExcludeAllColumns();
+
@BaseMessage("SELECT * REPLACE list contains unknown column(s): {0}")
ExInst<SqlValidatorException>
selectStarReplaceListContainsUnknownColumns(String columns);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlStarExclude.java
b/core/src/main/java/org/apache/calcite/sql/SqlStarExclude.java
index 884c4a8463..fc1d7f2988 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlStarExclude.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlStarExclude.java
@@ -27,11 +27,12 @@
import static java.util.Objects.requireNonNull;
/**
- * Represents {@code SELECT * EXCLUDE(...)}.
+ * Represents the arguments of {@code SELECT * EXCLUDE(...) or SELECT ROW(*
EXCLUDE(...))},
+ * without the SELECT itself.
*/
public class SqlStarExclude extends SqlCall {
public static final SqlOperator OPERATOR =
- new SqlSpecialOperator("SELECT_STAR_EXCLUDE", SqlKind.OTHER) {
+ new SqlSpecialOperator("STAR_EXCLUDE", SqlKind.OTHER) {
@SuppressWarnings("argument.type.incompatible")
@Override public SqlCall createCall(
@Nullable SqlLiteral functionQualifier,
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 f7388bdeb3..e2762d7926 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
@@ -481,7 +481,7 @@ private boolean expandSelectItem(final SqlNode selectItem,
SqlSelect select,
} else {
final SelectScope scope = (SelectScope) getWhereScope(select);
if (expandStar(selectItems, aliases, fields, includeSystemVars, scope,
- selectItem)) {
+ selectItem, false)) {
return true;
}
@@ -670,7 +670,7 @@ private void validateNoQualifiedCommonColumns(SqlNodeList
nodeList,
private boolean expandStar(List<SqlNode> selectItems, Set<String> aliases,
PairList<String, RelDataType> fields, boolean includeSystemVars,
- SelectScope scope, SqlNode node) {
+ SelectScope scope, SqlNode node, boolean inRowContext) {
final SqlIdentifier identifier;
final SqlNodeList excludeList;
final SqlNodeList replaceList;
@@ -831,9 +831,9 @@ private boolean expandStar(List<SqlNode> selectItems,
Set<String> aliases,
int offset = Math.min(calculatePermuteOffset(selectItems),
originalSize);
new Permute(from, offset).permute(selectItems, fields);
}
- throwIfUnknownExcludeColumns(excludeIdentifiers, excludeMatched);
+ throwIfUnknownExcludeColumns(excludeIdentifiers, excludeMatched,
inRowContext);
throwIfExcludeEliminatesAllColumns(excludeIdentifiers, fieldsBeforeStar,
- fields, identifier);
+ fields, identifier, inRowContext);
throwIfUnknownReplaceColumns(replaceMap, replaceMatched);
return true;
@@ -909,9 +909,9 @@ private boolean expandStar(List<SqlNode> selectItems,
Set<String> aliases,
} else {
throw newValidationError(prefixId, RESOURCE.starRequiresRecordType());
}
- throwIfUnknownExcludeColumns(excludeIdentifiers, excludeMatched);
+ throwIfUnknownExcludeColumns(excludeIdentifiers, excludeMatched,
inRowContext);
throwIfExcludeEliminatesAllColumns(excludeIdentifiers, fieldsBeforeStar,
- fields, identifier);
+ fields, identifier, inRowContext);
throwIfUnknownReplaceColumns(replaceMap, replaceMatched);
return true;
}
@@ -987,7 +987,7 @@ && matchesExcludeIdentifier(columnId,
excludeIdentifiers.get(i), nameMatcher)) {
}
private void throwIfUnknownExcludeColumns(List<SqlIdentifier>
excludeIdentifiers,
- boolean[] excludeMatched) {
+ boolean[] excludeMatched, boolean inRowContext) {
if (excludeIdentifiers.isEmpty()) {
return;
}
@@ -1002,20 +1002,24 @@ private void
throwIfUnknownExcludeColumns(List<SqlIdentifier> excludeIdentifiers
}
}
if (firstUnknownIndex >= 0) {
+ final String columns = String.join(", ", unknownExcludeNames);
throw newValidationError(
excludeIdentifiers.get(firstUnknownIndex),
- RESOURCE.selectStarExcludeListContainsUnknownColumns(
- String.join(", ", unknownExcludeNames)));
+ inRowContext
+ ? RESOURCE.rowStarExcludeListContainsUnknownColumns(columns)
+ : RESOURCE.selectStarExcludeListContainsUnknownColumns(columns));
}
}
private void throwIfExcludeEliminatesAllColumns(List<SqlIdentifier>
excludeIdentifiers,
int fieldsBeforeStar, PairList<String, RelDataType> fields,
- SqlIdentifier identifier) {
+ SqlIdentifier identifier, boolean inRowContext) {
if (!excludeIdentifiers.isEmpty()
&& fields.size() == fieldsBeforeStar) {
throw newValidationError(identifier,
- RESOURCE.selectStarExcludeCannotExcludeAllColumns());
+ inRowContext
+ ? RESOURCE.rowStarExcludeCannotExcludeAllColumns()
+ : RESOURCE.selectStarExcludeCannotExcludeAllColumns());
}
}
@@ -1113,7 +1117,8 @@ private boolean addOrExpandField(List<SqlNode>
selectItems, Set<String> aliases,
fields,
includeSystemVars,
scope,
- starExp);
+ starExp,
+ false);
return true;
default:
addToSelectList(
@@ -7633,6 +7638,11 @@ public static boolean isAmbiguousException(Exception ex)
{
default:
break;
}
+ // SqlStarExclude is expanded by expandStarInRow at the ROW level;
+ // its exclude identifiers must not be resolved as regular column
references.
+ if (call instanceof SqlStarExclude) {
+ return call;
+ }
// Only visits arguments which are expressions. We don't want to
// qualify non-expressions such as 'x' in 'empno * 5 AS x'.
CallCopyingArgHandler argHandler =
@@ -7663,8 +7673,9 @@ private SqlNode expandStarInRow(SqlNode node) {
if (!(scope instanceof SelectScope)) {
// Check if any operand is a star identifier before throwing error
for (SqlNode operand : call.getOperandList()) {
- if (operand instanceof SqlIdentifier
- && ((SqlIdentifier) operand).isStar()) {
+ if (operand instanceof SqlStarExclude
+ || (operand instanceof SqlIdentifier
+ && ((SqlIdentifier) operand).isStar())) {
throw validator.newValidationError(node,
RESOURCE.rowStarNotAllowed());
}
@@ -7675,22 +7686,28 @@ private SqlNode expandStarInRow(SqlNode node) {
final List<SqlNode> expandedOperands = new ArrayList<>();
boolean expanded = false;
for (SqlNode operand : call.getOperandList()) {
- if (operand instanceof SqlIdentifier) {
- final SqlIdentifier identifier = (SqlIdentifier) operand;
- if (identifier.isStar()) {
- final boolean expandedStar =
- validator.expandStar(expandedOperands,
- validator.catalogReader.nameMatcher().createSet(),
- PairList.of(),
- false,
- selectScope,
- identifier);
- if (!expandedStar) {
- throw new AssertionError("Row star expansion failed for " +
identifier);
- }
- expanded = true;
- continue;
+ final SqlIdentifier starId;
+ if (operand instanceof SqlStarExclude) {
+ starId = ((SqlStarExclude) operand).getStarIdentifier();
+ } else if (operand instanceof SqlIdentifier && ((SqlIdentifier)
operand).isStar()) {
+ starId = (SqlIdentifier) operand;
+ } else {
+ starId = null;
+ }
+ if (starId != null) {
+ final boolean expandedStar =
+ validator.expandStar(expandedOperands,
+ validator.catalogReader.nameMatcher().createSet(),
+ PairList.of(),
+ false,
+ selectScope,
+ operand,
+ true);
+ if (!expandedStar) {
+ throw new AssertionError("Row star expansion failed for " +
starId);
}
+ expanded = true;
+ continue;
}
expandedOperands.add(operand);
}
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 056aeb7b07..8906e45121 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -269,9 +269,11 @@ SelectStarRequiresFrom=SELECT * requires a FROM clause
SelectExcludeRequiresStar=EXCLUDE/EXCEPT clause must follow a STAR expression
SelectReplaceRequiresStar=REPLACE clause must follow a STAR expression
SelectStarExcludeListContainsUnknownColumns=SELECT * EXCLUDE/EXCEPT list
contains unknown column(s): {0}
+RowStarExcludeListContainsUnknownColumns=ROW(* EXCLUDE/EXCEPT list) contains
unknown column(s): {0}
SelectStarReplaceListContainsUnknownColumns=SELECT * REPLACE list contains
unknown column(s): {0}
SelectStarReplaceListContainsDuplicateColumns=SELECT * REPLACE list contains
duplicate column(s): {0}
SelectStarExcludeCannotExcludeAllColumns=SELECT * EXCLUDE/EXCEPT list cannot
exclude all columns
+RowStarExcludeCannotExcludeAllColumns=ROW(* EXCLUDE/EXCEPT list) cannot
exclude all columns
GroupFunctionMustAppearInGroupByClause=Group function ''{0}'' can only appear
in GROUP BY clause
AuxiliaryWithoutMatchingGroupCall=Call to auxiliary group function ''{0}''
must have matching call to group function ''{1}'' in GROUP BY clause
PivotAggMalformed=Measure expression in PIVOT must use aggregate function
diff --git
a/core/src/test/java/org/apache/calcite/sql/parser/CoreSqlParserTest.java
b/core/src/test/java/org/apache/calcite/sql/parser/CoreSqlParserTest.java
index c8ad180009..1c90c9484d 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/CoreSqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/CoreSqlParserTest.java
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.sql.parser;
+import org.apache.calcite.avatica.util.Quoting;
import org.apache.calcite.test.DiffTestCase;
import com.google.common.collect.ImmutableList;
@@ -70,4 +71,49 @@ public class CoreSqlParserTest extends SqlParserTest {
private boolean isNotSubclass() {
return this.getClass().equals(CoreSqlParserTest.class);
}
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7364";>[CALCITE-7364]
+ * Support the syntax ROW(T.* EXCLUDE cols) for creating nested ROW
values</a>. */
+ @Test void testRowStarExclude() {
+ // Use backticks to ensure that sql(q).same() in general
+ final SqlParserFixture f = fixture().withConfig(c ->
c.withQuoting(Quoting.BACK_TICK));
+ final String empExcludeEmpno = "SELECT (ROW(`EMP`.* EXCLUDE
(`EMP`.`EMPNO`)))\n"
+ + "FROM `EMP`";
+
+ // Simple star with one excluded column
+ final String starExcludeEmpno = "SELECT (ROW(* EXCLUDE (`EMPNO`)))\n"
+ + "FROM `EMP`";
+ sql("select row(* exclude(empno)) from emp").ok(starExcludeEmpno);
+ f.sql(starExcludeEmpno).same();
+
+ // Table-qualified star with excluded column
+ sql("select row(emp.* exclude(emp.empno)) from emp").ok(empExcludeEmpno);
+ f.sql(empExcludeEmpno).same();
+
+ // EXCEPT is normalized to EXCLUDE on unparse
+ sql("select row(emp.* except(emp.empno)) from emp").ok(empExcludeEmpno);
+
+ // Multiple excluded columns
+ final String starExcludeEmpnoMgr = "SELECT (ROW(* EXCLUDE (`EMPNO`,
`MGR`)))\n"
+ + "FROM `EMP`";
+ sql("select row(* exclude(empno, mgr)) from emp").ok(starExcludeEmpnoMgr);
+ f.sql(starExcludeEmpnoMgr).same();
+
+ // Mixed: table-qualified star with exclude, plus plain star
+ final String empExcludeEmpnoDeptStar =
+ "SELECT (ROW(`EMP`.* EXCLUDE (`EMP`.`EMPNO`), `DEPT`.*))\n"
+ + "FROM `EMP`\n"
+ + "INNER JOIN `DEPT` ON (`EMP`.`DEPTNO` = `DEPT`.`DEPTNO`)";
+ sql("select row(emp.* exclude(emp.empno), dept.*)"
+ + " from emp join dept on emp.deptno = dept.deptno")
+ .ok(empExcludeEmpnoDeptStar);
+ f.sql(empExcludeEmpnoDeptStar).same();
+
+ // Nested ROW with EXCLUDE
+ final String nestedStarExcludeEmpno = "SELECT (ROW((ROW(* EXCLUDE
(`EMPNO`)))))\n"
+ + "FROM `EMP`";
+ sql("select row(row(* exclude(empno))) from
emp").ok(nestedStarExcludeEmpno);
+ f.sql(nestedStarExcludeEmpno).same();
+ }
}
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 c60c2917ed..0f40dfbc6d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -2156,6 +2156,45 @@ void testLikeAndSimilarFails() {
sql("select row(*) from emp").ok();
sql("select row(emp.*) from emp").ok();
sql("select row(emp.*, dept.*) from emp join dept on emp.deptno =
dept.deptno").ok();
+ // Nested ROW with star
+ sql("select row(row(*)) from emp").ok();
+ sql("select row(row(emp.*)) from emp").ok();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7364";>[CALCITE-7364]
+ * Support the syntax ROW(T.* EXCLUDE cols) for creating nested ROW
values</a>. */
+ @Test void testRowWildcardExclude() {
+ sql("select row(* exclude(empno)) from emp").ok();
+ sql("select row(* exclude(empno, deptno)) from emp").ok();
+ sql("select row(emp.* exclude(emp.empno)) from emp").ok();
+ // EXCEPT is a synonym for EXCLUDE
+ sql("select row(emp.* except(emp.empno)) from emp").ok();
+ sql("select row(emp.* exclude(emp.empno), dept.*)"
+ + " from emp join dept on emp.deptno = dept.deptno").ok();
+ // Nested ROW with EXCLUDE
+ sql("select row(row(* exclude(empno))) from emp").ok();
+ sql("select row(row(emp.* exclude(emp.empno))) from emp").ok();
+ // Multiple nested ROWs, one with EXCLUDE
+ sql("select row(row(* exclude(empno)), row(dept.*)) "
+ + "from emp join dept on emp.deptno = dept.deptno").ok();
+ // Unknown column in exclude list
+ sql("select row(* exclude(^foo^)) from emp")
+ .fails("ROW\\(\\* EXCLUDE/EXCEPT list\\) contains unknown
column\\(s\\): FOO");
+ // Unknown column in nested ROW exclude list
+ sql("select row(row(* exclude(^foo^))) from emp")
+ .fails("ROW\\(\\* EXCLUDE/EXCEPT list\\) contains unknown
column\\(s\\): FOO");
+ // Unknown column in table-qualified exclude list
+ sql("select row(emp.* exclude(^emp.foo^)) from emp")
+ .fails("ROW\\(\\* EXCLUDE/EXCEPT list\\) contains unknown
column\\(s\\): EMP\\.FOO");
+ // Excluding all columns from a ROW expression is not allowed
+ sql("select row(^*^ exclude(empno, ename, job, mgr, hiredate, sal, comm,"
+ + " deptno, slacker)) from emp")
+ .fails("ROW\\(\\* EXCLUDE/EXCEPT list\\) cannot exclude all columns");
+ // Excluding all columns via qualified name is not allowed
+ sql("select row(^emp.*^ exclude(emp.empno, emp.ename, emp.job, emp.mgr,"
+ + " emp.hiredate, emp.sal, emp.comm, emp.deptno, emp.slacker)) from
emp")
+ .fails("ROW\\(\\* EXCLUDE/EXCEPT list\\) cannot exclude all columns");
}
@Test void testRowWithValidDot() {
diff --git a/core/src/test/resources/sql/struct.iq
b/core/src/test/resources/sql/struct.iq
index 61c8921573..1d894d254e 100644
--- a/core/src/test/resources/sql/struct.iq
+++ b/core/src/test/resources/sql/struct.iq
@@ -187,4 +187,55 @@ select row(d.*, row(d.*)) from dept d limit 1;
!ok
+# [CALCITE-7364] Support the syntax ROW(T.* EXCLUDE cols) for creating nested
ROW values
+select row(* exclude(empno)) from emp order by empno limit 1;
++----------------------------------------------------+
+| EXPR$0 |
++----------------------------------------------------+
+| {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} |
++----------------------------------------------------+
+(1 row)
+
+!ok
+
+select row(emp.* exclude(emp.empno)) from emp order by empno limit 1;
++----------------------------------------------------+
+| EXPR$0 |
++----------------------------------------------------+
+| {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20} |
++----------------------------------------------------+
+(1 row)
+
+!ok
+
+select row(* exclude(empno, mgr)) from emp order by empno limit 1;
++----------------------------------------------+
+| EXPR$0 |
++----------------------------------------------+
+| {SMITH, CLERK, 1980-12-17, 800.00, null, 20} |
++----------------------------------------------+
+(1 row)
+
+!ok
+
+select row(emp.* exclude(emp.empno), dept.*) from emp join dept on emp.deptno
= dept.deptno order by emp.empno limit 1;
++--------------------------------------------------------------------------+
+| EXPR$0 |
++--------------------------------------------------------------------------+
+| {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20, 20, RESEARCH, DALLAS} |
++--------------------------------------------------------------------------+
+(1 row)
+
+!ok
+
+select row(emp.* exclude(emp.empno), dept.* exclude(dept.deptno)) from emp
join dept on emp.deptno = dept.deptno order by emp.empno limit 1;
++----------------------------------------------------------------------+
+| EXPR$0 |
++----------------------------------------------------------------------+
+| {SMITH, CLERK, 7902, 1980-12-17, 800.00, null, 20, RESEARCH, DALLAS} |
++----------------------------------------------------------------------+
+(1 row)
+
+!ok
+
# End struct.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e50acf936f..026fa59c47 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -248,9 +248,16 @@ ## Grammar
*
| * REPLACE '(' expression AS column [, expression AS column ]* ')'
+rowStarItem:
+ *
+ | tableAlias . *
+ | * { EXCLUDE | EXCEPT } '(' column [, column ]* ')'
+ | tableAlias . * { EXCLUDE | EXCEPT } '(' column [, column ]* ')'
+
Note:
-* `SELECT * EXCLUDE (...)` and `SELECT * REPLACE (...)` are recognized only
when the Babel parser is enabled. `EXCLUDE` (or the alias `EXCEPT`) removes the
specified columns from the star expansion; `REPLACE` substitutes the given
expressions for the matching columns while keeping the original column order.
For `REPLACE`, the column alias must either be a simple identifier or, for a
table-qualified star such as `t.*`, a qualified identifier whose prefix matches
the star's table alias.
+* `EXCLUDE` (or the alias `EXCEPT`) removes the specified columns from the
star expansion; `REPLACE` substitutes the given expressions for the matching
columns while keeping the original column order. For `REPLACE`, the column
alias must either be a simple identifier or, for a table-qualified star such as
`t.*`, a qualified identifier whose prefix matches the star's table alias.
+* `ROW(rowStarItem [, rowStarItem ]*)` creates a nested ROW from all columns
(or all columns except the excluded ones) of one or more tables. `EXCEPT` is an
alias for `EXCLUDE` in this context.
projectItem:
expression [ [ AS ] columnAlias ]
@@ -1793,6 +1800,7 @@ ### Value constructors
| Operator syntax | Description
|:--------------- |:-----------
| ROW (value [, value ]*) | Creates a row from a list of values.
+| ROW (rowStarItem [, rowStarItem ]*) | Creates a row from all columns, or
all columns except those excluded, of one or more tables.
| (value [, value ]* ) | Creates a row from a list of values.
| row '[' index ']' | Returns the element at a particular location in a
row (1-based index).
| row '[' name ']' | Returns the element of a row with a particular
name.
