sql-server add tag
Phist0ne (imported from SE)
When selecting from a nested query in an `OUTER APPLY` statement the nested query seems to be evaluated only once in certain circumstances.

Is this the expected behavior or am I missing something in the documentation or is this a bug in SQL Server?

Also, is there any possibility to force evaluation of the nested query for every row?

## Test Case 1

Evaluates nested `FROM` query for every row in `VALUES` (expected behaviour)

```
SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
		FROM (
            SELECT 
                15 AS id,
                v AS inner_v2
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply
```

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

## Test Case 2

It also evaluates nested `FROM` query for every row in `VALUES` (expected behaviour)

```
SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT 
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp2.v AS inner_v2
            FROM
                (VALUES (1337, 0)) AS TestCaseTemp2(id, v)
            WHERE TestCaseTemp2.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;
```

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |

## Test Case 3

Evaluates nested `FROM` query only once

```
CREATE TABLE TestCaseTemp
(
    id int,
    v int
);
INSERT INTO TestCaseTemp VALUES (1337, 0);

SELECT
    v,
    v2
FROM
    (VALUES (1), (2), (3), (4)) AS inner_query(v)
    OUTER APPLY (
        SELECT
            MAX(inner_v2) AS v2
        FROM (
            SELECT 
                15 AS id,
                v AS inner_v2
            UNION ALL
            SELECT
                id AS id,
                TestCaseTemp.v AS inner_v2
            FROM
                TestCaseTemp
            WHERE TestCaseTemp.v != 0
        ) AS outer_query
        GROUP BY id
    ) AS outer_apply;

DROP TABLE TestCaseTemp;
```

Result:

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
Top Answer
Paul White (imported from SE)
This is a bug in the way SQL Server decides whether a rebind is required with certain plan shapes. It has been in the product since SQL Server 2005.

### Minimal repro

This uses a trace flag to force a spool:

```
CREATE TABLE #T (v integer NOT NULL); 
INSERT #T VALUES (1), (2), (3), (4);

SELECT
    #T.v,
    A.*
FROM #T
CROSS APPLY 
(
    SELECT #T.v
    UNION ALL
    SELECT #T.v WHERE @@SPID < 0
) AS A
OPTION (QUERYTRACEON 8691);
```

Plan:

[![plan][1]][1]

Wrong results:

[![wrong results][2]][2]

### Expanded repro

This does not require a trace flag to produce a spool:

```
DROP TABLE #T;
CREATE TABLE #T (v integer NOT NULL);

INSERT #T 
    (v)
VALUES 
    (1), (1), (1), (1),
    (1), (2), (3), (4);

SELECT 
    #T.v,
    A.v
FROM #T
CROSS APPLY 
(
    SELECT v = MAX(U.v) 
    FROM 
    (
        SELECT #T.v
        UNION ALL
        SELECT #T.v
        WHERE @@SPID < 0
    ) AS U
    GROUP BY U.v % 2
) AS A
ORDER BY #T.v
OPTION (USE HINT ('FORCE_DEFAULT_CARDINALITY_ESTIMATION'), HASH GROUP);
```

[![plan][3]][3]

[![wrong results][4]][4]

### Bug status

Fixed for Azure SQL Database and SQL Server from 2019 CU9 and 2016 SP2 CU16.

[KB5000649 - FIX: Wrong result due to undetected correlated parameter reference in CXteConcat in SQL Server 2016 and 2019][5]


  [1]: https://i.stack.imgur.com/FiiRl.png
  [2]: https://i.stack.imgur.com/uwCr8.png
  [3]: https://i.stack.imgur.com/RyIah.png
  [4]: https://i.stack.imgur.com/nx4Qj.png
  [5]: https://support.microsoft.com/en-us/office/kb5000649-fix-wrong-result-due-to-undetected-correlated-parameter-reference-in-cxteconcat-in-sql-server-2016-and-2019-ee29cb64-322d-477b-b146-7469d08b61a7
Answer #2
Martin Smith (imported from SE)
> is this a bug in SQL Server?

Yes, certainly, the `1` that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White [here](https://topanswers.xyz/databases?q=573#a618).

I executed your final query in dbfiddle (SQL Server 2019) and pasted the plan here https://www.brentozar.com/pastetheplan/?id=Sy4sBB5lI  It looks like the sort executes 4 times (once for each outer row) but for some reason it rewinds rather than rebinds so doesn't call the child operators of the sort more than once. This is a bug as the reference to the correlated parameter of the outer join (`Union1004`) should cause a rebind when this has changed value. As a result the reference to `Union1004` in Node 5 of the plan is never re-evaluated. 

I see you have now reported it here https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

> Is there any possibility to force evaluation of the nested query for every row?

Adding the query hint `OPTION (MERGE UNION)` works for your example, I don't know if this will necessarily be sufficient to avoid the bug in all cases but from the linked Paul White answer it appears it should work. In the case of your example it works as the sort is [pushed down lower][1] in the plan so it only rewinds the `TestCaseTemp` rows, not the entire unioned result. You could also add appropriate indexing to remove the sort entirely.


  [1]: https://www.brentozar.com/pastetheplan/?id=HJ--KIcxL

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.