MySQL Bugs: #72774: Alias gets concatted to result

Bug #72774	Alias gets concatted to result
Submitted:	28 May 2014 9:09	Modified:	5 Jun 2014 14:22
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.17	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
According to documentation an alias may be quoted as either a 'string' or an `identifier`.  But here it does not work when quoting as a string. 

How to repeat:
SELECT 'aa' AS 1aa; -- returns "aa"
SELECT 'aa' AS '1aa'; -- returns "aa"
SELECT 'aa' `1aa`; -- returns "aa" 
SELECT 'aa' '1aa'; -- returns "aa1aa"

Suggested fix:
The SPACE character in "'aa' '1aa'" should be recognized and thus '1aa' should be treated as an alias.

hmmm .. somehow one line got removed from my test case. This one was missing:

SELECT 'aa' 1aa; -- returns "aa"

Unquoted and `identifier quoted` it works as expected even without the "AS" keyword - 'string quoted' it does not. Unquoted as well as both types of quotes work if the "AS" keyword is specified.

I am not sure, but it may be related to the alias starting with a number (the parser may fool itself to expect another statement - but only guesswork from my side). I have also not tried other server versions than 5.6.17, so not sure if it is a regression or not.

this is a know way to concat the strings, so i guess not a bug:

select 'test' 'hello';

http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

"Quoted strings placed next to each other are concatenated to a single string. The following lines are equivalent:"

Is this concatenation documented at all? I don't accept 'that it was always like that' or 'it is like that in C' or whatever. What counts is MySQL docs and SQL standards.

Doesn't it contradicts documentation pages
1) SELECT syntax description where "AS" is optional for an alias
2) Docs stating that an identifier may be quoted as a string?

.. combining the two I think
SELECT 'hello' 'world';
.. should - as I understand docs - return "hello" with "world" as column header.

Hi Peter,

Thank you for your contribution. You have indeed discover and undocumented, but standard, behavior of our server.

Here are , in short my answers, for your four examples ....

SELECT 'aa' AS 1aa; -- returns "aa" = it has AS, so first item, being string constant in SELECT list, gets returned
SELECT 'aa' AS '1aa'; -- returns "aa" = the same as first query
SELECT 'aa' `1aa`; -- returns "aa"  = here, the first item, the string constant in SELECT list gets returned, but `laa` gets treated as alias, as it is in backquotes and not simple quotes
SELECT 'aa' '1aa'; -- returns "aa1aa" = Here a string concatenation occurs, since there is no reserve word AS and both first item in the SELECT list and the intended ALIAS use single quotes.

This is not documented properly, which makes this a verified Documentation bug !!!!

@Sinisa ..

Since you call this a 'standard behavior' would you mind provide a *link to* and a *quote from* the standard you are referring to?

I agree that current behaviour should be documented.  But I only find this a short-term solution. It rather looks to me like one more "year 2K MySQL gotcha' that is both inconsistent and non-standard.

Shane provided the link describing this behavior:

http://dev.mysql.com/doc/refman/5.7/en/string-literals.html