MySQLs mangled syntax handling and INNER JOINs
As my DBA skills have progressed over the years, I'm always impressed and upset to see the extent to which MySQL has allowed me to use terrible syntax generally not accepted by standards-compliant SQL servers.
A recent one really blew me away, because it exposed a lack of understanding on my part about how to understand the syntax of JOIN commands.
This is how I always built nested JOIN queries. The premise is that semantically you are always selecting from a single table; the use of the INNER JOIN statement is treated like a function that returns a compound table.
FROM role r
INNER JOIN (role_permission rp
INNER JOIN permission p ON (rp.permission_id = p.id))
ON (rp.role_id = r.id)
ORDER BY r.name, p.name
However, when trying to run this same snippet against an SQLite3 database, I would continue to get a no such column: p.name error. After some research, I found the preferable way to do nested joins is much simpler, if less semantically pleasing:
FROM role r
INNER JOIN role_permission rp ON rp.role_id = r.id
INNER JOIN permission p ON rp.permission_id = p.id
ORDER BY r.name, p.name
This works for me properly in both SQLite and MySQL. I'm curious what it was that made me ever start writing nested JOINs in the former method; it was fairly error-prone due to missing parenthesis, etc.