Description:
When using ANY_VALUE in a select on an unsigned INT field with a GROUP BY the datatype reported in java appears to change
As per https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-type-conversions.html#:....
For an unsigned int field I expect to get a Long java type, but instead get a BigInteger
As a comparison, the aggregate function MAX seems to behave better and have a consistent datatype
How to repeat:
Create a test table:
CREATE TABLE `test_any_value` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`a` int unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO test_any_value (a) VALUES(1);
INSERT INTO test_any_value (a) VALUES(2);
Create a basic java application:
package test.mysql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public final class DatabaseTypeTest {
public static void main(final String[] args) throws SQLException {
final String user = "your_user";
final String password = "your_password";
final Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/lmp?useSSL=false&requireSSL=false&allowPublicKeyRetrieval=true&verifyServerCertificate=false&serverTimezone=UTC", user, password);
final String sql = "SELECT ANY_VALUE(id) as any_id, max(id) as max_id FROM test_any_value GROUP BY a limit 1";
final PreparedStatement statement = connection.prepareStatement(sql);
final ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println("Any value java type: " + resultSet.getObject(1).getClass().getName() + "\nMax value java type: " + resultSet.getObject(2).getClass().getName());
}
}
}
