Hi all,

The following:

select
first.ID,
first.Type,
first.Value
from
vw_main first
where
first.Type = 'name'

returns a single row: (1, 'name', 'Shak').

When attempting to join this view on itself:

select
first.ID,
first.Value name,
second.Value job
from
vw_main first
left outer join
vw_main second
on
second.ID = first.ID
and
second.Type = 'job'
where
first.Type = 'name'

I get zero results. I think this is an incorrect answer.

I had to switch my optimizer_search_depth to a low value (0 or 6 or so)
since other joins were spending literally hours in the "statistics" state.
Turning this back to the default of 62 returns the expected resultset of (1,
'Shak', null) in the join above.

Is it possible for the optimizer_search_depth to affect results in this way?
I would assume that changing it only affects the speed of the query, but not
the actual results themselves. The above demonstrates otherwise.

I'm now stuck between using a slow but correct optimiser and a fast but
incorrect one!

Shak