Regression with CREATE ALIAS - Parameter "#2" is not set

[froque]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StoreProcedure {

    public static void main(String[] args) throws SQLException {

        try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:db1", "sa", "")) {
            try (var statement = conn.createStatement()) {
                statement.executeUpdate("CREATE ALIAS alias_with_parameter FOR \"" + StoreProcedure.class.getCanonicalName() + ".aliasWithParameter\";");
            }

            try (var statement = conn.prepareCall("call alias_with_parameter(?)")) {
                statement.setLong(1, 2L);
                System.out.println(statement.execute());
                final var resultSet = statement.getResultSet();
                while (resultSet.next()){
                    System.out.println(resultSet.getLong(1));
                }
            }

        }
    }

    public static ResultSet aliasWithParameter(Connection conn, long id) throws SQLException {
        return conn.createStatement().executeQuery( "select 42 * " + id);
    }
}

With H2 1.4.200

/home/froque/.jdks/adopt-openjdk-11.0.12/bin/java -classpath /home/froque/workspace/testes/h2database-bisect/out/production/h2database-bisect:/home/froque/Downloads/h2-2019-10-14/h2/bin/h2-1.4.200.jar -javaagent:/snap/intellij-idea-community/342/lib/idea_rt.jar=38125:/snap/intellij-idea-community/342/bin -Dfile.encoding=UTF-8 StoreProcedure
true
84

Process finished with exit code 0

With H2 2.0.202

/home/froque/.jdks/adopt-openjdk-11.0.12/bin/java -classpath /home/froque/workspace/testes/h2database-bisect/out/production/h2database-bisect:/home/froque/Downloads/h2-2021-11-25/h2/bin/h2-2.0.202.jar -javaagent:/snap/intellij-idea-community/342/lib/idea_rt.jar=37165:/snap/intellij-idea-community/342/bin -Dfile.encoding=UTF-8 StoreProcedure
Exception in thread "main" org.h2.jdbc.JdbcSQLDataException: Parameter "#2" is not set; SQL statement:
 call alias_with_parameter(?)  [90012-202]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:646)
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:477)
	at org.h2.message.DbException.get(DbException.java:223)
	at org.h2.message.DbException.get(DbException.java:199)
	at org.h2.expression.Parameter.checkSet(Parameter.java:75)
	at org.h2.command.Prepared.checkParameters(Prepared.java:181)
	at org.h2.command.CommandContainer.query(CommandContainer.java:254)
	at org.h2.command.Command.executeQuery(Command.java:187)
	at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:248)
	at StoreProcedure.main(StoreProcedure.java:17)

Process finished with exit code 1

[katzyn]

Parser incorrectly reads the same parameter twice and assigns a new number to it.

conn.prepareCall("select * from alias_with_parameter(?)") can be used as a workaround. Actually it will be better not to use CALL with table functions at all, but because it should work according to documentation, this issue needs to be fixed anyway.

[katzyn]

There is an another workaround: call alias_with_parameter(?1) also works.

[andreitokar]

As they say, reliance on exception handling for a normal flow control is rarely a good thing. 😄

[katzyn]

That's why H2 should never allow expression and a table in the same context.

Normal code flow is also affected, for example, in a semicolon-separated mix of DDL and DML commands. We don't support parameters in DDL, but parameters in DML can be numbered incorrectly.

We need to assign numbers to parameters immediately, it should solve these issues. After recent changes it should be relatively simple. When current H2 splits commands, they use tokens for their own parsing after execution of previous DDL commands. It means we can assign numbers directly in tokenizer and store them in these tokens.