Skip to content

Updating a Nullable column to a value would sets another value if where is used #13634

@mtoloon

Description

@mtoloon

Updating a Nullable column value by using alter .... where ... causes to set another value!

ClickHouse version: 20.6.3.28
Engine: MergeTree
OS: Ubuntu 18.04.3 LTS

Steps to reproduce:

CREATE TABLE table1(
	id int,
	price Nullable(Int32)
)
ENGINE = MergeTree()
PARTITION BY id
ORDER BY (id);
INSERT INTO table1 (id, price) VALUES (1, 100);
ALTER TABLE table1  update price = 150 where id=1;
SELECT * FROM table1;

We expect the price to be 150, but it's 93148480:

┌─id─┬────price─┐
│  1 │ 93148480 │
└────┴──────────┘

Using Decimal(9,2) field instead of Int32 would set the value 932342.88 always. It seems there's a fixed value for each data type.

If we don't use such a where clause, it works correctly:

ALTER TABLE db1.table1  update price = 150 where 1=1;
┌─id─┬─price─┐
│  1150 │
└────┴───────┘

Metadata

Metadata

Assignees

Labels

bugConfirmed user-visible misbehaviour in official releasecomp-mutationsALTER UPDATE/DELETE and mutation execution over parts (including lightweight updates/deletes).major

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions