MySQL Bugs: #69858: Make FULL a reserved word

Bug #69858	Make FULL a reserved word
Submitted:	27 Jul 2013 20:55	Modified:	15 Apr 2023 15:59
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

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.

Hi Bill,

This is a duplicate of http://bugs.mysql.com/bug.php?id=18003

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.

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.

This is now implemented in MySQL 8.0.31. 

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-31.html#mysqld-8-0-31-sql-syntax

Correction: FULL is a keyword in 8.0.32. It is not a *reserved* keyword, just an ordinary keyword. Using it as an identifier without delimiters returns the warning:

4119 Using FULL as unquoted identifier is deprecated, please use quotes or rename the identifier.

However, there is a small mistake in the documentation — it was not added to the list of new keywords:

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series