MySQL Bugs: #21521: # Symbols not allowed in column/table names.

Bug #21521	# Symbols not allowed in column/table names.
Submitted:	8 Aug 2006 22:01	Modified:	24 Aug 2006 12:08
Reporter:	Robert Hammond
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	5.0.0 Alpha	OS:	Microsoft Windows (Windows)
Assigned to:		Target Version:

Description:
The newest version of the connector does not allow the use of # sysmbols inside of back
ticks (`).  This is supported by MySQL Server and all past versions of the connector.

While debugging I found that Statement.TokenizeSql(String sql) checks for # to denote an
inlineComment and then waits for a delimiter before continuing to place the string into
the StringBuilder sqlPart.  It does not check to see if the # symbol is inside of back
ticks (`).

How to repeat:
CREATE TABLE `purchase orders` (
  `PO#` int(11) NOT NULL auto_increment,
  `PODate` date default NULL,
   PRIMARY KEY  (`PO#`)
);
INSERT INTO `purchase orders` ( `PO#`, `PODate` )
  VALUES ( NULL, '2006-01-01' );

conn.Open()
Dim selectStr As String = "SELECT `PO#` AS PurchaseOrderNumber, `PODate` AS OrderDate " &
_
"FROM `purchase orders`;"

Dim cmd As New MySql.Data.MySqlClient.MySqlCommand(selectStr, conn)
Dim table As New DataTable

Dim adap As New MySql.Data.MySqlClient.MySqlDataAdapter(cmd)
adap.Fill(table) <-- Throws MySqlException("#42S22Unknown column 'PO' in 'field list'")

Suggested fix:
Add a boolean variable which is set when the first ` is found and unset when the matching
is found.  This variable would disallow checks for inline comments.

Hi Robert and thanks for your problem report. I've decided to put it into "Verified" state
even though connector is alpha because:
  * Connector is officially released
  * Code works in prior released version (1.0.7)
  * I can't find any notes in changelog regarding this

            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString =
"DataSource=10.192.192.99;Database=test;UserID=root;Password=;PORT=3307";
            conn.Open();
            MySqlCommand command = new MySqlCommand();
            command.CommandText = "SELECT `PO#` AS PurchaseOrderNumber, `PODate` AS
OrderDate FROM `purchase orders`;";
            command.CommandType = CommandType.Text;
            command.Connection = (MySqlConnection)conn;
            MySqlDataReader dr = command.ExecuteReader();
            dr.Read(); << Error
            MessageBox.Show(dr.GetName(0));
            dr.Close();
            MessageBox.Show("Ready");
            command.Dispose();
            conn.Close();

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10687

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10688

fixed in 1.0.8 and 5.0.1

An entry has been added to the 1.0.8 and 5.0.1 changelog.