Bug #10309 "dependant subquery" should be only "subquery"

• Submitted: 2 May 2005 11:23
• Modified: 22 Jan 2008 21:57
• Reporter: Corin Langosch
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.4
• OS: Linux (linux debian)
• CPU Architecture: Any

[2 May 2005 11:23] Corin Langosch

Description:
query

EXPLAIN SELECT u.user,u.sex,u.geburt_age FROM user AS u LEFT JOIN user_profil AS up ON (u.id=up.owner_id) LEFT JOIN user_foto AS f ON (u.id=f.owner_id) WHERE u.land_id=1 AND u.stadt_id IN (SELECT s2.id FROM geodata.stadt AS s1,geodata.stadt AS s2 WHERE s1.id=17506 AND s1.kreis_id=s2.kreis_id) ORDER BY u.user:

returns:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1 	PRIMARY 	u 	ref 	land_stadt 	land_stadt 	4 	const 	4 	Using where; Using filesort 
1 	PRIMARY 	up 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich1.u.id 	1 	Using index 
1 	PRIMARY 	f 	eq_ref 	PRIMARY 	PRIMARY 	4 	findmich1.u.id 	1 	Using index 
2 	DEPENDENT SUBQUERY 	s1 	const 	PRIMARY,kreis_id_name 	PRIMARY 	4 	const 	1 	 
2 	DEPENDENT SUBQUERY 	s2 	eq_ref 	PRIMARY,kreis_id_name 	PRIMARY 	4 	func 	1 	Using where 

howevery the subquery is NOT dependant on the outer query, so it should be displayed as SUBQUERY only. then, may be the optimizer could handle it better also.

How to repeat:
see above

[3 May 2005 1:22] Jorge del Conde

Hi,

Can you please upload the table schema of the tables used in these queries ?

Thanks !

[4 May 2005 13:51] MySQL Verification Team

Hello,

Thank you for the report.
There are currently some limitations in our sub-query optimizer that we are working on. We can't fix it right now because it requires a complex fix and I don't know when these fixes will be made, so I mark this report as "To be fixed later".

[22 Jan 2008 21:51] Sergey Petrunya

The subquery is marked as "DEPENDENT SUBQUERY" because MySQL performs IN -> EXISTS transformation, essentially it takes the IN-equality and adds it into subquery's WHERE clause. This is not considered to be a bug.

[22 Jan 2008 21:57] Sergey Petrunya

Setting to duplicate of BUG#10312 as this is the same issue.