ORDER BY CASE ... expression is ignored, when bind variables are not cast

[GoogleCodeExporter]

I have discovered some casting "bugs". Here's how to reproduce #1:

Create test database
--------------------
create table x (i int);
insert into x values(1), (2), (3), (4), (5);

This works as expected:
-----------------------
PreparedStatement stmt = connection.prepareStatement(
  "select * from x order by case i when 3 then 1 end asc");
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
  System.out.println(rs.getInt(1));
}

Result:
1, 2, 4, 5, 3 (nulls first, then 3)

So does this (the cast is important):
-------------------------------------
PreparedStatement stmt = connection.prepareStatement(
  "select * from x order by case i when ? then cast(? as int) end asc");
stmt.setInt(1, 3);
stmt.setInt(2, 1);
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
  System.out.println(rs.getInt(1));
}

But this doesn't (without casting):
-----------------------------------

PreparedStatement stmt = connection.prepareStatement(
  "select * from x order by case i when ? then ? end asc");
stmt.setInt(1, 3);
stmt.setInt(2, 1);
ResultSet rs = stmt.executeQuery();

while (rs.next()) {
  System.out.println(rs.getInt(1));
}

Result (no error, and no ordering is applied):
1, 2, 3, 4, 5

Original issue reported on code.google.com by lukas.eder@gmail.com on 13 Jul 2011 at 5:09

[GoogleCodeExporter]

The 'else' part is missing, which actually means 'else null'.

Usually, for 'case ... then x else y end', the data type is the higher
data type of x and y. (the order of types is defined in Value.getOrder).

The problem is that the data type of the expression 'case ... then ? else null 
end'
is 'null'. The next version of H2 will not allow this and throw an exception 
instead.

Original comment by thomas.t...@gmail.com on 20 Aug 2011 at 10:38

• Changed state: Accepted

[GoogleCodeExporter]

I see. Thanks for addressing this

Original comment by lukas.eder@gmail.com on 22 Aug 2011 at 8:08

[GoogleCodeExporter]

I will keep the current behavior for now. Patches are always welcome of course.

Original comment by thomas.t...@gmail.com on 22 Sep 2011 at 4:31

• Changed state: Fixed