MySQL Bugs: #15617: getString() + Format() returns wrong result

Bug #15617	getString() + Format() returns wrong result
Submitted:	9 Dec 2005 8:06	Modified:	13 May 2006 9:06
Reporter:	Wai Wong	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A field containing decimcal values, when returned with format(), gives wrong result through JDBC connector.  It gives correct result when using mysql directly.

Please note that the problem will disappear if the server is running in latin1 and the connection string does not contains characterencoding=utf8, and if the "distinct" is removed in the query.

Regards,

Wai Wong.

How to repeat:
In mysql cmd, do the followings:

mysql> create table TA ( FA decimal(15,2) );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into TA values (12345678), (22345678), (22345678);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> describe TA;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| FA    | decimal(15,2) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show create table TA;
+-------+--------------------------------------------------------------------------------------------+
| Table | Create Table                                                                               |
+-------+--------------------------------------------------------------------------------------------+
| TA    | CREATE TABLE `TA` (
  `FA` decimal(15,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select distinct format(FA,0) as abc from TA;
+------------+
| abc        |
+------------+
| 12,345,678 |
| 22,345,678 |
+------------+
2 rows in set (0.01 sec)

All ok.

When using the JSP program at the end to test, it returns:

12,345,
22,345,

===================================
File content for testing
===================================

<%@ page language="java" import="java.sql.*" %>
<%
response.setContentType("TEXT/HTML; charset=utf8");
request.setCharacterEncoding("utf8");

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/tic", "default", "");
try {
    ResultSet rs = conn.createStatement().executeQuery("select distinct format(FA,0) as abc from TA;");
    while (rs.next()) out.println(rs.getString("abc") + "<BR>");
    rs.close();
    conn.close();
}catch(Exception e ){
    out.println(e.getMessage());
}
%>

Forgotten to mention that my mysql server is 5.0.16 running in redhat.

Wai Wong

Wai, I wrote a simple testcase and run it against mysql 5.0.16-max/connector-j 3.1.12 on linux , java 1.5.0_04 and it returns proper values:

java -cp .:my.jar:junit.jar  Bug15617
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.16-max
12,345,678
22,345,678

Time: 1,075

OK (1 test)

testcase is attached, could you check it in your environment?

the testcase

Attachment: Bug15617.java (text/x-java), 976 bytes.

Thanks for your prompt response.

Sorry that I am not familiar with junit, as I work with jsp mostly.  I download the 3.8.1.zip from www.junit.org, put it junit.jar and mysql-connector-java-3.1.12-bin.jar in the same folder with your Bug15617.java, then in that folder run:

bash>java -cp .:mysql-connector-java-3.1.12-bin.jar:junit.jar:/opt/java/lib Bug15617

but get the error:

Exception in thread "main" java.lang.NoClassDefFoundError: Bug15617

Regards,

Wai Wong.

You need to compile it first into the .class file by javac command.

1) create subdirectory testsuite

2) put the file BaseTestCase.java (which is included into the connector-j source distribution and is used for testing this driver. I attached it to this bugreport for your convenience) into the testcase directory

3) compile Bug15617.java
javac -cp .:mysql-connector-java-3.1.12-bin.jar:junit.jar Bug15617.java

4) start mysql server

5) run Bug15617.class

java -cp .:mysql-connector-java-3.1.12-bin.jar:junit.jar Bug15617

BaseTestCase.java

Attachment: BaseTestCase.java (text/x-java), 11.69 KiB.

Sorry,
 of course BaseTestCase.java must be in the 'testsuite' directory (not in 'testcase' directory)

Here is my result, same problem occurs.

[root@dev03 testsuite]# java -cp .:mysql-connector-java-3.1.12-bin.jar:junit.jar:/opt/java/lib/ Bug15617
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.16-standard-log
12,345,
22,345,

Time: 2.187

OK (1 test)

[root@dev03 testsuite]#

so. If it works for me and doesnt work for you,  looks like we have some difference in the environment.

Could you provide your output of `locale` command?

My locale is zh_TW.Big5.  But even if I change it to us-ascii, the result is the same.

Anyway, here is the output.

# locale
LANG=zh_TW.Big5
LC_CTYPE="zh_TW.Big5"
LC_NUMERIC="zh_TW.Big5"
LC_TIME="zh_TW.Big5"
LC_COLLATE="zh_TW.Big5"
LC_MONETARY="zh_TW.Big5"
LC_MESSAGES="zh_TW.Big5"
LC_PAPER="zh_TW.Big5"
LC_NAME="zh_TW.Big5"
LC_ADDRESS="zh_TW.Big5"
LC_TELEPHONE="zh_TW.Big5"
LC_MEASUREMENT="zh_TW.Big5"
LC_IDENTIFICATION="zh_TW.Big5"
LC_ALL=

Something that may be related.  My colleage reports to me that, in some case, the getScale() of ResultSetMetaData returns incorrect values.  Unfortunately, we cannot reproduce this easily.

Another supplement.  I am not surprised if you cannot reproduce this in your environment.  In our own environment, we have found that this problem occurs in some cases but not in others.  As they involve complicated joins and where's, I tried to reproduce with this simple case hoping that it will help.  Hence if this does not show any problem in your environment, it just confirms that the problem is not deterministic.

With this in mind, I tested the case in another two machines (also in redhat).  One has problem and another is ok.

I found the reason for the difference.  When I use default-character-set=latin1, there is no problem.  When I use default-character-set=utf8, the problem occurs.

default-character-set where? in the table definition?

In the my.cnf.

Yes, hot the same result as you:

java -cp .:junit.jar:my.jar Bug15617
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.17-max
12,345,
22,345,

Time: 2,086

OK (1 test)

verified with my.cnf that contains
[mysqld]
default-character-set=utf8

latest testcase that I used

Attachment: Bug15617.java (text/x-java), 1.21 KiB.

In mysql client:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.17-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select distinct format(FA,0) as abc from TA;
+------------+
| abc        |
+------------+
| 12,345,678 |
| 22,345,678 |
+------------+
2 rows in set (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> select distinct format(FA,0) as abc from TA;
+---------+
| abc     |
+---------+
| 12,345, |
| 22,345, |
+---------+
2 rows in set (0.00 sec)

Note that format is bad from MySQL command line, so not a connector/j issue.

Duplicated with bug#16678 FORMAT gives wrong result if client run with default-character-set=utf8