Environment:
- psqlODBC: main @ 61f852b (v18.00.0002), built from source
- unixODBC: 2.3.12
- Backend: PostgreSQL 14.4
- OS: Rocky Linux 9.6, x86_64
Summary
SELECT 'infinity'::date and SELECT '-infinity'::date are silently converted to today's local date (or 0000-00-00, depending on the requested C type — see below) instead of a recognizable sentinel. The +/-
direction is lost entirely when both collapse to the same value. No error, warning, or truncation indicator (SQLSTATE 01S07) is raised.
By contrast, 'infinity'::timestamp / '-infinity'::timestamp are handled — they return fixed sentinel values — so this looks like an oversight specific to the DATE type rather than a deliberate design choice.
Steps to reproduce
SELECT current_date;
SELECT 'infinity'::date;
SELECT '-infinity'::date;
SELECT 'infinity'::timestamp;
SELECT '-infinity'::timestamp;
Via isql:
SQL> SELECT 'infinity'::date;
+-----------+
| date |
+-----------+
| 0000-00-00|
+-----------+
Via a client that binds the column as SQL_C_TYPE_DATE (91) or SQL_C_TYPE_TIMESTAMP (93) — e.g. pyodbc:
>>> cur.execute("SELECT 'infinity'::date").fetchone()[0]
datetime.date(2026, 8, 10) # == today; changes every day; identical for -infinity
Expected: a fixed, direction-distinguishable sentinel (e.g. 9999-12-31 / 0001-01-01, matching the existing timestamp behavior), or a raised truncation/error indicator — not a silently wrong, moving-target
value.
Root cause
In convert.c, copy_and_convert_field():
- The PG_TYPE_DATE case (~line 1448) does only:
secure_sscanf(value, &status, "%4d-%2d-%2d", &std_time.y, &std_time.m, &std_time.d);
- "infinity"/"-infinity" don't match "%4d-%2d-%2d", so the sscanf silently fails and std_time.y/m/d stay at the memset-zeroed value (line ~1357).
- The PG_TYPE_TIMESTAMP case (~line 1465) does special-case it via strnicmp(value, INFINITY_STRING/MINFINITY_STRING, ...), setting a sentinel — this logic was apparently never mirrored into the DATE case.
- Downstream, what the caller sees depends on which C type is requested for the zeroed std_time:
- SQL_C_CHAR (text output path, line ~1677): sprintf("%.4d-%.2d-%.2d", 0, 0, 0) → literal 0000-00-00.
- SQL_C_TYPE_DATE / SQL_C_TYPE_TIMESTAMP (struct-binding paths, lines ~1772–1777 and ~1820–1825): each has a if (std_time.y == 0) std_time.y = fallback (intended for filling the date part
of a TIME-only value), which papers over the zeroed struct with today's date instead.
Environment:
Summary
SELECT 'infinity'::date and SELECT '-infinity'::date are silently converted to today's local date (or 0000-00-00, depending on the requested C type — see below) instead of a recognizable sentinel. The +/-
direction is lost entirely when both collapse to the same value. No error, warning, or truncation indicator (SQLSTATE 01S07) is raised.
By contrast, 'infinity'::timestamp / '-infinity'::timestamp are handled — they return fixed sentinel values — so this looks like an oversight specific to the DATE type rather than a deliberate design choice.
Steps to reproduce
Via a client that binds the column as SQL_C_TYPE_DATE (91) or SQL_C_TYPE_TIMESTAMP (93) — e.g. pyodbc:
Expected: a fixed, direction-distinguishable sentinel (e.g. 9999-12-31 / 0001-01-01, matching the existing timestamp behavior), or a raised truncation/error indicator — not a silently wrong, moving-target
value.
Root cause
In convert.c, copy_and_convert_field():
secure_sscanf(value, &status, "%4d-%2d-%2d", &std_time.y, &std_time.m, &std_time.d);
of a TIME-only value), which papers over the zeroed struct with today's date instead.