Thread: BUG #18585: Date/time conversion functions are not protected against integer overflow

The following bug has been logged on the website:

Bug reference:      18585
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 17beta3
Operating system:   Ubuntu 22.04
Description:

The date/time conversion functions do_to_timestamp(), DCH_from_char(),
... lack reliable checks for integer overflow in the input components.

1) Millennium:
SELECT to_timestamp('2147483647,999', 'Y,YYY');
       to_timestamp        
---------------------------
 0001-01-01 00:00:00+00 BC
vs
SELECT to_timestamp('2147483,999', 'Y,YYY');
ERROR:  timestamp out of range

2) Century:
SELECT to_date('-2147483648', 'CC');
  to_date   
------------
 0001-01-01
vs
SELECT to_date('-21474836', 'CC');
ERROR:  date out of range: "-21474836"

3) Century + year:
SELECT to_date('-2147483648 01', 'CC YY');
  to_date   
------------
 0100-01-01
vs
SELECT to_date('-21474836 01', 'CC YY');
ERROR:  date out of range: "-21474836 01"

4) Milliseconds:
SELECT to_timestamp('0.-2147483648', 'SS.MS');
       to_timestamp        
---------------------------
 0001-01-01 00:00:00+00 BC
vs
SELECT to_timestamp('0.-2147483', 'SS.MS');
ERROR:  date/time field value out of range: "0.-2147483"

5) Week of month:
SELECT to_timestamp('613566758', 'W');
       to_timestamp        
---------------------------
 0001-01-04 00:00:00+00 BC
vs
SELECT to_timestamp('613566750', 'W');
ERROR:  date/time field value out of range: "613566750"

6) Week number of year:
SELECT to_timestamp('2024 613566758 1', 'YYYY WW D');
      to_timestamp      
------------------------
 2024-01-04 00:00:00+00
vs
SELECT to_timestamp('2024 61356675 1', 'YYYY WW D');
ERROR:  date/time field value out of range: "2024 61356675 1"

7)  ISO Week:
SELECT to_date('2024 613566758 1', 'IYYY IW ID');
  to_date   
------------
 2024-01-04
vs
SELECT to_date('2024 61356675 1', 'IYYY IW ID');
    to_date    
---------------
 1177946-01-28

Observed on REL_12_STABLE .. master.

(A couple of such anomalies were noticed at
https://www.postgresql.org/message-id/31ad2cd1-db94-bdb3-f91a-65ffdb4bef95%40gmail.com,
but as these ones trigger an incorrect behaviour even without -ftrapv and
in the back branches, maybe it's better to fix these defects separately.)

PG Bug reporting form <noreply@postgresql.org> writes:
> The date/time conversion functions do_to_timestamp(), DCH_from_char(),
> ... lack reliable checks for integer overflow in the input components.

It's pretty hard to get excited about that, given that the overflow
limits are so many orders of magnitude beyond the useful input range.
There are plenty of garbage-in-garbage-out scenarios associated with
(mis)use of the to_xxx functions, and this feels like just another one.

If anybody does feel excited about the topic, I'm not going to stand
in their way ... but I'm not excited.

            regards, tom lane

For future reference, all of the above reports except for case 7 should be
fixed by commit 0a27c3d.

-- 
nathan