Bug #8927 prepared statement with multiple left joins of same table hangs
Submitted: 3 Mar 2005 16:36 Modified: 3 Jun 2005 11:31
Reporter: Daniel Blumenthal Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:4.1.10 OS:Linux (Linux RH 8.0)
Assigned to: Timour Katchaounov CPU Architecture:Any

[3 Mar 2005 16:36] Daniel Blumenthal
Description:
When I run a prepared statement which has multiple left joins of the same table, the prepared statement only works once. It appears that the "where" clause is not respected after the first iteration, so in the second iteration all values are returned.
There are no error messages in the .err file.
The error only occurs with PreparedStatements, and happens with both MyISAM and INNODB tables (I haven't checked the others).

How to repeat:
create table word
(
	ID int unsigned not null primary key auto_increment,
	languageID tinyint unsigned not null,
	text varchar(255) not null
);

create table wordTuplePair
(
	tupleID int unsigned not null,
	wordID int unsigned not null
);

insert into word (languageID,text) values (1,"a"),(2,"b"),(2,"c"),(2,"d");
insert into wordTuplePair (tupleID,wordID) values (1,1),(1,2),(2,3),(2,4);

PREPARE pstmt FROM
'select w1.ID,w1.text,w2.ID,w2.text,w1.tupleID,w2.tupleID from word as w1
left join wordTuplePair as wtp1 on wtp1.wordID=w1.ID
left join wordTuplePair as wtp2 on wtp2.tupleID=wtp1.tupleID
left join word as w2 on w2.ID=wtp2.wordID
where w1.text="a" and w2.languageID=2';

execute pstmt;
+----+------+----+------+---------+---------+
| ID | text | ID | text | tupleID | tupleID |
+----+------+----+------+---------+---------+
|  1 | a    |  2 | b    |       1 |       1 |
+----+------+----+------+---------+---------+
1 row in set (0.01 sec)

execute pstmt;
+----+------+----+------+---------+---------+
| ID | text | ID | text | tupleID | tupleID |
+----+------+----+------+---------+---------+
|  1 | a    |  2 | b    |       1 |       1 |
|  1 | a    |  3 | c    |       1 |       2 |
|  1 | a    |  4 | d    |       1 |       2 |
+----+------+----+------+---------+---------+
3 rows in set (0.00 sec)

The first time it correctly finds the one row.  The second time, though, it returns everything from wordTuplePair. Note that the two "tupleID" columns should equal each other (the SQL specifies "left join wordTuplePair as wtp2 on wtp2.tupleID=wtp1.tupleID"), but in the second case this doesn't happen.
[3 Mar 2005 16:51] Daniel Blumenthal
I should mention - the reason the title of the bug indicates a hang bug is that with millions of lines of data, it would hang the machine and overrun disk space trying to prepare an answer.
[14 Mar 2005 11:46] Hartmut Holzgraefe
Verified, the statement should look like this though:

PREPARE pstmt 
FROM 'select w1.ID,w1.text,w2.ID,w2.text,wtp1.tupleID,wtp2.tupleID 
from word as w1 left join wordTuplePair as wtp1 on wtp1.wordID=w1.ID 
left join wordTuplePair as wtp2 on wtp2.tupleID=wtp1.tupleID 
left join word as w2 on w2.ID=wtp2.wordID where w1.text="a" and w2.languageID=2';

(note the different table aliases for the 2 last result columns)
[24 Mar 2005 5:09] Jorge del Conde
verified using 4.1.11 from bk
[25 Apr 2005 16:27] [ name withheld ]
I have a similar problem with 5.0.4beta, using a query where the subselect contains multiple left outer joins.
[3 Jun 2005 11:31] Timour Katchaounov
I tried to reproduce this bug with 4.1.13 (debug and release) and 5.0.7 (debug) and
everything worked. I am running on SuSe 9.3. Could you please re-verify and check
if this is RH 8.0 specific ?
[21 Jul 2005 14:04] Daniel Blumenthal
I have tested this on RH8 with 4.1.13, and it appears to work fine. I will test to see if it works in the more complex, actual code.