具有計算列的視圖上的外部聯接執行缺失（“外部”）記錄的計算

我在 MySQL 中有一個視圖，它有一些計算欄位——COALESCE等等。如果我在該視圖上進行左外連接（因此該視圖中的記錄可能會失去），那麼仍然會為失去的記錄計算計算欄位，而不是顯示為NULL.

例子

我已經在 MySQL 版本 5.5.40-36.1-log 和 5.5.53-38.5（因為這是我手頭的）以及 SQL Fiddle ‡版本 5.5 和 5.6 上測試了以下範例。

設置

-- A simple view
CREATE OR REPLACE VIEW foo
AS
     SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
     SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
  f.x,
  b.y,
  SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

詢問

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

預期結果

我希望得到以下結果，我們看到一行有記錄baz，兩行沒有。

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

實際結果

但相反，我得到以下內容，確實有一行記錄來自baz，但有兩行baz除了計算列之外沒有記錄，計算列是針對所有三行計算的！

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

如此z計算，即使 where不返回任何記錄（如andbaz的 NULL 所證明的那樣）。x``y

我的實際案例涉及視圖和視圖所基於的表之間的連接。我在範例中複製了這一點，僅使用視圖。我還測試了沒有“自加入”的情況；這沒什麼區別。

解決方法

對我有用的是隱藏baz在子選擇中，就像這樣。

SELECT *
FROM foo f
LEFT JOIN (
  SELECT *
  FROM baz
) b ON f.x = b.y;

這產生了預期的結果：

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

這是預期的行為嗎？為什麼？

---

‡：昨天為我工作，今天無法工作。

如果兩個查詢：

SELECT * 
FROM foo f 
 LEFT JOIN baz b 
 ON f.x = b.y; 

和

SELECT * 
FROM foo f 
 LEFT JOIN (SELECT * FROM baz) b 
 ON f.x = b.y; 

返回不同的結果，那麼這是一個錯誤，表和視圖定義無關緊要。那不應該發生。

在您發布錯誤報告之前，請檢查您在版本 (5.5.54) 中的視圖和查詢。您的 5.5.40 是 14 個次要版本，比最新的 5.5 版本大約早 2.5 年。

如果錯誤仍然存在，則使用此範例向 MySQL 送出錯誤報告。已經報告了一個類似的錯誤：使用內聯 SELECT 與 VIEW 進行左連接時返回不一致的結果，所以如果你報告我建議你在那裡添加你的範例。

MySQL 開發人員已經確認了這一點並提出了一些解決方法（直到它被修復）。您可以嘗試使用以下建議創建視圖ALGORITHM = TEMPTABLE：

有一些解決方法：

在所有版本中，如果使用" "創建視圖，使用視圖的查詢將給出正確的結果CREATE ALGORITHM=TEMPTABLE VIEW ...

對於 5.7/8.0，兩個查詢都將通過設置 optimizer_switch=‘derived_merge=off’ 給出正確的結果

在 8.0 中，NO_MERGE 提示可用於在兩種情況下獲得正確的行為： SELECT /*+ NO_MERGE(t2) */ …

---

話雖如此，查詢比需要的更複雜。您可以使用更簡單的東西（儘管我不喜歡列名稱相同的結果）：

CREATE VIEW baz2
AS
SELECT
  f2.x AS x
  f1.x AS x,
  CASE WHEN f1.x IS NOT NULL THEN f2.x END AS y,
  SIGN(f1.x) AS z
FROM foo f2                   -- changed:   f2 LEFT JOIN f1
 LEFT JOIN bar f1
 ON f1.x = 2 * f2.x;

和

SELECT * FROM baz2 ;

引用自：https://dba.stackexchange.com/questions/164285