Bug #69858 Make FULL a reserved word
Submitted: 27 Jul 2013 20:55 Modified: 31 Jul 2013 17:14
Reporter: Bill Karwin (Candidate Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Parser Severity:S4 (Feature request)
Version:5.6.12 OS:Any
Assigned to: CPU Architecture:Any

[27 Jul 2013 20:55] Bill Karwin
Description:
"FULL" should be a reserved word.  A query that uses a FULL JOIN on another SQL implementation may return unexpected results on MySQL.

Other word that are commonly used with JOIN are already reserved words in MySQL, such as INNER, OUTER, LEFT, RIGHT, CROSS, and NATURAL.

"FULL" is a reserved word in ANSI SQL-92, as well as in SQL implementations including PostgreSQL, SQLite, Oracle, Microsoft SQL Server, IBM DB2 UDB, HSQLDB, and even Microsoft Access.

"FULL JOIN" is legal syntax in standard SQL and in many implementations.  It is optional to use "FULL OUTER JOIN".

If a developer wants to use the word "FULL" intentionally as a correlation name, they should delimit it as an identifier using back-ticks, or double-quotes in ANSI SQL mode.

How to repeat:
Run a query involving a FULL JOIN that runs on another brand of RDBMS.  Example:

SELECT * FROM Employee FULL JOIN Department USING (dept_no);

In MySQL, this query runs without error or warning, but it executes an INNER JOIN, because the word "FULL" is interpreted as a correlation name (table alias) for the employee table.

Suggested fix:
Make the word "FULL" a reserved word in MySQL.
[29 Jul 2013 7:23] MySQL Verification Team
Hi Bill,

This is a duplicate of http://bugs.mysql.com/bug.php?id=18003
[31 Jul 2013 17:09] Bill Karwin
Shane,

Thanks for your attention, but this feature request is not a duplicate.  Bug #18003 is a request to implement FULL [OUTER] JOIN.

Whereas this is a request to make FULL JOIN fail (correctly) because MySQL does not implement FULL [OUTER] JOIN.

Allowing the query syntax to run, but give wrong results, is worse than not supporting the query syntax.
[31 Jul 2013 17:14] MySQL Verification Team
Okay, we can assume that throwing an ER_UNSUPPORTED error message will be easier and quicker to do and might get done before actually implementing the functionality required to make it work as expected.