MySQL Bugs: #101823: MySQL JDBC driver incorrectly reports bigint as BigInteger in generated keys

Bug #101823	MySQL JDBC driver incorrectly reports bigint as BigInteger in generated keys
Submitted:	1 Dec 2020 17:47	Modified:	25 Nov 2022 16:14
Reporter:	Puf Puf	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.22, 8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From Stack Overflow:
https://stackoverflow.com/questions/65078455/mysql-jdbc-driver-incorrectly-reports-bigint-...

MySQL (like most SQL databases) does not call its 64-bit integer datatype a "long". It's a "bigint". Ok, fine.

But the MySQL JDBC driver (8.0.22) incorrectly returns this column as java.math.BigInteger, which it isn't.

Yes, I could convert the BigInteger into a long in my application code, but this turns out to be really inconvenient for some internal, application-specific reasons.

Is there any way to force the driver to return the correct data type, which should be a java.lang.Long?

Update

Per the comment, yes, it appears that regular select statements do return the column as a java.lang.Long. My bad.

Where it is failing is when it returns generated keys after an insert. There, the column comes back as a BigInteger. 

How to repeat:
Here's the code:

public static void main(String[] args) throws SQLException {

    Connection conn = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false&allowPublicKeyRetrieval=true&user=root");

    Statement state = conn.createStatement();
    state.executeUpdate("drop table if exists foo");
    state.executeUpdate("create table foo (id bigint auto_increment, name varchar(255), primary key(id))");
    state.executeUpdate("insert into foo (name) values ('bob')", Statement.RETURN_GENERATED_KEYS);

    ResultSet rs = state.getGeneratedKeys();
    rs.next();

    Object obj = rs.getObject(1);

    System.out.println(obj);
    System.out.println(obj.getClass());

    conn.close();

}

returns:

1
class java.math.BigInteger

Hello!

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
The information regarding generated keys is not directly related to the table primary key in the sense that it does not share the same metadata rules. MySQL protocol indicates what has the last insert-id of the executed query independently of how many ids were actually generated and this information is not directly linked to any column in particular (https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html), as such, Connector/J has no alternative other than using the most comprehensive data type in the generated keys result set. Also note that the spec does not make any assumptions regarding the columns or types to return in this result set (https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#getGeneratedKeys--) - for MySQL Connector/J it is BIGINT_UNSIGNED.

Having said that, this report will be closed.

Thanks.