cast_keep_nullable for default profile and ALTER UPDATE bug

[alexey-milovidov]

This is the bug in implementation of new setting cast_keep_nullable that was added in #11733.
This setting is disabled by default.

$ clickhouse-client -n
ClickHouse client version 20.7.1.1.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 20.7.1 revision 54437.

milovidov-desktop :) DROP TABLE IF EXISTS t;　CREATE TABLE t (x UInt64) ENGINE = MergeTree ORDER BY tuple();　INSERT INTO t SELECT number FROM numbers(10);　　SELECT * FROM t;　　SET mutations_sync = 1;　SET cast_keep_nullable = 1;　ALTER TABLE t UPDATE x = x % 3 = 0 ? NULL : x WHERE x % 2 = 1;　

DROP TABLE IF EXISTS t

Ok.

0 rows in set. Elapsed: 0.001 sec. 


CREATE TABLE t
(
    `x` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

Ok.

0 rows in set. Elapsed: 0.002 sec. 


INSERT INTO t SELECT number
FROM numbers(10)

Ok.

0 rows in set. Elapsed: 0.002 sec. 


SELECT *
FROM t

┌─x─┐
│ 0 │
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘

10 rows in set. Elapsed: 0.001 sec. 


SET mutations_sync = 1

Ok.

0 rows in set. Elapsed: 0.000 sec. 


SET cast_keep_nullable = 1

Ok.

0 rows in set. Elapsed: 0.000 sec. 


ALTER TABLE t
    UPDATE x = if((x % 3) = 0, NULL, x) WHERE (x % 2) = 1


Ok.

0 rows in set. Elapsed: 0.004 sec. 

milovidov-desktop :) SELECT *　FROM t　

SELECT *
FROM t

↖ Progress: 0.00 rows, 0.00 B (0.00 rows/s., 0.00 B/s.) Error on processing query: SELECT *
FROM t
Code: 271, e.displayText() = DB::Exception: Data compressed with different methods, given method byte 0x69, previous method byte 0x82: while receiving packet from localhost:9000, Stack trace (when copying this message, always include the lines below):

0. /home/milovidov/work/ClickHouse/build/../contrib/libcxx/include/exception:129: Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x104afd2c in /home/milovidov/work/ClickHouse/build/programs/clickhouse
1. /home/milovidov/work/ClickHouse/build/../src/Common/Exception.cpp:38: DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x9b317f9 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
2. /home/milovidov/work/ClickHouse/build/../src/Compression/CompressedReadBufferBase.cpp:0: DB::CompressedReadBufferBase::readCompressedData(unsigned long&, unsigned long&) @ 0xdc18548 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
3. /home/milovidov/work/ClickHouse/build/../src/Compression/CompressedReadBuffer.cpp:13: DB::CompressedReadBuffer::nextImpl() @ 0xdc170b0 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
4. /home/milovidov/work/ClickHouse/build/../src/IO/ReadBuffer.h:54: DB::NativeBlockInputStream::readImpl() @ 0xe1b3f02 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
5. /home/milovidov/work/ClickHouse/build/../src/DataStreams/IBlockInputStream.cpp:0: DB::IBlockInputStream::read() @ 0xdcd3155 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
6. /home/milovidov/work/ClickHouse/build/../contrib/libcxx/include/memory:3821: DB::Connection::receiveDataImpl(std::__1::shared_ptr<DB::IBlockInputStream>&) @ 0xe67719a in /home/milovidov/work/ClickHouse/build/programs/clickhouse
7. /home/milovidov/work/ClickHouse/build/../src/Core/Block.h:27: DB::Connection::receivePacket() @ 0xe6767af in /home/milovidov/work/ClickHouse/build/programs/clickhouse
8. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:1591: DB::Client::receiveAndProcessPacket(bool) @ 0x9bdd5a3 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
9. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:0: DB::Client::receiveResult() @ 0x9bdf3d4 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
10. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:0: DB::Client::processOrdinaryQuery() @ 0x9bd7c37 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
11. /home/milovidov/work/ClickHouse/build/../contrib/libcxx/include/memory:4206: DB::Client::processParsedSingleQuery() @ 0x9bd6e5b in /home/milovidov/work/ClickHouse/build/programs/clickhouse
12. /home/milovidov/work/ClickHouse/build/../contrib/libcxx/include/memory:4059: DB::Client::processMultiQuery(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&) @ 0x9bd5ff8 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
13. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:908: DB::Client::processQueryText(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&) @ 0x9bcd9be in /home/milovidov/work/ClickHouse/build/programs/clickhouse
14. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:630: DB::Client::mainImpl() @ 0x9bc9e1b in /home/milovidov/work/ClickHouse/build/programs/clickhouse
15. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:0: DB::Client::main(std::__1::vector<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> >, std::__1::allocator<std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > > > const&) @ 0x9bc70e6 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
16. /home/milovidov/work/ClickHouse/build/../contrib/poco/Util/src/Application.cpp:0: Poco::Util::Application::run() @ 0x1043ee4a in /home/milovidov/work/ClickHouse/build/programs/clickhouse
17. /home/milovidov/work/ClickHouse/build/../programs/client/Client.cpp:0: mainEntryClickHouseClient(int, char**) @ 0x9bbf4d3 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
18. /home/milovidov/work/ClickHouse/build/../programs/main.cpp:0: main @ 0x9b245c3 in /home/milovidov/work/ClickHouse/build/programs/clickhouse
19. __libc_start_main @ 0x271e3 in /usr/lib/x86_64-linux-gnu/libc-2.30.so
20. _start @ 0x9b2402e in /home/milovidov/work/ClickHouse/build/programs/clickhouse
 (version 20.7.1.1)

