Description:
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 :
https://ibb.co/ZJMmCYQ
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.
How to repeat:
Use the database from the following dump :
https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416
Run the query :
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
See the result is 0 rows (after restart of MySQL) or 1 row (after running the query below once).
Run the query :
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
See that 226 rows are returned.
