MySQL – USING() considered harmful
Posted on October 30th, 2008 by whinger. Filed under Tech, Web Development, Whinges.
I’m not a great fan of making use of non-ANSI SQL: not only does it make it harder for new coders to pick up but it means any chance of changing databases becomes completely financially unviable.
The MySQL USING() clause is the worst type of example: not only is its requirements for brackets confusing and unnatural, it provides no advantage except to the incredibly lazy – being purely syntactic sugar.
For those who don’t know, it replaces
FROM a INNER JOIN b ON a.fieldname=b.fieldname
with
FROM a INNER JOIN b USING(fieldname)
Up until today I’ve been mildly irritated by it, until I came across a bizarre piece of behaviour. Given two tables, “pubs” and “beers”, with a joining field “brewery” and both with named fields “name”, running
SELECT strength FROM beers INNER JOIN pubs USING(brewery) WHERE name="the white horse"
utterly fails to warn me that “name” is ambiguous and returns no results (unless there’s a beer called “the white horse”, of course). On the other hand,
SELECT strength FROM beers INNER JOIN pubs ON beers.brewery=pubs.brewery WHERE name="the white horse"
immediately produces the “Column ‘name’ in where clause is ambiguous” error you expect.
Do I need to go into detail why this makes USING() harmful?
This is admittedly fixed in more recent versions (bugs.mysql.com) since October last year but how many installations out there (like mine) will still exhibit the bug? I’d guess at a fair few.