[MDEV-30015] full join vs left join results in a table scan vs. 1 row access Created: 2022-11-15  Updated: 2022-11-21  Resolved: 2022-11-21

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Marc Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

In version 10.5.17

The reordering algorithm looks very strange and the results are not the best.

explain extended SELECT o.Wache,oi.OrganisationID,sli.showItem
 
FROM
 
	rdShopOrder o JOIN
 
	rdShopOrderItems oi ON oi.ID=o.ID AND oi.status="unbearbeitet" JOIN
 
	rdWache w ON o.OrganisationID=w.OrganisationID AND w.ID=o.Wache JOIN
 
	rdShopItems i ON oi.ItemID=i.ID  JOIN
 
	rdMPGAdresse a ON i.OrganisationID=a.OrganisationID AND FIND_IN_SET("Lieferant",a.rolle) AND FIND_IN_SET(a.ID, i.LieferantIDs)  AND FIND_IN_SET("Shop",a.showTool)  LEFT JOIN
 
	rdShopLieferantItems sli ON (sli.OrganisationID,sli.LieferantID,sli.ItemID,sli.showItem)=(i.OrganisationID,a.ID,i.ID,1)
 
WHERE
 
 
 
 
 
	(
 
		a.autoCommit="immer" OR
 
		w.Shop_autoCommit="immer" OR
 
		(a.autoCommit="nur ausgewählte Dienststellen" AND w.Shop_autoCommit="nur ausgewählte Lieferanten")
 
	)
 
 GROUP BY w.ID

The query plan is as follows:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE oi ref PRIMARY,status_OrganisationID status_OrganisationID 1 const 1671 100.00 Using index condition; Using temporary; Using filesort
1 SIMPLE i eq_ref PRIMARY,OrganisationID,Name PRIMARY 4 qmsystems.oi.ItemID 1 100.00  
1 SIMPLE o eq_ref PRIMARY,Wache,OrganisationID_Datum,OrganisationID_BuHa_Status PRIMARY 4 qmsystems.oi.ID 1 100.00  
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 qmsystems.o.Wache 1 100.00 Using index condition; Using where
1 SIMPLE a ref OrganisationID OrganisationID 2 qmsystems.i.OrganisationID 65 100.00 Using index condition; Using where
1 SIMPLE sli eq_ref PRIMARY,LieferantID_ItemID,OrganisationID_LieferantID_ItemID_showItem PRIMARY 6 qmsystems.a.ID,qmsystems.oi.ItemID 1 100.00 Using where

if you change the last LEFT JOIN as intended to a FULL JOIN, the result is:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE sli index PRIMARY,LieferantID_ItemID,OrganisationID_LieferantID_ItemID_showItem OrganisationID_LieferantID_ItemID_showItem 10 NULL 0 0.00 Using where; Using index; Using temporary; Using filesort
1 SIMPLE w ALL PRIMARY,Ressource,Shop,Strassensperrung NULL NULL NULL 1294 100.00 Using join buffer (flat, BNL join)
1 SIMPLE oi ref PRIMARY,status_OrganisationID status_OrganisationID 1 const 1671 100.00 Using index condition; Using where
1 SIMPLE a ref PRIMARY,OrganisationID,DROP_INDEX_ID DROP_INDEX_ID 3 qmsystems.sli.LieferantID 1 100.00 Using index condition; Using where
1 SIMPLE i eq_ref PRIMARY,OrganisationID,Name PRIMARY 4 qmsystems.sli.ItemID 1 100.00 Using where
1 SIMPLE o ref PRIMARY,Wache,OrganisationID_Datum,OrganisationID_BuHa_Status Wache 3 qmsystems.w.ID 186 100.00 Using index condition; Using where

For me (looking at the tables), it is not obvious, how mariadb manages to move "sli" to the front and reorder the query - and has the result of "0 rows" - which should be filtered=100.00 not 0 - and as a result, this query should be faster than the left join - but the oposite is the case. I had to manually kill this query; the left join query runs in 0.084s.

I assume the probleme here is the result of 0 rows in the first place. The total result is 0 rows.

 Comments   
Comment by Marc [ 2022-11-15 ]

reanalyzing all tables for the whole database seems to somehow "fix" the problem, as the full join is appended:

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE oi ref PRIMARY,status_OrganisationID status_OrganisationID 1 const 1671 100.00 Using index condition; Using temporary; Using filesort
1 SIMPLE i eq_ref PRIMARY,OrganisationID,Name PRIMARY 4 qmsystems.oi.ItemID 1 100.00  
1 SIMPLE o eq_ref PRIMARY,Wache,OrganisationID_Datum,OrganisationID_BuHa_Status PRIMARY 4 qmsystems.oi.ID 1 100.00  
1 SIMPLE w eq_ref PRIMARY,Ressource,Shop,Strassensperrung PRIMARY 2 qmsystems.o.Wache 1 100.00 Using index condition; Using where
1 SIMPLE a ref OrganisationID OrganisationID 2 qmsystems.i.OrganisationID 65 100.00 Using index condition; Using where
1 SIMPLE sli eq_ref PRIMARY,LieferantID_ItemID,OrganisationID_LieferantID_ItemID_showItem PRIMARY 6 qmsystems.a.ID,qmsystems.oi.ItemID 1 99.22 Using where

this is still weired.

Comment by Ralf Gebhardt [ 2022-11-17 ]

Changed to bug to get the bug report verified

Comment by Alice Sherepa [ 2022-11-18 ]

mokraemer, there is no FULL JOIN in MariaDb, could you please show the query in a question? maybe FULL was interpreted as an alias?

Comment by Marc [ 2022-11-21 ]

I think the problem occured, when table stats are inaccurate, e.g. a table is just created and still empty and then gets filled. If no analyze table is issued, the optimizer makes very strange decissions. In that particular case "rdShopLieferantItems sli" was [inner] joined and does not have any results (0 items), but was stated as filtered "0.00%" instead of "100%" (as no results were found).

But after issueing an analyze table, I'm not able to reproduce this issue, even with a new table - just close it - sorry for bothering you.

Generated at Thu Feb 08 10:13:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.