Bug #32251 Connector/J doesn't report failure when calling function with extra parameters

• Submitted: 9 Nov 2007 21:38
• Modified: 21 Dec 2015 17:16
• Reporter: Mark Matthews
• Status: Not a Bug
• Category: Connector / J
• Severity: S2 (Serious)
• OS: Any
• Assigned to: Alexander Soklakov
• CPU Architecture: Any

[9 Nov 2007 21:38] Mark Matthews

Description:
{?= call test_function(?,?)} fails at the server, when it should fail at the client, since we don't have overloading, and the driver can tell that the parameter set doesn't match the signature.

How to repeat:
import java.io.*;
import java.sql.*;

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

	private static void runTest(String url) throws Exception
	{
		// Connect to the database
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection(url);

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

		// Create test table and function
		Statement statement = connection.createStatement();
		statement.executeUpdate("DROP TABLE IF EXISTS test_table_2");
		statement.executeUpdate("DROP TABLE IF EXISTS test_table_1");
		statement.executeUpdate("CREATE TABLE test_table_1 (value_1 BIGINT PRIMARY KEY) ENGINE=InnoDB");
		statement.executeUpdate("INSERT INTO test_table_1 VALUES (1)");
		statement.executeUpdate("CREATE TABLE test_table_2 (value_2 BIGINT PRIMARY KEY, " +
				" FOREIGN KEY (value_2) REFERENCES test_table_1 (value_1) ON DELETE CASCADE) ENGINE=InnoDB");
		statement.executeUpdate("DROP FUNCTION IF EXISTS test_function");
		System.out.println("creating function ...");
		statement.executeUpdate("CREATE FUNCTION test_function(value BIGINT) RETURNS BIGINT " +
				"DETERMINISTIC MODIFIES SQL DATA BEGIN " +
				"INSERT INTO test_table_2 VALUES (value); RETURN value; END;"); 

		// Prepare the function call
		System.out.println("preparing function call ...");
		CallableStatement callable = connection.prepareCall("{? = call test_function(?,?)}");
		callable.registerOutParameter(1,Types.BIGINT);

		System.out.println("calling function with too many arguments; this should throw an exception ...");
		callable.setLong(2,1);
		callable.setLong(3,2);
		callable.executeUpdate();

		System.out.println("impossible; we should never get here.");

		// Cleanup test objects
		statement.executeUpdate("DROP TABLE test_table_2");
		statement.executeUpdate("DROP TABLE test_table_1");
		statement.executeUpdate("DROP FUNCTION test_function");

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

[7 Feb 2011 11:06] Tonci Grgin

Problem still there.

[7 Feb 2011 11:36] Tonci Grgin

This should have been caught in PreparedStatement.java, checkBounds.

[21 Dec 2015 17:16] Filipe Silva

Posted by developer:
 
Can't be fixed.

There's no way to tell that the parameter set needs to match the function signature without fully parsing the call statement, which we don't. The placeholders in a function call can be part of an expression or another function call for example.

E.g.: func_1(v) and func_2(v1, v2) can be called as conn.prepareCall("{? = call func_1(func_2(?, ?))}");