Hi,
I think that the only problematic case is when an integer is left padded with
zero.
Currently this it's parse as Octal constant in H2 and parse as a valid integer
on other dbms.
It would be nice to retain the possibility of parsing numbers in hexadecimal
representation that start with 0x
is a useful feature that other dbms don't have.
-- Test run on IBM DB2 9.7
--
create table test(id int);
insert into test values(1);
select cast('11' as int) a from test;
-- 1: ok : return = 11
select cast(' 11 ' as int) b from test;
-- 2: ok : return = 11
select cast('011' as int) c from test;
-- 3: ok : return = 11
select cast('0x011' as int) d from test;
-- 4: Error: DB2 SQL Error: SQLCODE=-420, SQLSTATE=22018, SQLERRMC=INTEGER,
DRIVER=4.9.78
drop table test;
regards,
Dario
El 16/10/10 10:37, Thomas Mueller escribió:
> H2 currently uses Integer.decode(s.trim()) to convert strings to int
> (see Value.convertTo,
> http://code.google.com/p/h2database/source/browse/trunk/h2/src/main/org/h2/value/Value.java#493
> ). I tought that's a good idea because hex numbers can be converted
> ('0x...'). However, I see it's different from how other databases
> work. Unless I find bigger problems, I will change the behavior of H2
> to match PostgreSQL (replacing Byte/Short/Integer/Long.decode with
> parseByte/Short/Integer/Long). My test case is:
>
> drop table test;
> create table test(id int);
> insert into test values(1);
> select cast('11' as int) a from test;
> select cast(' 11 ' as int) b from test;
> select cast('011' as int) c from test;
> select cast('0x11' as int) d from test;
>
> H2 currently returns 11, 11, 9, 17.
> PostgreSQL, Derby, HSQLDB return 11, 11, 11, exception
> SQLite returns 11, 11, 11, 0 (converting up to the first non-digit)
> MySQL throws an exceptions in each case (cast to int is not supported)
>
> select cast('11' as decimal(10,0)) a from test;
> select cast(' 11 ' as decimal(10,0)) b from test;
> select cast('011' as decimal(10,0)) c from test;
> select cast('0x11' as decimal(10,0)) d from test;
>
> PostgreSQL, MySQL, H2, Derby, HSQLDB return 11, 11, 11, exception
> SQLite and MySQL return 11, 11, 11, 0 (converting up to the first non-digit)
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to h2-datab...@googlegroups.com.
To unsubscribe from this group, send email to
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
