Why does the TOP function not function as I expect in SQL?-CodePudding

I thought I knew how the TOP function works, but with this code below I'm not sure.

Please can someone tell me why it returns fruit:b ordinal:9, instead of the expected fruit:b ordinal:8?

;WITH CTE
AS (
 SELECT fruit, ordinal, row_number() OVER (
   ORDER BY (
     SELECT 1
     )
   ) AS rn
 FROM (
  VALUES (1, 'a'), (2, 'b'), (3, 'b'), (4, 'c'), (5, 'c'), (6, 'a'), (7, 'a'), (8, 'b'), (9, 'b')
  ) fruits(ordinal, fruit)
 ), CTE2
AS (
 SELECT fruit, ordinal
 FROM cte AS cteouter
 WHERE rn = 1
  OR fruit != (
   SELECT fruit
   FROM cte AS cteinner
   WHERE cteinner.rn = cteouter.rn - 1
   )
 )
--SELECT * FROM CTE2
SELECT TOP 1 *
FROM cte2
ORDER BY ordinal DESC

CodePudding user response：

row_number() OVER (ORDER BY (SELECT 1)) (as well as over (order by 1) or over (order by 1/0)) does not guarantee stable reproducible numbering of the incoming rows. Quite opposite, it effectively switches off the order by clause and makes it random.

When you run the top query with TOP 1, you get one execution plan, and when without TOP, you get another. These plans happen to randomly result in a different ordering of the rows in CTE, which in turn changes which rows are returned from CTE2.