Bug #100852 ANY_VALUE gives unexpected type with GROUP BY

• Submitted: 15 Sep 2020 9:40
• Modified: 15 Sep 2020 11:43
• Reporter: Craig Archer
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.20, 8.0.21
• OS: Windows
• CPU Architecture: Any

[15 Sep 2020 9:40] Craig Archer

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());
        }
    }

}

[15 Sep 2020 11:43] MySQL Verification Team

Hello Craig Archer,

Thank you for the report and test case.

regards,
Umesh

[26 Nov 2022 0:26] Filipe Silva

Posted by developer:
 
This is not a Connector/J bug. Connector/J simply maps whatever MySQL types it gets to Java types or objects. In this case MySQL is actually sending a different metadata information when ANY_VALUE is used, which is not supposed to happen, as explicitly said in the documentation (https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_any-value).

This behavior was still observed in latest MySQL version, 8.0.31. Moving to a server bug category.