MySQL Bugs: #35199: Parse error addressing stored function returning bigint

Bug #35199	Parse error addressing stored function returning bigint
Submitted:	10 Mar 23:46	Modified:	7 Jul 12:38
Reporter:	Larry Brunelle
Status:	Closed
Category:	Connector/J	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:		Target Version:
Tags:	parse error addressing stored function returning bigint
Triage:	D2 (Serious)

Description:
In calling a stored function returning a bigint, an exception is
encountered beginning 
"java.sql.SQLException: java.lang.NumberFormatException: For input string:"
followed by text of the stored function beginning somewhere following the
argument list.   (This is pretty astonishing to see!)

The sole change of replacing the return type with 
decimal(22,0) works around the issue - but this doesn't agree with the
use of types which can be autoincremented.

How to repeat:
Run the attached test files
testFails.java    and
testSucceeds.java

They are identical save for the return type change.

Exhibits failure mode

Attachment: testFails.java (application/octet-stream, text), 5.04 KiB.

Exhibits contrasting success with workaround

Attachment: testSucceeds.java (application/octet-stream, text), 5.04 KiB.

Test case

Attachment: TestBug35199.java (text/java), 4.58 KiB.

Hi Larry and thanks for your report.

Verified as described on latest c/J 5.1 sources:
.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.58-pb1083-log
java.vm.version         : 1.5.0_12-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_12-b04
os.name                 : Windows XP
os.version              : null
sun.management.compiler : HotSpot Client Compiler
E
Time: 0,406
There was 1 error:
1) testBug35199(testsuite.simple.TestBug35199)java.lang.NumberFormatException: For input
string: "20)
    MODIFIES SQL DATA
    DETERMINISTIC
    COMMENT 'This function provides lookup/insert/update behavior for table t'
BEGIN DECLARE test_id_l bigint; IF test_code_v IS NULL OR external_system_id_v IS NULL
THEN RETURN NULL; END IF; SELECT test_id INTO test_id_l FROM test35199 WHERE test_code =
test_code_v AND external_system_id = external_system_id_v; IF test_id_l IS NULL THEN
INSERT INTO test35199 (test_id"
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:48)
	at java.lang.Integer.parseInt(Integer.java:456)
	at java.lang.Integer.valueOf(Integer.java:553)
	at com.mysql.jdbc.DatabaseMetaData$TypeDescriptor.<init>(DatabaseMetaData.java:247)
	at com.mysql.jdbc.DatabaseMetaData.getCallStmtParameterTypes(DatabaseMetaData.java:1652)
	at
com.mysql.jdbc.DatabaseMetaData.getProcedureOrFunctionColumns(DatabaseMetaData.java:4120)
	at com.mysql.jdbc.DatabaseMetaData.getProcedureColumns(DatabaseMetaData.java:4057)
	at com.mysql.jdbc.CallableStatement.determineParameterTypes(CallableStatement.java:809)
	at com.mysql.jdbc.CallableStatement.<init>(CallableStatement.java:614)
	at com.mysql.jdbc.CallableStatement.getInstance(CallableStatement.java:502)
	at com.mysql.jdbc.ConnectionImpl.parseCallableStatement(ConnectionImpl.java:3881)
	at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3965)
	at com.mysql.jdbc.ConnectionImpl.prepareCall(ConnectionImpl.java:3939)
	at testsuite.simple.TestBug35199.testBug35199(TestBug35199.java:94)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at testsuite.simple.TestBug35199.main(TestBug35199.java:111)

There appears to be an error in parsing function return parameter type. Fetching from I__S
retrieves BIGINT(20) (although I removed (20) from definition) which is discovered as
BIGINT thus not in need of (m,d). But (m,d) still gets parsed throwing the error you see.

DataBaseMetaData.java 1652, TypeDescriptor returnDescriptor = new
TypeDescriptor(returnsDefn, null);
returnsDefn =
bigint(20)
    MODIFIES SQL DATA ... which is not correct.

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

An entry has been added to the 5.1.6 Changelog:

In calling a stored function returning a bigint, an exception is encountered beginning: 

java.sql.SQLException: java.lang.NumberFormatException: For input string:

followed by the text of the stored function starting after the argument list.

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