Skip to content

unique constraint broken with compressed INSERT and non-deterministic collation #10297

Description

@akuzm
-- INSERT into compressed hypertable with a case-insensitive COLLATE on the
-- unique index admits a duplicate that the same index rejects on a plain table.

CREATE COLLATION nocase (provider = icu, locale = 'und-u-ks-level2', deterministic = false);

-- Baseline: plain table rejects the case-equivalent duplicate.
BEGIN;
CREATE TABLE vintage_wines(batch_date int NOT NULL, label text NOT NULL, score int);
CREATE UNIQUE INDEX vintage_wines_uq ON vintage_wines(label COLLATE nocase, batch_date);
INSERT INTO vintage_wines VALUES (10, 'merlot', 90);
INSERT INTO vintage_wines VALUES (10, 'MERLOT', 85);  -- ERROR 23505
ROLLBACK;

-- Compressed hypertable: same index, same data, INSERT succeeds.
CREATE TABLE vintage_wines(batch_date int NOT NULL, label text NOT NULL, score int) WITH (
    tsdb.hypertable, tsdb.partition_column = 'batch_date', tsdb.chunk_interval = 1000
);
INSERT INTO vintage_wines VALUES (10, 'merlot', 90);
ALTER TABLE vintage_wines SET (timescaledb.compress);
SELECT compress_chunk(c) FROM show_chunks('vintage_wines') c;

CREATE UNIQUE INDEX vintage_wines_uq ON vintage_wines(label COLLATE nocase, batch_date);

INSERT INTO vintage_wines VALUES (10, 'MERLOT', 85);  -- INSERT 0 1, expected 23505

Metadata

Metadata

Assignees

No one assigned

    Labels

    ColumnstoreRelated to the column store / compressionbugllm-fuzzerIssue fixed found by llm-fuzzer

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions