Sample Header Ad - 728x90

Microsoft SQL Server 2014 Nested From Query in Cross-Apply

10 votes
2 answers
686 views
When selecting from a nested query in an OUTER APPLY statement the nested query seems to be evaluated only once in certain circumstances. Bug reported to Azure Feedback Forum: https://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s 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 (imho 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 (imho > 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 |
Testcase 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 |
Asked by Phist0ne (103 rep)
Jan 13, 2020, 04:44 PM
Last activity: Jun 18, 2021, 10:05 AM