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

[8 Aug 2006 20:01] Robert Hammond
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.
[14 Aug 2006 6:39] Tonci Grgin
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();
[21 Aug 2006 16:39] Bugs System
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
[21 Aug 2006 16:42] Bugs System
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
[21 Aug 2006 16:45] Reggie Burnett
fixed in 1.0.8 and 5.0.1
[24 Aug 2006 10:08] MC Brown
An entry has been added to the 1.0.8 and 5.0.1 changelog.