CAST('015' AS INTEGER)

[GoogleCodeExporter]

The following SELECT-Statement
SELECT 
   CAST('015' AS DECIMAL) AS "015 DEC", 
   CAST('015' AS INTEGER) AS "015 INT", 
   CAST('018' AS DECIMAL(2,0)) AS "018 DEC"

has as Result:
015 DEC     015 INT     018 DEC
15             13             18

SELECT  CAST('018' AS INTEGER) AS "018 INT"
even gets an Exception:
Datenumwandlungsfehler beim Umwandeln von "018"
Data conversion error converting "018"; SQL statement:
SELECT
   CAST('018' AS INTEGER) AS "018 INT" [90021-127] 90021/90021

Obviously the Java conversion routine for String to int is used. 
Java inherits here the "hacker" representation of numbers from C (and C++):
A number starting with the digit 0 is an octal number.
Therefore 015 is 8^1 + 5 = 13
and 018 is illegal because 8 is no octal digit.

SQL doesn't inherit this (archaic) feature from C.
Therefore you cannot use Java number conversion for this SQL feature.

Günter Matthiessen
gmatthiessen@hs-bremerhaven.de

Original issue reported on code.google.com by gmatthie...@hs-bremerhaven.de on 6 Nov 2010 at 4:35

[GoogleCodeExporter]

What version of H2 did you use? The behavior was changed in version 1.2.145. 
See also the change log, http://www.h2database.com/html/changelog.html - 
"CAST('011' AS INT) will no longer use decode the value as an octal number 
(using Integer.decode) but now use Integer.parseInt. The same logic applied to 
byte, short, and long. This also means that trying to convert hex numbers 
(0x...) with CAST now fails."

Original comment by thomas.t...@gmail.com on 7 Nov 2010 at 8:00

[GoogleCodeExporter]

I'm closing the bug as "works for me".

Original comment by thomas.t...@gmail.com on 13 Nov 2010 at 8:26

• Changed state: WorksForMe