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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.