[
https://issues.apache.org/jira/browse/CALCITE-7331?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18045031#comment-18045031
]
Zhen Chen commented on CALCITE-7331:
------------------------------------
[~julianhyde] Thank you very much for your reply. I tested it on duckdb, and
both forms are supported. Am I misunderstanding your meaning?
{code:java}
duckdb> SELECT e.* EXCLUDE (e.deptno) from emp e limit 1;
┌───────┬───────┬───────┬──────┬────────────┬────────┬──────┐
│ EMPNO ┆ ENAME ┆ JOB ┆ MGR ┆ HIREDATE ┆ SAL ┆ COMM │
╞═══════╪═══════╪═══════╪══════╪════════════╪════════╪══════╡
│ 7369 ┆ SMITH ┆ CLERK ┆ 7902 ┆ 1980-12-17 ┆ 800.00 ┆ │
└───────┴───────┴───────┴──────┴────────────┴────────┴──────┘
duckdb> SELECT e.* EXCLUDE (deptno) from emp e limit 1;
┌───────┬───────┬───────┬──────┬────────────┬────────┬──────┐
│ EMPNO ┆ ENAME ┆ JOB ┆ MGR ┆ HIREDATE ┆ SAL ┆ COMM │
╞═══════╪═══════╪═══════╪══════╪════════════╪════════╪══════╡
│ 7369 ┆ SMITH ┆ CLERK ┆ 7902 ┆ 1980-12-17 ┆ 800.00 ┆ │
└───────┴───────┴───────┴──────┴────────────┴────────┴──────┘ {code}
Using the current PR, I added the following test cases to the quidem tests in
Babel, and they work well.
{code:java}
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 {code}
> 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)
