Why do we have Top N Sort in this example?

In the example below the result of Index Spool is already sorted, why do we have Top N Sort here instead of simple Top?

use tempdb;
go

create table dbo.t1 (id int identity primary key, v int);
create table dbo.t2 (id int identity primary key, v int);

insert into dbo.t1
(v)
select top (1000)
row_number() over (order by 1/0)
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

insert into dbo.t2
(v)
select top (10000)
row_number() over (order by 1/0) + 10000
from
master.dbo.spt_values a cross join
master.dbo.spt_values b;

set statistics xml, io on;

select
sum(a.v + b.v)
from
dbo.t1 a outer apply
(select top (1) v from dbo.t2 where v >= a.v order by v) b;

set statistics xml,io off;
go

drop table dbo.t1, dbo.t2;
go

It's reproducible in all versions starting from 2008 R2 (I just have no server with earlier versions to test)

It's a somewhat annoying limitation of the current optimizer.

I wrote about this a little as part of The Eager Index Spool and The Optimizer:


Index spools do not tell the optimizer they support output ordered by the spool's index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

StackExchange Database Administrators Q#253288, answer score: 2