Mysql

LEFT JOIN 子查詢在主查詢中存在 EXISTS 子句時失敗

  • March 17, 2021

我有這個查詢:

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
          ;
    
    • 結合上述任何或所有重寫和測試執行計劃和結果,並將未能產生正確結果的那些(查詢和計劃)添加到報告中。

建議在最新的子版本中進行測試,因為這可能是一個已修復的錯誤。

引用自:https://dba.stackexchange.com/questions/287133