Outer apply (and cross apply ) against the same table result

Question

Considering the following script (with SQL Server 2017):

declare @mytable as table (n int identity(1,1), name varchar(10),mydate  date)
insert into @mytable(name,mydate) values('a','01/01/2019')

select * from @mytable t1
cross apply (select   * from t1 ) t2;

select *,mydate from @mytable t1
cross apply (select   * from t1 ) t2

How do you explain that

1. I'm getting 5 rows

2. Columns 1 and 2 are named c1 and c2 instead of original names in @mytable

3. I'm not getting mydate in script one and I am getting it only if I write it (* is not sufficient)

4. "this is a text" is returned in rows 2 to 5 => how do you explain that?

Answer 1

Your CROSS APPLY definition is select * from t1 - this does not select from the alias defined above. Instead it looks for a table called t1 and selects from it - this is in no way correlated to the rest of the query.

If you wanted to APPLY the values from the current row of the aliased table you would need to do

select * from @mytable t1
cross apply (select   t1.*  ) t2;

without the FROM.

This behaves as you were expecting.