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

Bug #21521	# Symbols not allowed in column/table names.
Submitted:	8 Aug 2006 20:01	Modified:	24 Aug 2006 10:08
Reporter:	Robert Hammond	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.0 Alpha	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any

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.