Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.3.29
-
CentOS
Description
Since 10.3.29-1, MariaDb is not able to complete correctly LEFT JOIN in SUBQUERY that have multiple ON clause columns coming from different tables.
The only way to make it works is to substitute all INNER JOIN with the "STRAIGHT_JOIN" on all tables that have the columns needed by "ON clause" of the LEFT JOIN , this will fix results but on large datasets will take likely 30 - 40 seconds instead of few microseconds.
So the only way it works back again is to downgrade to 10.3.28.
I have to said that this is really disappointing: this is likely the 3rd critical bug in less than 1 year.
APPs suddenly stop to works and it tooks hours to understand that the issue is not the query itself but instead the latest MariaDb's update.
This is becoming really too much frequent.
Attachments
Issue Links
- relates to
-
-
- Closed
-
Activity
I'm very sorry about the trouble you experienced. Could you please add a test case to demonstrate the bug?
Hi @alice,
I would like to, but i'm new to create a test case.
Is there a tool to do so?
The query is really complex.
It included 21 tables, 6 left joins, 2 nested suquery that flatten GROUP BY results.
The query itself it builded by a compiler class i've done, that split the subquerys in discrete query.
Do you have a sort of tool that I can use to export a report?
Or can you give me some advides to create it?
I think my Issue is related to:
MDEV-25714
In this case too the issue is due to wrong results in nested suquery that aggregate results.
Having the exact same problem, on MariaDB 10.3.29.
When using a LEFT JOIN SUBQUERY with a GROUP BY clause, MariaDB is unable to correctly apply multiple ON clause conditions coming from different tables.
EXPLAIN states that it is using a LATERAL DERIVED, which is likely the issue, since if `split_materialized=off` is set, the plan changes to DERIVED (without LATERAL) and the query returns the correct results (however much slower).
Will keep an eye on https://jira.mariadb.org/browse/MDEV-25714 and hope for a release soon, since it's IN REVIEW.
Have you had a chance to upgrade to 10.3.30 and check if the problem goes away?
I decided to exclude mariadb* packages from yum updates.
So it'll not roll back to 10.3.29
for whom that need to downgrade use this:
yum downgrade MariaDB-server MariaDB-common MariaDB-shared MariaDB-client MariaDB-compat MariaDB-devel