General Data Exception detected by database - DOUBLE value is out of range in 'coalesce

[Vinicius Luz1]

Hey Guys,

I'm struggling to solve this, this custom table suddently started showing this error message when we try to see the list.

DOUBLE value is out of range in 'coalesce(`thehackettgroup_1`.`sn_c13e_time_entries0`.`u_entitlement`,0) / coalesce(`thehackettgroup_1`.`sn_c13e_time_entries0`.`u_time_spent`,0)': java.sql.SQLDataException: DOUBLE value is out of range in 'coalesce(`thehackettgroup_1`.`sn_c13e_time_entries0`.`u_entitlement`,0) /

Here's a screenshot of it:

These fields mentioned on the error msg.

u_entitlement - its a reference to [service_entitlement] table

u_time_spent - decimal field used to deduct from anothe field inside the entitlement field - Sometimes it can deduct from 0 making the value negative ....

Please I need some help on it !!!

@Hitoshi Ozawa Can you help?

[Syavasya Vadava · Accepted solution]

Thinking out loud. Do you happen to have a functionField = true and FunctionDefinition for a column defined in sys_dictionary for that table?

It looks like whats happening is you have a glideFunction defined that does value x/y. Now, this works well if x and y are numeric.

What I see here is the COALESCE() function being used. It returns the first NON-NULL value of the field i.e. SELECT COALESCE(NULL,1) returns 1 and COALESCE('somevalue',1) returns somevalue.

With the division operation, COALESCE('value',0)/ COALESCE('value2',0) database converts both the values COALESCE('value',0),COALESCE('value2',0) into DOUBLE and then perform the divide operation.

For example:
SELECT CAST(COALESCE('12345e',1) AS DOUBLE);
+--------------------------------------+
| CAST(COALESCE('12345e',1) AS DOUBLE) |
+--------------------------------------+
|                                12345 |
+--------------------------------------+

SELECT CAST(COALESCE('12345e12345',1) AS DOUBLE);
+-------------------------------------------+
| CAST(COALESCE('12345e12345',1) AS DOUBLE) |
+-------------------------------------------+
|                    1.7976931348623157e308 |
+-------------------------------------------+

Because glide references in servicenow are VARCHAR(32), it is possible we have reference values starting with numericals followed by e and then some more numericals and this seem to convert the value exponentially until it reaches the max value for DOUBLE i.e. 1.7976931348623157e308 and then division 

Here is an example in your use case:

Table: sn_c13e_time_entries
Sys_id: 0015db9187ec11508e4097d83cbb353b
u_entitlement value: 57e732ad8716811090aa7b9acebb35dc
u_time_spent: 0.50
CAST(COALESCE('57e732ad8716811090aa7b9acebb35dc',0) AS DOUBLE): 1.7976931348623157e308
CAST(COALESCE('0.50',0) AS DOUBLE): 0.5

so the division throws the DOUBLE value is out of range.

Given it is non-number division you are trying to do, you should probably revert that change on the instance. 

PS: This is my first response on a community window. Apologies if its too long (detailed).

[chaimaenhs]

For me i have this error when try to removed some group and role in a user