MySQL Bugs: #35863: parsing of ALIAS containing apostrophe and backslash

Bug #35863	parsing of ALIAS containing apostrophe and backslash
Submitted:	6 Apr 2008 22:58	Modified:	12 Jun 2008 16:48
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.1.22	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
Not sure if this a bug(s) or lack of documentation.  I was probably designed so but I do not feel it is correct!

But I think the examples below show

* apostrophe surrounding an alias are ignored.  Inside an alias they are not ignored.  why ?
* backslash seem to be treated like an escape charcater what I think it only should inside a quoted string.  I found no documenatation for such behaviour.

How to repeat:
CREATE TABLE `tester` (               
          `id` bigint(20) DEFAULT NULL        
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `tester` values (1),(2),(3);

SELECT  tester.id idalias  FROM
    test.tester order by idalias;

SELECT  tester.id 'idalias'  FROM
    test.tester order by 'idalias';

SELECT  tester.id idalias  FROM
    test.tester order by 'idalias';

SELECT  tester.id 'idalias'  FROM
    test.tester order by idalias;

-- and even those 

SELECT  tester.id \'idalias\'  FROM
    test.tester order by idalias;

SELECT  tester.id '\idalias'\ FROM
    test.tester order by `idalias`;

/* return

idalias
-------
      1
      2
      3
*/

-- but 

SELECT  tester.id i'dalia's FROM
    test.tester order by i'dalia's;

-- returns error 1064
-- 
--
-- why 'idalias' works when i'dalia's does not? 
--
-- this

SELECT  tester.id \'idalias'\  FROM
    test.tester order by idalias;

-- also returns error 1064 
-- and that looks to me like backslash is behaving as an escape character

-- I know I can

SELECT  tester.id `\'idalias\'`  FROM
    test.tester order by `\'idalias\'`;

-- though I do not think that is documented either that alias'es use the rules of identifiers in this respect!

Suggested fix:
Not sure! maybe there are good reasons to keep it like that.  But then I think it needs more docs on how ALIAS'es are parsed!

updated synopsis

Thank you for a problem report. On 5.0.56 on Windows I've got a bit different results:

mysql> SELECT  tester.id '\idalias'\ FROM
ERROR:
Unknown command '\ '.
    ->     test.tester order by `idalias`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '\ FRO
M
    test.tester order by `idalias`' at line 1
mysql> SELECT  tester.id \'idalias\'  FROM
ERROR:
Unknown command '\''.
ERROR:
Unknown command '\''.
    ->     test.tester order by idalias;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '\'ida
lias\'  FROM
    test.tester order by idalias' at line 1
mysql> SELECT  tester.id '\idalias'\ FROM
ERROR:
Unknown command '\ '.
    ->     test.tester order by `idalias`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '\ FRO
M
    test.tester order by `idalias`' at line 1
mysql> SELECT  tester.id \'idalias'\  FROM
ERROR:
Unknown command '\''.
    '>     test.tester order by idalias;
    '>
    '> Bye

I was forced to use Ctrl-C, as server expected "'"...

I think that allowing 'abc' as alias is incorrect anyway. Behaviour when backslashes are used may be mysql client and OS dependent.

Valeriy, unfortunately, your results are irrelevant. You were using mysql command line client and your results only show how it parses quotes and backslashes. Peter has used a different client (webyog, I would guess :), and he shows queries as they were sent to the server, verbatim.

ok .. I was using SQLyog.  The 'history' of the program (a kind of client side log) tells that what was actually sent to the server was 

/*[09:51:52][   0 ms]*/ SELECT  tester.id idalias  FROM     test.tester order by idalias;
/*[09:51:52][   0 ms]*/ SELECT  tester.id 'idalias'  FROM     test.tester order by 'idalias';
/*[09:51:52][   0 ms]*/ SELECT  tester.id idalias  FROM     test.tester order by 'idalias';
/*[09:51:52][   0 ms]*/ SELECT  tester.id 'idalias'  FROM     test.tester order by idalias;
/*[09:51:52][   0 ms]*/ SELECT  tester.id 'idalias'  FROM     test.tester order by idalias;
/*[09:51:52][   0 ms]*/ SELECT  tester.id '\idalias' FROM     test.tester order by `idalias`;
/*[09:51:52][   0 ms]*/ SELECT  tester.id i'dalia's FROM     test.tester order by i'dalia's;
/*[09:51:52][   0 ms]*/ SELECT  tester.id 'idalias'\  FROM     test.tester order by idalias; SELECT  tester.id `\'idalias\'`  FROM     test.tester order by `\'idalias\'`;  ;

.. so the behaviour with backslash is happening client-side.
This I will find out more about!

But actually this 

SELECT  tester.id idalias  FROM
    test.tester order by 'idalias';

SELECT  tester.id 'idalias'  FROM
    test.tester order by idalias;

was my original problem and here the clients agree!  5.1.23:

Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.23-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT  tester.id idalias  FROM
    ->     test.tester order by 'idalias';
+---------+
| idalias |
+---------+
|       1 |
|       2 |
|       3 |
+---------+
3 rows in set (0.00 sec)

mysql>
mysql> SELECT  tester.id 'idalias'  FROM
    ->     test.tester order by idalias;
+---------+
| idalias |
+---------+
|       1 |
|       2 |
|       3 |
+---------+
3 rows in set (0.00 sec)

In command line the escaping behaviour happens if the alias is quoted - compare

SELECT  tester.id 'ida\lias'  FROM
    test.tester order by 'idalias';

SELECT  tester.id ida\lias  FROM
    test.tester order by 'idalias';

mysql> SELECT  tester.id 'ida\lias'  FROM
    ->     test.tester order by 'idalias';
+---------+
| idalias |
+---------+
|       1 |
|       2 |
|       3 |
+---------+
3 rows in set (0.00 sec)

mysql>
mysql> SELECT  tester.id ida\lias  FROM
ERROR:
Unknown command '\l'.
    ->     test.tester order by 'idalias';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '\lias
  FROM
    test.tester order by 'idalias'' at line 1
mysql>

This is somehow logical (probably the parser 'sees' that this is a quoted string and uses escaping inside it without understanding the context).  But not logical or documented that a quoted alias ('myalias') and a non-quoted (myalias) can be use randomly.

to both of you ...

Actually SQLyog did not send the backslashes as HISTORY tells.
I tried QB briefly and it seems to behave as command line in this respect.

So let us concentrate on quoted/non-quoted alias here!

I have to withdraw what I wrote in the first post that id is not documented that alias'es are treated like identifiers as regards `backquotes`-

According to:
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
"Database, table, index, column, and alias names are identifiers."
what what about VIEWS and STORED PROGRAMS?

I will create a seperate report on this.

But if alias'es *are* identifiers the quote behaviour that 'myalias'/myalias can be used randomly is incorrect.

There are many different topics discussed in this bug report.

First, as Sergey pointed out, the client used to enter the queries might
have it's own escape sequences.

For the mysql client for example, '\' is an escape character, see
http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

If another client is used, please refer to the documentation of that client.

Once a query is sent to the server, the server also has some escape
sequences, needed to represent certain characters.

For example, "Hello,\nWorld !" uses an escape sequence for the new line
character, see
http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html

In case of SELECT aliases, the alias can be either an identifier,
as in:
  SELECT cust_name AS Customer from t1;
or a string literal, as in:
  SELECT cust_name AS 'Customer Name' from t1;

The following aliases are all equivalent, the alias name starts with
<i> and ends with <s>:
  SELECT tester.id idalias FROM ...
  SELECT tester.id 'idalias' FROM ...
  SELECT tester.id AS idalias FROM ...
  SELECT tester.id AS 'idalias' FROM ...

All the queries in the bug report using these syntaxes are working properly,
as expected.

For the following query:
  SELECT  tester.id \'idalias\'  FROM ...
the client used is likely to replace \' by a single quote character, and send
the following to the server:
  SELECT  tester.id 'idalias'  FROM ...
which is legal and works as expected.

For the following query:
  SELECT  tester.id '\idalias'\ FROM ...
the client used is likely to replace unknown \i and \<space> escape
sequences by a character i and space respectively, so that the following is
sent to the server:
  SELECT  tester.id 'idalias'  FROM ...
which is legal and works as expected.

The following query:
  SELECT  tester.id i'dalia's FROM
    test.tester order by i'dalia's;
uses an alias like:
  i'dalia's
which is neither a proper identifier nor a proper string literal,
so the parser rejects the query with a syntax error (1064),
which is the expected result.

Identifiers can be quoted, but with a different character, a back quote,
so that
  `idalias`
  'idalias'
are respectively a (back-quoted) identifier and a (quoted) string literal.
Given that these syntaxes are visually very close,
this is a source of confusion.

Also, please read the section about SQL_MODE=ANSI_QUOTES,
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
which relates to
  "idalias"

Closing this report as not a bug.

I am sorry, but it is NOWHERE documented that a alias may be a 'quoted string'.
On the opposite the docs state that an alias *is* an identifier - not that it *may be*

As regards the backslash I have allready withdrawn that myself.  Please read everything I wrote!

Looks like the main concern here is to things closed at any price ...

Manual (http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) cleraly says that alias IS identifier:

"Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, and other object names are known as identifiers."

Moreover, http://dev.mysql.com/doc/refman/5.0/en/select.html does NOT say anything about 'string' as possible alias. So, if accepting 'string' as alias was intended, then documentation should be updated.

Now, if it is intended, there are more serious bugs/potential problems then with 'string' as alias. Look:

mysql> select user as 'u' from mysql.user order by 'u';
+--------+
| u      |
+--------+
| a23145 |
| junk   |
| user1  |
| user2  |
| user3  |
| user4  |
| user5  |
| user1  |
| user2  |
| root   |
| upriv  |
| vadim  |
+--------+
12 rows in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.56-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.00 sec)

