Bug #20306 ResultSet getShort method gives wrong value for UNSIGNED TINYINT column

• Submitted: 7 Jun 2006 4:28
• Modified: 26 Jul 2006 18:17
• Reporter: Yoshinori Matsunobu
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 3.1.13
• OS: Any (Any)
• CPU Architecture: Any

[7 Jun 2006 4:28] Yoshinori Matsunobu

Description:
ResultSet#getShort() for UNSIGNED TINYINT column returns wrong(+256) value.
C/J Version: 3.1.13
MySQL Version:after 4.1 (when using ServerSide PreparedStatement)

How to repeat:
mysql> CREATE TABLE tbl1 ( col1 TINYINT UNSIGNED);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO tbl1 VALUES (2);
Query OK, 1 row affected (0.06 sec)

import java.sql.*;

public class TestUnsignedTinyInt {
	public static void main(String args[]) throws Exception {

		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection(
				"jdbc:mysql://127.0.0.1/test", "user1", "pass1");

		PreparedStatement pstmt = conn
				.prepareStatement("SELECT col1 FROM tbl1");
		ResultSet rs = pstmt.executeQuery();
		rs.next();

		// correct
		System.out.println(rs.getInt(1));

		// wrong
		System.out.println(rs.getShort(1));

	}
}

---
Result:
2
258

Suggested fix:
I wrote a patch, and checked it works correctly.

*** ResultSet.java      Wed Jun  7 12:47:47 2006
--- ResultSet.java      Wed Jun  7 12:48:45 2006
***************
*** 7899,7905 ****
                case MysqlDefs.FIELD_TYPE_TINY:
                        byte tinyintVal = getNativeByte(columnIndex + 1);

!                       if (!f.isUnsigned()) {
                                return tinyintVal;
                        }

--- 7899,7905 ----
                case MysqlDefs.FIELD_TYPE_TINY:
                        byte tinyintVal = getNativeByte(columnIndex + 1);

!                       if (!f.isUnsigned() || tinyintVal >= 0) {
                                return tinyintVal;
                        }

[9 Jun 2006 7:26] Tonci Grgin

Hi Yoshinori and thanks for your bug report.
Verified as described by reporter with connector 3.1.13, JDK 1.6, MySQL 5.0.23-debug on Suse, client Win XP SP2:
compile:
run:
Connected
2
258
BUILD SUCCESSFUL (total time: 3 seconds)

[16 Jun 2006 21:50] 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/7786

[17 Jul 2006 19:08] Tobias F

There was another bug in the getShort function for UNSIGNED TINYINT.

CREATE TABLE Test ( i TINYINT UNSIGNED );
insert into Test values (255);

import java.sql.DriverManager;
import com.mysql.jdbc.*;
class Test
{
        public static void main(String args[]) {
                try {
                        Connection con;
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        con = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1/mydb", "myuser", "mypwd");
                        String selectStmt =
                                "SELECT i FROM Test";
                        PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(selectStmt);
                        ResultSet rs = (ResultSet) pstmt.executeQuery();
                        if(rs.next()) {
                                short i = rs.getShort(1);
                                System.out.println(i);
                        }
                } catch(Exception e) {
                        e.printStackTrace();
                }
        }
}

I get back an exception:
java.sql.SQLException: '255' in column '1' is outside valid range for the datatype TINYINT.
	at com.mysql.jdbc.ResultSet.throwRangeException(ResultSet.java:6559)
	at com.mysql.jdbc.ResultSet.getNativeByte(ResultSet.java:7500)
	at com.mysql.jdbc.ResultSet.getNativeByte(ResultSet.java:7444)
	at com.mysql.jdbc.ResultSet.getNativeShort(ResultSet.java:7900)
	at com.mysql.jdbc.ResultSet.getNativeShort(ResultSet.java:7868)
	at com.mysql.jdbc.ResultSet.getShort(ResultSet.java:4620)
	at Test.main(Test.java:16)

If I use 'int i = rs.getInt(1);' instead I get the right value (255) for i.

This bug is also removed by the bugfix http://lists.mysql.com/commits/7786

Thanks for that.