MySQL Bugs: #10972: Views: natural join of view and underlying table gives wrong result

Bug #10972	Views: natural join of view and underlying table gives wrong result
Submitted:	30 May 2005 22:10	Modified:	25 Aug 2005 11:55
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.7-beta-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Timour Katchaounov	Target Version:

Description:
I have a table, and a view which is based on the table.
Both table and view have a column with the same name -- s3 -- but the contents are
different.
Since the contents are different, a natural join should not succeed.
But it succeeds.

I believe that this is different from bug#6276, since it doesn't crash,
and since it depends on columns with different contents.

How to repeat:
mysql> create table tb1 (s1 int, s2 char(5), s3 decimal(10)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> create view vb1 as select s1, s2, 'x' as s3 from tb1;//
Query OK, 0 rows affected (0.04 sec)

mysql> select * from tb1 natural join vb1;//
Empty set (0.00 sec)

mysql> insert into tb1 values (1,'x',5)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1 natural join vb1;//
+------+------+------+
| s1   | s2   | s3   |
+------+------+------+
|    1 | x    | 5    |
+------+------+------+
1 row in set (0.00 sec)

Thank you for the bug report.

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/internals/28693

Fixed in 5.0.12.

The bug is fixed by WL#2486.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 5.0.12 changelog. Closed.