Presto fails to read hexadecimal string: Not a valid base-16 number

Is there a way for presto to check if a string is hex or not? I have the following query keeps failing:

from_base(hexstring, 16)

with error

>     /usr/local/lib/python3.7/dist-packages/pyhive/presto.py in _process_response(self, response)
>     347             self._state = self._STATE_FINISHED
>     348         if 'error' in response_json:
> --> 349             raise DatabaseError(response_json['error'])
>     350 
>     351 
> 
> DatabaseError: {'message': 'Not a valid base-16 number:
> ffffffffffdfae90', 'errorCode': 7, 'errorName':
> 'INVALID_FUNCTION_ARGUMENT', 'errorType': 'USER_ERROR', 'failureInfo':
> {'type': 'io.prestosql.spi.PrestoException', 'message': 'Not a valid
> base-16 number: ffffffffffdfae90', 'cause': {'type':
> 'java.lang.NumberFormatException', 'message': 'For input string:
> "ffffffffffdfae90"', 'suppressed': [], 'stack':
>

However, python is ok with the string:

int('ffffffffffdfae90',16)

returns

18446744073707433616

Solution

from_base returns BIGINT which can hold up to 2^63 - 1 i.e. 9223372036854775807 which is less then 18446744073707433616 while python's int is undounded, so this particular number is just too big for Presto.

To some extent you can work around this issue by splitting the number in smaller parts, casting to DECIMAL and doing some math. See PL/SQL to Superset/Trino - convert hexadecimal funcion.