Window function ignores order if there is unused named window in the query

Question

CREATE TABLE tablename (
  `SchoolName` VARCHAR(10),
  `Marks` INTEGER
);

INSERT INTO tablename
  (`SchoolName`, `Marks`)
VALUES
  ('A', 71),
  ('A', 71),
  ('A', 71),
  ('B', 254),
  ('B', 135),
  ('B', 453),
  ('B', 153);

SELECT
    *
FROM
    (  
        SELECT 
            SchoolName,
            Marks,
            row_number() OVER (PARTITION BY SchoolName ORDER BY Marks) rn
        FROM tablename
        WINDOW w  AS (PARTITION BY SchoolName ORDER BY Marks)
) t

Does it ok that rn is unsorted if there is unused named window in the query with order by clause?

SchoolName  Marks   rn
A           71      1
A           71      2
A           71      3
B           254     1
B           135     2
B           453     3
B           153     4

https://www.db-fiddle.com/f/pWH7ar9V8JLQ6FzM9Qd9K6/1

EDIT

I suppose it to be like this

SchoolName  Marks   rn
A           71      1
A           71      2
A           71      3
B           135     1
B           153     2
B           254     3
B           453     4

EDIT 2

OK, I have a little bit confused others, the problem is not with order of rows but why rn has value 1 next to 254 if I specified the order for row_number().

P.s. I uderstand if I delete window w it will work well but why the unused named windows affects other window function?

Answer 1

Your query defines a window frame that is not used - that part is just a no-op.

These are equivalent:

SELECT 
    SchoolName,
    Marks,
    row_number() OVER (PARTITION BY SchoolName ORDER BY Marks) rn
FROM tablename

And:

SELECT 
    SchoolName,
    Marks,
    row_number() OVER w rn
FROM tablename
WINDOW w AS (PARTITION BY SchoolName ORDER BY Marks)

I don't see the point for using a subquery either - so I removed that part.

Note that the ORDER BY clause applies to the window function only. Both your query and the above two offer no guarantee about the ordering of the rows in the resultset itself. If you want a consistent ordering in the resultset, then use an order by clause, as in:

SELECT 
    SchoolName,
    Marks,
    row_number() OVER (PARTITION BY SchoolName ORDER BY Marks) rn
FROM tablename
ORDER BY SchoolName, Marks