Hi, I found a serious problem with comparisons of DECIMAL data type. Only work when constant number used to compare has the same decimal digits as the compared field.
Below is a test case to reproduce it. <-- START TEST CASE --> drop table imp.kk; create table imp.kk ( id int, email varchar(100), damount double, *decamount dec(16,2)* ); insert into imp.kk values (1,'[email protected]',null,null); insert into imp.kk values (2,'[email protected]',-1.000,-1.000); insert into imp.kk values (3,'[email protected]',-1.00,-1.00); insert into imp.kk values (4,'[email protected]',-1.0,-1.0); insert into imp.kk values (5,'[email protected]',-1,-1); insert into imp.kk values (6,'[email protected]',0,0); insert into imp.kk values (7,'[email protected]',1,1); insert into imp.kk values (8,'[email protected]',1.0,1.0); insert into imp.kk values (9,'[email protected]',1.00,1.00); insert into imp.kk values (10,'[email protected]',1.000,1.000); insert into imp.kk values (11,'[email protected]',12345,12345); insert into imp.kk values (12,'[email protected]',12345.6,12345.6); insert into imp.kk values (13,'[email protected]',12345.67,12345.67); insert into imp.kk values (14,'[email protected]',12345.678,12345.678); insert into imp.kk values (15,'[email protected]',80308113, 80308113); select count(*) from imp.kk where decamount>0; select count(*) from imp.kk where decamount>0.0; *select count(*) from imp.kk where decamount>0.00;* -- ONLY THIS WORK. select count(*) from imp.kk where decamount>0.000; select count(*) from imp.kk where decamount>1; select count(*) from imp.kk where decamount>100; <-- END TEST CASE --> H2 version 1.2.128 URL: jdbc:h2:~/xxdb;PAGE_STORE=TRUE;CACHE_SIZE=65535 Platform Linux 64bit JVM Sun Java HotSpot(TM) 64-Bit Server VM (build 16.0-b13, mixed mode) FS: ext3 -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
