Unknown data type in prepared statement with integer parameter comparison

[Jesse Long]

Hi,

At work we use a framework that translates queries in the
object-orientated form to sql, eg.

[implied: select from Company where]
Company.Branch.ContactPerson.PhoneNumber = 'xxx'

to

select company.* from company
inner join branch
on company.id = branch.company_id
inner join contact_person
on branch.contact_person_id = contact_person.id
where contact_person.phone_number = 'xxx'

The framework parses the object orientated query, and builds prepared
statements. Integer literals in the object-orientated query are inserted
as parameters in the jdbc prepared statement, and set using
PreparedStatement.setInt(). I recently saw someone perform a query that
failed:

[implied: select from someObject where]
(1 = 1)

Which was translated to:
select * from sometable where ? = ?
followed by
preparedStatement.setInt(1, 1);
preparedStatement.setInt(2, 1);

H2 threw an exception about unknown data type during optimisation of the
"? = ?" comparison.

Is this type of comparison supported? It works fine on MySQL. I haven't
tested PostgreSQL. We could change the framework to embed the integer
literals in the SQL query string, but we prefer using prepared statement
parameters.

Here is a simple test case and exception.

public class Main
{
public static void main(String[] args) throws
ClassNotFoundException, SQLException
{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:/tmp/x",
"sa", "");
PreparedStatement pth = conn.prepareStatement("select * from
information_schema.catalogs where ? = ?");
pth.setInt(1, 1);
pth.setInt(2, 1);
pth.executeQuery().close();
pth.close();
conn.close();
}
}

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Unknown data
type: "(?1 = ?2)"; SQL statement:
select * from information_schema.catalogs where ? = ? [50004-126]
at org.h2.message.Message.getSQLException(Message.java:110)
at org.h2.message.Message.getSQLException(Message.java:121)
at org.h2.message.Message.getSQLException(Message.java:74)
at org.h2.expression.Comparison.optimize(Comparison.java:178)
at org.h2.command.dml.Select.prepare(Select.java:720)
at org.h2.command.Parser.prepareCommand(Parser.java:236)
at org.h2.engine.Session.prepareLocal(Session.java:416)
at org.h2.engine.Session.prepareCommand(Session.java:377)
at
org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1066)
at
org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
at
org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:233)
at Main.main(Main.java:24)

Thanks,
Jesse

[Thomas Mueller]

Hi,

> At work we use a framework that translates queries in the object-orientated
> form to sql, eg.

What framework do you use?

> Which was translated to:
> select * from sometable where ? = ?
> followed by
> preparedStatement.setInt(1, 1);
> preparedStatement.setInt(2, 1);

Instead, I would use:
select * from sometable where 1 = ?
preparedStatement.setInt(1, 1);

> Is this type of comparison supported?

No.

Regards,
Thomas

[Jesse Long]

Hi Thomas,

Thanks for the reply.

Thomas Mueller wrote:
> Hi,
>
>> At work we use a framework that translates queries in the object-orientated
>> form to sql, eg.
>>
> What framework do you use?
>

It's our own internal framework, not published on the net yet. Basically
Data Access Layer code generation with integrated object orientated
query language and object orientated language integrated querying (no
strings).

>> Which was translated to:
>> select * from sometable where ? = ?
>> followed by
>> preparedStatement.setInt(1, 1);
>> preparedStatement.setInt(2, 1);
>>
>
> Instead, I would use:
> select * from sometable where 1 = ?
> preparedStatement.setInt(1, 1);
>

Thanks, we'll do that.

>> Is this type of comparison supported?
>>
> No.
>

OK, we'll work around that.

Thanks,
Jesse