Discussion: SELECT * EXCEPT syntax

[TennyZhuang]

This is an interesting feature supported by BigQuery. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

Why do we need that?

The feature is very useful to us since we don't support output rank in the common pattern Group TopN, see https://www.risingwave.dev/docs/current/sql-pattern-topn/

It can be very frustrating if we use the existing syntax and the table has a large number of columns (We've seen about 30-100 columns in user queries).

SELECT c1, c2, c3, c4, c5, c6
  FROM (
    SELECT c1, c2, c3, c4, c5, c6, 
      row_number() OVER (PARTITION BY grp
        ORDER BY sk) AS rank
    FROM t1)
WHERE rank <= N;

If we use * in the outer query, then the query will fail by:

fail to transform overAgg to groupTopN: the rank cannot be included in the outer select_list, see https://www.risingwave.dev/docs/current/sql-pattern-topn/ for more information

If we support the * EXCEPT [column_list] syntax, we can simply write the following query:

SELECT * EXCEPT rank
  FROM (
    SELECT c1, c2, c3, c4, c5, c6, 
      row_number() OVER (PARTITION BY grp
        ORDER BY sk) AS rank
    FROM t1)
WHERE rank <= N;

[xiangjinwu]

#9092

[TennyZhuang]

#9092

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

[st1page]

#9092

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

Sorry it is my typo 🥵 😿

[xxchan]

Let's discuss in #9092