sql: `ALTER COLUMN ... TYPE` between BIT/VARBIT widths keeps bit strings of the wrong length (`BIT(4)` → `BIT(8)`, `VARBIT(4)` → `BIT(4)`, `BIT(4)` → `BIT(2)`); lookups miss the rows and UNIQUE accepts duplicates

Aperta
#176,238 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
52/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
go, sql

Direzione di ricerca

Start with pkg/sql/schemachange/alter_column_type.go, especially the bit family conversion classification, then read pkg/sql/schemachanger/scbuild/internal/scbuildstmt/alter_table_alter_column_type.go for how trivial and validated conversions are applied. Reproduce with the SQL statements in the issue and inspect existing ALTER COLUMN TYPE tests for bit types. Done means conversions leave every stored value valid for its new type and the reproduction no longer produces mismatched lengths, missed lookups, or duplicate UNIQUE values.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

C-bug O-community X-blathers-triaged

Describe the problem

BIT(n) is a fixed-length type: every value has exactly n bits. INSERT and UPDATE enforce this
(bit string length 4 does not match type BIT(8)), and an explicit cast pads or truncates with zero
bits (B'1010'::BIT(8) is 10100000). ALTER COLUMN ... TYPE between bit types does neither. The
column type is changed, and the stored values keep their old length:

  • BIT(n) → BIT(m) with m > n, and VARBIT(m) → BIT(m), are classified as trivial conversions
    (relabel only);
  • narrowing conversions such as BIT(4) → BIT(2) and BIT(4) → VARBIT(2) are validated, but the
    validation casts to the new type and back. That round trip only adds or removes zero bits, so values
    whose dropped bits are zero pass and are relabelled unchanged.

After the ALTER returns success, the column holds values that its type cannot hold. Lookups by the value
the cast produces miss these rows, and a UNIQUE index accepts the relabelled value and the correctly
sized one side by side. The stored values cannot be inserted again (INSERT rejects them), and a
COPY TO / COPY FROM reload into a table with the same schema fails on the UNIQUE index, because
COPY FROM pads the short values, which makes rows equal.

To Reproduce

  1. cockroach start-single-node --insecure
  2. In cockroach sql --insecure (default settings), one statement at a time:
CREATE TABLE flags (id INT PRIMARY KEY, f BIT(4), v VARBIT(4), UNIQUE INDEX flags_f_key (f));
INSERT INTO flags VALUES (1, B'1010', B'1'), (2, B'0110', B'0101');
ALTER TABLE flags ALTER COLUMN f TYPE BIT(8);   -- succeeds
ALTER TABLE flags ALTER COLUMN v TYPE BIT(4);   -- succeeds
INSERT INTO flags VALUES (9, B'1010', B'1');    -- ERROR: bit string length 4 does not match type BIT(8)
INSERT INTO flags VALUES (3, B'10100000', B'1000');
SELECT id, f, length(f) AS f_len, v, length(v) AS v_len FROM flags ORDER BY id;

Output on master (identical on v26.3.5):

  id |    f     | f_len |  v   | v_len
-----+----------+-------+------+--------
   1 |     1010 |     4 |    1 |     1
   2 |     0110 |     4 | 0101 |     4
   3 | 10100000 |     8 | 1000 |     4
(3 rows)

SHOW CREATE TABLE flags shows f BIT(8) and v BIT(4), but rows 1 and 2 still hold 4-bit values in
f, and row 1 a 1-bit value in v. Converted with the cast, row 1 would be B'1010'::BIT(8) =
10100000 and B'1'::BIT(4) = 1000, the values of row 3, but it is not found by those values:

SELECT id FROM flags WHERE f = B'10100000' ORDER BY id;   -- 3
SELECT id FROM flags WHERE v = B'1000' ORDER BY id;       -- 3
SELECT f FROM flags@flags_f_key ORDER BY f;               -- 0110, 1010, 10100000

The table cannot be reloaded into the same schema. Its COPY flags TO STDOUT output is
1 1010 1, 2 0110 0101, 3 10100000 1000. Loading that with COPY ... FROM STDIN (psycopg2
copy_expert) into CREATE TABLE flags3 (id INT PRIMARY KEY, f BIT(8), v BIT(4), UNIQUE INDEX flags3_f_key (f))
fails with duplicate key value violates unique constraint "flags3_f_key", because COPY FROM pads 1010
to 10100000. Without the UNIQUE index, the load succeeds and stores the padded values.

  1. Narrowing goes through validation and is accepted for values whose removed bits are zero:
