Bug #15355 Common natural join column not resolved in prepared statement nested query
Submitted: 30 Nov 2005 16:27 Modified: 23 Jun 2006 5:10
Reporter: Timour Katchaounov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.17 OS:Any (any)
Assigned to: Georgi Kodinov CPU Architecture:Any

[30 Nov 2005 16:27] Timour Katchaounov
Description:
A query with 5 levels of nested natural joins that works in
non-prepared mode results in a name resolution error when
executed as prepared statement.

How to repeat:
drop table if exists t1, t2, t3, t4, t5;
create table t1 (c int, b int);
create table t2 (a int, b int);
create table t3 (b int, c int);
create table t4 (y int, c int);
create table t5 (y int, z int);

-- this fails
prepare stmt1 from "select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5";
execute stmt1;
-- ERROR 1054 (42S22): Unknown column 'test.t4.y' in 'field list'

-- this works
select * from ((t3 natural join (t1 natural join t2)) natural join t4) natural join t5;
--+------+------+------+------+------+
--| y    | c    | b    | a    | z    |
--+------+------+------+------+------+
--|   11 |    3 |    1 |    2 |    4 |
--+------+------+------+------+------+
[30 Nov 2005 17:14] Timour Katchaounov
This query fails as well with a name resolution error:
select * from ((t3 natural left join (t1 natural left join t2)) natural left join t4) natural left join t5;
[8 Jun 2006 8:38] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7380
[8 Jun 2006 10:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7382
[19 Jun 2006 0:12] Evgeny Potemkin
Fixed in 5.0.23, 5.1.12
[20 Jun 2006 10:50] Evgeny Potemkin
There was a wrong context assigned to the columns that were added in insert_fields()
  when expanding a '*'. When this is done in a prepared statement it causes 
  fix_fields() to fail to find the table that these columns reference.
  Actually the right context is set in setup_natural_join_row_types() called at the 
  end of setup_tables(). However when executed in a context of a prepared statement
  setup_tables() resets the context, but setup_natural_join_row_types() was not
  setting it to the correct value assuming it has already done so.
[23 Jun 2006 5:10] Paul DuBois
Noted in 5.0.23, 5.1.12 changelogs.

Nested natural joins worked executed correctly when executed as a 
non-prepared statement could fail with an Unknown column 'col_name'
in 'field list' error when executed as a prepared statement due to a
name resolution problem.