This is an automated email from the ASF dual-hosted git repository.
zhenchen 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 5e309f24c4 [CALCITE-7331] Support the alias form SELECT * EXCEPT() for
SELECT * EXCLUDE()
5e309f24c4 is described below
commit 5e309f24c4b62a9416586d6e3797d5134c678d63
Author: Zhen Chen <[email protected]>
AuthorDate: Wed Jan 14 13:54:41 2026 +0800
[CALCITE-7331] Support the alias form SELECT * EXCEPT() for SELECT *
EXCLUDE()
---
.../java/org/apache/calcite/test/BabelTest.java | 36 +++++++++++++--
babel/src/test/resources/sql/select.iq | 52 ++++++++++++++++++++++
core/src/main/codegen/templates/Parser.jj | 2 +-
.../apache/calcite/runtime/CalciteResource.java | 6 +--
.../calcite/runtime/CalciteResource.properties | 6 +--
site/_docs/reference.md | 2 +-
6 files changed, 93 insertions(+), 11 deletions(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
index 769975c4a8..6970280ed4 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
@@ -195,7 +195,21 @@ names, is(
});
fixture.withSql("select * exclude (empno, ^foo^) from emp")
- .fails("SELECT \\* EXCLUDE list contains unknown column\\(s\\): FOO");
+ .fails("SELECT \\* EXCLUDE/EXCEPT list contains unknown column\\(s\\):
FOO");
+
+ // Alias form: EXCEPT behaves the same as EXCLUDE
+ fixture.withSql("select * except(empno, deptno) from emp")
+ .type(type -> {
+ final List<String> names = type.getFieldList().stream()
+ .map(RelDataTypeField::getName)
+ .collect(Collectors.toList());
+ assertThat(
+ names, is(
+ ImmutableList.of("ENAME", "JOB", "MGR", "HIREDATE", "SAL",
"COMM", "SLACKER")));
+ });
+
+ fixture.withSql("select * except (empno, ^foo^) from emp")
+ .fails("SELECT \\* EXCLUDE/EXCEPT list contains unknown column\\(s\\):
FOO");
fixture.withSql("select e.* exclude(e.empno, e.ename, e.job, e.mgr)"
+ " from emp e join dept d on e.deptno = d.deptno")
@@ -210,7 +224,23 @@ names, is(
fixture.withSql("select e.* exclude(e.empno, e.ename, e.job, e.mgr,
^d.deptno^)"
+ " from emp e join dept d on e.deptno = d.deptno")
- .fails("SELECT \\* EXCLUDE list contains unknown column\\(s\\):
D.DEPTNO");
+ .fails("SELECT \\* EXCLUDE/EXCEPT list contains unknown column\\(s\\):
D.DEPTNO");
+
+ // Alias form: EXCEPT for table-qualified star
+ fixture.withSql("select e.* except(e.empno, e.ename, e.job, e.mgr)"
+ + " from emp e join dept d on e.deptno = d.deptno")
+ .type(type -> {
+ final List<String> names = type.getFieldList().stream()
+ .map(RelDataTypeField::getName)
+ .collect(Collectors.toList());
+ assertThat(
+ names, is(
+ ImmutableList.of("HIREDATE", "SAL", "COMM", "DEPTNO",
"SLACKER")));
+ });
+
+ fixture.withSql("select e.* except(e.empno, e.ename, e.job, e.mgr,
^d.deptno^)"
+ + " from emp e join dept d on e.deptno = d.deptno")
+ .fails("SELECT \\* EXCLUDE/EXCEPT list contains unknown column\\(s\\):
D.DEPTNO");
fixture.withSql("select e.* exclude(e.empno, e.ename, e.job, e.mgr), d.*
exclude(d.name)"
+ " from emp e join dept d on e.deptno = d.deptno")
@@ -242,7 +272,7 @@ names, is(
// To verify that the exclude list contains all columns in the table
fixture.withSql("select ^*^ exclude(deptno, name) from dept")
- .fails("SELECT \\* EXCLUDE list cannot exclude all columns");
+ .fails("SELECT \\* EXCLUDE/EXCEPT list cannot exclude all columns");
}
/** Tests that DATEADD, DATEDIFF, DATEPART, DATE_PART allow custom time
diff --git a/babel/src/test/resources/sql/select.iq
b/babel/src/test/resources/sql/select.iq
index 073daf8cd2..c969ad7655 100755
--- a/babel/src/test/resources/sql/select.iq
+++ b/babel/src/test/resources/sql/select.iq
@@ -234,4 +234,56 @@ WHERE d.loc = 'CHICAGO';
!ok
+# [CALCITE-7331] Support the alias form SELECT * EXCEPT() for SELECT *
EXCLUDE()
+select 1 as x, 2 as y except (select 3 as a, 4 as b);
++---+---+
+| X | Y |
++---+---+
+| 1 | 2 |
++---+---+
+(1 row)
+
+!ok
+
+with t(x, y) as (values(1, 2))
+select 1 as x, 2 as y except (y) from t;
+Non-query expression encountered in illegal context
+!error
+
+with t(x, y) as (values(1, 2))
+select x except (x) from t;
+EXCLUDE/EXCEPT clause must follow a STAR expression
+!error
+
+with t(x, y) as (values(1, 2))
+select * except (x) from t;
++---+
+| Y |
++---+
+| 2 |
++---+
+(1 row)
+
+!ok
+
+select 1 as x, e.* except(e.empno, e.ename, e.job, e.mgr), d.*
except(d.dname), 2 as y
+from emp e join dept d on e.deptno = d.deptno limit 1;
++---+------------+---------+------+--------+---------+----------+---+
+| X | HIREDATE | SAL | COMM | DEPTNO | DEPTNO0 | LOC | Y |
++---+------------+---------+------+--------+---------+----------+---+
+| 1 | 1981-06-09 | 2450.00 | | 10 | 10 | NEW YORK | 2 |
++---+------------+---------+------+--------+---------+----------+---+
+(1 row)
+
+!ok
+
+select d1.* except(d1.dname) from dept d1 except(select d2.* except(d2.dname)
from dept d2);
++--------+-----+
+| DEPTNO | LOC |
++--------+-----+
++--------+-----+
+(0 rows)
+
+!ok
+
# End select.iq
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 2069882cc2..d87339e660 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -2049,7 +2049,7 @@ SqlNodeList StarExcludeList() :
SqlIdentifier id;
}
{
- <EXCLUDE> <LPAREN> { s = span(); }
+ ( <EXCLUDE> | <EXCEPT> ) <LPAREN> { s = span(); }
id = CompoundIdentifier() {
list.add(id);
}
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 026715fbf1..baf5fca10e 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -807,13 +807,13 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("SELECT * requires a FROM clause")
ExInst<SqlValidatorException> selectStarRequiresFrom();
- @BaseMessage("EXCLUDE clause must follow a STAR expression")
+ @BaseMessage("EXCLUDE/EXCEPT clause must follow a STAR expression")
ExInst<CalciteException> selectExcludeRequiresStar();
- @BaseMessage("SELECT * EXCLUDE list contains unknown column(s): {0}")
+ @BaseMessage("SELECT * EXCLUDE/EXCEPT list contains unknown column(s): {0}")
ExInst<SqlValidatorException>
selectStarExcludeListContainsUnknownColumns(String columns);
- @BaseMessage("SELECT * EXCLUDE list cannot exclude all columns")
+ @BaseMessage("SELECT * EXCLUDE/EXCEPT list cannot exclude all columns")
ExInst<SqlValidatorException> selectStarExcludeCannotExcludeAllColumns();
@BaseMessage("Group function ''{0}'' can only appear in GROUP BY clause")
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 e0b1414a16..ac137d058e 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -266,9 +266,9 @@ CannotStreamResultsForNonStreamingInputs=Cannot stream
results of a query with n
MinusNotAllowed=MINUS is not allowed under the current SQL conformance level
SelectMissingFrom=SELECT must have a FROM clause
SelectStarRequiresFrom=SELECT * requires a FROM clause
-SelectExcludeRequiresStar=EXCLUDE clause must follow a STAR expression
-SelectStarExcludeListContainsUnknownColumns=SELECT * EXCLUDE list contains
unknown column(s): {0}
-SelectStarExcludeCannotExcludeAllColumns=SELECT * EXCLUDE list cannot exclude
all columns
+SelectExcludeRequiresStar=EXCLUDE/EXCEPT clause must follow a STAR expression
+SelectStarExcludeListContainsUnknownColumns=SELECT * EXCLUDE/EXCEPT list
contains unknown column(s): {0}
+SelectStarExcludeCannotExcludeAllColumns=SELECT * 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/site/_docs/reference.md b/site/_docs/reference.md
index 2668cbf83c..e1ea8e7206 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -246,7 +246,7 @@ ## Grammar
Note:
-* `SELECT * EXCLUDE (...)` is recognized only when the Babel parser is
enabled. It sets the generated parser configuration flag `includeStarExclude`
to `true` (the standard parser leaves that flag `false`), which allows a `STAR`
token followed by `EXCLUDE` and a parenthesized identifier list to be parsed
into a `SqlStarExclude` node and ensures validators respect the exclusion list
when expanding the projection. Reusing the same parser configuration elsewhere
enables the same syntax for [...]
+* `SELECT * EXCLUDE (...)` is recognized only when the Babel parser is
enabled. It sets the generated parser configuration flag `includeStarExclude`
to `true` (the standard parser leaves that flag `false`), which allows a `STAR`
token followed by `EXCLUDE` (or the alias `EXCEPT`) and a parenthesized
identifier list to be parsed into a `SqlStarExclude` node and ensures
validators respect the exclusion list when expanding the projection. Reusing
the same parser configuration elsewhere enab [...]
projectItem:
expression [ [ AS ] columnAlias ]
