21.10+: MODIFY COLUMN to Nullable may lead to data becoming unavaliable / incorrect

[filimonov]

repro

DROP TABLE IF EXISTS column_modify_test;

CREATE TABLE column_modify_test (id UInt64, val String, other_col UInt64) engine=MergeTree ORDER BY id SETTINGS min_bytes_for_wide_part=0;
INSERT INTO column_modify_test VALUES (1,'one',0);
INSERT INTO column_modify_test VALUES (2,'two',0);

-- on 21.9 that was done via mutations mechanism
ALTER TABLE column_modify_test MODIFY COLUMN val Nullable(String);

-- but since 21.10 it only applies that to new part, so old parts keep the old schema
SELECT * FROM system.mutations;

INSERT INTO column_modify_test VALUES (3,Null,0);

select name, path, type, active, modification_time from system.parts_columns where table='column_modify_test' and column='val';

-- till now everythings looks ok
SELECT * FROM column_modify_test;

-- now we do mutation. It will affect one of the parts
-- and it what part it will update columns.txt to the latest 'correct' state w/o updating the column file!
alter table column_modify_test update other_col=1 where id = 1 SETTINGS mutations_sync=1;

-- row 1 is damaged now: the column files of val columns & columns.txt is out of sync!
SELECT *, throwIf(val <> 'one') as issue FROM column_modify_test WHERE id = 1 FORMAT CSV;
-- expected result is: 1,"one",1,0

-- Code: 366. DB::Exception: Received from localhost:9000. DB::Exception: Logical error: Sizes of nested column and null map of Nullable column are not equal. (SIZES_OF_NESTED_COLUMNS_ARE_INCONSISTENT)

[filimonov]

Workaround to make data readable again: for parts with old state/type of columns rewrite columns.txt

[Algunenano]

I can't seem to reproduce it, neither in master nor in 22.3. Neither if I run the queries manually or if I call them with --queries-file:

$ ch_client_prod-01 --queries-file repro.txt 
delpop  ds      0000000000      DELETE WHERE t > \'1970-01-01 00:00:00\'        2022-10-03 18:14:44     ['all'] [1]     []  01               1970-01-01 00:00:00
all_1_1_0       /mnt/ch/ch_data/data/prod-1/store/e3a/e3a7f67e-b40d-4b69-a3cf-eecd12a36483/all_1_1_0/   String  1       2022-10-05 11:29:20
all_2_2_0       /mnt/ch/ch_data/data/prod-1/store/e3a/e3a7f67e-b40d-4b69-a3cf-eecd12a36483/all_2_2_0/   String  1       2022-10-05 11:29:20
all_3_3_0       /mnt/ch/ch_data/data/prod-1/store/e3a/e3a7f67e-b40d-4b69-a3cf-eecd12a36483/all_3_3_0/   Nullable(String)    12022-10-05 11:29:20
3       \N      0
2       two     0
1       one     0
3       \N      0
1       \N      1
2       two     0
1       \N      1
3       \N      0
2       two     0

Does this need some kind of restart, debug build or anything?

[filimonov]

I've edited test a bit to show that it reproduces.

1       \N      1

that line is wrong, it should be

1       one      1

[filimonov]

Latest (22.9.3.18)

https://fiddle.clickhouse.com/4735a190-339a-42fc-adc1-86eab7b62ee1

Last good

https://fiddle.clickhouse.com/269f60b2-e5f6-44eb-9fe2-644bc292678c

[Algunenano]

Great, now I understand it. Thanks!

I guess that as a workaround to avoid this problem at the user level would be to force a rewrite of the parts after MODIFY COLUMN by calling OPTIMIZE FINAL.

[filimonov]

Great, now I understand it. Thanks!

I guess that as a workaround to avoid this problem at the user level would be to force a rewrite of the parts after MODIFY COLUMN by calling OPTIMIZE FINAL.

OPTIMIZE FINAL can be superexpensive.
ALTER TABLE MODIFY COLUMN x = x WHERE 1

can be used.

But usually the problem discovered much later.

for d in `find . -name "*-*"`; do
  lc=`cat $d/columns.txt | grep "val String" | wc -l`
  if [[ ! -f $d/val.null.bin && $lc -gt 0 ]]; then
    echo "Fixing $d"
    cp -f /tmp/columns.txt $d/columns.txt
  fi
done

[filimonov]

Root cause: #27787

Related: #42074

[Slach]

ALTER TABLE MODIFY COLUMN x = x WHERE 1

Doesn't work, part types still inconsistent ;( but not fot all cases
Only OPTIMIZE FINAL helps ;(

[filimonov]

Revert does not resolves the root cause of the issue.

columns.txt file still can be damaged by mutation / alter in a way when some columns will become unreadable.

Also related to #38067

[alexey-milovidov]

@filimonov please provide a test case.

[den-crane]

The root cause has been fixed #42319
tests are in 02461_alter_update_respect_part_column_type_bug.sql

[lesandie]

Also another case after ALTER TABLE t MODIFY COLUMN c Nullable(UInt64):

Code: 341. DB::Exception: Exception happened during execution of mutation '0000000001' with part 'all_0_7_1_8' 
It seems part all_0_7_1_8 has missing columns ...

And this column is part of a projection but I don't know if it also is affected. I'll investigate if this is also fixed by #42319

[alexey-milovidov]

The issue is not fixed.

[alexfernandez]

This issue seems fixed in all versions from 23.x onwards. At least the reproducer is not reproduced, e.g.. Are there any other cases where a MODIFY TABLE to Nullable may cause corruption? If so, do you have a reproducer?

[fm4v]

Fixed