During insert to a MergeTree table materialized column breaks materialized view insert #1645
Description
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.