Materializing Multistatement Table-valued Function Result

SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;

--select @@VERSION;
drop view lAN_forever;
drop view lAN_quickly;
drop function fAT_with_pk;
drop function fAT_without_pk;
drop table AT1;
drop table AT2;
drop table AN;

create table AN (
    id int not null
);
go

create table AT1 (
    id int not null
);
go

create table AT2 (
    id int not null
);
go

create function fAT_with_pk (
    @doNotLoopForever bit
)
returns @AT table  (
    id int not null primary key
    -- with the primary key definition
    -- the optimizer should be able
    -- to do table elimination and
    -- in that case never materialize
    -- this table
) as
begin
    declare @i int = 0;
    while(@i >= @doNotLoopForever)
    begin
        set @i = @i + 1;
        insert into @AT values(@i);
    end
    return
end
go

create function fAT_without_pk (
    @dummy bit
)
returns @AT table  (
    id int not null
) as
begin
    insert into @AT (id)
    select id from AT1;
    return
end
go

create view lAN_forever
as
select
    an.id as AN_id,
    at1.id as AT1_id,
    at2.id as AT2_id,
    at3.id as AT3_id
from
    AN an
left join
    AT1 at1
on
    at1.id = an.id
left join
    fAT_with_pk(0) at2
on
    at2.id = an.id
left join
    fAT_without_pk(0) at3
on
    at3.id = an.id;
go

create view lAN_quickly
as
select
    an.id as AN_id,
    at1.id as AT1_id,
    at2.id as AT2_id,
    at3.id as AT3_id
from
    AN an
left join
    AT1 at1
on
    at1.id = an.id
left join
    fAT_with_pk(1) at2
on
    at2.id = an.id
left join
    fAT_without_pk(0) at3
on
    at3.id = an.id;
go


declare @numberOfRows int = 1000;

with rowGen(rowNum) as (
    select
        1
    union all
    select
        rowNum + 1
    from
        rowGen
    where
        rowNum < @numberOfRows
)
insert into AN (id)
select rowNum from rowGen
option (maxrecursion 0);

insert into AT1 (id) select id from AN;
insert into AT2 (id) select id from AN;

alter table AN add constraint pkAN primary key (id);
alter table AT1 add constraint pkAT1 primary key (id);
alter table AT1 add constraint fkAT1 foreign key (id) references AN(id);

update statistics AN;
update statistics AT1;
update statistics AT2;
go

SET STATISTICS XML ON;
SET STATISTICS IO,TIME ON;
-------------------------------------
select
    avg(cast(AT1_id as bigint))
from
    lAN_quickly
where
    AT1_id is not null;
-- this should execute and not run
-- forever due to the infinite loop
select
    avg(cast(AT1_id as bigint))
from
    lAN_forever
where
    AT1_id is not null;
-------------------------------------
SET STATISTICS XML OFF;
SET STATISTICS IO,TIME OFF;
go