CREATE TABLE n (id INT PRIMARY KEY, b BIT(4), vb BIT(4));
INSERT INTO n VALUES (1, B'1000', B'1000');
ALTER TABLE n ALTER COLUMN b TYPE BIT(2);       -- succeeds
ALTER TABLE n ALTER COLUMN vb TYPE VARBIT(2);   -- succeeds
SELECT id, b, length(b) AS b_len, vb, length(vb) AS vb_len FROM n;
  id |  b   | b_len |  vb  | vb_len
-----+------+-------+------+---------
   1 | 1000 |     4 | 1000 |      4

The same values cannot be written to these columns:
INSERT INTO n VALUES (2, B'1000', B'10') fails with bit string length 4 does not match type BIT(2),
and INSERT INTO n VALUES (2, B'10', B'1000') with bit string length 4 too large for type VARBIT(2).

Expected behavior

After ALTER COLUMN ... TYPE, every stored value is a valid value of the new type. Either the ALTER
fails when a value does not fit, or it rewrites the values. PostgreSQL 20devel refuses both ALTERs
of step 2: ALTER TABLE flags ALTER COLUMN f TYPE bit(8) fails with
bit string length 4 does not match type bit(8), and ... ALTER COLUMN v TYPE bit(4) fails with
bit string length 1 does not match type bit(4).

Additional data / screenshots

Cause (permalinks at d30c905fff79, the current public master HEAD; the master build tested gives the
output above):

  1. BitFamily → BitFamily conversions use classifierWidth:
    https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachange/alter_column_type.go#L91-L95
    classifierWidth treats the width as a maximum length. It returns ColumnConversionTrivial for an
    equal or larger width and ColumnConversionValidate for a smaller one, without checking whether the
    target is the fixed-length BIT (oid bit) or VARBIT:
    https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachange/alter_column_type.go#L213-L229

  2. A trivial conversion only updates the column type:
    https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/alter_table_alter_column_type.go#L223-L230

  3. A validated conversion also keeps the stored values. Its check is
    CAST(CAST(col AS <new>) AS <old>) = col:
    https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachanger/scbuild/internal/scbuildstmt/alter_table_alter_column_type.go#L232-L265
    For bit strings the explicit casts pad and truncate with zero bits, so the check does not test the
    length:

SELECT CAST(CAST(B'1010'::BIT(4) AS BIT(8)) AS BIT(4)) = B'1010'::BIT(4) AS widening_check,
       CAST(CAST(B'1000'::BIT(4) AS BIT(2)) AS BIT(4)) = B'1000'::BIT(4) AS narrowing_check;
--  widening_check | narrowing_check
--        t        |        t

The check does catch values whose removed bits are not zero (BIT(8) → BIT(4) with B'10100001'
fails with validation of CHECK "CAST(CAST(b AS BIT(4)) AS BIT(8)) = b" failed). It also catches a
short value when narrowing from VARBIT (VARBIT(8) → BIT(4) with B'1' fails), because the cast
back to VARBIT keeps the padding.

So making these conversions validated would not be enough: BIT(4) → BIT(8) would pass the same check
for every value. Any change of the width of a BIT column, any conversion to BIT(m) from VARBIT, and
any narrowing to BIT(m)/VARBIT(m) needs either a rewrite of the values with the cast, or a
validation that compares the bit length (length(col)) with the new width, as an assignment to the
column does.

Related:

  • #176156 (open): the same classifier treats VARCHAR(n) → CHAR(m) as trivial and keeps trailing
    spaces. The fix proposed there is specific to CHAR (bpchar) targets, where a validated conversion
    is sufficient. The bit types need different handling, because BIT(n) → BIT(m) between two
    fixed-length types is also wrong, and the validation check cannot detect a length change.
  • #164721 / #164739 (closed): fixed classifierWidth for unbounded → bounded widths (e.g.
    VARBIT → VARBIT(100)), a different case.

Environment:

  • CockroachDB version: master edge build v27.1.0-alpha.00000000-dev-fc36d2fe578aab6d0c861e46403f029f938c5a5a (built 2026-11-08) and v26.3.5; identical output of step 2 on both (step 3, the COPY reload and the check expressions were run on master)
  • Server OS: Linux x86_64 (Ubuntu 22.04), single node, --insecure
  • Client app: cockroach sql, psycopg2

Additional context

Widening a BIT(n) flag set or turning a VARBIT column into a fixed-length BIT column are ordinary
schema migrations. They report success, but leave rows that are not found by their converted value, a
UNIQUE index that admits both forms of the same flags, and stored values that INSERT rejects and that
a COPY reload into the same schema changes or refuses. No error or notice is shown.

Jira issue: CRDB-71985

Lingua principale
Go
Stelle
32.6k
Fork
4.2k
Metriche di merge delle PR
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di cockroachdb/cockroach

Tutte le issue di cockroachdb/cockroach

Issue simili

Altre issue su Go

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.