maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

  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.

This is a non-standard way, and it conflicts with some other important
standard SQL grammar. See below.



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.


Notice the difference:

SELECT date`2001-01-01` FROM t1;
+------------+
| 2001-01-01 |
+------------+
| 2016-02-19 |
+------------+

This is confusing.



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 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";

MySQL/MariaDB extensions:


SELECT a `test`;
SELECT a AS `test`;

That should be enough.



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.



2. In 10.2 we disallow this syntax by default and add either sql_mode or 
old_mode to enable the old behavior.

Sergei thinks that old_mode is something that should sooner or later be 
completely deleted. So perhaps sql_mode is better here, as the old
behavior might be needed forever for MySQL compatibility.


We'll appreciate your feedback.

Thanks.