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

[10 Mar 2008 22:46] Larry Brunelle
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.
[10 Mar 2008 22:47] Larry Brunelle
Exhibits failure mode

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

[10 Mar 2008 22:48] Larry Brunelle
Exhibits contrasting success with workaround

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

[19 Mar 2008 10:21] Tonci Grgin
Test case

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

[19 Mar 2008 10:28] Tonci Grgin
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.
[19 Mar 2008 15:45] Bugs System
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
[7 Jul 2008 10:38] Tony Bedford
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.
[30 Jul 2008 14:53] Bugs System
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
[18 Sep 2008 16:47] Sveta Smirnova
Bug #39510 was marked as duplicate of this one.