[
https://issues.apache.org/jira/browse/CALCITE-7331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18045079#comment-18045079
]
Zhen Chen edited comment on CALCITE-7331 at 12/15/25 4:32 AM:
--------------------------------------------------------------
I tested both scenarios and they both work. The asterisk supports two formats
({*}* EXCLUDE/EXCEPT{*} and \{*}table.{*}* {*}EXCLUDE/EXCEPT{*}), but not
{*}column EXCLUDE/EXCEPT{*}. So far, it seems to work well.
{code:java}
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{code}
If you approve of this proposal, I can add these test cases to the PR. If you
have other concerns, we can temporarily abandon this proposal, as it's just an
alias and doesn't offer any substantial functionality. Please let me know your
thoughts.
was (Author: jensen):
I tested both scenarios and they both work. The asterisk supports two formats
({*}*{*} {*}EXCLUDE/EXCEPT{*}{*}{*} and *table.** {*}EXCLUDE/EXCEPT{*}), but
not {*}column EXCLUDE/EXCEPT{*}. So far, it seems to work well.
{code:java}
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{code}
If you approve of this proposal, I can add these test cases to the PR. If you
have other concerns, we can temporarily abandon this proposal, as it's just an
alias and doesn't offer any substantial functionality. Please let me know your
thoughts.
> Support the alias form SELECT * EXCEPT() for SELECT * EXCLUDE()
> ---------------------------------------------------------------
>
> Key: CALCITE-7331
> URL: https://issues.apache.org/jira/browse/CALCITE-7331
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.41.0
> Reporter: Zhen Chen
> Assignee: Zhen Chen
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.42.0
>
>
> Based on the CALCITE-7310 implementation, this Jira primarily serves as an
> alias extension. For example:
> SELECT * EXCLUDE(deptno, empno) FROM emp;
> Similarly, it can be written as:
> SELECT * EXCEPT(deptno, empno) FROM emp;
> Similar to the implementation in ClickHouse[1], but the expr in <expr>
> EXCEPT(col1, col2) only supports *.
> [1]
> [https://clickhouse.com/docs/zh/sql-reference/statements/select/except-modifier]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
