Why does left join cause NEWID() to materialize sooner than join?

Question

Using MSSQL Server 2008 Enterprise Edition, and most likely other versions of MSSQL, here is a proof of concept that makes a temp table and materializes NEWID() differently depending on if you used a JOIN or LEFT JOIN, even though we are matching two rows exactly.

If you look at the execution plan, you can see that the compute scalar to get NEWID() is performed last using JOIN, but not when using LEFT JOIN. I would have expected the LEFT JOIN behavior. Is this weirdness due to naivety in the execution plan or is there something more going on?

Demonstration With Temp Table:

Create Table #Temp
(
    ChildGuid uniqueidentifier,
    ParentGuid uniqueidentifier
)
insert into #Temp (ChildGuid, ParentGuid) Values('5E3211E8-D382-4775-8F96-041BF419E70F', '96031FA0-829F-43A1-B5A6-108362A37701')
insert into #Temp (ChildGuid, ParentGuid) Values('FFFFFFFF-D382-4775-8F96-041BF419E70F', '96031FA0-829F-43A1-B5A6-108362A37701')

--Use a join. Get different NewIDs.
select * from #Temp 
join 
(
    select ParentGuid, NewParentGuid from(
    select ParentGuid, NEWID() as NewParentGuid from #Temp
    group by ParentGuid
    ) tb2
) temp2 on #Temp.ParentGuid = temp2.ParentGuid

--Do exactly as above, but use a left join. Get a pair of the same NewIDs.
select * from #Temp 
left join 
(
    select ParentGuid, NewParentGuid from(
    select ParentGuid, NEWID() as NewParentGuid from #Temp
    group by ParentGuid
    ) tb2
) temp2 on #Temp.ParentGuid = temp2.ParentGuid

With Join, NewParentGuid is different for both rows.

With Left Join, NewParentGuid is the same.

EDIT2: If you append this to the left join, the results change.

where temp2.ParentGuid = temp2.ParentGuid

Or as another user pointed out, where that column is not null. They'll stay the same when performing comparisons on other columns or where 1=1.Schroedinger's column?

See also:

Why does newid() materialize at the very end of a query?

Answer 1

Not really an answer but an observation

This returns duplicate

select * from #Temp 
inner hash join 
(
     select ParentGuid, NEWID() as NewParentGuid 
     from #Temp
     group by ParentGuid
     union 
     select null, NEWID()    
) temp2 
  on #Temp.ParentGuid = temp2.ParentGuid

--Do exactly as above, but use a left join. Get a pair of the same NewIDs.
select * from #Temp 
left hash join 
(
     select ParentGuid, NEWID() as NewParentGuid 
     from #Temp
     group by ParentGuid
) temp2 
  on #Temp.ParentGuid = temp2.ParentGuid

this forces them both to be different

select * from #Temp  join  (
     select ParentGuid, NEWID() as NewParentGuid 
     from #Temp
     group by ParentGuid    ) temp2 
     on #Temp.ParentGuid = temp2.ParentGuid

--Do exactly as above, but use a left join. Get a pair of the same NewIDs. select * from #Temp  left  join  (
     select ParentGuid, NEWID() as NewParentGuid 
     from #Temp
     group by ParentGuid ) temp2 
     on #Temp.ParentGuid = temp2.ParentGuid 
    and temp2.ParentGuid is not null