LEFT JOIN 子查詢在主查詢中存在 EXISTS 子句時失敗
我有這個查詢:
SELECT c.id contact_id, engagement.sent FROM contact c LEFT JOIN ( /* ➊ Inner query */ SELECT eq.contact_id contact_id, COUNT(*) sent FROM mailing_job j INNER JOIN mailing_event_queue eq ON eq.job_id = j.id WHERE j.end_date > NOW() - INTERVAL 3 MONTH GROUP BY eq.contact_id ) engagement ON engagement.contact_id = c.id WHERE /* ➋ various conditions on table 'c' */ do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0 AND contact_type = 'Individual' /* ➌ */ AND EXISTS ( SELECT gc.contact_id FROM group_contact gc WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12) ) /* ➍ AND c.id=1 */ ORDER BY c.id
- 內部查詢 ➊ 自行執行時會產生結果,例如sent: 15 for contact 1。這是正確的、預期的數據。
- 但是,執行整個查詢會為整個發送的列提供**NULL!這讓我很煩惱。
- 消除 ➋ 處的 WHERE 子句,或消除 ➌ 處的 EXISTS,或包括註釋掉的 ➍ 子句會導致其執行並返回sent: 15用於測試聯繫人。
我在 MariaDB 10.3 上。
編輯
希望增加清晰度。從根本上說,這很奇怪——我認為——而且可能是 MariaDB 中的一個錯誤,儘管我希望不會!
內部查詢 ➊ 返回行,例如
contact_id sent 1 15 ...
主查詢還使用 ID 獲取聯繫人
1
。現在在我所有的歲月裡,當我離開加入一個主鍵(即
c.id
)上的表時,子查詢中的記錄匹配,它從來沒有改變它的值。這些值隨著其他 WHERE 子句的變化而變化,但在某種程度上仍然意味著外部查詢會生成匹配的聯繫人 ID,我認為這很奇怪。
我真的希望我做了一些愚蠢的事情,但我看不到它。
這看起來像一個錯誤。請向 MariaDB 發送錯誤報告。一些細節:
首先,確保它是一個錯誤。
查詢 A 生成單行(假設
contact (id)
具有唯一(或主鍵)約束:-- Query A SELECT c.id contact_id FROM contact c WHERE /* ➋ various conditions on table 'c' */ c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0 AND c.contact_type = 'Individual' /* ➌ */ AND EXISTS ( SELECT gc.contact_id FROM group_contact gc WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12) ) /* ➍ */ AND c.id=1 ORDER BY c.id ;
查詢 B 產生一行(值
15
在 中sent
)-- Query B /* ➊ Inner query */ SELECT eq.contact_id contact_id, COUNT(*) sent FROM mailing_job j INNER JOIN mailing_event_queue eq ON eq.job_id = j.id WHERE j.end_date > NOW() - INTERVAL 3 MONTH AND eq.contact_id = 1 GROUP BY eq.contact_id ;
查詢 C 生成單行但具有值
NULL
(或與查詢 B 中的值不同的任何內容15
)。-- Query C SELECT c.id contact_id, engagement.sent FROM contact c LEFT JOIN ( /* ➊ Inner query */ SELECT eq.contact_id contact_id, COUNT(*) sent FROM mailing_job j INNER JOIN mailing_event_queue eq ON eq.job_id = j.id WHERE j.end_date > NOW() - INTERVAL 3 MONTH GROUP BY eq.contact_id ) engagement ON engagement.contact_id = c.id WHERE /* ➋ various conditions on table 'c' */ c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0 AND c.contact_type = 'Individual' /* ➌ */ AND EXISTS ( SELECT gc.contact_id FROM group_contact gc WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12) ) /* ➍ */ AND c.id=1 ORDER BY c.id ;
如果確實發生了上述情況,則幾乎可以肯定是表/索引中的錯誤或損壞的數據。
其他要檢查/添加到報告中的事項:
所有查詢的執行計劃(使用
EXPLAIN SELECT ...
)完成
CREATE TABLE
所有涉及的表,包括索引。你可以使用SHOW CREATE TABLE tablename ;
它。您的 MariadDB 的確切版本(嘗試
select version();
)。如果可能,請嘗試刪除並重新創建涉及的表(不是表!)上的索引,然後再次測試。
如果您沒有執行最新的 10.3,請盡可能嘗試升級到最新的 10.3,然後再次測試。
如果您有更新主要版本(10.4 或 10.5)的可用伺服器,請嘗試使用相同數據重新創建表並再次測試。
嘗試刪除條件(如您所做的那樣)或重寫 C 並測試它是否產生不同的計劃以及正確或錯誤的結果,例如:
- 刪除其中的每一項:
do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0 AND contact_type = 'Individual'
- 去除那個
ORDER BY
- 刪除子查詢 3 和/或 4。
- 在 b 子查詢中添加條件:
LEFT JOIN ( /* ➊ Inner query */ SELECT eq.contact_id contact_id, COUNT(*) sent FROM mailing_job j INNER JOIN mailing_event_queue eq ON eq.job_id = j.id WHERE j.end_date > NOW() - INTERVAL 3 MONTH AND eq.contact_id = 1 GROUP BY eq.contact_id ) engagement ON engagement.contact_id = c.id
- 將整個查詢重寫為:
-- Query Z SELECT z.contact_id, engagement.sent FROM ( SELECT c.id contact_id FROM contact c WHERE /* ➋ various conditions on table 'c' */ c.do_not_email = 0 AND c.is_opt_out = 0 AND c.is_deceased = 0 AND c.is_deleted = 0 AND c.contact_type = 'Individual' /* ➌ */ AND EXISTS ( SELECT gc.contact_id FROM group_contact gc WHERE gc.contact_id = c.id AND gc.group_id IN (30,386,14,6,214,5,88,361,334,18,9,17,240,7,13,10,292,291,290,12) ) /* ➍ */ AND c.id=1 ) AS z LEFT JOIN ( /* ➊ Inner query */ SELECT eq.contact_id contact_id, COUNT(*) sent FROM mailing_job j INNER JOIN mailing_event_queue eq ON eq.job_id = j.id WHERE j.end_date > NOW() - INTERVAL 3 MONTH GROUP BY eq.contact_id ) engagement ON engagement.contact_id = z.contact_id ;
- 結合上述任何或所有重寫和測試執行計劃和結果,並將未能產生正確結果的那些(查詢和計劃)添加到報告中。
建議在最新的子版本中進行測試,因為這可能是一個已修復的錯誤。