Bug #118002 The setFetchSize() method in the Statement class may have a potential bug

• Submitted: 17 Apr 5:25
• Modified: 27 Nov 18:03
• Reporter: 策 吕
• Status: Verified
• Category: Connector / J
• Severity: S2 (Serious)
• Version: mysql-connector-j-9.2.0 && mysql Ver 8.0
• OS: Windows
• CPU Architecture: Any
• Tags: A potential bug when use the method named setFetchSize(int) in Statement Class

[17 Apr 5:25] 策 吕

Description:
Version:mysql-connector-j-9.2.0 && mysql Ver 8.0.36 for Win64 on x86_64 (MySQL Community Server - GPL)
When the setFetchSize(int) method provided in the Statement class is used continuously, an exception occurs: java.sql.SQLException: Illegal value for setFetchSize().
When we use stmt.setFetchSize() for the third time, an exception occurs (line 264 of the complete code). The pseudo code is as follows:
stmt = conn.createStatement(1003, 1007, 1);
stmt.setFetchSize(2147483647);
stmt.setFetchSize(-2147483648);
stmt.setFetchSize(2147483647); // ERROR 
// java.sql.SQLException: Illegal value for setFetchSize().

How to repeat:
The complete code is as follows:

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class TestJDBC {

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Savepoint savepoint = null;

        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb0?user=root&password=XXXX");
        conn.setAutoCommit(false);
        stmt = conn.createStatement(1003, 1007, 1);
        System.out.println(stmt.getQueryTimeout());
        stmt.setQueryTimeout(30);
        System.out.println(stmt.getFetchSize());
        stmt.setFetchSize(2147483647);
        System.out.println(stmt.getMaxRows());
        stmt.setMaxRows(100);
        stmt.setEscapeProcessing(true);
        System.out.println(stmt.getResultSetType());
        System.out.println(stmt.getResultSetConcurrency());
        System.out.println(stmt.getResultSetHoldability());

        System.out.println(stmt.executeUpdate("DROP TABLE IF EXISTS table0_0;"));
        System.out.println(stmt.executeLargeUpdate(
                "CREATE TABLE table0_0(Id VARCHAR(5) PRIMARY KEY, Value0 TINYINT, Value1 DECIMAL, Value2 BIGINT);", 1));
        savepoint = conn.setSavepoint();

        stmt.addBatch("INSERT INTO table0_0 VALUES ('ID1', 1, 10.5, 100);");
        stmt.addBatch("INSERT INTO table0_0 VALUES ('ID2', 2, 20.5, 200);");
        System.out.println(Arrays.toString(stmt.executeBatch()));
        rs = stmt.executeQuery("SELECT * FROM table0_0;");
        System.out.println("rs.isFirst: " + rs.isFirst()); //OB、Mysql 输出false
        System.out.println("rs.isLast: " + rs.isLast()); //OB、Mysql 输出false
        //rs.afterLast(); //结果集类型为 TYPE_FORWARD_ONLY OB没有报错，Mysql报错
        //rs.beforeFirst(); //这OB直接执行了，没有报错，Mysql报错抛出了异常
        //rs.afterLast(); //这OB直接执行了，没有报错，Mysql报错抛出了异常
        //System.out.println("疑惑!" + rs.last()); //这里也离谱，OB返回的是true，Mysql还是抛出异常 Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY
        //System.out.println("疑惑！" + rs.first()); //这里更离谱，OB返回的是true，Mysql也是抛出异常 Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY
        //System.out.println("疑惑！" + rs.previous());//Mysql这里直接抛出异常，但是OB这里只是输出false（createStatement(1003, 1007, 1)），mysql：Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.
        System.out.println(rs.next());
        System.out.println(rs.getObject("Id"));
        System.out.println(rs.getObject("Value0"));
        System.out.println(rs.getObject("Value1"));
        System.out.println(rs.getObject("Value2"));
        try{
            System.out.println(rs.isFirst());
        } catch (SQLException e) {
            System.out.println(e);
        }
        try{
            System.out.println(rs.isLast());
        } catch (SQLException e) {
            System.out.println(e);
        }
        System.out.println(rs.isBeforeFirst());
        System.out.println(rs.isAfterLast());

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        System.out.println(stmt.executeUpdate("UPDATE table0_0 SET Value0 = 10 WHERE Id = 'ID1';"));

        try {
            conn.rollback(savepoint);
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            conn.commit();
        } catch (SQLException e) {
            System.out.println(e);
        }

        conn.setAutoCommit(false);
        savepoint = conn.setSavepoint("SP1");
        System.out.println(stmt.executeUpdate("INSERT INTO table0_0 VALUES ('ID3', 3, 30.5, 300);"));
        System.out.println(stmt.executeUpdate("INSERT INTO table0_0 VALUES ('ID4', 4, 40.5, 400);"));
        rs = stmt.executeQuery("SELECT * FROM table0_0 WHERE Value0 > 2;");
        //try {
            //rs.setFetchSize(-2147483648); //这里OB报错，mysql没有报错
        //} catch (SQLException e) {
        //    System.out.println("ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!   OB : " + e);
        //}
        System.out.println(rs.next());
        System.out.println(rs.getObject(1));
        System.out.println(rs.getObject(2));
        System.out.println(rs.getObject(3));
        System.out.println(rs.getObject(4));

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        pstmt = conn.prepareStatement("INSERT INTO table0_0 VALUES (?, ?, ?, ?);");
        pstmt.setObject(1, "ID5");
        pstmt.setObject(2, 5);
        pstmt.setObject(3, 50.5);
        pstmt.setObject(4, 500);
        System.out.println(pstmt.executeUpdate());
        pstmt.clearParameters();
        try {
            pstmt.addBatch();
        } catch (SQLException e) {
            System.out.println(e);
        }
        System.out.println(Arrays.toString(pstmt.executeBatch()));

        rs = stmt.executeQuery("SELECT * FROM table0_0 WHERE Value0 = 5;");
        System.out.println(rs.next());

        try {
            rs.updateObject("Value0", 15);
            rs.updateRow();
        } catch (SQLException e) {
            System.out.println(e);//
        }

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        System.out.println(stmt.getQueryTimeout());
        stmt.setQueryTimeout(60);
        System.out.println(stmt.getMaxRows());
        stmt.setMaxRows(200);
        System.out.println(stmt.getFetchSize());
        //try {
            stmt.setFetchSize(-2147483648);
        //} catch (SQLException e) {
        //    System.out.println("ERROR2!!!!!!!!!");
        //    System.out.println(e);
        //}
        rs = stmt.executeQuery("SELECT * FROM table0_0;");
        try {
            rs.setFetchDirection(1001);
        } catch (SQLException e) {
            System.out.println(e);
        }
        //JDK 17 中给出的解释：如果发生数据库访问错误；在关闭的结果集上调用此方法或结果集类型为TYPE_FORWARD_ONLY且提取方向不是FETCH_FORWARD
        System.out.println("rs.getFetchDirection() : " + rs.getFetchDirection()); //这里OB输出的是1002！！！！！！Mysql输出的是1000（第一行->下一行），1002：ResultSet.FETCH_UNKNOWN表示结果集的处理方向由 JDBC 驱动程序和数据库系统决定，这里虽然没问题但是感觉怪怪的
        System.out.println(rs.next());
        try {
            rs.previous(); // OB和Mysql都不支持因为是 Result set of type is ResultSet.TYPE_FORWARD_ONLY
        } catch (SQLException e) {
            System.out.println(e);
        }
        try {
            rs.beforeFirst(); // OB和Mysql都不支持因为是 Result set of type is ResultSet.TYPE_FORWARD_ONLY
        } catch (SQLException e) {
            System.out.println(e);
        }
        try {
            rs.afterLast(); // OB和Mysql都不支持因为是 Result set of type is ResultSet.TYPE_FORWARD_ONLY
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            System.out.println(stmt.executeUpdate("INSERT INTO table0_0 VALUES ('ID1', 1, 10.5, 100);"));
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            System.out.println(stmt.executeUpdate("UPDATE table0_0 SET Value0 = 200 WHERE Id = 'ID2';"));
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            stmt.executeQuery("SELECT * FROM non_existent_table;");
        } catch (SQLException e) {
            System.out.println(e);
        }

        conn.setAutoCommit(false);
        savepoint = conn.setSavepoint("SP2");
        System.out.println(stmt.executeUpdate("UPDATE table0_0 SET Value1 = 15.5 WHERE Id = 'ID3';"));

        try {
            conn.rollback(savepoint);
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            conn.commit();
        } catch (SQLException e) {
            System.out.println(e);
        }

        rs = stmt.executeQuery("SELECT * FROM table0_0 WHERE Value0 > 1 OR Id != 'ID2';");
        //try{
            //rs.setFetchSize(2147483647);
        //} catch (Exception e){
        //    System.out.println("ERROR3!!!!!!!!!!!!!!!!!");
        //    System.out.println(e);
        //}
        System.out.println(rs.next());
        System.out.println(rs.getObject("Id"));
        System.out.println(rs.getObject("Value0"));
        System.out.println(rs.getObject("Value1"));
        System.out.println(rs.getObject("Value2"));
        try {
            System.out.println("rs.ifFirst(): " + rs.isFirst()); //这里Mysql输出了true
            System.out.println("rs.isLast(): " + rs.isLast()); //这里mysql输出了false
        } catch (Exception e) {
            System.out.println(e + "这两个方法OB报错"); //java.sql.SQLException: Invalid operation on STREAMING ResultSet
        }
        System.out.println("ERROR 1 " + rs.isBeforeFirst()); //这里mysql、OB输出的是false
        System.out.println("ERROR 2 " + rs.isAfterLast()); // 这里mysql、OB输出的是false
        try {
            rs.close(); //这里OB直接执行
        } catch (SQLException e) {
            System.out.println(e);
        }

        System.out.println(stmt.getResultSetConcurrency());
        System.out.println(stmt.getResultSetType());
        System.out.println(stmt.getResultSetHoldability());

        pstmt = conn.prepareStatement("SELECT * FROM table0_0 WHERE Value0 > ?");
        pstmt.setObject(1, 2);
        rs = pstmt.executeQuery();
        System.out.println(rs.next());
        System.out.println(rs.getObject(1));
        System.out.println(rs.getObject(2));
        System.out.println(rs.getObject(3));
        System.out.println(rs.getObject(4));

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        System.out.println(stmt.getQueryTimeout());
        stmt.setQueryTimeout(120);
        System.out.println(stmt.getMaxRows());
        stmt.setMaxRows(500);
        System.out.println(stmt.getFetchSize());
        try {
            stmt.setFetchSize(2147483647);
        } catch (Exception e){
            System.out.println("ERROR BIG !!!!!!!!!!!!!!!!!" + e);
            //System.out.println(e);
        }
        rs = stmt.executeQuery("SELECT * FROM table0_0;");
        try{
            rs.setFetchDirection(1001); //Mysql和OB都报错了，OB报错：Invalid operation. Allowed direction are ResultSet.FETCH_FORWARD and ResultSet.FETCH_UNKNOWN
        } catch (SQLException e) {
            System.out.println(e);
        }
        System.out.println(rs.next());
        try{
            rs.previous(); // 这里也是
        } catch (Exception e){
            System.out.println("mysql报错：Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY.,OB 报错Invalid operation on STREAMING ResultSet。");

        }
        try {
            rs.beforeFirst();
        } catch (SQLException e) {
            System.out.println("Mysql 报错Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY. OB 报错Invalid operation on STREAMING ResultSet");

        }
        try{
            rs.afterLast(); // 这里也是
        } catch (SQLException e) {
            System.out.println(" OB1 !!! Invalid operation on STREAMING ResultSet            mysql报错：Operation not allowed for a result set of type ResultSet.TYPE_FORWARD_ONLY");

        }

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        conn.setAutoCommit(false);
        savepoint = conn.setSavepoint("SP3");
        System.out.println(stmt.executeUpdate("UPDATE table0_0 SET Value2 = 150 WHERE Id = 'ID4';"));

        try {
            conn.rollback(savepoint);
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            conn.commit();
        } catch (SQLException e) {
            System.out.println(e);
        }

        rs = stmt.executeQuery("SELECT * FROM table0_0 WHERE Value1 > 20.0 AND Value2 < 400;");
        try{
            rs.setFetchSize(-2147483648);
        } catch (Exception e){
            System.out.println("ERROR4!!!!!!!!!!!!!!!!!    OB 的 rs.setFetchSize(-2147483648) 报错" + e);
        }
        System.out.println(rs.next());
        System.out.println(rs.getObject("Id"));
        System.out.println(rs.getObject("Value0"));
        System.out.println(rs.getObject("Value1"));
        System.out.println(rs.getObject("Value2"));
        try {
            System.out.println(rs.isFirst());
        } catch (Exception e){
            System.out.println("rs.isFirst() : OB Invalid operation on STREAMING ResultSet" + e);
           // System.out.println(e);
        }
        try {
            System.out.println(rs.isLast());
        } catch (Exception e){
            System.out.println("rs.isLast() : OB Invalid operation on STREAMING ResultSet" + e);
            //System.out.println(e);
        }
        System.out.println(rs.isBeforeFirst());
        System.out.println(rs.isAfterLast());

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            System.out.println(stmt.executeUpdate("UPDATE table0_0 SET Value0 = -200 WHERE Id = 'ID2';"));
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            stmt.executeQuery("SELECT invalid_column FROM table0_0;");
        } catch (SQLException e) {
            System.out.println(e);
        }

