309680 - Use SQL "JOIN ... ON ..." syntax instead of "JOIN ... USING(...)"

Status: RESOLVED FIXED

(Bugzilla :: Database, enhancement)

Description

[Lance Larsh]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.3) Gecko/20041119

Queries using the JOIN...USING() syntax are not always portable to different
databases.  For example, if a column name is referenced in a JOIN...USING()
clause, Oracle will not allow that column name to be qualified by a table/alias
name anywhere in the query.  Otherwise Oracle returns an error ("ORA-25154:
column part of USING clause cannot have qualfier").  But if we remove the
table/alias qualifier, then MySQL returns an error ("ERROR 1052 (23000): Column
'id' in field list is ambiguous").  Therefore it's not possible to write a
JOIN...USING() query that works on both databases if the join column is
referenced outside the JOIN itself.  However queries using the "JOIN...ON"
syntax can be written to work on both databases.

Reproducible: Always

Steps to Reproduce:
Run the following statements against either a MySQL or an Oracle database:
1. CREATE TABLE t1 ( id INTEGER, name VARCHAR(10) );
2. CREATE TABLE t2 ( id INTEGER, version VARCHAR(10) );

3. SELECT t1.id, t1.name, t2.version FROM t1 JOIN t2 USING(id);
   (Succeeds on MySQL; returns error ORA-25154 on Oracle)

4. SELECT    id, t1.name, t2.version FROM t1 JOIN t2 USING(id);
   (Returns ERROR 1052 on MySQL; succeeds on Oracle)

5. SELECT t1.id, t1.name, t2.version FROM t1 JOIN t2 ON t1.id = t2.id;
   (Succeeds on both MySQL and Oracle)

Comment 1

[Max Kanat-Alexander]

Yeah, agreed. I much prefer JOIN ON anyway.

Comment 2

[Frédéric Buclin]

only 3 occurences, right?

Bugzilla/Bug.pm:180:           USING (bug_id)
Bugzilla/Bug.pm:1014:         "LEFT JOIN bugs USING(bug_id) " .
checksetup.pl:2448:     "LEFT JOIN bugs using(bug_id) " .

Comment 3

[Lance Larsh]

Attachment: Patch to use JOIN...ON instead of JOIN...USING

Yep, those are the only 3 occurrences I found, all addressed by this patch.

Comment 4

[Max Kanat-Alexander]

Comment on attachment 198348 [details] [diff] [review]
Patch to use JOIN...ON instead of JOIN...USING

r=mkanat by inspection

Comment 5

[Max Kanat-Alexander]

Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.440; previous revision: 1.439
done
Checking in Bugzilla/Bug.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Bug.pm,v  <--  Bug.pm
new revision: 1.96; previous revision: 1.95
done