Using Bind Variables - incorrect results

[Vinod]

I am executing dual queries using H2(in memory).

But the results do not seem correct.

---------------------------------

***Without bind variables:

---------------------------------

SELECT 1 - '0.1' FROM DUAL

Output=0 [OracleOutput: 0.9]

---------------------------------

---------------------------------

***Using bind variables:

---------------------------------

SELECT 1 - ? FROM DUAL

stmt.setString(1, "0.1");

Output: 0 [OracleOutput: 0.9]

-- tried double

SELECT 1 - ? FROM DUAL

stmt.setDouble(1, 0.1);

Output: 1 [OracleOutput: 0.9]

SELECT (? - (1 -?) * ?) FROM DUAL

set bind variables ["0.95", "1", "0.025"] using setString.

Output: 1 [OracleOutput: 0.95]

---------------------------------

Am I missing something? I am already using "MODE=Oracle" while creating connection.

Any help would be much appreciated.

[Vinod]

Tried it with the latest h2-1.3.166.jar (earlier I was using 'h2-1.3.160.jar'), and now am getting the following DataConversion exception.

Looks like it is trying to convert 0.1 into an int. Is it possible to convert it into a double when the data is of double type.

org.h2.jdbc.JdbcSQLException: Data conversion error converting "0.1"; SQL statement:

SELECT 1 - '0.1' FROM DUAL [22018-166]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)

at org.h2.message.DbException.get(DbException.java:158)

at org.h2.value.Value.convertTo(Value.java:852)

at org.h2.expression.Operation.getValue(Operation.java:115)

at org.h2.expression.Operation.optimize(Operation.java:317)

at org.h2.command.dml.Select.prepare(Select.java:799)

at org.h2.command.Parser.prepareCommand(Parser.java:218)

at org.h2.engine.Session.prepareLocal(Session.java:415)

at org.h2.engine.Session.prepareCommand(Session.java:364)

at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)

at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)

at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:266)

......

Caused by: java.lang.NumberFormatException: For input string: "0.1"

at java.lang.NumberFormatException.forInputString(Unknown Source)

at java.lang.Integer.parseInt(Unknown Source)

at java.lang.Integer.parseInt(Unknown Source)

at org.h2.value.Value.convertTo(Value.java:809)

... 33 more

[Noel Grandin]

Looks like a bug.
Patches welcome :-)

[Thomas Mueller]

Hi,

I suggest not to use varchar, or cast as appropriate. Another suggestion is to use 1.0 instead of 1 if you want the result to be decimal.

drop table test;

create table test(id int);

insert into test values(0);

select 1 - '0.1' from test;

--> H2 (no matter which mode is used), Data conversion error converting "0.1"

--> PostgreSQL: ERROR: invalid input syntax for integer: "0.1"

--> Derby: Invalid character string format for type INTEGER.

select 1.0 - '0.1' from test;

--> H2, PostgreSQL, Derby: 0.9

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/wwsLFpgcGnYJ.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Vinod]

We have always used setString() to set bind variables for String, int, double, and with Oracle it never complained. Due to this we have a lot of queries with similar cases. I am sure, that I will not be able to modify these queries.

Another thing I tried, was before execution using H2, if the values is of double type, then use setDouble, but that also does not give correct result.

SELECT 1 - ? FROM DUAL

stmt.setDouble(1, 0.1);

Output = 1

If setDouble works, will using setDouble be a good idea?

a) Will have to test to see if it work for complicated cases, like [SELECT (? - (1 -?) * ?) FROM DUAL]

b) Putting an additional check before setting each bind variable to see whether it is a int or double, will impact performance as we execute a lot of queries per request.

A patch to use double instead of integer (only in cases where int conversion fails) would impact less in terms of performance.

What do you suggest?

To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

> I am sure, that I will not be able to modify these queries.

If you write code that only works with Oracle and no other database,
then well, you need to use Oracle. It seems only Oracle works they way
you want it, and no other database I have tested (PostgreSQL, Apache
Derby, HSQLDB, H2). It is not my plan to make H2 100% compatible with
Oracle.

If you know that you want to calculate with a given precision, you
should use that precision in the calculation. In your case that seems
to be (1.0 - ?) and not (1 - ?). That way the database knows it's a
decimal when compiling (preparing) the statement.

Regards,
Thomas

[Vinod]

I understand that my queries are more inclined towards oracle side as we have been using that. Now we are trying to execute dual queries in memory using H2.

Also as there are thousands of existing queries, so it will not be feasible to changes those and perform testing again.

For newly added queries, yes I can use as per your suggestion of using 1.0.

For now, the only thing that is going to work is patching the code.

Can you please give me some pointers for making this change to convert value into a double when convert to integer fails.

Thanks,

Vinod

[Vinod]

I have made the following change in Operation.optimize(Session session), and it is working fine now.

The change is to consider datatype as DECIMAL when:

a) one side is integer and other is unknown (1 + ?) 

b) one side is integer and other is String (1 + '0.5')  

Following is the change (added an else-if block for math operators case, just after the case where ? + ? is handled)

--------------------------------------

            } else if ((l == Value.INT && (r == Value.NULL || r == Value.UNKNOWN || r == Value.STRING)) || (r == Value.INT && (l == Value.NULL || l == Value.UNKNOWN || l == Value.STRING))) {

                // (1 + ?) - one side is int, other is unknown or string, use decimal by default (the most safe data type) or

                // string when text concatenation with + is enabled

                if (opType == PLUS && session.getDatabase().getMode().allowPlusForStringConcat) {

                    dataType = Value.STRING;

                    opType = CONCAT;

                } else {

                    dataType = Value.DECIMAL;

                }

-------------------------------------- 

It still works well for the cases with integers in string:

SELECT 8 - '5' FROM DUAL // output=3

Will update if I face any issues.

Thanks,

Vinod

[Noel Grandin]

On 2012-06-28 17:14, Vinod wrote:
> I have made the following change in Operation.optimize(Session
> session), and it is working fine now.
>
> The change is to consider datatype as DECIMAL when:
> a) one side is integer and other is unknown (1 + ?)
>

This part is likely to break other places where the second part is a
non-number string, because they tend to expect the result to be a string.
So I don't think this patch is acceptable as is.

[Vinod]

> When the second part is non-number string.

Did you mean something like:

select 1 + 'aaa' from dual

The above example will fail anyway, when we are trying to add a number and string. 

In oracle, executing the above query gives: ORA-01722: invalid number

Did you mean in other databases the above query expects 1aaa as output?

[Thomas Mueller]

Hi,

Yes, if you need to patch H2, most likely that would be in Value.getHigherOrder and Operation.optimize().

> convert value into a double when convert to integer fails.

For H2, the data type of an expression is usually evaluated at prepare time (Connection.prepareStatement(...)). For parameters of the form "(1 - ?)", the evaluation can only happen at execute time, which would be too late usually. There is an option to flag prepared statements as "always re-compile" that could potentially be used here. So far the flag was mainly used to re-compile queries containing "... LIKE ?", because depending on the data an index could be used or not - and using an index is really important. The flag is Prepared.prepareAlways. I'm not sure if it's a good idea to use the flag to allow late evaluation of the data type however.

Regards,

Thomas

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/39TOUQlhi8wJ.

To post to this group, send email to h2-da...@googlegroups.com.

To unsubscribe from this group, send email to h2-database...@googlegroups.com.