Bug #10972 Views: natural join of view and underlying table gives wrong result
Submitted: 30 May 2005 20:10 Modified: 25 Aug 2005 9:55
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Timour Katchaounov CPU Architecture:Any

[30 May 2005 20:10] Peter Gulutzan
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)
[30 May 2005 20:15] Miguel Solorzano
Thank you for the bug report.
[23 Aug 2005 16:01] 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/internals/28693
[23 Aug 2005 16:02] Timour Katchaounov
Fixed in 5.0.12.

The bug is fixed by WL#2486.
[25 Aug 2005 9:55] Jon Stephens
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.