MariaDB supports this non-standard syntax:
where the quoted string 'alias' is an alias.
There are a few problems with this syntax.
In some contexts a quoted string cannot be an alias, which is confusing:
- Multi-chunk string literals
Notice, the 'alias' part here is concatenated to the string, according to the SQL standard. Therefore, a quoted string cannot be an alias for a string literal.
- Temporal literals
Notice, the '2001-01-01' part is not an alias here. A combination of the keywords DATE, TIME and TIMESTAMP followed by a quoted string is treated as a temporal literal, according to the SQL standard. Therefore, a quoted string cannot be an alias for column names DATE, TIME, TIMESTAMP.
The above exception produce shift/reduce conflicts in sql_yacc.yy. Removing the "| TEXT_STRING_sys" options from the select_alias rule:
removes 4 shift/reduce conflicts.
There is an option that we'll replace the current bison syntax analyzer to something else in the future. It would be nice to clean up all shift/reduce conflicts eventually, as they will be show stoppers for a new analyzer.
We'll eventually add the SQL standard Unicode character string literals, e.g.:
The 'alias' part in the above query should be concatenated to the string, it's not an alias!
We'll eventually add the SQL standard multi-chunk hex literals, e.g.:
The above query should return a binary string 0x6061. '61' is not an alias!
We support this SQL standard syntax for binary and nchar literals:
But we don't support this standard syntax (notice the space between X/N and the quoted string):
According to the SQL standard, the space does not matter. To avoid conflicts, it's currently implemented on the tokenizer level in sql_lex.cc. It should be implemented on the syntax parser level instead.
The SQL standard defines DATE, TIME, TIMESTAMP literals as the data type name followed by a quoted string:
PostgreSQL extends this idea to all other data types, e.g.:
- A literal of the INET data type:
- A literal of the BIGINT data type:
Teradata also uses a similar syntax for the PERIOD data type literals:
IBM DB2 uses a similar syntax for the GRAPHIC data type literals and UCS-2 graphic string literals:
We want to reuse the same idea for pluggable data types, and possibly even for built-in data types.
We'll deprecate use of quoted string literals as aliases, as follows:
1. In 10.5 we'll add a warning:
2. In 10.5 we'll start using this syntax for known pluggable data types:
- If INET6 is a known data type, this will be treated as an INET6 litersl
- If INET6 is not a known data type, this will be treated in the old way: as an identifier followed by an alias
3. In 10.6 we'll disallow this syntax by default and will return an error instead of the warning.
4. In 10.6 we'll add a new sql_mode value STRING_AS_ALIAS, to switch to the legacy behavior when needed.
5. In 10.6 we'll start using this syntax for all data types:
to mean typified literals in the default sql_mode, so the above expression will mean a literal of the INET6 data type with 'f::f' as a value.
Note, with sql_mode=STRING_AS_ALIAS the above query will resolve INET6 as an identifier (e.g. a column or an SP variable name) and '00f::000f' as an alias, for backward compatibility.
Note, in the sql_mode=default (when ANSI_QUOTES is not enabled) we'll deprecate use of both single and double quoted strings, so these queries will return an error:
Bar originally proposed to deprecate only single quotes, but Monty and Serg are afraid that it will be confusing, because MariaDB does not distinguish between single and double string elsewhere (when ANSI_QUOTES is not set).
Note, this change will assume that this SQL-standard compliant query:
will stop working in the default sql_mode. But Monty and Serg think that this is OK: "alias" is a string here, it's not an identifier! Those users who strongly need this syntax can use sql_mode=ANSI_QUOTES or sql_mode=STRING_AS_ALIAS.
does not seem to cause any conflicts. We could still support this. However, for consistency and to move closer towards the SQL standard we'll also deprecate this syntax.
Note, similar to the previous paragraph, this change will assume that this SQL-standard compliant query:
will stop working with the default sql_mode. But Monty and Serg think that this is OK: "alias" is a string here, it's not an identifier! Those users who strongly need this syntax can use sql_mode=ANSI_QUOTES or sql_mode=STRING_AS_ALIAS.