mysql> select user as u from mysql.user order by u;
+--------+
| u      |
+--------+
| a23145 |
| junk   |
| root   |
| upriv  |
| user1  |
| user1  |
| user2  |
| user2  |
| user3  |
| user4  |
| user5  |
| vadim  |
+--------+
12 rows in set (0.03 sec)

So, 'alias' in single quotes can NOT be used the same way as normal alias. Who will fix that?

This is a bug.

thanks Valeriy!

The manual need to clarify that an alias can be quoted as an identifier or as a string.

I would like to add to I agree with Sergei's last comment. 
The documentation should also state that single quoted aliases (string literal as alias) will prevent that alias from being used in an expression as alias - rather it may be (will be) interpreted as a string literal expression - not a column reference. 

Basically, single quotes are allowed to enclose column aliases, but it is a pretty bad idea to do so.

No, it won't:

mysql> select left(a,1) 'q' from t2;
+------+
| q    |
+------+
| W    | 
| G    | 
| G    | 
| G    | 
+------+
4 rows in set (0.00 sec)

mysql> select left(a,1) 'q' from t2 order by q;
+------+
| q    |
+------+
| G    | 
| G    | 
| G    | 
| W    | 
+------+
4 rows in set (0.00 sec)

As you can see, even if alias was quoted as a string (using single quotes) it can still be used in ORDER BY.

did you consider wildcard characters and scape character a 'quoted alias'?

select id  from test.tab1 where id = 1;  -- returns '1'

-- variations 
select id as 'myalias' from test.tab1 where id = 1 order by 'myalias';  -- 1
select id as 'myalias' from test.tab1 where id = 1 order by `myalias`;  -- 1
select id as 'my%alias' from test.tab1 where id = 1 order by `my%alias`; -- 1
select id as `my%alias` from test.tab1 where id = 1 order by 'my%alias'; -- 1

... but this is then inconsistent I think as "\%" = "%" and "\a" = "a"

select id as 'my\%alias' from test.tab1 where id = 1 order by `my%alias`; -- Unknown column 'my%alias' in 'order clause'
select id as `my\alias` from test.tab1 where id = 1 order by 'myalias'; -- 1

It's about identifiers, shouldn't they use backticks (except in ANSI mode when it should be doublequotes)?
The problem is that afaik MySQL has been accepting regular quotes for alias identifiers, as Serg's demo has also proven. Apps will be using it, so now we're stuck with it.
There's still some parsing weirdness though, I'm with Peter on that.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Clarified that aliases may be quoted as identifiers or as strings here:

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html