Hi!
Hello Monty, all,
We discussed this with Sergei and both think that it will be a good idea to deprecate this syntax:
SELECT a'test';
where 'a' is an identifier and 'test' its alias.
Is this dependent on if there is a space or not between a and 'test' ? Note that in MySQL and MariaDB, ' and " are both used for quoting strings and I don't think we can change that without affecting a lot of applications! I think it's ok to require one to have a space between a and 'test', if that would help at all.
This is a non-standard way, and it conflicts with some other important standard SQL grammar. See below.
Note that in MySQL/MariaDB, when not using ANSI mode, ' and " are equal and ` is used for identifiers, so this is expected to be non-standard. For quoting identifiers ` should be used. Of course, if you are using ANSI mode, then things should be as close to ANSI as possible.
Typified literals and a syntax conflict ---------------------------------------
An identifier followed by a single-quoted text string is needed for typified literals:
SELECT INET6'ffff::ffff';
We already support this for temporal literals, which is a part of the SQL standard:
SELECT TIME'10:10:10'; SELECT TIMESTAMP'2001-01-01 10:20:30'; SELECT DATE'2001-01-01';
Note, the conflict already exists in MySQL and MariaDB.
This script:
DROP TABLE IF EXISTS t1; CREATE TABLE t1 (date date); INSERT INTO t1 VALUES ('2016-02-19'); SELECT date'2001-01-01' FROM t1;
returns:
+------------------+ | date'2001-01-01' | +------------------+ | 2001-01-01 | +------------------+
Notice, it returns a DATE literal, it does not return the value of the column t1.date using '2001-01-01' as an alias.
As ansi sql doesn't allow you to use date as a column name, this isn't really as big problem as it may seam ;) Anyway, I agree that this can be confusing, but so is having date as a column namn.
Notice the difference:
SELECT date`2001-01-01` FROM t1; +------------+ | 2001-01-01 | +------------+ | 2016-02-19 | +------------+
This is confusing.
Not really, as you are using different quotes and you get the expected answer. This should be same as if you used " in ANSI SQL, isn't it?
Shift-reduce conflicts ---------------------- By the way, the fact that we support single-quoted string as an alias is the reason for shift-reduce conflicts in sql_yacc.yy, because TIME/DATE/TIMESTAMP followed by 'string' can be interpreted in two ways: - a column name followed by an alias - an SQL-standard time literal
What is the SQL-standard way to use an alias? (Sorry, can't access the standard just now): SELECT date "2001-01-01" FROM t1; or SELECT date '2001-01-01' FROM t1; And if you execute both of these queries, will you not get the same confusing result for one of them? As ' and " are identical in MySQL mode, I don't see how you can fix this without causing even more confusion.
What can be used instead ------------------------ We have a number of other ways to specify aliases:
The standard ways:
SELECT a AS test; SELECT a "test"; SELECT a AS "test";
Ok, this answers my question above. The standard says that an alias is a text string. In MySQL / MariaDB a text string can be specified with both ' or ". I don't think it's good idea to make alias a special case where you have to specify the text string only with ".
MySQL/MariaDB extensions:
SELECT a `test`; SELECT a AS `test`;
That should be enough.
Note that this has nothing exclusively to do with alias, but with the MySQL/MariaDB extension that you can use ' instead of " as a string delimiter.
Proposal -------- 1. In 10.1 we add a warning when a single-quoted string is used as a column alias. Something like this should work:
Single quotes in a select sublist alias are deprecated. Use double quotes instead.
This would break the specification of what a text strings is in MariaDB. It would also be very confusing that text strings needs to be specified differently in different places of the SQL syntax.
2. In 10.2 we disallow this syntax by default and add either sql_mode or old_mode to enable the old behavior.
As you can already get the behavior you want by specifying ANSI MODE, I don't think this change is necessary. I think it's also bad as it makes it harder to define what a text string is. For example: In MariaDB, a text string can be delimited with ' or ", except in the case of alias when you have to use ' I think it's better to require, as most ANSI SQL databases does, one to quote column names like 'date' and 'time' to ensure they are not mixed with operators or string prefixes. Regards, Monty