Parameter data type is data type of other operand

[GoogleCodeExporter]

An operation like this:

2.1 + 1 should result in 3.1

In h2 the result is 3.

The workaround is to use an expression like:

2.1 + 1.0.

In this case the result is 3.1

Here is a sample program to demonstrate this issue:

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class H2Bug {
    public static void main(String[] args) throws Exception {
        Class.forName("org.h2.Driver").newInstance();
        Connection connection = DriverManager.getConnection("jdbc:h2:mem:");
        PreparedStatement stmt = connection.prepareStatement("select ? + 1");
        stmt.setBigDecimal(1, new BigDecimal("2.1"));
        ResultSet rs = stmt.executeQuery();
        rs.next();
        System.out.println("select 2.1 + 1 returns 3 instead of 3.1: " +
rs.getBigDecimal(1));
    }
}

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

Run the attached program, it prints 3 instead of 3.1

What is the expected output? What do you see instead?

Expected output is 3.1.  Instead, 3 is printed.

What version of the product are you using? On what operating system, file
system, and virtual machine?

h2-1.2.136.jar on ubuntu 10.04

Do you know a workaround?

Yes, one must enter all values in expression using 
a decimal point.

How important/urgent is the problem for you?

Critical

In your view, is this a defect or a feature request?

Defect

Please provide any additional information below.

Original issue reported on code.google.com by dmitri.m...@gmail.com on 3 Jun 2010 at 9:55

Attachments:

• H2Bug.java

[GoogleCodeExporter]

Hi,

H2 works like HSQLDB and Apache Derby for this case. The problem is that while
parsing, the database can't detect the data type of the "?". It decides the 
data type
is "int" (the same as the 1 that is added). MySQL and PostgreSQL return the 
'correct'
result. I don't know if there is an easy way to behave like MySQL and 
PostgreSQL. One
option would be to document that parameters use the same data type as the other
operation (but that wouldn't solve your problem).

Test case:

package db;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;

public class TestMultiDb {

   public static void main(String[] a) throws Exception {
       DeleteDbFiles.execute("data/test", "test", true);
       test("com.mysql.jdbc.Driver",
               "jdbc:mysql://localhost:3306/test",
               "sa", "sa");
       test("org.postgresql.Driver",
               "jdbc:postgresql:test",
               "sa", "sa");
       test("org.apache.derby.jdbc.EmbeddedDriver",
               "jdbc:derby:data/test;create=true", 
               "sa", "sa");
       test("org.hsqldb.jdbcDriver",
               "jdbc:hsqldb:data/test",
               "sa", "");
       test("org.h2.Driver",
               "jdbc:h2:data/test2", 
               "sa", "sa");
   }

   static void test(String driver,
           String url, String user,
           String password) throws Exception {
       Class.forName(driver);
       Connection conn = DriverManager.getConnection(
               url, user, password);
       Statement stat = conn.createStatement();
       try {
           stat.execute("drop table test");
       } catch (SQLException e) {
           // ignore
       }
       System.out.println(url);
       stat.execute(
               "create table test(id int)");
       stat.execute("insert into test values(1)");
       PreparedStatement prep;
       prep = conn.prepareStatement(
               "select ?+1 from test");
       prep.setBigDecimal(1, new BigDecimal("2.1"));
       ResultSet rs = prep.executeQuery();
       rs.next();
       System.out.println(rs.getString(1));
       prep = conn.prepareStatement(
           "select ?+1.0 from test");
        prep.setBigDecimal(1, new BigDecimal("2.1"));
        rs = prep.executeQuery();
        rs.next();
        System.out.println(rs.getString(1));
       conn.close();
   }
}

Original comment by thomas.t...@gmail.com on 5 Jun 2010 at 1:41

[GoogleCodeExporter]

I changed the summary, I hope it's clearer what the problem is. "Decimal part 
lost if
other operand not using decimal point" is a bit misleading, because the problem 
only
occurs if one of the operands is a parameter.

Original comment by thomas.t...@gmail.com on 5 Jun 2010 at 1:44

• Changed title: Parameter data type is data type of other operand

[GoogleCodeExporter]

Hi Thomas,
Thanks for the quick response.
I respect your opinion if you consider this a design choice.
I still think it would be more intuitive for numbers to never loose 
the decimal portion.  You may consider a mode for H2
where it would only use BigDecimal as its internal numeric representation
that would allow arbitrary precision arithmetic.  I guess in theory 
BigDecimal could be slower than double or int, but
it seemed to me from looking at the H2 code that was adding numbers
wrappers are used anyway (or perhaps it's my mistake of using autoboxing 
of an int).  But if wrappers are always used then there is no need to worry 
about
performance.
By the way congratulations on H2 performance.  A quick comparison with 
using sqlite from Java shows that H2 expressions are about 5 times faster 
(well maybe this has to do with switching between java and c code).
Thanks,
Dmitri

Original comment by dmitri.m...@gmail.com on 7 Jun 2010 at 5:24

[GoogleCodeExporter]

> it would be more intuitive for numbers 
> to never loose the decimal portion.

I agree. The current behavior is unfortunate. I will keep this issue open. For 
me this is not a very urgent problem however. I want to change it eventually, 
but will not have time to do that in the near future. Patches are welcome of 
course!

Original comment by thomas.t...@gmail.com on 13 Jun 2010 at 4:54

[GoogleCodeExporter]

I have added the issue to the roadmap at http://h2database.com/html/roadmap.html

I know the priority is quite low, and I'm afraid I will not have time to 
implement this change in the near future. Patches are welcome of course.

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 6:43

• Changed state: InRoadmap