| Bug #52187 | FunctionsReturnString=true messes up decimal separator | ||
|---|---|---|---|
| Submitted: | 18 Mar 2010 16:02 | Modified: | 22 Apr 2010 14:54 |
| Reporter: | Jorge Bastos | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 6.2.2, trunk | OS: | Windows |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[21 Mar 2010 11:23]
Jorge Bastos
extra info on this, I've found the source of the problem.
----------
create table a (a decimal(5,2) not null;
create table b (b decimal(5,2) not null;
insert into a values (1.25);
insert into b values (5.99);
select *,(select b from b) as field_b from a;
.net code:
----------
Df_Region.NumberFormat.CurrencySymbol = "€"
Df_Region.NumberFormat.CurrencyDecimalSeparator = ","
Df_Region.NumberFormat.CurrencyDecimalDigits = 2
Df_Region.NumberFormat.CurrencyGroupSeparator = "."
Df_Region.NumberFormat.NumberDecimalSeparator = ","
Df_Region.NumberFormat.NumberDecimalDigits = 2
Df_Region.NumberFormat.NumberGroupSeparator = "."
Df_Region.NumberFormat.NegativeSign = "-"
dim mycon as new mysqlconnection("my cs here")
dim mycmd as new mysqlcommand("select a,(select b from b) as field_b from a;",mycon)
dim myrd as mysqldatareader
myrd=mycmd.executereader
myrd.read
msgbox(myrd("a"))
msgbox(myrd("field_b"))
-------------
on field "a", the returned value will be: 1,25
which is correct, acording to my decimal separator defenition.
on field "field_b", the returned value is: 5.99
which is wrong, is SHOULD be 5,99 as my decimal separator is a "," and not a "."
can you reproduce it there?
[26 Mar 2010 12:26]
Jorge Bastos
Hi Toncy, Were you able to reproduce this?
[6 Apr 2010 12:03]
Tonci Grgin
Jorge, been busy, checking now.
[6 Apr 2010 12:06]
Tonci Grgin
mysql> select *,(select b from b) as field_b from a; Field 1: `a` Catalog: `def` Database: `test` Table: `a` Org_table: `a` Type: NEWDECIMAL Collation: binary (63) Length: 7 Max_length: 4 Decimals: 2 Flags: NOT_NULL NO_DEFAULT_VALUE Field 2: `field_b` Catalog: `def` Database: `` Table: `` Org_table: `` Type: NEWDECIMAL Collation: binary (63) <<< Length: 7 Max_length: 4 Decimals: 2 Flags: BINARY <<< +------+---------+ | a | field_b | +------+---------+ | 1.25 | 5.99 | +------+---------+ 1 row in set (0.00 sec)
[6 Apr 2010 12:32]
Tonci Grgin
private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
private void bnBug52187_Click(object sender, EventArgs e)
{
Console.WriteLine("Default culture={0}", Thread.CurrentThread.CurrentCulture);
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("pt-PT");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = nothing special
conn.Open();
MySqlCommand cmdQry = new MySqlCommand();
cmdQry.Connection = conn;
cmdQry.CommandTimeout = 0;
cmdQry.CommandText = "DROP TABLE IF EXISTS bug52187a";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "DROP TABLE IF EXISTS bug52187b";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "CREATE TABLE bug52187a (a decimal(5,2) not null)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "CREATE TABLE bug52187b (b decimal(5,2) not null)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "insert into bug52187a values (1.25)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "insert into bug52187b values (5.99)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "select *,(select b from bug52187b) as field_b from bug52187a";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmdQry);
da.Fill(dt);
DisplayData(dt);
dt.Clear();
dt.Dispose();
da.Dispose();
cmdQry.Dispose();
conn.Close();
Console.WriteLine("Done.");
}
Produces:
Default culture=hr-HR
a = 1,25
field_b = 5,99
============================
Done.
[6 Apr 2010 12:36]
Tonci Grgin
Jorge, as you can see from my test case, I can not repeat this behavior... What could be a problem for you is shown in command line client: Field 2: `field_b` Catalog: `def` Database: `` Table: `` Org_table: `` Type: NEWDECIMAL Collation: binary (63) <<< Length: 7 Max_length: 4 Decimals: 2 Flags: BINARY <<< `field_b` is marked as BINARY and with binary charset in MySQL server 5.1.x (this will be fixed for 5.5 I think). So you might want to add respect binary flags=false"; to your connection string and retest.
[6 Apr 2010 16:48]
Jorge Bastos
Hi Toncy, For this bug and for #52227, i'm going to try with the CS option "respect binary flags=false" to se if i can resolve both problems. You mentioned that i changed OS and mysql server version but did not, only changes the .net connector and jumped from vs2003 to vs2008. I'm on trip now and I'm going to test this and let you know if it resolves my problem.
[7 Apr 2010 7:35]
Tonci Grgin
Ok Jorge, be sure to check on my code for setting the locale as it is different than yours.
[13 Apr 2010 16:47]
Jorge Bastos
Hi Toncy Again, Going to add a test sample in vs2008 also, you can see that you'll get diferrent results, in the case, different decimal separator. Playing with the ConnectionString options doesn't help, but if I go back to the old 1.0.11.x version, it works just Fine. Can you reproduce it?
[13 Apr 2010 16:47]
Jorge Bastos
VS2008 test project
Attachment: Bug 52187.zip (application/octet-stream, text), 218.43 KiB.
[14 Apr 2010 5:43]
Tonci Grgin
Jorge, I will take a peak when time allows. In the meantime, did you changed your way of addressing CultureInfo to match mine:
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("pt-PT");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
[14 Apr 2010 10:14]
Jorge Bastos
Toncy, Didn't test what you asked for, but found the source of the problem. IF: FunctionsReturnString=true is used, that's what is causing the problem, like bug #52227 that is not respecting charset defenitions, on this case, it is not respecting the locale/decimal separator defenitions... Can you reproduce it?
[14 Apr 2010 10:34]
Tonci Grgin
Jorge, you were right: a = 1,25 field_b = 5.99 <<< ============================ Done. Ok, so we have finally got to the bottom of problem.
[14 Apr 2010 11:02]
Tonci Grgin
Setting FunctionsReturnString=true changes the locale-defined decimal separator. Workaround is to set it to false.
Console.WriteLine("Default culture={0}", Thread.CurrentThread.CurrentCulture);
CultureInfo curCulture = Thread.CurrentThread.CurrentCulture;
CultureInfo curUICulture = Thread.CurrentThread.CurrentUICulture;
CultureInfo c = new CultureInfo("pt-PT");
Thread.CurrentThread.CurrentCulture = c;
Thread.CurrentThread.CurrentUICulture = c;
MySqlConnection conn = new MySqlConnection();
conn.ConnectionString = "DataSource=**;Database=**;UserID=**;Password=**;PORT=**;allow user variables = true;FunctionsReturnString=true";
conn.Open();
MySqlCommand cmdQry = new MySqlCommand();
cmdQry.Connection = conn;
cmdQry.CommandTimeout = 0;
cmdQry.CommandText = "DROP TABLE IF EXISTS bug52187a";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "DROP TABLE IF EXISTS bug52187b";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "CREATE TABLE bug52187a (a decimal(5,2) not null)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "CREATE TABLE bug52187b (b decimal(5,2) not null)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "insert into bug52187a values (1.25)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "insert into bug52187b values (5.99)";
cmdQry.ExecuteNonQuery();
cmdQry.CommandText = "select *,(select b from bug52187b) as field_b from bug52187a";
DataTable dt = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmdQry);
da.Fill(dt);
DisplayData(dt);
dt.Clear();
dt.Dispose();
da.Dispose();
cmdQry.Dispose();
conn.Close();
Console.WriteLine("Done.");
private static void DisplayData(System.Data.DataTable table)
{
foreach (System.Data.DataRow row in table.Rows)
{
foreach (System.Data.DataColumn col in table.Columns)
{
Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
}
Console.WriteLine("============================");
}
}
[16 Apr 2010 12:23]
Jorge Bastos
When could i expect this on trunk to test?
[16 Apr 2010 17:43]
Tonci Grgin
Jorge, don't know. It is up to Reggie to decide.
[21 Apr 2010 17:26]
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/106283 806 Reggie Burnett 2010-04-21 changed how we respond when the user selects 'functions return string=true'. Now we no longer force the type to varstring but just let the underlying type come through but we strip the binary flag (bug #52187)
[21 Apr 2010 17:48]
Reggie Burnett
fixed in 6.0.6, 6.1.4, 6.2.4, and 6.3.2
[22 Apr 2010 13:20]
Jorge Bastos
Perfect, Compiled the trunk sources, and works OK now! Thank you Reggie.
[22 Apr 2010 14:54]
Tony Bedford
An entry has been added to the 6.0.6, 6.1.4, 6.2.4, 6.3.2 changelogs: If FunctionsReturnString=true was used in the connection string, the decimal separator (according to locale) was not interpreted.
Description: Howdy, I have a different behavior with the 6.2.2x that i had with 1.0.11x. How to repeat: I have the application currentculture set to PT-PT, that works with the decimal separator " , ". The following code, will result with "65.00", and it should result with "65,00". Is this a bug, or any change on the connection/something that needs to be inserted on the connection string? ----------- Dim c As New param Dim mycon As New MySqlConnection(c.LerMy) mycon.Open() sql = "SELECT sum(quotaano-(vp12+vp11+vp10+vp9+vp8+vp7+vp6+vp5+vp4+vp3+vp2+vp1)) as sal FROM rqtbl WHERE socio='6679'" Dim mycmd As New MySqlCommand(sql, mycon) Dim myrd As MySqlDataReader myrd = mycmd.ExecuteReader myrd.Read() TextBox1.Text = myrd("sal")