Bug #1584 problem with using fields (names) from derived table subquery tables

• Submitted: 17 Oct 2003 5:29
• Modified: 20 Oct 2003 9:27
• Reporter: Oleksandr Byelkin
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1
• OS: Any (any)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[17 Oct 2003 5:29] Oleksandr Byelkin

Description:
there are problem with using fields (names) from derived table subquery tables 
in EXPLAIN, if used same table everywhere. 

How to repeat:
+ create table t1 (E1 INTEGER UNSIGNED NOT NULL, E2 INTEGER UNSIGNED NOT NULL, 
E3 INTEGER UNSIGNED NOT NULL, PRIMARY KEY(E1) 
+ ); 
+ insert into t1 VALUES(1,1,1), (2,2,1); 
+ select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A 
WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX ON 
t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 
+ count(*) 
+ 2 
+ explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 
AS A WHERE A.E3 = (SELECT MAX(B.E3) FROM t1 AS B WHERE A.E2 = B.E2)) AS THEMAX 
ON t1.E1 = THEMAX.E2 AND t1.E1 = t1.E2; 
+ id    select_type     table   type    possible_keys   key     key_len ref    
rows     Extra 
+ 1     PRIMARY <derived2>      ALL     NULL    NULL    NULL    NULL    2 
+ 1     PRIMARY t1      eq_ref  PRIMARY PRIMARY 4       ????????(.E2    1      
Using where 
+ 2     DERIVED t1      ALL     NULL    NULL    NULL    NULL    2       Using 
where 
+ 3     DEPENDENT SUBQUERY      B       ALL     NULL    NULL    NULL    NULL   
2Using where 
 
 
it is impossible to repeat bug whisout SELECT before EXPLIN 

Suggested fix:
Have not yet...

[19 Oct 2003 4:26] Oleksandr Byelkin

ChangeSet 
  1.1613 03/10/19 14:25:33 [e-mail] +6 -0 
  correct table name assigned to temporary table field: 
   - correct table name shown in EXPLAIN Iindex reference) 
   - pointer on freed memmory (reallocation of table name in reusing table 
entry) can't be used in EXPLAIN 
  (BUG#1584)

[20 Oct 2003 9:27] Oleksandr Byelkin

patch is pushed