RANK() OVER (PARTITION BY.. results in thread 'main' panicked at ... called `Option::unwrap()` on a `None` value

[simplenotezy]

Using Rank() OVER (PARTITION...) results in this error:

thread 'main' panicked at src/ts_generator/sql_parser/expressions/translate_expr.rs:163:61:
called `Option::unwrap()` on a `None` value

Full query:

WITH cte AS (
  SELECT
    s.title,
    sg.genre,
    RANK() OVER (PARTITION BY sg.genre ORDER BY s.title) AS rank
  FROM
    show s
  JOIN
    show_genre sg ON s.id = sg.show_id
  WHERE
    sg.genre IN ('Action', 'Adventure')
)
SELECT
  title,
  genre
FROM
  cte
WHERE
  rank <= 10;

[simplenotezy]

A workaround is to create a second cte and do the WHERE rank... filtering there, and then select from this new CTE:

WITH cte AS (
  SELECT
    s.title,
    sg.genre,
    RANK() OVER (PARTITION BY sg.genre ORDER BY s.title) AS rank
  FROM
    show s
  JOIN
    show_genre sg ON s.id = sg.show_id
  WHERE
    sg.genre IN ('Action', 'Adventure')
),
filtered_cte AS (
  SELECT
    title,
    genre
  FROM
    cte
  WHERE
    rank <= 10
)
SELECT * FROM filtered_cte

[JasonShin]

This is interesting, thanks. I actually haven't considered CTE yet but that's quite common in MySQL 8 and PG. Will take a look shortly with the other issues