主页 / user-198821

Phist0ne's questions

Martin Hope
Phist0ne
Asked: 2020-01-14 08:44:27 +0800 CST
  • 10

从 OUTER APPLY 语句中的嵌套查询中进行选择时，嵌套查询似乎在某些情况下只被评估一次。

向 Azure 反馈论坛报告的错误：https ://feedback.azure.com/forums/908035-sql-server/suggestions/39428632-microsoft-sql-server-2014-incorrect-result-when-s

这是预期的行为，还是我在文档中遗漏了什么，或者这是 SQL Server 中的错误？

此外，是否有可能强制评估每一行的嵌套查询？

测试用例 1

评估 VALUES 中每一行的嵌套 FROM 查询（恕我直言预期行为）

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

结果：

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

测试用例 2

它还评估 VALUES 中每一行的嵌套 FROM 查询（恕我直言预期行为）

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;

结果：

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

测试用例 3

只计算一次嵌套的 FROM 查询

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;

结果：

| v | v2|
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
sql-server union