ERROR 1690 (22003): BIGINT UNSIGNED value is out getting

Question

SELECT alertid,
       ackuid,
       severity,
       ticketid,
       From_unixtime(display_ts)                           AS Detection_Time,
       From_unixtime(ack_ts)                               AS Ack_Time,
       Round(( ack_ts - display_ts ) / 60)                 AS MTTA_MINS,
       IF (Round(( ack_ts - display_ts ) / 60) > 15, 1, 0) AS SLA_MISSED
FROM   alerts_test
WHERE  display_ts > Unix_timestamp(Now() - INTERVAL 26 day)
ORDER  BY From_unixtime(display_ts);

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(dba.DBA_IRIS_ALERTS.ack_ts - dba.DBA_IRIS_ALERTS.display_ts)'

How to resolve this error? Especially in this condition it is failing.

round((ack_ts-display_ts)/60) as MTTA_MINS,
IF ( round((ack_ts-display_ts)/60) > 15, 1, 0) as SLA_MISSED

Sample Data

+----------------------------------+-----------------+----------+-------------+---------------------+---------------------+-----------+------------+
| alertid                          | ackuid          | severity | ticketid    | Detection_Time      | Ack_Time            | MTTA_MINS | SLA_MISSED |
+----------------------------------+-----------------+----------+-------------+---------------------+---------------------+-----------+------------+
| xxxxxx                           | him            |        5 | NULL        | 2018-11-01 00:03:23 | 2018-11-01 00:06:18 |         3 |          0 |
| xxxxxx                           | him            |        5 | NULL        | 2018-11-01 00:11:08 | 2018-11-01 00:17:45 |         7 |          0 |

Please add some sample data to the question. Or setup a fiddle: https://www.db-fiddle.com/ Without looking at the data, it is hard to figure out. — Madhur Bhaiya, Nov 28 '18 at 05:25
What you have added to the question is not sample data. As, it seems that the columns `ack_ts` and `display_ts` store Unix timestamp (seconds) value. Can you add `CREATE TABLE` statement results to the question. Do `SHOW CREATE TABLE alerts_test` and post the result of this query to the question — Madhur Bhaiya, Nov 28 '18 at 06:15

score 1 · Accepted Answer · answered Nov 28 '18 at 06:28

From Out-of-Range and Overflow Handling:

Subtraction between integer values, where one is of type UNSIGNED, produces an unsigned result by default. If the result would otherwise have been negative, an error results:
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

So, I think that ack_ts and/or display_ts column(s), which seems to represent Unix Timestamp values, are defined using UNSIGNED flag in your CREATE TABLE statement (which is good, as Unix Timestamp values should be positive only).

So, you are subtracting columns of type UNSIGNED and the result of ack_ts - display_ts is negative. So, you are getting cases where ack_ts < display_ts.

Now, there are two possibilities:

You may not be looking for cases where ack_ts < display_ts.

In this case, you will need to add another WHERE condition to the query:

SELECT alertid,
       ackuid,
       severity,
       ticketid,
       From_unixtime(display_ts)                           AS Detection_Time,
       From_unixtime(ack_ts)                               AS Ack_Time,
       Round(( ack_ts - display_ts ) / 60)                 AS MTTA_MINS,
       IF (Round(( ack_ts - display_ts ) / 60) > 15, 1, 0) AS SLA_MISSED
FROM   alerts_test
WHERE  display_ts > Unix_timestamp(Now() - INTERVAL 26 day) AND 
       ack_ts >= display_ts   /* added one more condition to remove negative cases */
ORDER  BY From_unixtime(display_ts);

You are OK with the negative values.

In this case, you will need to cast them to SIGNED, to allow for the computation result in negative values:

SELECT alertid,
       ackuid,
       severity,
       ticketid,
       From_unixtime(display_ts)                           AS Detection_Time,
       From_unixtime(ack_ts)                               AS Ack_Time,
       Round(( CAST(ack_ts AS SIGNED) - CAST(display_ts AS SIGNED) ) / 60) AS MTTA_MINS,
       IF (Round(( CAST(ack_ts AS SIGNED) - CAST(display_ts AS SIGNED) ) / 60) > 15, 1, 0) AS SLA_MISSED
FROM   alerts_test
WHERE  display_ts > Unix_timestamp(Now() - INTERVAL 26 day) 
ORDER  BY From_unixtime(display_ts);

Another option would be to set the sql_mode to using NO_UNSIGNED_SUBTRACTION. Now, you can use your original query after setting the mode:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

You may not have access to change this setting Globally. So you can run the following query just before your original query, to make this change only for the specific client session.

SET SESSION sql_mode = 'NO_UNSIGNED_SUBTRACTION';

Check this answer for more details about setting sql_modes: https://stackoverflow.com/a/26104070/2469308

Thank you Madhur Bhaiya - The 1st one had resolved my issue. Yes ack_ts( Acknowledgement time) should always greater than Display time. — sarat, Nov 28 '18 at 12:26

ERROR 1690 (22003): BIGINT UNSIGNED value is out getting

1 Answers1