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
Nessuno ha ancora preso questa issue.
Valutazione
- Difficoltà
- 4/5
- Tempo stimato
- 3-5 giorni
- Idoneità per principianti
- 52/100
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
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)withm > n, andVARBIT(m)→BIT(m), are classified as trivial conversions
(relabel only);- narrowing conversions such as
BIT(4)→BIT(2)andBIT(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
cockroach start-single-node --insecure- 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.
- 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):
-
BitFamily→BitFamilyconversions useclassifierWidth:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachange/alter_column_type.go#L91-L95
classifierWidthtreats the width as a maximum length. It returnsColumnConversionTrivialfor an
equal or larger width andColumnConversionValidatefor a smaller one, without checking whether the
target is the fixed-lengthBIT(oidbit) orVARBIT:
https://github.com/cockroachdb/cockroach/blob/d30c905fff79ef825adc96bcc647f1872a90f2ff/pkg/sql/schemachange/alter_column_type.go#L213-L229 -
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 -
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 toCHAR(bpchar) targets, where a validated conversion
is sufficient. The bit types need different handling, becauseBIT(n)→BIT(m)between two
fixed-length types is also wrong, and the validation check cannot detect a length change. - #164721 / #164739 (closed): fixed
classifierWidthfor 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
COPYreload 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
- Nessun Dockerfile né file Docker Compose
- Nessun modello di pull request
- Leggi la guida per i contributori
Come iniziare
- Leggi tutta la issue e poi la guida ai contributi del progetto.
- Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
- Fai un fork del repository e lavora su un branch.
- Apri una pull request che faccia riferimento al numero della issue.
Altre issue di cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
cockroachdb/cockroach#176245 · 1 commento ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinForse già presa @1AbdullahButt l’ha presa 14 giorni fa. ApertaC-bug O-community X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
cockroachdb/cockroach#176228 · 1 commento ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEForse di nuovo libera @darshpatel1052 l’ha presa 18 giorni fa e non c’è nessuna pull request aperta. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 2/5 1-3 ore Idoneità per principianti 62/100
cockroachdb/cockroach#176223 · 2 commenti ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutForse già presa @dajiaohuang l’ha presa 15 giorni fa. ApertaC-bug O-community X-blathers-untriaged
Difficoltà 1/5 Meno di un'ora Idoneità per principianti 84/100
cockroachdb/cockroach#176210 · 1 commento ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
cockroachdb/cockroach#176150 · 1 commento ·
Tutte le issue di cockroachdb/cockroach
Issue simili
-
☢️ Bug v3
Difficoltà 2/5 1-3 ore Idoneità per principianti 82/100
I maintainer di solito rispondono entro 1 giorno
-
area/import-export area/projects concern/ux
Difficoltà 2/5 1-3 ore Idoneità per principianti 68/100
go-vikunja/vikunja#4455 · 1 commento ·
I maintainer di solito rispondono entro 2 giorni
-
Dependabot PRs no longer merge automaticallyForse già presa @dwisiswant0 l’ha presa oggi. Aperta
Difficoltà 2/5 1-3 ore Idoneità per principianti 78/100
projectdiscovery/urlfinder#160 ·
I maintainer di solito rispondono entro 1 giorno
-
database
Difficoltà 2/5 1-3 ore Idoneità per principianti 72/100
chnm/legal-modernism#450 ·
I maintainer di solito rispondono entro 1 giorno
-
tech-debt
Difficoltà 2/5 1-3 ore Idoneità per principianti 76/100
I maintainer di solito rispondono entro 1 giorno