Why do these similar queries use different optimisation phases (transaction processing vs quick plan)?

The example code in this connect item

Shows a bug where

SELECT COUNT(*)
FROM   dbo.my_splitter_1('2') L1
       INNER JOIN dbo.my_splitter_1('') L2
         ON L1.csv_item = L2.csv_item

Returns the correct results. But the following returns incorrect results (on 2014 using the new Cardinality Estimator)

SELECT
    (SELECT COUNT(*)
    FROM dbo.my_splitter_1('2') L1
     INNER JOIN dbo.my_splitter_1('') L2
        ON L1.csv_item = L2.csv_item)

As it incorrectly loads the results for L2 into a common sub expression spool then replays the result of that for the L1 result.

I was curious as to why the difference in behaviour between the two queries. Trace Flag 8675 shows that the one that works enters search(0) - transaction processing and the one that fails enters search(1) - quick plan.

So I assume that the availability of additional transformation rules is behind the difference in behaviour (disabling either BuildGbApply or GenGbApplySimple seems to fix it for example).

But why do the two plans for these very similar queries encounter different optimization phases? From what I've read search (0) requires at least three tables and that condition certainly isn't met in the first example.

Each stage has entry conditions. "Having at least three table references" is one of the entry conditions we talk about when giving simple examples, but it's not the only one.

Generally, only basic joins and unions are allowed for entry to search 0; scalar subqueries, semi joins etc. prevent entry to search 0. This stage really is for the very common OLTP-type query shapes. The rules necessary to explore the less common things just aren't enabled. Your example query has a scalar subquery, so it fails entry.

It also depends how you count table references. I've never looked deeply into this with functions, but it's possible the logic is counting the Table Valued Functions as well as the table variables they produce. It could even be counting the table reference inside the function itself - I'm not sure; though I do know that functions are just hard work all round.

The bug with GenGbApplySimple is ugly. This plan shape was always a possibility, but rejected for cost reasons until the change to 100-rows assumed table variable cardinality came in. It is possible to force the problematic plan shape on the pre-2014 CE with a USE PLAN hint, for example.

You're correct about the new Connect item being the same issue reported previously.

To provide an example, the following query qualifies for search 0:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY U.c1) 
FROM 
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;

Making a small change to include a scalar subquery means it goes straight to search 1:

DECLARE @T AS table (c1 integer NULL);

SELECT U.c1, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -- Changed!
FROM 
(
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
    UNION
    SELECT c1 FROM @T AS T
) AS U;

StackExchange Database Administrators Q#114853, answer score: 7