[MDEV-25725] Suddenly Queryplan skip LEFT JOINS and fail to retrieve full results on certain dataset Created: 2021-05-18  Updated: 2021-08-02  Resolved: 2021-08-02

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.3.29
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Massimiliano Cuttini Assignee: Unassigned
Resolution: Incomplete Votes: 1
Labels: need_feedback, regression
Environment:

CentOS

Issue Links:
Relates
relates to MDEV-25714 Join using derived with aggregation r... Closed

 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.

 Comments   
Comment by Massimiliano Cuttini [ 2021-05-18 ]

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

Comment by Alice Sherepa [ 2021-05-19 ]

I'm very sorry about the trouble you experienced. Could you please add a test case to demonstrate the bug?

Comment by Massimiliano Cuttini [ 2021-05-19 ]

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?

Comment by Massimiliano Cuttini [ 2021-05-19 ]

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.

Comment by Calin [ 2021-05-31 ]

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.

Comment by Elena Stepanova [ 2021-06-28 ]

maxcuttins, clns,

Have you had a chance to upgrade to 10.3.30 and check if the problem goes away?

Generated at Thu Feb 08 09:39:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.