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
• Status: Closed
• 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.