CREATE TABLE test_row_number(id INTEGER PRIMARY KEY, rand INTEGER);

INSERT INTO test_row_number(id,rand)
VALUES (94,73), (91,76), (90,78), (92,79), (96,75), (95,71), (99,74), (97,70), (93,77), (98,72);

SET sql_mode = '';
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number ORDER BY id DESC; # test=1 run with OK result
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number GROUP BY id ORDER BY id DESC; # test=2 run with wrong result
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number GROUP BY id, rand ORDER BY id DESC; # test=3 run with OK result

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number ORDER BY id DESC; # test=4 won't run
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number GROUP BY id ORDER BY id DESC; # test=5 won't run
SELECT ROW_NUMBER() OVER (ORDER BY rand) AS row, id, rand FROM test_row_number GROUP BY id, rand ORDER BY id DESC; # test=6 run with OK result

DROP TABLE test_row_number;

# Why does test 2 return the wrong result?