1

We have some really strange behaviour on MySQL (8.0.29) that we can't explain.

We have 2 tables + 1 link table in-between, illustrated by this schema : enter image description here

We run a query whereby we need to fetch the id and organization_id from table1 for all the records that have a linked record in table2. However, if there are 2 linked records in table2, we still only want a single record from table1.

We use Doctrine (PHP) integrated in a complex application so the query is generated for us. The resulting query is :

SELECT
  table1.organization_id,
  table1.id AS id_0
FROM
  table1
  LEFT JOIN table2 ON (
    EXISTS (
      SELECT
        1
      FROM
        link1
      WHERE
        link1.table2_id = table2.uuid
        AND link1.table1_id IN (table1.id)
    )
  )
WHERE
  table1.location_id = 605
  AND table1.status IN ('confirmed')
  and table1.organization_id=1
ORDER BY table1.id DESC
LIMIT
  1000

This query is supposed to return 260 rows, but it returns only 1. Unless we just restarted MySQL, then it returns 0 and will continue to return 0 until we remove either the LIMIT clause or the ORDER BY clause.

On MariaDB it gets worse : the query just uses CPU and we killed it after a few minutes.

If you want to give it a go, the data dump is at https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416

Tried it on MySQL 8.0.29 (currently the most recent version).

Anyone have any idea what's going on here ?

10
  • I have no idea what is causing the strange issue, but I'll point out that the JOIN to table2 in this query has no purpose. If you remove the JOIN to table2, but keep the ORDER BY and LIMIT, it returns 226 rows. Also if you change the weird EXISTS format of the join to just use LEFT JOIN link1 LEFT JOIN table2, it also returns 226 rows. I think your Doctrine query generator is your problem. May 16 at 17:18
  • Hi Bill, thanks for your reply. It looks like I stripped the query a little bit too much. In the select we also need table2.uuid, so we do need to join on table2 as well.
    – wimg
    May 16 at 17:55
  • Just to be clear : Doctrine is indeed creating this query, but it appears this not an exceptional query. If it is nonsensical, we would expect MySQL to refuse to run it, but in this case it is behaving in an irrational way, sometimes producting 0 or 1 results, sometimes 226.
    – wimg
    May 16 at 18:04
  • I am assuming it is a bug in MySQL. It clearly doesn't handle this type of nonsensical query well, even though the query syntax is not an error. I suggest you need to fix your Doctrine query generator, or stop using it. May 16 at 18:21
  • I'm afraid not using Doctrine anymore is not an option, since it's used throughout the entire project. I'll open a big ticket in MySQL...
    – wimg
    May 18 at 8:55

1 Answer 1

0

Indexes needed:

table1:  INDEX(location_id, organization_id, status, id)
link1:  PRIMARY KEY(table2_id, table1_id)
link1:  INDEX      (table1_id, table2_id)

LEFT JOIN ... ON EXISTS (...) is a very strange pattern. "the query is generated for us" == Yuck! Please rewrite it. Try changing

    FROM  table1
    LEFT JOIN  table2  ON ( EXISTS 
            (
                SELECT  1
                    FROM  link1
                    WHERE  link1.table2_id = table2.uuid
                      AND  link1.table1_id IN (table1.id) ) 
                          )

to

    FROM  table1
    JOIN  link1  ON link1.table1_id = table1.id
    JOIN  table2 ON table2.uuid = link1.table2_id

(if that is what you meant.)

For further discussion, please provide EXPLAIN SELECT on each server. And add it to the Bug report(s).

A discussion of the indexes for a Many-to-many table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.