feat: SELECT * EXCEPT [columnA,columnB,...]

[st1page]

It is a SQL grammar supported by Snowflake
It can improve the usability of GroupTopN pattern https://www.risingwave.dev/docs/current/sql-pattern-topn/
Still need discussion.

SELECT * EXCEPT(rank)
  FROM (
    SELECT [column_list], 
      function_name() OVER ([PARTITION BY col1[, col2...]] 
        ORDER BY col1 [ ASC | DESC ][, col2 [ ASC | DESC ]...]) AS rank 
    FROM table_name)
WHERE rank <= N [AND conditions];

[fuyufjh]

-1. It sounds very ad-hoc and not part of SQL standard, and it's very easy to work around.

[xxchan]

This syntax is from BigQuery. It seems more reasonable for an AP system to support convenient syntax for ad-hoc queries 🤔, and less reasonable for us.

[xxchan]

Similar request for adding new syntax: #7506

My main concern is that adding new things may break other things in unexpected ways, especially for SQL, a complex and not well-designed language.

Anyway, such topic might unavoidably involve a lot of bikeshedding, and I agree we should decide the general attitude before talking about specific sugars.

[neverchanje]

I found a solution for the specific query you mentioned. Instead of select * in the outer query, we can rewrite this by select * in the inner query and excluding the rank in the outer side:

The original query:

select * from (select v1, v2, row_number() over (order by v1 asc) rn from t) as foo where rn = 1;

The rewritten version:

select v1, v2 from (select *, row_number() over (order by v1 asc) rn from t) as foo where rn = 1;

[lmatz]

I think it appears to be some dbt-level stuff, like a macro and users write that and get translated automatically, but still send the original to our Frontend to process. No changes need to be made on our side.

I do agree that it improves user experience.

Edit:
Since so many databases support it natively, changed my mind

Edit:
Also considered TennyZhuang's use case.

[TennyZhuang]

-1. It sounds very ad-hoc and not part of SQL standard, and it's very easy to work around.

I'm modifying a query related to a 35-cols table, and I think it's hard to work around :(

[st1page]

          > > #9092

In all well-known database, only BigQuery supports that, but they use EXCEPT keyword :(

Sorry it is my typo 🥵 😿

Originally posted by @st1page in #10265 (comment)

[TennyZhuang]

So at least both Snowflake and BigQuery support similar things.

[TennyZhuang]

https://cloud.google.com/spanner/docs/reference/standard-sql/query-syntax#select_list And Spanner

[TennyZhuang]

https://clickhouse.com/docs/en/sql-reference/statements/select#except
Clickhouse

[xxchan]

We can continue to discuss this request now.

@lmatz said:

Since so many databases support it natively, changed my mind

@fuyufjh how do you think?

BTW, I think @neverchanje 's workaround of using select * in the inner query still applies. @TennyZhuang how to you think?

[xxchan]

workaround of using select * in the inner query

Well, it only works if we are going to decide the columns to use after the TopN. But maybe we just want to do a TopN and select all columns for later processing.

[xxchan]

BTW, duckdb/snowflake use EXCLUDE

[st1page]

BTW, duckdb/snowflake use EXCLUDE

we can implement it too 👀

[xxchan]

What do you mean? Isn't EXCEPT the same as EXCLUDE? We need to choose one name. 👀

[st1page]

What do you mean? Isn't EXCEPT the same as EXCLUDE? We need to choose one name. eyes

You see, in SQL we can use different word for the same feature, such as "EXCEPT" and "MINUS"