Negative `INTERVAL` values lose their sign in the text decoder, returning incorrect cross-shard `SUM` and `ORDER BY` values

[bryanmehall]

PgDog Version

v0.1.50, PostgreSQL 18, 2 shards.

Description

The text decoder for INTERVAL parses each colon-separated component of the
time-of-day portion as an independently signed integer. For example, -00:45:00 will parse as +45 minutes because the leading negative sign will be dropped on parsing when converting to an integer. Rows are stored correctly on each shard but computed results for SUM and ORDER BY are returned incorrectly.

Only clients that request text result format are affected (libpq, psql, psycopg2, node-postgres, etc.)

The relevant code is in pgdog-postgres-types/src/interval.rs, in the Format::Text arm of FromDataType::decode:

let mut value = value.split(":");   // "-00:45:00" -> ["-00", "45", "00"]
result.hours   = bigint(hours)?;    // bigint("-00") == 0  sign lost here
result.minutes = bigint(minutes)?;  // sign never applied to minutes
result.seconds = bigint(seconds)?;

Configuration

Requires a sharded table. Minimum configuration:

[[sharded_tables]]
database = "pgdog"
name = "time_entries"
column = "user_id"

Reproduction

CREATE TABLE time_entries (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,      -- sharding key
    duration INTERVAL NOT NULL
);

Insert two rows that hash to different shards.

INSERT INTO time_entries (id, user_id, duration) VALUES (101, 1, '30 minutes');
INSERT INTO time_entries (id, user_id, duration) VALUES (102, 3, '-45 minutes');

Values are stored correctly:

shard 0: 00:30:00
shard 1: -00:45:00

Incorrect Sum

SELECT SUM(duration) FROM time_entries;

expected: -00:15:00

returned: 0 years 0 mons 0 days 01:15:00.0

-45 minutes is added as +45 minutes, so the total comes back positive.

Incorrect Ordering

SELECT id, duration FROM time_entries ORDER BY duration ASC;

expected: 102 (-45 min), then 101 (+30 min)

returned: 101, then 102

Note the sort column has to be in the SELECT list. SELECT id FROM time_entries ORDER BY duration returns rows in shard order regardless of the sign bug, which masks this symptom.

Happy to submit a PR if this looks right

[levkk]

Since we have zero tests covering negative intervals, I can only assume that this is correct :) Please, if you have some time, a PR would be amazing. Thanks!