Bug #13067 JOIN xxx USING is case sensitive

• Submitted: 8 Sep 2005 16:11
• Modified: 14 Sep 2005 0:23
• Reporter: Stefano Crimi'
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.12 beta/BK source
• OS: Windows (Windows NT/Linux)
• Assigned to: Timour Katchaounov
• CPU Architecture: Any

[8 Sep 2005 16:11] Stefano Crimi'

Description:
When you use "JOIN" between tables with the "USING" keyword, the fields used for the join seems to be case sensitive. When you use the "ON" keyword, the fields seems to be case insensitive. I believe the latter approach is the correct one.

How to repeat:
mysql> create table table1 (a int(10),b int(10));
Query OK, 0 rows affected (0.61 sec)

mysql> create table table2 (a int(10),b int(10));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 values (1,10),(2,20),(3,30);
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicati: 0  Avvertimenti: 0

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

mysql> select * from table1 inner join table2 using (A);
ERROR 1054 (42S22): Colonna sconosciuta 'A' in 'from clause'
mysql> select * from table1 inner join table2 using (a);
+------+------+------+
| a    | b    | b    |
+------+------+------+
|    1 |   10 |   10 |
+------+------+------+
1 row in set (0.11 sec)

mysql> select * from table1 inner join table2 on table1.a=table2.a;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |   10 |    1 |   10 |
+------+------+------+------+
1 row in set (0.03 sec)

mysql> select * from table1 inner join table2 on table1.A=table2.A;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |   10 |    1 |   10 |
+------+------+------+------+
1 row in set (0.02 sec)

[8 Sep 2005 18:58] MySQL Verification Team

Thank you for the bug report.

ChangeSet@1.1961, 2005-09-08 18:27:05+02:00, [e-mail]
  Merge bk-internal.mysql.com:/home/bk/mysql-5.0
  into mysql.com:/usersnfs/pchardin/mysql-5.0

miguel@hegel:~/dbs/5.0> bin/mysql -uroot kl
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-beta-debug

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

mysql> create table table1 (a int(10),b int(10));
Query OK, 0 rows affected (0.07 sec)

mysql> create table table2 (a int(10),b int(10));
Query OK, 0 rows affected (0.39 sec)

mysql> insert into table1 values (1,10),(2,20),(3,30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> select * from table1 inner join table2 using (A);
ERROR 1054 (42S22): Unknown column 'A' in 'from clause'
mysql> select * from table1 inner join table2 using (a);
+------+------+------+
| a    | b    | b    |
+------+------+------+
|    1 |   10 |   10 |
+------+------+------+
1 row in set (0.11 sec)

mysql> select * from table1 inner join table2 on table1.a=table2.a;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |   10 |    1 |   10 |
+------+------+------+------+
1 row in set (0.07 sec)

mysql> select * from table1 inner join table2 on table1.A=table2.A;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |   10 |    1 |   10 |
+------+------+------+------+
1 row in set (0.07 sec)

mysql>

[12 Sep 2005 5:29] 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/29635

[12 Sep 2005 6:44] Timour Katchaounov

Fixed in 5.0.13.

[12 Sep 2005 15:42] Timour Katchaounov

Fixed in 5.0.13.

[14 Sep 2005 0:23] Paul DuBois

Noted in 5.0.13 changelog.