[alexey-milovidov]

@4ertus2 We can avoid this issue (and possibly many other) if we introduce another function named castInternal (or _cast, depending on your taste) that will not be affected by cast_keep_nullable and make all internal usages to use this function.

[kssenii]

@alexey-milovidov looks like it is already fixed on master because:

kssenii.ru-central1.internal  :) DROP TABLE IF EXISTS t;　CREATE TABLE t (x UInt64) ENGINE = MergeTree ORDER BY tuple();　INSERT INTO t SELECT number FROM numbers(10);　　SELECT * FROM t;　　SET mutations_sync = 1;　SET cast_keep_nullable = 1;　ALTER TABLE t UPDATE x = x % 3 = 0 ? NULL : x WHERE x % 2 = 1;　

DROP TABLE IF EXISTS t

Query id: fe2b9cdf-db26-41cd-bfa7-d329bdc44357

Ok.

0 rows in set. Elapsed: 0.006 sec.


CREATE TABLE t
(
    `x` UInt64
)
ENGINE = MergeTree
ORDER BY tuple()

Query id: e2881436-26fc-481e-a8c5-daa6bc67f0f1

Ok.

0 rows in set. Elapsed: 0.020 sec.


INSERT INTO t SELECT number
FROM numbers(10)

Query id: d331f663-f489-49da-806b-cd906a4e671f

Ok.

0 rows in set. Elapsed: 0.009 sec.


SELECT *
FROM t

Query id: 06c9a353-886b-497e-8931-e79e826629bc


┌─x─┐
│ 0 │
│ 1 │
│ 2 │
│ 3 │
│ 4 │
│ 5 │
│ 6 │
│ 7 │
│ 8 │
│ 9 │
└───┘

10 rows in set. Elapsed: 0.008 sec.


SET mutations_sync = 1

Query id: 4d4daebe-d507-4ae2-9103-74ce315c09c2

Ok.

0 rows in set. Elapsed: 0.002 sec.


SET cast_keep_nullable = 1

Query id: c812e1ea-c0f9-489f-8d80-13f7462bda83

Ok.

0 rows in set. Elapsed: 0.001 sec.


ALTER TABLE t
    UPDATE x = if((x % 3) = 0, NULL, x) WHERE (x % 2) = 1

Query id: 5ec06747-3037-4977-82aa-33d2de761417


0 rows in set. Elapsed: 0.038 sec.

Received exception from server (version 21.9.1):
Code: 341. DB::Exception: Received from localhost:9000. DB::Exception: Exception happened during execution of mutation 'mutation_2.txt' with part 'all_1_1_0'
 reason: 'Code: 349. DB::Exception: Cannot convert NULL value to non-Nullable type: while executing 'FUNCTION CAST(if(equals(modulo(x, 3), 0), NULL, x) :: 3, 'UInt64' : 5) -> CAST(if(equals(modulo(x, 3), 0), NULL, x), 'UInt64') UInt64 : 4'. (CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN) (version 21.9.1.1)'. This error maybe retryable or not. In case of unretryable error, mutation can be killed with KILL MUTATION query. (UNFINISHED)