Bug #24426 Invalid JDBC column types: VARCHAR instead of DOUBLE
Submitted: 19 Nov 2006 8:11 Modified: 20 Nov 2006 19:21
Reporter: Razvan Surdulescu Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.10 OS:Linux (Linux RedHat)
Assigned to: CPU Architecture:Any

[19 Nov 2006 8:11] Razvan Surdulescu
Description:
I have a SQL query that retrieves the ratio of two integer columns from a table. The type of this ratio, in Java, is java.sql.Types.VARCHAR instead of java.sql.Types.DOUBLE (or some other numberic type).

Besides being incorrect, this is also a performance hindrance: I have to end up calling Double.parseDouble() on ResultSet.getString(), which over a table of about 140 million rows essentialy takes twice as long as if I just retrieve the columns raw and divide them in Java myself.

I would be surprised if MySQL does in fact compute the result of this column division as a VARCHAR (instead of an internal numeric type), and I would like the MySQL JDBC driver to return it to java as a numeric type, so I don't have to parse it again.

This happens against MySQL 5.0.27, using Connector/J 3.1.10 on Linux RedHat and Windows XP SP2.

How to repeat:
First, create a MYSQL table to hold the data (note the type of the columns involved):

mysql> create table test(a int, b double);
Query OK, 0 rows affected (0.17 sec)

mysql> insert into test values(1, 2);
Query OK, 1 row affected (0.05 sec)

===

Second, run the following Java code against the database above. This code simply displays the Java type of the SQL columns as retrieved in the query. Since all columns are some ratio involving "a" and "b" from the table above, I would expect all columns to come back as numeric on the Java side. Instead, they sometimes come back as VARCHAR (String):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {
  public static void main(String[] args) throws Exception {
    try {
      // Load the database driver
      Class.forName("org.gjt.mm.mysql.Driver");
    } catch (ClassNotFoundException cnfe) {
      throw new SQLException(cnfe.getMessage());
    }

    Connection conn = DriverManager.getConnection(
      ... jdbc url ...);

    Statement stmt = conn.createStatement();

    ResultSet res = stmt.executeQuery(
      "SELECT " +
      "a / a AS IntOverInt," +
      "b / a AS DoubleOverInt," +
      "b / b AS DoubleOverDouble " +
      "FROM test;");

    for (int c = 1; c <= res.getMetaData().getColumnCount(); c++) {
      System.out.println(
        res.getMetaData().getColumnName(c) + " => " +
        res.getMetaData().getColumnType(c));
    }
    
    res.close();
    stmt.close();
    conn.close();
  }
}

===

Here is the outcome from running the code above (12 = java.sql.Types.VARCHAR, while 8 = java.sql.Types.DOUBLE):

IntOverInt => 12
DoubleOverInt => 8
DoubleOverDouble => 8

Suggested fix:
Fix the JDBC driver to return DOUBLE not VARCHAR when appropriate.
[20 Nov 2006 9:30] Tonci Grgin
Hi Razvan and thanks for your problem report. You are using rather old version of c/J. Can you please upgrade and inform me of result.
[20 Nov 2006 10:38] Tonci Grgin
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Explanation:
MySQL server 5.0.27BK
JDK 1.5.0.07
connector/J 3.1 latest SVN

create table test(a int, b double);
insert into test values(1, 2);

mysql> SELECT a / a AS IntOverInt, b / a AS DoubleOverInt, b / b AS DoubleOverDo
uble FROM test;
Field   1:  `IntOverInt`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     16
Max_length: 6
Decimals:   4
Flags:      BINARY

Field   2:  `DoubleOverInt`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 1
Decimals:   31
Flags:      BINARY NUM

Field   3:  `DoubleOverDouble`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DOUBLE
Collation:  binary (63)
Length:     23
Max_length: 1
Decimals:   31
Flags:      BINARY NUM

+------------+---------------+------------------+
| IntOverInt | DoubleOverInt | DoubleOverDouble |
+------------+---------------+------------------+
|     1.0000 |             2 |                1 |
+------------+---------------+------------------+

Test case result is correct:
IntOverInt => 3
DoubleOverInt => 8
DoubleOverDouble => 8
[20 Nov 2006 10:39] Tonci Grgin
Test case

Attachment: TestBug24426.java (text/x-java), 1.75 KiB.

[20 Nov 2006 17:53] Razvan Surdulescu
That's great to know that it's been fixed (I assume latest SVN means latest source trunk).

When is the next binary release of Connector/J 3.1.x expected to come out with this bug fix?
[20 Nov 2006 19:04] Tonci Grgin
Razvan, just try last GA. I think it was fixed some time ago.
[20 Nov 2006 19:21] Razvan Surdulescu
You're right, I just upgraded to the latest Connector/J and it's working now.

Thanks!