Bug #121757 ADDDATE() interprets a large positive BIGINT UNSIGNED value as a negative day interval

Submitted: 13 Nov 3:01 Modified: 18 Nov 11:29
Reporter: b a Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:26.7.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: addtime, UNSIGNED

[13 Nov 3:01] b a
Description:
In MySQL 26.7.0, `ADDDATE(date, days)` incorrectly interprets certain large positive `BIGINT UNSIGNED` values as negative day intervals, causing the resulting date to move backward instead of forward.

For example:

```sql
SELECT ADDDATE(
    '2026-01-01',
    CAST(18446744073709550876 AS UNSIGNED)
);
```

**Actual result:**

```text
2023-12-23
```

The supplied value is a positive unsigned integer:

```text
18446744073709550876 = 2^64 - 740
```

However, MySQL produces the same result as adding `-740` days:

```sql
SELECT ADDDATE('2026-01-01', -740);
-- 2023-12-23
```

This suggests that the large unsigned value is being reinterpreted as a negative signed 64-bit integer during interval evaluation.

According to the documented semantics of `ADDDATE(date, days)`, the second argument represents the number of days to add. A positive day interval should not produce an earlier valid date.

If the positive interval exceeds the supported date range, an overflow condition or `NULL` would be expected instead of silently producing an earlier date.

## Possible Cause

In MySQL 26.7.0, the interval conversion logic in `sql/item_timefunc.cc`, inside `get_interval_value()`, contains the following code:

```cpp
longlong value = 0;

// ...

value = args->val_int();

if (args->null_value)
    return true;

if (value == LLONG_MIN)
    return true;

if (value < 0) {
    interval->neg = true;
    value = -value;
}
```

The function obtains the interval using `args->val_int()` and then tests the returned signed `longlong` value to determine the interval sign.

This path appears not to preserve the original argument's `UNSIGNED` property when determining the interval direction.

Consequently, unsigned 64-bit values greater than `LLONG_MAX` may be interpreted as negative signed values.

For the reported input:

```text
UNSIGNED: 18446744073709550876
SIGNED:                  -740
```

This explains the observed date subtraction.

Source code:

https://github.com/mysql/mysql-server/blob/mysql-26.7.0/sql/item_timefunc.cc

## References

MySQL ADDDATE() documentation:

https://dev.mysql.com/doc/refman/26.7/en/date-and-time-functions.html#function_adddate

MySQL CAST() documentation:

https://dev.mysql.com/doc/refman/26.7/en/cast-functions.html

How to repeat:
**Environment**

- DBMS: MySQL 26.7.0
- No tables or additional setup required.
- No dependence on the current date.

SQL mode:

```text
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```

### Test 1: A positive day interval moves the date backward

```sql
SELECT
    CAST(18446744073709550876 AS UNSIGNED) > 0
        AS positive_days,

    ADDDATE(
        '2026-01-01',
        CAST(18446744073709550876 AS UNSIGNED)
    ) AS result_date,

    DATEDIFF(
        ADDDATE(
            '2026-01-01',
            CAST(18446744073709550876 AS UNSIGNED)
        ),
        '2026-01-01'
    ) AS actual_delta;
```

**Actual result:**

```text
+---------------+-------------+--------------+
| positive_days | result_date | actual_delta |
+---------------+-------------+--------------+
|             1 | 2023-12-23  |         -740 |
+---------------+-------------+--------------+
1 row in set (0.000 sec)
```

The query confirms that the input is positive (`positive_days = 1`), yet the returned date is 740 days earlier than the original date (`actual_delta = -740`).

This contradicts the expected direction of date addition for a nonnegative day interval.

### Test 2: Different results for numerically equal values using different numeric types

```sql
WITH args AS (
    SELECT CAST(
        18446744073709550876 AS UNSIGNED
    ) AS n
)
SELECT
    n = CAST(n AS DECIMAL(20,0)) AS same_value,

    ADDDATE('2026-01-01', n) AS q1,

    ADDDATE(
        '2026-01-01',
        CAST(n AS DECIMAL(20,0))
    ) AS q2
FROM args;
```

**Actual result:**

```text
+------------+------------+------+
| same_value | q1         | q2   |
+------------+------------+------+
|          1 | 2023-12-23 | NULL |
+------------+------------+------+
1 row in set, 2 warnings (0.001 sec)
```

Immediately executing:

```sql
SHOW WARNINGS;
```

returns:

```text
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DECIMAL value: '18446744073709550876' |
| Warning | 1441 | Datetime function: datetime field overflow                |
+---------+------+-----------------------------------------------------------+
2 rows in set (0.000 sec)
```

Although the equality comparison reports that the two numeric representations have equal values, the `ADDDATE()` results differ:

- `BIGINT UNSIGNED`: `2023-12-23`
- `DECIMAL(20,0)`: `NULL`

The conversion warning (`1292`) indicates that additional investigation of the DECIMAL evaluation path may be necessary. Therefore, this test is provided as supporting diagnostic evidence rather than a definitive equivalence proof.

### Test 3: Explicit signed conversion

```sql
SELECT
    CAST(18446744073709550876 AS SIGNED)
        AS signed_days,

    ADDDATE(
        '2026-01-01',
        CAST(18446744073709550876 AS SIGNED)
    ) AS result;
```

**Actual result:**

```text
signed_days | result
-740        | 2023-12-23
```

This confirms that the original unexpected result is consistent with interpreting the large positive unsigned value as signed `-740`.

## Expected Result

`ADDDATE()` should preserve the intended signedness of the supplied integer day interval.

For a positive `BIGINT UNSIGNED` value, the function should not silently reinterpret the argument as a negative number and return an earlier valid date.

If adding the positive interval exceeds the supported date range, the function should report the corresponding overflow condition or return `NULL`, according to its documented error-handling behavior.

In particular, the following property should hold whenever the function returns a valid date:

```text
days >= 0
AND ADDDATE(date, days) IS NOT NULL
IMPLIES ADDDATE(date, days) >= date
```
[17 Nov 14:21] Jean-François Gagné
Related: Bug #121802.
[17 Nov 14:24] Jean-François Gagné
Looks like a duplicate of Bug #121508.
[18 Nov 11:29] Chaithra Marsur Gopala Reddy
Hi b a,

Thank you for the test case. Verified as described. We think this is same issue as that of Bug#121508. So we are closing this as duplicate.

Thanks,
Chaithra

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.042 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.