MySQL Bugs: #33729: Delimiter for identifier is used as identifier in some cases

Bug #33729	Delimiter for identifier is used as identifier in some cases
Submitted:	7 Jan 2008 22:06	Modified:	8 Oct 2008 10:32
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 6.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Don't make identifier delimiter(s) part of identifier.

How to repeat:
CREATE TABLE t1 (a int) Engine MyISAM;
INSERT INTO t1 VALUES (1);

mysql> SELECT a FROM t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT `a` FROM t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

-- Please note that the backtick delimiter is not used as column name.

mysql> SELECT a - 1 FROM t1;
+-------+
| a - 1 |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT `a` - 1 FROM t1;
+---------+
| `a` - 1 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

-- Please note that the backtick delimiter is used in the column name now.
-- This behavior leads to unexpected results:

mysql> CREATE TABLE t2 SELECT `a` - 1 FROM t1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT `a` - 1 FROM t2;
ERROR 1054 (42S22): Unknown column 'a' in 'field list'

mysql> SHOW CREATE TABLE t2;
+-------+--------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                     |
+-------+--------------------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  ```a`` - 1` bigint(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- Now we have to use the backtick delimiter as part of the column identifier.
-- When a simple column name is used, then the backtick delimiters are ignored,
-- but when a combined column is used, then all of a sudden the backtick
-- delimiter is part of the column name. Not nice!

mysql> SELECT ```a`` - 1` FROM t2;
+---------+
| `a` - 1 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

Suggested fix:
Don't make identifier delimiter(s) part of identifier.

Hakan,

I can see thousands of reasons, why we should forbid it. But on the other hand, I saw lots of questions about weird behaviour with backticks from mostly French guys.

Afaik using backticks is for French or other languages like using 'ä' for us.

Just my two cents ...

Thank you for the bug report.

MySQL CREATE TABLE SELECT derives the name of the table columns from the select list verbatim, unless aliases are provided. This is documented. You may suggest a smarter way to derive column names, i.e. remove quoting from identifiers, and this would be a valid feature request. 
Meanwhile please use aliases instead.

Kostja,

this is a bug.

According to the standard (2003, part 2, 11.3 <table definition>, Syntax Rules):
=============
6) If <as subquery clause> is specified, then:
  a) Let QT be the table specified by the <subquery>.
  ...
  c) Let D be the degree of QT.
  ...
  f) For i, 1 (one) ≤ i ≤ D:
  i) Case:
     1) If <column name list> is specified, then let QCNi be the i-th <column name> in that <column name list>.
     2) Otherwise, let QCNi be the <column name> of the i-th column of QT.
=============

As you can see, as "column name" (as shown by SELECT) is `a`+1, then the column name in the table created by CREATE ... SELECT, *must* be `a`+1.

Now, the column name in SELECT. According to the standard (2003, part 2, 7.12 <query specification>, Syntax Rules):
=============
17) Case:
    a) If the i-th <derived column> in the <select list> specifies an <as clause> that contains a <column name> CN, then the <column name> of the i-th column of the result is CN.
    b) If the i-th <derived column> in the <select list> does not specify an <as clause> and the <value expression> of that <derived column> is a single column reference, then the <column name> of the i-th column of the result is the <column name> of the column designated by the column reference.
    c) Otherwise, the <column name> of the i-th column of the <query specification> is implementation-dependent.
=============

`a`+1 is the case c) - the column name is implementation-dependent, and it can be `a`+1, it's explicitly allowed by the SQL standard.