LEFT JOIN 子查询在主查询中存在 EXISTS 子句时失败

这看起来像一个错误。请向 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
          ;
    

  • 结合上述任何或所有重写和测试执行计划和结果，并将未能产生正确结果的那些（查询和计划）添加到报告中。

建议在最新的子版本中进行测试，因为这可能是一个已修复的错误。

试试这个 C 的重新表述：

-- Query C 
SELECT  c.id contact_id, 
        ( SELECT COUNT(*)
            FROM mailing_event_queue eq
            JOIN mailing_job AS j  ON eq.job_id = j.id
            WHERE  eq.contact_id = c.id 
              AND  j.end_date > NOW() - INTERVAL 3 MONTH
        ) AS sent
    FROM contact c
    WHERE  /* ➋ various conditions on table 'c' */
     ... (as before)