Bug #12816 JOIN with USING clause doesn't return expected result

• Submitted: 25 Aug 2005 23:58
• Modified: 29 Aug 2005 7:38
• Reporter: Shuichi Tamagawa
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.0.13-bk
• OS: Linux (Linux)
• CPU Architecture: Any

[25 Aug 2005 23:58] Shuichi Tamagawa

Description:
JOIN with USING clause doesn't return expected result as previous version does. JOIN with ON clause  is fine.

How to repeat:
mysql> create table t1(c1 int primary key);
Query OK, 0 rows affected (0.12 sec)

mysql> create table t2(c1 int primary key);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 using (c1);
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

mysql> select * from t1 left join t2 on (t1.c1=t2.c1);
+----+------+
| c1 | c1   |
+----+------+
|  1 |    1 |
|  2 | NULL |
|  3 | NULL |
+----+------+
3 rows in set (0.00 sec)

Suggested fix:
In 5.0.10 and before, the result was ...

mysql> create table t1(c1 int primary key);
Query OK, 0 rows affected (0.14 sec)

mysql> create table t2(c1 int primary key);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 left join t2 using (c1);
+----+------+
| c1 | c1   |
+----+------+
|  1 |    1 |
|  2 | NULL |
|  3 | NULL |
+----+------+
3 rows in set (0.01 sec)

mysql> select * from t1 left join t2 on (t1.c1=t2.c1);
+----+------+
| c1 | c1   |
+----+------+
|  1 |    1 |
|  2 | NULL |
|  3 | NULL |
+----+------+
3 rows in set (0.00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.10-beta |
+-------------+
1 row in set (0.08 sec)

[26 Aug 2005 13:48] Valeriy Kravchuk

In 5.0.11-beta and 4.1.13a the results were correct too (2 columns). 

Still waiting for the today's BK source to compile...

[26 Aug 2005 18:31] Shuichi Tamagawa

Same result on 5.0.13-bk Aug 25.

mysql> create table t1(c1 int primary key);
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2(c1 int primary key);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 left join t2 using (c1);
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.06 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.13-beta-debug |
+-------------------+
1 row in set (0.00 sec)

[27 Aug 2005 9:09] Valeriy Kravchuk

mysql> create table t1(c1 int primary key);
Query OK, 0 rows affected (0,00 sec)

mysql> create table t2(c1 int primary key);
Query OK, 0 rows affected (0,00 sec)

mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0,00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1);
Query OK, 1 row affected (0,01 sec)

mysql> select * from t1 left join t2 using (c1);
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0,00 sec)

mysql> select version();
+-------------+
| version()   |
+-------------+
| 5.0.13-beta |
+-------------+
1 row in set (0,00 sec)