A Sign type for CollapsingMergeTree, and the CHECK that made it unnecessary

CollapsingMergeTree trusts that its sign column holds only 1 or -1, but the column is an Int8. On a production system one client inserted a 2; the row could not collapse against anything, merges complained, and the error flooded the logs. The only exit I knew was copying the table without the bad rows. I proposed a Sign data type accepting only +1 and -1, or any way to refuse other values on insert.

Two replies the next day made it unnecessary. A user suggested CONSTRAINT c_sign CHECK sign IN (-1, 1), a feature I had forgotten. A ClickHouse developer pointed out that optimize_on_insert (on by default) collapses each block before writing it and throws if the sign is unexpected. The issue was closed in August 2021 with nothing implemented.

On 26.4 the engine alone rejects the row:

CREATE TABLE c1 (k UInt32, sign Int8) ENGINE = CollapsingMergeTree(sign) ORDER BY k;
INSERT INTO c1 VALUES (1, 1), (2, 2);
-- Code: 117. Incorrect data: Sign = 2 (must be 1 or -1). (INCORRECT_DATA)

That check depends on optimize_on_insert; a profile that disables the setting loses it. The constraint does not depend on any setting, and it is the line I put on every collapsing table.