row_number() calculation does not agree with sort order when there are ties and there is a nested view

[christopherswenson]

In a query like:

run: something -> {
  group_by: thing
  aggregate: stuff
  order_by: stuff
  nest: cool_chart
  calculate: n is row_number()
}

We generate SQL like:

WITH __stage0 AS (
  SELECT
    group_set,
    ... as "stuff__0"
    ... row_number() ... as "n__0"
  FROM (...)
  GROUP BY ...
)
SELECT
  ... as "stuff",
  ... as "n"
FROM __stage0
GROUP BY 1
ORDER BY "stuff" desc

If there is a tie between rows A and B in the stuff field, the row_number() can be calculated with A before B in __stage0, then ordered with B before A in the outer select. This results in something like:

• thing: "C" stuff: 100, cool_chart: ..., n: 1
• thing: "D" stuff: 90, cool_chart: ..., n: 2
• thing: "B" stuff: 80, cool_chart: ..., n: 4
• thing: "A" stuff: 70, cool_chart: ..., n: 3

This does not happen without a nest in the query because it only generates a one-stage query.