Bug #40076 "Functions Return String" option does not set the proper encoding for the string

• Submitted: 16 Oct 2008 14:19
• Modified: 10 Nov 2008 15:28
• Reporter: Bogdan Degtyariov
• Status: Closed
• Category: Connector/Net
• Severity: S3 (Non-critical)
• Version: 5.2.3
• OS: Microsoft Windows
• Assigned to: Bogdan Degtyariov

[16 Oct 2008 14:19] Bogdan Degtyariov

Description:
Even though the option ("Functions Return String=true;") is set, the result of SELECT
DES_DECRYPT() contains "??" instead of national symbols ("Trädgårdsvägen" is displayed
as "Tr?dg?rdsv?gen"). 

This option makes Connector/NET to ignore VARBINARY type and set VARCHAR to the result
metadata. However, setting the correct type is not enough because the result received
from the server is processed using ASCII encoding instead of default connection encoding.

How to repeat:
        private void button1_Click(object sender, EventArgs e)
        {
            string result = "";
            MySqlConnection con = null;
            try {
                con = new MySqlConnection("server=localhost;database=test;user
id=****;pwd=****;Pooling=false;Functions Return String=true;"); //
                con.Open();

                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = con;

                cmd.CommandText = "DROP TABLE IF EXISTS cnet_binary_test;";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "CREATE TABLE cnet_binary_test (id int auto_increment
primary key," + 
                                  "vc1 varchar(64), vc2 varchar(64))DEFAULT
CHARSET=Latin1;";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "INSERT INTO cnet_binary_test (vc1) VALUES
('Trädgårdsvägen')";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "UPDATE cnet_binary_test SET vc2=des_encrypt(vc1)";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT vc1, des_decrypt(vc2) FROM cnet_binary_test";
                MySqlDataReader reader = cmd.ExecuteReader();
                if( reader.Read() ) {
                    result = reader.GetString(0);
                    result += "\r\n" + reader.GetString(1);
                }
            reader.Close();
            } catch( Exception ex ) {
                string error = ex.Message.ToString();
                MessageBox.Show(error);
            } finally {
                if( con != null )
                con.Close();
            }
            MessageBox.Show(result);
        }
    }
}

Suggested fix:
--- NativeDriver.cs.old	2008-10-16 14:13:37.000000000 +0200
+++ NativeDriver.cs	2008-10-16 14:13:40.000000000 +0200
@@ -724,11 +724,17 @@
                 stream.ReadInteger(2); // reserved
             }
 
-            if (charSets != null && field.CharacterSetIndex != -1)
+            if (charSets != null && field.CharacterSetIndex != -1 &&
+                !(connection.Settings.FunctionsReturnString && field.CharacterSetIndex
== 63))
             {
-                CharacterSet cs = CharSetMap.GetChararcterSet(Version, (string)
charSets[field.CharacterSetIndex]);
+                CharacterSet cs = CharSetMap.GetChararcterSet(Version,
(string)charSets[field.CharacterSetIndex]);
                 field.MaxLength = cs.byteCount;
-                field.Encoding = CharSetMap.GetEncoding(version, (string)
charSets[field.CharacterSetIndex]);
+                field.Encoding = CharSetMap.GetEncoding(version,
(string)charSets[field.CharacterSetIndex]);
+            }
+            else if (connection.Settings.FunctionsReturnString &&
field.CharacterSetIndex == 63)
+            {
+                /* If binary, use default connection encoding */
+                field.Encoding = Encoding;
             }
 
             return field;

[20 Oct 2008 21:28] 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/56617

[20 Oct 2008 21:29] Reggie Burnett

- fixed bug where specifying 'functions return string=yes' would cause strings to be
returned using the 'binary' charset which would not properly render some characters.  Now
the connection character set is used. (bug #40076)  

Fixed in 5.2.4

[10 Nov 2008 15:28] Tony Bedford

Added an entry to the 5.2.4 changelog:

The connection string option Functions Return String did not set the correct encoding for
the result string. Even though the connection string option Functions Return String=true;
is set, the result of SELECT DES_DECRYPT() contained “??” instead of the correct
national character symbols.