Bug #60816 Cannot pass NULL to an INOUT procedure parameter
Submitted: 9 Apr 2011 11:13 Modified: 3 Apr 2013 1:58
Reporter: Lukas Eder Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.15 OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Tags: INOUT parameter, null, stored procedure

[9 Apr 2011 11:13] Lukas Eder
Description:
I cannot pass NULL to a stored procedure's INOUT parameter using JDBC's CallableStatement. This can be reproduced with both versions 5.1.14 and 5.5.8

The error I get is this:
------------------------
java.sql.SQLException: Incorrect integer value: 'null' for column 'x' at row 7
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356)
	at com.mysql.jdbc.CallableStatement.execute(CallableStatement.java:877)

I haven't tested this for any other data types

How to repeat:
SQL code
----
DROP PROCEDURE IF EXISTS test1;
DROP PROCEDURE IF EXISTS test2;

CREATE PROCEDURE test1 (INOUT x INTEGER)
BEGIN
	SET x = x + 1;
END 

CREATE PROCEDURE test2 (x INTEGER, OUT y INTEGER)
BEGIN
	SET y = x + 1;
END 

Java code
----
// I can correctly pass a primitive type int to the procedure
CallableStatement call = connection.prepareCall("{ call test.test1(?) }");
call.setInt(1, 1);
call.registerOutParameter(1, Types.INTEGER);
call.execute();

// Here I correctly retrieve "2"
System.out.println(call.getInt(1));

// This also works when I split IN and OUT parameters into two parameters
call = connection.prepareCall("{ call test.test2(?, ?) }");
call.setInt(1, 1);
call.registerOutParameter(2, Types.INTEGER);
call.execute();
System.out.println(call.getInt(2));

// When calling the split parameter procedure, I can also pass NULL like this:
call = connection.prepareCall("{ call test.test2(?, ?) }");
call.setNull(1, Types.INTEGER);
call.registerOutParameter(2, Types.INTEGER);
call.execute();

// This correctly returns "0"
System.out.println(call.getInt(2));

// This correctly returns "true"
System.out.println(call.wasNull());

// But this doesn't work
call = connection.prepareCall("{ call test.test1(?) }");
call.setNull(1, Types.INTEGER);
call.registerOutParameter(1, Types.INTEGER);

// The error happens here:
call.execute();
[11 Apr 2011 14:17] Tonci Grgin
Hi Lukas and thanks for your report.

Alas, it is not as complete as you think. Please provide full test case including the connection string and do note MySQL server version you run against.
[11 Apr 2011 14:37] Lukas Eder
The server versions I discovered this with, are in the bug's header (5.1.14 and 5.5.8).

This is how I connect to the database:

Class.forName(driver);
Connection connection = DriverManager.getConnection(
  "jdbc:mysql://localhost/test", "root", "");
[11 Apr 2011 14:47] Tonci Grgin
Thank you Lukas.

What happens if you put useInformationSchema=true in your connection string?
[11 Apr 2011 14:47] Tonci Grgin
And, btw, "Version" field is for *c/J* version, not server version so please note it somewhere.
[11 Apr 2011 16:11] Lukas Eder
This has been observed with Connector/J versions 5.1.10 and 5.1.15
And with the MySQL database version 5.1.14 and 5.5.8
[11 Apr 2011 16:16] Lukas Eder
This JDBC URL produces the same issue:

jdbc:mysql://localhost/test?useInformationSchema=true

Or when I create the connection like this:

Properties properties = new Properties();
properties.setProperty("user","root");
properties.setProperty("password","");
properties.setProperty("useInformationSchema","true");

connection = DriverManager.getConnection("jdbc:mysql://localhost/test", properties);
[13 Apr 2011 8:08] Tonci Grgin
Lukas, we have several points here:
  o Since NULL is not "" or 0, your actual return value is NULL (not 1 as you might expected):
mysql> DELIMITER $$
mysql> CREATE PROCEDURE bug60816(INOUT x INTEGER)
       BEGIN
         SET x = x + 1;
       END$$
Query OK, 0 rows affected (0.14 sec)

mysql> DELIMITER ;
mysql> SET @x = NULL;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL bug60816(@x);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

  o Next, we have JDBC specs not completely clear on INOUT parameter being NULL in http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html. As can be seen, only 
7.1.5     Retrieving NULL Values as OUT Parameters
is covered which makes sense in your test with 2 parameters.

There is also a http://forums.mysql.com/read.php?39,412249,412249 so I will have to consult on this more.
[13 Apr 2011 8:16] Lukas Eder
Tonci, thanks for analysing this. NULL is what I would expect to be returned, not 1. Maybe there was a misunderstanding somewhere... 

This is the expected behaviour on the failing execution after a fix:

assertEqual(0, call.getInt(1));
assertTrue(call.wasNull());
[26 Jul 2011 17:06] Sveta Smirnova
Set to "Verified" as case was confirmed. Test case will be attached.
[26 Jul 2011 17:10] Sveta Smirnova
test case for the testsuite

Attachment: bug60816.java (text/plain), 1.52 KiB.

[27 Mar 2013 10:33] Andrey Denisov
When this error is corrected?
Library version 5.1.24 and still not fixed
[27 Mar 2013 13:01] Alexander Soklakov
Hi,

The reason of this exception is that driver sets variable to 'null' literal instead of real NULL value. So this isn't the matter of JDBC spec of something, just the bug in driver impl.

Patch is quite simple, I guess the fix will be included to next release.
[28 Mar 2013 8:51] Andrey Denisov
Hi
Thank you, we will wait for the next release
[3 Apr 2013 1:58] Paul DuBois
Noted in 5.1.25 changelog.

An exception occurred if NULL was passed to a stored procedure INOUT
parameter.