Materialized view cannot copy materialized columns

[ozcelgozde]

Bug Report

Hey, I have byconity version 0.2.2 installed and i created a base table like this

create table test_db.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

and i have a materialized view that works to copy data from the main table to downsampled %10 table randomly.

CREATE MATERIALIZED VIEW test_db.test_mv TO test_db.test_table_samp AS WITH exp2(64) - 1 AS MAX_UINT64 SELECT * FROM test_db.test_table WHERE cityHash64(id) < (MAX_UINT64 / 10)

Briefly describe the bug

When I tried to insert data to this table over http with native format and i get errors

<Error> executeQuery: Code: 8, e.displayText() = DB::Exception: Cannot find column `test.column` in source stream: while pushing to view test_db.test_mv SQLSTATE: 22000 (version 21.8.7.1) (from [::ffff:127.0.0.1]:40908) (in query: INSERT INTO test_db.test_table FORMAT Native SETTINGS max_threads_for_cnch_dump=4 ), 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) @ 0x1dbd2852 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) @ 0xd2b5aa0 in /opt/byconity/bin/clickhouse
2. DB::ConvertingBlockInputStream::ConvertingBlockInputStream(std::__1::shared_ptr<DB::IBlockInputStream> const&, DB::Block const&, DB::ConvertingBlockInputStream::MatchColumnsMode) @ 0x17a42c6d in /opt/byconity/bin/clickhouse
3. DB::PushingToViewsBlockOutputStream::process(DB::Block const&, DB::PushingToViewsBlockOutputStream::ViewInfo&) @ 0x180a5983 in /opt/byconity/bin/clickhouse
4. DB::PushingToViewsBlockOutputStream::write(DB::Block const&) @ 0x180a4a1e in /opt/byconity/bin/clickhouse
5. DB::AddingDefaultBlockOutputStream::write(DB::Block const&) @ 0x180a12ec in /opt/byconity/bin/clickhouse
6. DB::SquashingBlockOutputStream::finalize() @ 0x180a0a34 in /opt/byconity/bin/clickhouse
7. DB::SquashingBlockOutputStream::writeSuffix() @ 0x180a0aee in /opt/byconity/bin/clickhouse
8. DB::CountingBlockOutputStream::writeSuffix() @ 0x17a43e23 in /opt/byconity/bin/clickhouse
9. DB::ISink::prepare() @ 0x18f2bd3f in /opt/byconity/bin/clickhouse
10. 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>) @ 0x18f61125 in /opt/byconity/bin/clickhouse
11. 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) @ 0x18f60fc5 in /opt/byconity/bin/clickhouse
12. 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>) @ 0x18f616ed in /opt/byconity/bin/clickhouse
13. 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) @ 0x18f60fc5 in /opt/byconity/bin/clickhouse
14. 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>) @ 0x18f616ed in /opt/byconity/bin/clickhouse
15. DB::PipelineExecutor::executeStepImpl(unsigned long, unsigned long, std::__1::atomic<bool>*) @ 0x18f6640d in /opt/byconity/bin/clickhouse
16. DB::PipelineExecutor::executeImpl(unsigned long) @ 0x18f6495c in /opt/byconity/bin/clickhouse
17. DB::PipelineExecutor::execute(unsigned long) @ 0x18f64365 in /opt/byconity/bin/clickhouse
18. 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::optional<DB::FormatSettings> const&, bool) @ 0x184f4e84 in /opt/byconity/bin/clickhouse
19. DB::HTTPHandler::processQuery(std::__1::shared_ptr<DB::Context>, DB::HTTPServerRequest&, DB::HTMLForm&, DB::HTTPServerResponse&, DB::HTTPHandler::Output&, std::__1::optional<DB::CurrentThread::QueryScope>&) @ 0x18eb8382 in /opt/byconity/bin/clickhouse
20. DB::HTTPHandler::handleRequest(DB::HTTPServerRequest&, DB::HTTPServerResponse&) @ 0x18ebaf4f in /opt/byconity/bin/clickhouse
21. DB::HTTPServerConnection::run() @ 0x18f0c319 in /opt/byconity/bin/clickhouse
22. Poco::Net::TCPServerConnection::start() @ 0x1db4802c in /opt/byconity/bin/clickhouse
23. Poco::Net::TCPServerDispatcher::run() @ 0x1db4850c in /opt/byconity/bin/clickhouse
24. Poco::PooledThread::run() @ 0x1dc2f5da in /opt/byconity/bin/clickhouse
25. Poco::ThreadImpl::runnableEntry(void*) @ 0x1dc2d18c in /opt/byconity/bin/clickhouse
26. start_thread @ 0x7fa3 in /lib/x86_64-linux-gnu/libpthread-2.28.so
27. clone @ 0xf906f in /lib/x86_64-linux-gnu/libc-2.28.so

The result you expected

Without this mv it works and for native clickhouse installation this also works so I also expected it to work here.

Version

0.2.2

[chenbay]

@ozcelgozde what's your defines for test_db.test_table_samp. can u show me the return of show create table test_db.test_table_samp and desc test_db.test_table_samp

[ozcelgozde]

They are basically the same, we do manual sampling.
show create table ed.test_table

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),    
 `id` UUID DEFAULT generateUUIDv4() 
) 
ENGINE = CnchMergeTree 
ORDER BY timestamp 
SETTINGS storage_policy = 'cnch_default_s3', index_granularity = 8192

show create table ed.test_table_samp_10

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),
    `id` UUID DEFAULT generateUUIDv4()
)
ENGINE = CnchMergeTree
ORDER BY timestamp
SETTINGS storage_policy = 'cnch_default_s3', index_granularity = 8192

Sample insert:
insert into ed.test_table (timestamp, test_keys, test_values) VALUES ('2023-09-24 06:07:40.954',['environment','non-mat-columns'], ['staging', 'columnsnsns']);

[chenbay]

@ozcelgozde i reproduce this bug, there is a bug when target table has MATERIALIZED columns

[ozcelgozde]

Hey @chenbay any progress on this? Anything more I can do to help?

[chenbay]

we will solve it next week

…

------------------ Original ------------------ From: Gozde ***@***.***> Date: Fri,Oct 27,2023 8:42 PM To: ByConity/ByConity ***@***.***> Cc: baiyang ***@***.***>, Mention ***@***.***> Subject: Re: [ByConity/ByConity] Materialized view cannot copy materializedcolumns (Issue #774) Hey @chenbay any progress on this? Anything more I can do to help? — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

[ozcelgozde]

Hey, i was able to trace the issue to line 270 within InterpreterSelectWithUnionQuery.cpp where this call happens:

return cache[key] = InterpreterSelectWithUnionQuery(query_ptr_, context_, SelectQueryOptions().analyze()).getSampleBlock();

These options provided cascades all the way to copying of materialized columns.
I was able to test this by adding this line to InterpreterSelectQuery::getSubqueryContext

subquery_settings.asterisk_include_materialized_columns = true;

with this it works. I guess the missing value is options.with_all_cols
This is for select * queries only, explicitly setting columns still fails to copy materialized ones