maria-developers team mailing list archive

[Michael Widenius <michael.widenius@xxxxxxxxx>]

Hi!

On 25 Feb 2016 19:16, "Alexander Barkov" <bar@xxxxxxxxxxx> wrote:

>> As ' and " are identical in MySQL mode, I don't see how you can fix
>> this without causing even more confusion.

I want to emphasise the above, as you haven't really understood the
implications of this.
This, in addition to that we support that one can use a string as an alias
makes things a bit tricky to fix in a compatible way.

> I only propose to change the behavior for the SELECT expression aliases,
> not to all strings generally.

As I understand it, you propose to change things in the following ways:

From:
- aliases can be identifiers or strings
To:
- aliases has to be identifiers or double quoted strings when used without
AS. When used with AS they can be identifiers or single or double quoted
strings.

I don't think there is any clear logic for the end user to understand why
we would do this.

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

> Sorry, no. The standard says that an alias is an identifier.
> Text strings are not valid aliases.

Sorry, I was not clear.
In MySQL mode " is a text string. If we want to support the above
statements , we also have to support using ' for aliases.
The other option would be to say that one must use back tick for aliases,
as that's the equal to ansi sql ".

> I only propose to deprecate string literals (i.e. single quoted strings)

In MariaDB, a string literal starts with ' or ". If your are deprecating
one in some context you have also to depricate the other.

> as SELECT expression aliases, because this is a non-standard and
> harmful  extension, which conflicts with another standard syntax assumed
> for typified literals.

To make things clear. There are 3 non standard things we are talking about
here:
- Both ' and " are string delimiters.
- quoting of identifiers are done with back tick.
- Aliases can be given with identifiers or strings.

You can't depricate something in one context and keep it in another
context. That's not consistent. Either ' and " are string delimiterx or
they are not. They should not be something different depending on context.

>>
>>> 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.
>
>
> My proposal is exclusively about aliases, not about using of " as a
> string delimiter.

See above. You can't make a special case for aliases that breaks the
general rules.
Things gets to complex to describe.

>> This would break the specification of what a text strings is in
>> MariaDB.

> No, this will only change  what a SELECT expression alias is.

If your proposal would be to only accept identifiers as aliases, then we
shouldn't accept " either for aliases. You can't say that " is sometimes an
identifier and sometimes a string.

>> It would also be very confusing that text strings needs to be
>> specified differently in different places of the SQL syntax.
>
> You're reordering causes and consequences :)
>
> Again, this is not about text strings. It's about
> what can be used as an alias.

It's well defined what a text string is in MySQL mode. You can't change the
definition just for some part of the alias syntax.

> I think it will be very obvious:
>
> - Expressions support single-quoted and double-quoted strings
>
> - Aliases support regular identifiers, delimited (i.e. double-quoted)
>   identifiers and backtick-quoted identifiers.

We don't have double quoted identifiers in MySQL mode!
You CAN NOT change the meaning of " just for aliases.
Note also that depending on the syntax suggested, you would in some cases
allow strings but not in other cases.

> Expressions and aliases are instances of very different nature.

Syntax of things must be consistent. You should be able to look at any part
of a sql construct and know what the part is. In your proposal the meaning
of " depends of context, which is the wrong way to do it.

Your options are to allow one to specify aliases with either identifiers or
strings. You should not try to change the meaning of ' or " depending on
context.

> Expression is a value that has a data type and attributes.
> Alias is an identifier, nothing else.

If that's would be the case in MySQL mode, then you should not be allowed
to use " for aliases either.

> There is nothing common in them at all.
> It's quite obvious that they *can* have different syntactic rules.

Sorry, no. It's important to be consistent.

>>> 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,
>
>
> Supporting typified literals in ANSI MODE only is not a way to go.
> We should support them in all modes.

I agree.

>
>> 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.
>
> This is not about to define what a text string is.
> It's the other way around,
> to define what are valid expressions and valid aliases.

Same thing, if we allow aliases in any case to be defined with a string.
And remember that "aaa" in MySQL mode is ALWAYS a text string.

>> For example:
>> In MariaDB, a text string can be delimited with ' or ", except in the
>> case of alias when you have to use '

> Sorry, not sure that I understood. Can you clarify about aliases and '
> please?

That's the point. With our proposed change it well be hard to define what
is a text string and what is an identifier and when one can use which where.

>> 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.
>
> I am afraid this is not really true.
>
> Both date and time are perfectly valid regular identifiers.
> Requiring to put them in quotes will be non-Standard.

As far as I remember, date and time are in the sql reserved keywords.
It's allowed by the standard to require that identifiers that are keywords
should be quoted

> I just checked this script:
>
>
> DROP TABLE t1;
> CREATE TABLE t1 (date DATE);
> INSERT INTO t1 VALUES('2001-01-01');
> SELECT date FROM t1;
>
> It works perfectly well in SQL Server, PostgreSQL and SQLite3.

Doesn't say anything about the standard.

> It will be pity if we won't be able to support this syntax:
>
> SELECT INET6'ffff::ffff';
>
> unless we deprecate this useless extension that allows string
> literals as aliases (i.e. single quoted string literals).

That's not true. Wei can easily support this. Just define inet6 as a
keyword that has higher precidence than an alias.
This means that if a user has inet6 as a column name he needs to quote it
if he is using it with an alias without AS.

Note that we also have to support:

SELECT INET6'"ffff::ffff";

As " and ' are interchangeable.

Which is something you have not discussed before and would break your
original proposal as you can't distinguish if "..." is a string or an
identifier.

Regards,
Monty