Mysql – LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query

mariadbmariadb-10.3MySQLsubquery

I have this query:

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

The inner query ➊ when run on its own yields results, e.g. sent: 15 for contact 1. This is the correct, expected data.
However, running the whole query gives NULL for the whole sent column! Which is vexing me.
Eliminating the WHERE clauses at ➋, or eliminating the EXISTS at ➌, or including the commented out ➍ clause causes it to behave and return sent: 15 for the test contact.

I'm on MariaDB 10.3.

EDIT

Hoping to add clarity. Fundamentally this is weird -I think- and possibly a bug in MariaDB, though I hope not!

The Inner query ➊ returns rows, e.g.

contact_id  sent
1           15
...

The main query also picks up contact with ID 1.

Now in all my years, when I've left joined a table on a primary key (i.e. c.id) where a record in the subquery matches, it's never changed its values.

The fact that those values come and go with changing other WHERE clauses, but in ways that still mean the outer query generates the matching contact ID, I think, is weird.

I'm really hoping I've done something stupid, but I can't see it.

Best Answer

This looks like a bug. Please send a bug report to MariaDB. Some details:

First, make sure it is a bug.

Query A produces a single row (assuming that contact (id) has a unique (or primary key) constraint:

-- 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 ;

Query B produces one row (with value 15 in 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 ;

Query C produces a single row but with value NULL (or anything different than the 15 in Query B).

-- 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 ;

If the above do happen, it's almost surely either a bug or corrupted data in tables/indexes.

Other things to check / add to the report:

Execution plans for all queries (use EXPLAIN SELECT ...)
Complete CREATE TABLE for all tables involved, including indexes. You can use SHOW CREATE TABLE tablename ; for that.
Your exact version of MariadDB (try select version(); ).
Try dropping and recreating the indexes on the tables involved if possible (not the tables!) and then test again.
If you are not running the latest 10.3, try upgrading to the latest 10.3 if possible, and test again.
If you have available servers with more recent major versions (10.4 or 10.5), try recreating the tables with the same data and test again.

Try to remove conditions (as you did) or rewrite C and test if it produces different plan and correct or wrong results, eg.:

remove each one of these:

  do_not_email = 0 AND is_opt_out = 0 AND is_deceased = 0 AND is_deleted = 0
  AND contact_type = 'Individual'

remove the ORDER BY
remove subqueries 3 and/or 4.

add a condition in b subquery:

  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

rewrite the whole query as:

  -- 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
      ;

combine any or all of the above rewrites and test execution plans and results and add to the report the ones (queries and plans) that fail to produce correct results.

Testing in the latest subversions is recommended, as this may be a bug that has been fixed.

Related Solutions

Mysql – Optimizing multi-table left joins

PLEASE use SHOW CREATE TABLE; DESCRIBE is not descriptive enough.

You seem to be working with IPv4 IP addresses. IPv6 is upon us!

Your query inherently must do a full table scan in order to get the "total" upon which you do the ORDER BY. Since there are a million rows, it will take time.

count('*')

The quotes are not needed.

The two SELECTs are likely to be identical; order across JOIN does not matter. It does matter across LEFT JOIN. (INNER and OUTER add nothing.)

Suggestion...

First, see if this gives the correct information (albeit incomplet info):

SELECT  e.sid, e.cid, COUNT(*) AS total
FROM  event AS e
JOIN  iphdr as h ON e.sid = h.sid
                AND  e.cid = h.cid
WHERE  e.is_deleted = 0
GROUP BY  i.ip_src, i.ip_dst
ORDER BY  total DESC
LIMIT  5;

If so, then use that as a subquery, a la FROM (that-select) and LEFT JOIN to the other two tables to get the rest of the info.

The trick... The subquery is doing less work since it is touching 2 tables (vs 4). The LEFT JOINs only have to do 5 probes each, not a million.

Mysql – Why are these two queries having such different executions

LEFT is killing performance; remove it unless you have a good reason for keeping it. To elaborate...

((but first... The purpose for the LEFT was missing from the original query; this answer assumes the LEFT was not necessary.))

LEFT JOIN says that you want data from the right table, whether or not there was a match with the left table.

JOIN says to display only the rows that match (via ON) both tables.

(OUTER is optional and adds no semantics.)

If you are running a version before 5.6, the derived table (subquery in LEFT JOIN) will have no index, so it must be scanned repeatedly. This is a big reason to get rid of LEFT.

Without the LEFT, the Optimizer is likely to evaluate the subquery once, then efficiently JOIN to reporter to finish the query.

For JOIN, the inner query needs this composite (and covering) index INDEX(messagetypeenum, createdOn, reporterid).

There is another technique (I think)... Get rid of the inner SELECT, simply JOIN (or LEFT JOIN) to the table:

select  r.*
    from  reporter r
    left join  nubamessage m  ON m.reporterid = r.id
      AND  m.messagetypeenum = 7
      and  m.createdOn >= '2016-06-18 00:00:00'
      and  m.createdOn <= '2016-06-18 09:30:00'
    WHERE  r.enabled = 1
      AND  m.reporterid is null;

In this case, it may needs INDEX(reporterid, messagetypeenum, createdOn).

Yet another variant would use EXISTS and, I think, provides the equivalent of the LEFT

select  r.*
    from  reporter r
    WHERE  r.enabled=1
      AND NOT EXISTS 
    (
        SELECT  *
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
              AND  m.reporterid=r.id 
    )

I can't predict which variant will be fastest. It partially depends on how "many" in the many:1 mapping of m.reporterid : r.id.

EDIT

Best Answer

Related Solutions

Mysql – Optimizing multi-table left joins

Mysql – Why are these two queries having such different executions

Related Question