4

In preparation of my previous Constant Scan question I was experimenting with VALUES in various ways and came across the thing regarding joining of VALUES that looks odd to me.

Setup is simple

CREATE TABLE #data ([Id] int);
INSERT INTO #data VALUES (101), (103);

Then there is a query

DECLARE @id1 int = 101, @id2 int = 102;

SELECT *
FROM (VALUES (@id1), (@id2)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

There is nothing special about it. It works and produces its result, if you run it. Here is its execution plan

Constant Scan joining plan

Removing a row from VALUES however

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL HASH JOIN #data d ON d.[Id] = p.[Id];

causes optimizer to fail

Msg 8622, Level 16, State 1, Line 1
Query processor could not produce a query plan ...

Why? Is there a way (besides putting parameter into temporary table) to make it work using hash algorithm?

Note: This is of no real appliance and serve purpose of studying optimizer behavior and capabilities.


The example above was tested on

Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

6

Why?

Saying it in short. Because of HASH hint shots to optimizer's leg and optimizer itself shots to the other. Being shot to both optimizer can't cross the finish line.


To better illustrate what is going on, let's rewrite problematic query to join two VALUES and to use merge algorithm instead

DECLARE @id1 int = 101, @id3 int = 103;

SELECT *
FROM (VALUES (@id1)) p([Id])
    FULL MERGE JOIN (VALUES (@id1), (@id3)) d([Id]) ON d.[Id] = p.[Id];

Execution plan of this query is simple. There is the Merge Join operator with two Constant Scan inputs.

Execution plan

These two Constant Scans are different to optimizer though.

Execution plan properties

The one representing single-row input has column name prefixed with Expr, whereas the other representing multiple-rows input has column name prefixed with Union. Data from multiple-rows Constant Scan is accessed in the Merge Join predicates a kind of "by reference" ([Union1001]), whereas single-row Constant Scan data is accessed a kind of "by value" (see that @id1 is substituted instead of [Expr1000]).

This "by reference"→"by value" substitution happens at simplification stage, as can be derived (using trace flag 8606) from comparison of input

*** Input Tree: ***
    ...
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: Expr1000
    ...

and simplified

*** Simplified Tree: ***
    LogOp_FullOuterJoin
        ...
        ScaOp_Comp x_cmpEq
            ScaOp_Identifier COL: Union1001
            ScaOp_Identifier COL: @id1

trees.

One may did noticed before, that Merge Join node has only residual predicate and no join equality predicate. This is because of join equality predicate has been eliminated at simplification stage. The [Union1001] = @id1 is equality predicate, but it can not serve as join equality predicate. To be such it has to reference columns from both inputs, but @id1 is variable and not a column.

So, being equijoin ON d.[Id] = p.[Id] originally the query transformed to non-equijoin (which is special case, and because of that, by the way, optimizer did not introduced sorting below Merge Join for the non-sorted Constant Scan inputs). Fortunately, in case of merge algorithm optimizer has such non-equijoin alternative.

In case of using hash algorithm non-equijoin alternative does not exists, and so, join equality predicate elimination at simplification stage causes optimizer to fail later on.


Is there a way (besides putting parameter into temporary table) to make it work using hash algorithm?

Disabling all transformation rules applied at simplification stage with

OPTION (
    QUERYRULEOFF SimplifyFOJN,
    QUERYRULEOFF JoinPredNorm,
    QUERYRULEOFF SimplifyMultiColumnJoinPred,
    QUERYRULEOFF ReducePrjExpr,
    QUERYRULEOFF PrjOnConstTbl
    );

did not prevented "by value" substitution. Perhaps it happens as a part of context properties manipulations performed at simplification stage rather than as a part of some simplification rule application.

I don't know if there is a combination of trace flags driving this optimization or if there is a global trace flag responsible for it, but trying every single trace flag at query and session levels did not prevented substitution too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.