wrong order in batch sorted merge with nullable multi-column DESC NULLS FIRST orderby after recompression

[svenklemm]

Found by the Optimizations ON-OFF LLM fuzzer oracle (run https://github.com/timescale/timescaledb/actions/runs/30649015906, seen on PR #10377). Reproduced on current main (418b9dbc0).

A query whose ORDER BY matches a multi-column compress_orderby with a nullable leading column ordered DESC NULLS FIRST returns rows in the wrong order once the chunk has more than one compressed batch (e.g. after a delayed insert + recompression). The two batches are each internally sorted but are concatenated instead of merge-sorted.

Reproduction

SET timescaledb.enable_optimizations = on;

CREATE TABLE bug_repro (
    time timestamptz NOT NULL,
    seg int NOT NULL,
    rank int,          -- nullable orderby column
    val int NOT NULL
);
SELECT create_hypertable('bug_repro', 'time', chunk_time_interval => interval '1 year');

ALTER TABLE bug_repro SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg',
    timescaledb.compress_orderby = 'rank DESC NULLS FIRST, time'
);

INSERT INTO bug_repro VALUES
    ('2024-01-01 01:00:00+00', 1, NULL, 10),
    ('2024-01-01 02:00:00+00', 1, NULL, 20),
    ('2024-01-01 03:00:00+00', 1, 5, 30),
    ('2024-01-01 04:00:00+00', 1, 3, 40),
    ('2024-01-01 05:00:00+00', 1, 1, 50);
SELECT compress_chunk(c) FROM show_chunks('bug_repro') c;

-- Delayed insert into the compressed chunk, then recompress -> second batch.
INSERT INTO bug_repro VALUES
    ('2024-01-01 01:30:00+00', 1, NULL, 15),
    ('2024-01-01 03:30:00+00', 1, 4, 35),
    ('2024-01-01 06:00:00+00', 1, NULL, 60),
    ('2024-01-01 02:30:00+00', 1, 10, 25);
SELECT compress_chunk(c) FROM show_chunks('bug_repro') c;

SELECT rank, time, val
FROM bug_repro
WHERE seg = 1
ORDER BY rank DESC NULLS FIRST, time;

Expected (matches enable_optimizations = off)

 rank |          time          | val
------+------------------------+-----
      | 2024-01-01 01:00:00+00 |  10
      | 2024-01-01 01:30:00+00 |  15
      | 2024-01-01 02:00:00+00 |  20
      | 2024-01-01 06:00:00+00 |  60
   10 | 2024-01-01 02:30:00+00 |  25
    5 | 2024-01-01 03:00:00+00 |  30
    4 | 2024-01-01 03:30:00+00 |  35
    3 | 2024-01-01 04:00:00+00 |  40
    1 | 2024-01-01 05:00:00+00 |  50

Actual (enable_optimizations = on, default)

 rank |          time          | val
------+------------------------+-----
      | 2024-01-01 01:30:00+00 |  15
      | 2024-01-01 06:00:00+00 |  60
   10 | 2024-01-01 02:30:00+00 |  25
    4 | 2024-01-01 03:30:00+00 |  35
      | 2024-01-01 01:00:00+00 |  10
      | 2024-01-01 02:00:00+00 |  20
    5 | 2024-01-01 03:00:00+00 |  30
    3 | 2024-01-01 04:00:00+00 |  40
    1 | 2024-01-01 05:00:00+00 |  50

The output is the two compressed batches concatenated (second batch first), each internally ordered, but not merged across batches.

Cause

The plan sorts the compressed batches by their min/max metadata and then decompresses them in that order, relying on the batches being non-overlapping:

Custom Scan (ColumnarScan) on _hyper_1_1_chunk
  ->  Sort
        Sort Key: _ts_meta_v2_first_rank DESC, _ts_meta_v2_last_rank DESC,
                  _ts_meta_v2_first_time, _ts_meta_v2_last_time
        ->  Bitmap Heap Scan on _hyper_1_1_chunk_compressed

Both batches contain NULL rank values, which are not represented in the _ts_meta_v2_*_rank min/max metadata. With DESC NULLS FIRST the real first tuple of each batch is a NULL, so ordering the batches by their metadata does not reflect that the batches actually overlap, and the batches end up concatenated rather than merge-sorted.

This is the same nulls-not-in-minmax overlap-detection problem as #9444 / #9445, but for a multi-column orderby with a nullable leading DESC NULLS FIRST column — a combination not covered by those (single-column) fixes. Related: #9922 (multi-column, but non-null), #10058 (recompression + orderby direction).