During insert to a MergeTree table materialized column breaks materialized view insert

[ozcelgozde]

Bug Report

Briefly describe the bug

We previously reported inconsistent behaviour with materialized columns + materialized views during both server and direct worker insert. They got tagged duplicate and did not get a resolution so opening a fresh ticket with a reproducible sample to speed things up. Currently we are unable to insert to due to error:

Code: 10, e.displayText() = DB::Exception: Expected column test.column: while pushing to view ed.test_mv SQLSTATE: 22000 (version 21.8.7.1)

2024.04.22 18:14:09.446540 [ 406 ] {00782240-42bf-4038-9c3c-3fbf24e66de0} (449264916613824523) <Error> DynamicQueryHandler: Code: 10, e.displayText() = DB::Exception: Expected column test.column: while pushing to view ed.test_mv SQLSTATE: 22000, Stack trace (when copying this message, always include the lines below):

0. Poco::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) @ 0x273a0892 in /opt/byconity/bin/clickhouse
1. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0x108fb1a0 in /opt/byconity/bin/clickhouse
2. DB::StorageInMemoryMetadata::check(DB::Block const&, bool) const @ 0x21b9217c in /opt/byconity/bin/clickhouse
3. DB::MergeTreeDataWriter::splitBlockIntoParts(DB::Block const&, unsigned long, std::__1::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::__1::shared_ptr<DB::Context const>) @ 0x220fc6ec in /opt/byconity/bin/clickhouse
4. DB::CloudMergeTreeBlockOutputStream::convertBlockIntoDataParts(DB::Block const&, bool) @ 0x21eb46bc in /opt/byconity/bin/clickhouse
5. DB::CloudMergeTreeBlockOutputStream::write(DB::Block const&) @ 0x21eb39be in /opt/byconity/bin/clickhouse
6. DB::PushingToViewsBlockOutputStream::process(DB::Block const&, DB::PushingToViewsBlockOutputStream::ViewInfo&) @ 0x2112a091 in /opt/byconity/bin/clickhouse
7. DB::PushingToViewsBlockOutputStream::write(DB::Block const&) @ 0x21128fbe in /opt/byconity/bin/clickhouse
8. DB::AddingDefaultBlockOutputStream::write(DB::Block const&) @ 0x211255ac in /opt/byconity/bin/clickhouse
9. DB::SquashingBlockOutputStream::finalize() @ 0x21124cf4 in /opt/byconity/bin/clickhouse
10. DB::SquashingBlockOutputStream::writeSuffix() @ 0x21124dae in /opt/byconity/bin/clickhouse
11. DB::CountingBlockOutputStream::writeSuffix() @ 0x2079a003 in /opt/byconity/bin/clickhouse
12. DB::ISink::prepare() @ 0x223f91bf in /opt/byconity/bin/clickhouse
13. DB::PipelineExecutor::prepareProcessor(unsigned long, unsigned long, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::unique_lock<std::__1::mutex>) @ 0x2242f8c5 in /opt/byconity/bin/clickhouse
14. DB::PipelineExecutor::tryAddProcessorToStackIfUpdated(DB::ExecutingGraph::Edge&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, unsigned long) @ 0x2242f765 in /opt/byconity/bin/clickhouse
15. DB::PipelineExecutor::prepareProcessor(unsigned long, unsigned long, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::unique_lock<std::__1::mutex>) @ 0x2242fe8d in /opt/byconity/bin/clickhouse
16. DB::PipelineExecutor::tryAddProcessorToStackIfUpdated(DB::ExecutingGraph::Edge&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, unsigned long) @ 0x2242f765 in /opt/byconity/bin/clickhouse
17. DB::PipelineExecutor::prepareProcessor(unsigned long, unsigned long, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::queue<DB::ExecutingGraph::Node*, std::__1::deque<DB::ExecutingGraph::Node*, std::__1::allocator<DB::ExecutingGraph::Node*> > >&, std::__1::unique_lock<std::__1::mutex>) @ 0x2242fe8d in /opt/byconity/bin/clickhouse
18. DB::PipelineExecutor::executeStepImpl(unsigned long, unsigned long, std::__1::atomic<bool>*) @ 0x22434bad in /opt/byconity/bin/clickhouse
19. DB::PipelineExecutor::executeImpl(unsigned long) @ 0x2243310c in /opt/byconity/bin/clickhouse
20. DB::PipelineExecutor::execute(unsigned long) @ 0x22432b05 in /opt/byconity/bin/clickhouse
21. DB::executeQuery(DB::ReadBuffer&, DB::WriteBuffer&, bool, std::__1::shared_ptr<DB::Context>, std::__1::function<void (std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, std::__1::shared_ptr<DB::MPPQueryCoordinator>)>, std::__1::optional<DB::FormatSettings> const&, bool) @ 0x215aa389 in /opt/byconity/bin/clickhouse
22. DB::HTTPHandler::processQuery(std::__1::shared_ptr<DB::Context>, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0x22361dd9 in /opt/byconity/bin/clickhouse
23. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x223649cf in /opt/byconity/bin/clickhouse
24. DB::HTTPServerConnection::run() @ 0x223d8d59 in /opt/byconity/bin/clickhouse
25. Poco::Net::TCPServerConnection::start() @ 0x273161ec in /opt/byconity/bin/clickhouse
26. Poco::Net::TCPServerDispatcher::run() @ 0x273166cc in /opt/byconity/bin/clickhouse
27. Poco::PooledThread::run() @ 0x273fd5ba in /opt/byconity/bin/clickhouse
28. Poco::ThreadImpl::runnableEntry(void*) @ 0x273fb16c in /opt/byconity/bin/clickhouse
29. start_thread @ 0x7ea7 in /lib/x86_64-linux-gnu/libpthread-2.31.so
30. clone @ 0xfca2f in /lib/x86_64-linux-gnu/libc-2.31.so
 (version 21.8.7.1)

cc @kevinthfang since you seemed to tag this issue as duplicate the last time.

The result you expected

Insert to go through without a problem.

How to Reproduce

CREATE TABLE ed.test_table (    
 `timestamp` DateTime64(3) CODEC(Delta(8), ZSTD(1)),    
 `test_keys` Array(LowCardinality(String)) CODEC(ZSTD(1)),     
`test_values` Array(String) CODEC(ZSTD(1)),     
`test.column` LowCardinality(String) MATERIALIZED test_values[indexOf(test_keys, 'environment')] CODEC(LZ4),    
) 
ENGINE = CnchMergeTree 
ORDER BY timestamp 
SETTINGS storage_policy = 'cnch_default_s3', index_granularity = 8192


CREATE TABLE ed.test_table_samp_10
(
    `timestamp` DateTime64(3) CODEC(Delta(8), ZSTD(1)),
    `test_keys` Array(LowCardinality(String)) CODEC(ZSTD(1)),
    `test_values` Array(String) CODEC(ZSTD(1)),
    `test.column` LowCardinality(String) MATERIALIZED test_values[indexOf(test_keys, 'environment')] CODEC(LZ4),
)
ENGINE = CnchMergeTree
ORDER BY timestamp
SETTINGS storage_policy = 'cnch_default_s3', index_granularity = 8192


CREATE MATERIALIZED VIEW ed.test_mv TO ed.test_table_samp_10 AS SELECT timestamp, test_keys, test_values FROM ed.test_table

INSERT INTO ed.test_table (timestamp, test_keys, test_values) 
VALUES ('2024-09-24 08:00:00.000',['environment','mat-column'], ['staging', 'test']);

Version

This does not work on version 0.4.0 still.

[kevinthfang]

Sorry for the unexpected tagging, will look into this

[jenrryyou]

we will fix it in next week

[jenrryyou]

The short-term fix is to temporarily prohibit the creation of materialized views on tables with materialized columns(it's already merged to master). As for the long-term fix, we will need to wait for further solutions. One workaround is to move the logic of the materialized columns to the SELECT statement of the materialized view. This can help avoid the issue.

[ozcelgozde]

Short term fix would still be a blocker for us since we already have our tables deployed in this manner. Do you have an ETA in mind for the long term solution? I will try to decide on an action plan according to that.