//        try {
//            stmt.executeQuery("SELECT * FROM table0_0 WHERE invalid_condition;");
//        } catch (SQLException e) {
//            System.out.println(e);
//        }

        try {
            rs.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            stmt.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            pstmt.close();
        } catch (SQLException e) {
            System.out.println(e);
        }

        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
    }
}

[17 Apr 6:50] MySQL Verification Team

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[24 Apr 19:20] Axyoan Marcelo

Posted by developer:
 
Hello 策 吕,

Thank you for your report. The exception you are seeing is actually caused by these lines in your code:

...
 stmt.setMaxRows(500);
 System.out.println(stmt.getFetchSize());
 try {
     stmt.setFetchSize(2147483647);
...

Note the documentation for setMaxRows: "Sets the limit for the maximum number of rows that any ResultSet object generated by this Statementobject can contain to the given number."
This means that you cannot set a fetch size larger than the value you have set for max rows. I hope this clarifies the issue, please let me know if you have any other questions. This report will be rejected as not a bug.

Regards,
Axyoan

[9 May 10:53] 策 吕

Thank you for your response and explanation!

However, I still have concerns and believe this should be considered a bug. I kindly ask you to reconsider the status of this report for the following reasons:

The behavior of the MySQL JDBC driver when handling setFetchSize() and setMaxRows() appears to violate the JDBC specification.

You mentioned:
"This means that you cannot set a fetch size larger than the value you have set for max rows."

But based on my understanding:

setFetchSize() and setMaxRows() are independent settings

According to the JDBC specification:

setMaxRows(int max): sets the maximum number of rows a ResultSet object can return — this is a strict, limiting constraint.

setFetchSize(int rows): provides a hint to the driver about the number of rows that should be fetched from the database at one time — this is only a suggestion, not a constraint.

These two methods serve different purposes and should not interfere with each other.

setFetchSize() is only a performance optimization hint

The intention of setFetchSize() is to help the driver improve performance when processing large datasets. Even if the fetchSize is set larger than maxRows, the driver should internally adjust or ignore the fetch size without throwing an exception.

References:

JDBC Statement.setFetchSize(): https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setFetchSize-int-

JDBC Statement.setMaxRows(): https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html#setMaxRows-int-

In summary, I believe the current behavior contradicts the design intention of the JDBC API. A more robust and compliant implementation would either ignore the fetch size or automatically adjust it, rather than throwing an exception.

I hope you will consider this perspective and reconsider changing the report status accordingly.

[27 Nov 18:03] Filipe Silva

We have reconsidered. This shall be fixed.