1.2.128 returns wrong results.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Use provided database in zip file
2. run query in zip file that reproduces problem with 1.2.128 but not
1.1.11. The query should return 1.
3. Start with a new database. RUNSCRIPT from 'script.sql' (or do one
yourself with provided database ) using provided script.sql
4. Try running query again. It returns expected results regardless of 
database version

What is the expected output? What do you see instead?
The query is supposed to return 1.

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

I am running jdk 1.6, winxp pro x64, ntfs.

Do you know a workaround?
No

How important/urgent is the problem for you?
Very important. We cannot put 1.2.* version of H2 into production because
we have tests that mysteriously fail if running in-mem but not file db,
and we need a parser bug fix and MVCC.

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

Please provide any additional information below.
We are having quite a few tests that return wrong results. The scenarios
vary but usually are because of running in-mem instead of file db. For
example, we have tests that pass when running file db and fail running
in-mem. We also have a few tests that pass if only one test is run but fail
if two tests are run (the second test fails).

What's really strange is that with provided database 1.2.128 produces wrong
results but 1.1.11 produces correct result, but if this database that is
saved with RUN command is restored with RUNSCRIPT on a new database, both
1.1.11 AND 1.2.128 produce the correct result.

I think there is a bug in the storage engine. Good luck and thank you for
looking into it!

Original issue reported on code.google.com by youn...@gmail.com on 4 Feb 2010 at 7:39

Attachments:

• script.zip

[GoogleCodeExporter]

I know you guys are busy, but the wrong results we've been getting from any 
1.2.*
version of H2 is dragging out, and now with the fixed parser bug in 1.2.128, I'm
getting more pressure as to why we aren't using the latest version. It's been
difficult to find a reproducible case but I have one here. I'd appreciate if 
you'd at
least try look at it and try to reproduce.

Thanks!
Randy

Original comment by youn...@gmail.com on 9 Feb 2010 at 5:53

[GoogleCodeExporter]

Sorry for the delay. This is a problem that was introduced in version 1.2.128, 
when
trying to fix the bug "Comparing an column against a constant expression with a
higher precision or length than the column could give wrong results (the 
expression
was truncated before comparing)." See also: 
http://www.h2database.com/html/changelog.html

In this case, the problem is not with VARCHAR, but with DECIMAL and using 
different
scales. A simpler test case:

drop table test;
create table test(id decimal(10, 2) primary key) as select 0;
select * from test where id = 0.00;
select * from test where id = 0.0;

The first query returns the row, the second does not.

I will fix this of course.

Original comment by thomas.t...@gmail.com on 9 Feb 2010 at 10:42

• Changed state: Accepted

[GoogleCodeExporter]

This should be fixed with version 1.2.129.

Original comment by thomas.t...@gmail.com on 20 Feb 2010 at 9:44

• Changed state: Fixed