Bug #21968 natural join does not work for self join (as join using)

• Submitted: 1 Sep 2006 14:14
• Modified: 4 Sep 2006 12:30
• Reporter: Jihng Liu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• CPU Architecture: Any

[1 Sep 2006 14:14] Jihng Liu

Description:
natural join and join using() should have the same functionality, there is exception for natural join which does not work for self join. Please see the following examples.

I have a country table as follows:

mysql> create table country(
 name varchar(20),
 continent varchar(20),
 population int unsigned);

mysql> select * from country;
+--------+-----------+------------+
| name   | continent | population |
+--------+-----------+------------+
| China  | Asia      |        567 |
| India  | Asia      |        879 |
| USA    | N.America |        344 |
| Canada | N.America |        233 |
+--------+-----------+------------+
4 rows in set (0.00 sec)

mysql> select * from country c join country c1 using(continent);
+-----------+--------+------------+--------+------------+
| continent | name   | population | name   | population |
+-----------+--------+------------+--------+------------+
| Asia      | China  |        567 | China  |        567 |
| Asia      | India  |        879 | China  |        567 |
| Asia      | China  |        567 | India  |        879 |
| Asia      | India  |        879 | India  |        879 |
| N.America | USA    |        344 | USA    |        344 |
| N.America | Canada |        233 | USA    |        344 |
| N.America | USA    |        344 | Canada |        233 |
| N.America | Canada |        233 | Canada |        233 |
+-----------+--------+------------+--------+------------+
8 rows in set (0.00 sec)

mysql> select * from country c natural join country c1;
+--------+-----------+------------+
| name   | continent | population |
+--------+-----------+------------+
| China  | Asia      |        567 |
| India  | Asia      |        879 |
| USA    | N.America |        344 |
| Canada | N.America |        233 |
+--------+-----------+------------+
4 rows in set (0.00 sec)

How to repeat:
mysql> select * from country c join country c1 using(continent);
+-----------+--------+------------+--------+------------+
| continent | name   | population | name   | population |
+-----------+--------+------------+--------+------------+
| Asia      | China  |        567 | China  |        567 |
| Asia      | India  |        879 | China  |        567 |
| Asia      | China  |        567 | India  |        879 |
| Asia      | India  |        879 | India  |        879 |
| N.America | USA    |        344 | USA    |        344 |
| N.America | Canada |        233 | USA    |        344 |
| N.America | USA    |        344 | Canada |        233 |
| N.America | Canada |        233 | Canada |        233 |
+-----------+--------+------------+--------+------------+
8 rows in set (0.00 sec)

mysql> select * from country c natural join country c1;
+--------+-----------+------------+
| name   | continent | population |
+--------+-----------+------------+
| China  | Asia      |        567 |
| India  | Asia      |        879 |
| USA    | N.America |        344 |
| Canada | N.America |        233 |
+--------+-----------+------------+
4 rows in set (0.00 sec)

[4 Sep 2006 12:30] MySQL Verification Team

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional Info:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from country c join country c1 using(name,continent,population);
+--------+-----------+------------+
| name   | continent | population |
+--------+-----------+------------+
| China  | Asia      |        567 | 
| India  | Asia      |        879 | 
| USA    | N.America |        344 | 
| Canada | N.America |        233 | 
+--------+-----------+------------+
4 rows in set (0.00 sec)

mysql> select * from country c natural join country c1;
+--------+-----------+------------+
| name   | continent | population |
+--------+-----------+------------+
| China  | Asia      |        567 | 
| India  | Asia      |        879 | 
| USA    | N.America |        344 | 
| Canada | N.America |        233 | 
+--------+-----------+------------+
4 rows in set (0.01 sec)

mysql>