MySQL Bugs: #31823: JDBC setNull throws ArrayIndexOutOfBoundsException

Bug #31823	JDBC setNull throws ArrayIndexOutOfBoundsException
Submitted:	24 Oct 2007 19:29	Modified:	3 Dec 2007 7:20
Reporter:	Larry Brunelle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.0.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	JDBC setNull ArrayIndexOutOfBoundsException

Description:
Problem occurs when a stored FUNCTION (not PROCEDURE) is addressed as a
CallableStatement and setNull() is called to set a field null.  It seems
apparent when the LAST field in the row is set null, at least.

Inspection of code in PreparedStatement.java suggests that the member
array isNull[] is not constructed long enough (this may not be the sole
problem).  The behavior seems not to obtain in PROCEDURES, but FUNCTIONS;
is there an issue with dealing with the return value?

How to repeat:
The following Java code (please insert correct local connection values)
illustrates the problem with output thus:

[machine prompt]$ java TestMySQL
database=MySQL version 5.0.46-enterprise-gpl-log
driver=MySQL-AB JDBC Driver version mysql-connector-java-5.0.8 ( Revision: ${svn.Revision} )
id=1
This next call to setNull() will throw ArrayIndexOutOfBoundsException. Why?
java.lang.ArrayIndexOutOfBoundsException: 2
        at com.mysql.jdbc.PreparedStatement.setNull(PreparedStatement.java:2864)
        at TestMySQL.runTest(TestMySQL.java:52)
        at TestMySQL.main(TestMySQL.java:10)

============================================================================

import java.io.*;
import java.sql.*;

public class TestMySQL
{
    public static void main(String[] args)
    {
        try
            {
                runTest();
            }
        catch(Exception exception)
            {
                exception.printStackTrace();
                System.exit(1);
            }
    }

    private static void runTest() throws Exception
    {
        // Connect to the database
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://<hostname>:<mysql port>/<db/schema name>","<db login>","<db password>");

        // Print connection meta-data
        DatabaseMetaData meta = connection.getMetaData();
        System.out.println("database=" + meta.getDatabaseProductName() + " version " + meta.getDatabaseProductVersion());
        System.out.println("driver=" + meta.getDriverName() + " version " + meta.getDriverVersion());

        // Create test table and function
        Statement statement = connection.createStatement();
        statement.executeUpdate("DROP TABLE IF EXISTS test_table");
        statement.executeUpdate("CREATE TABLE test_table (value_1 BIGINT PRIMARY KEY,value_2 VARCHAR(20)) ENGINE=InnoDB");
        statement.executeUpdate("DROP FUNCTION IF EXISTS add_test_table");
        statement.executeUpdate("CREATE FUNCTION add_test_table(value_1_v BIGINT,value_2_v VARCHAR(20)) RETURNS BIGINT " +
                                "DETERMINISTIC MODIFIES SQL DATA BEGIN INSERT INTO test_table VALUES (value_1_v,value_2_v); " +
                                "RETURN value_1_v; END;");

        // Prepare the function call
        CallableStatement callable = connection.prepareCall("{? = call add_test_table(?,?)}");
        callable.registerOutParameter(1,Types.BIGINT);

        // Add row with non-null value
        callable.setLong(2,1);
        callable.setString(3,"Non-null value");
        callable.executeUpdate();
        System.out.println("id=" + callable.getLong(1));

        // Add row with null value
        callable.setLong(2,2);
        System.out.println("This next call to setNull() will throw ArrayIndexOutOfBoundsException. Why?");
        callable.setNull(3,Types.VARCHAR);
        System.out.println("Well, maybe everything's OK.");
        callable.executeUpdate();
        System.out.println("id=" + callable.getLong(1));
        callable.close();

        // Cleanup test objects
        statement.executeUpdate("DROP TABLE test_table");
        statement.executeUpdate("DROP FUNCTION add_test_table");

        // Free resources
        statement.close();
        connection.close();
    }
}

Test code was provided my Marc Gfeller.

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/36302

Patch tested here today and looks like good output.
We are using the patched connector and await the
containing release.
Thank you!!

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/37212

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/38019

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/38024

A note has been added to the 5.1.6 changelog: 

Using CallableStatement.setNull() on a stored function would throw an ArrayIndexOutOfBounds exception when setting the last parameter to null.