We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
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 ?