Bug #6136 NATURAL JOIN changed behavior - table order matters now

• Submitted: 17 Oct 2004 23:29
• Modified: 29 Sep 2008 18:17
• Reporter: Ondrej Brablc
• Status: Won't fix
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.6
• OS: Windows (Windows XP)
• Assigned to: Timour Katchaounov
• CPU Architecture: Any

[17 Oct 2004 23:29] Ondrej Brablc

Description:
Simple NATURAL JOIN using one common column produces different results depending on the table order in the FROM clause:

Common column (nid - see structure how to repeat) will not be in the output:

SELECT node.* FROM root NATURAL  JOIN node

Common column (nid) will be in the output

SELECT node.* FROM node NATURAL  JOIN root

In version 4.1.1alpha it worked fine, people started reporting this problem with 4.1.5, I have repeated it with 4.1.6.

I believe that the output should be same and should include the common column.

How to repeat:
Create structure as follows and then run the two selects from description.

CREATE TABLE `node` (
  `nid` tinyint(4) NOT NULL default '0',
  `name` varchar(10) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `node` VALUES (1, 'A');
INSERT INTO `node` VALUES (2, 'B');

CREATE TABLE `root` (
  `uid` tinyint(4) NOT NULL default '0',
  `nid` tinyint(4) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `root` VALUES (1, 1);
INSERT INTO `root` VALUES (1, 2);

[18 Oct 2004 6:52] Alexander Keremidarski

mysql> SELECT node.* FROM node NATURAL JOIN root;
+-----+------+
| nid | name |
+-----+------+
|   1 | A    |
|   2 | B    |
+-----+------+
2 rows in set (0.00 sec)
 
mysql> SELECT node.* FROM root NATURAL JOIN node;
+------+
| name |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

[17 Feb 2005 9:40] Timour Katchaounov

The fix for this bug by Ramil has been incorporated in the fix for BUG#6489 since
both are changing the same condition.

[14 Mar 2005 11:50] Timour Katchaounov

Correcting this bug in v. 5.0 requires a different approach which is part
of a bigger ongoing task for ANSI compliant processing of
NATURAL/UNSING joins. In order to keep both versions (4.1 and 5.0)
in sync, the patch for this bug will be pushed once we are ready with
the related task for v. 5.0.

[30 Aug 2005 10:02] Timour Katchaounov

The bug has been fixed for 5.0 (in 5.0.12). As the fix for 4.1 is completely different, for
4.1 it remains to be implemented later.

[29 Sep 2008 18:17] Konstantin Osipov

We do not have plans to fix this bug in 4.1