ALIAS function not supporting Number parameter type

Jack Green

unread,

Oct 23, 2019, 7:18:24 AM10/23/19

to H2 Database

I've come across a problem with H2 (1.4.200) where an ALIAS'ed Java method that takes a java.lang.Number as a parameter is not being processed correctly - specifically, an error is thrown:

As an example, take the following SQL - it creates an ALIAS function which takes in a parameter, and returns a toString() representation of it. Specifically, the parameter is an integer.

CREATE ALIAS valueOfInteger AS $$ String valueOfInteger(Integer number) { return number.toString(); } $$;

CALL valueOfInteger (1);

This outputs "1" as you would expect.

If you then modify it slightly to instead take in a Number:

CREATE ALIAS valueOfNumber AS $$ String valueOfNumber(Number number) { return number.toString(); } $$;

SELECT valueOfNumber (1);

You would expect an identical output - but instead the error above is thrown.

It appears that in org.h2.value.DataType.getTypeFromClass(Class<?>), there is no support for the Number type, as such it treats it as a "Value.JAVA_OBJECT" and then ultimately in org.h2.value.Value.convertToJavaObject() it falls through the switch and tries to parse it with StringUtils.convertHexToBytes which fails.

I don't know the scenario in which an object would need to be decoded from hex, but could support for this type be added?
And in addition, if the object is an unsupported type, could an exception to that effect be thrown, rather than the hexadecimal one?

Thanks in advance.

Evgenij Ryazanov

unread,

Oct 23, 2019, 8:32:16 AM10/23/19

to H2 Database

Hello.

java.lang.Number is an abstract class in Java that has different implementations, such as `BigInteger`, `BigDecimal`, wrappers over primitive data types and you can create your own implementations too. So, technically, it can hold everything.

This data type is not supported by JDBC. It cannot be supported as a number, because it can hold different not really compatible with each other objects. Its mapping to a JAVA_OBJECT looks like being perfectly valid.

Note that NUMBER data type from SQL is mapped to a BigDecimal in JDBC specification and in H2.

Jack Green

unread,

Oct 23, 2019, 8:56:15 AM10/23/19

to h2-da...@googlegroups.com

Thanks - I understand that, but if it simply returned the String from the function (that it already computed) it’d be fine. But instead it’s then trying to convert that String from hex for some reason that’s the part that’s failing?

On 23 Oct 2019, at 1:32 pm, Evgenij Ryazanov <kat...@gmail.com> wrote:

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/9h-koML1_NY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/a9dcf9cc-bb34-4df9-a975-796702803282%40googlegroups.com.

Noel Grandin

unread,

Oct 23, 2019, 9:01:35 AM10/23/19

to h2-da...@googlegroups.com

the problem is that H2 is trying to convert the argument to a valid type and because it doesn't know how to deal with
Number, ends up in a fallback path that produces a less than useful error message.

Probably we should just remove that fallback path and instead throw a conversion error.

You could work around your issue by making your ALIAS take String as a parameter, and then converting it to a number
yourself.

Reply all

Reply to author

Forward