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

Bug #35199	Parse error addressing stored function returning bigint
Submitted:	10 Mar 2008 22:46	Modified:	7 Jul 2008 10:38
Reporter:	Larry Brunelle	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	parse error addressing stored function returning bigint

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

Bug #39510 was marked as duplicate of this one.