← Back to team overview

maria-developers team mailing list archive

A proposal to deprecate syntax: SELECT a'test'


  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:

CREATE TABLE t1 (date date);
INSERT INTO t1 VALUES ('2016-02-19');
SELECT date'2001-01-01' FROM t1;


| 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.

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.