Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

TTL materialization fail when table contains projection #53740

Open
Ravojay opened this issue Aug 23, 2023 · 7 comments
Open

TTL materialization fail when table contains projection #53740

Ravojay opened this issue Aug 23, 2023 · 7 comments
Labels
st-need-repro We were not able to reproduce the problem, please help us. unexpected behaviour

Comments

@Ravojay
Copy link

Ravojay commented Aug 23, 2023 •
edited

Describe the unexpected behaviour
I encountered a problem with projection and TTL modification.
Modifying TTL for table (with materialize_ttl_recalculate_only set to 1) with projection may lead to a 'missing column' exception.

How to reproduce

  1. create table with materialize_ttl_recalculate_only set to 1

CREATE TABLE test.test_modify_ttl_local ( event_day Date, cnt Int64, PROJECTION proj_xxx ( SELECT event_day, sum(cnt) GROUP BY event_day ) ) ENGINE = MergeTree PARTITION BY event_day ORDER BY event_day TTL event_day + toIntervalDay(400) SETTINGS index_granularity = 8192, materialize_ttl_recalculate_only = 1, old_parts_lifetime = 60

Query id: ea07d6ef-74a0-48e7-8190-4011c58133f4
Ok.
0 rows in set. Elapsed: 0.004 sec.

  1. insert data into table

INSERT INTO test.test_modify_ttl_local SELECT '2023-08-22', 1 FROM numbers(10000000)

Query id: 86dfa0df-ddfa-4595-b27a-643235c96218

Ok.

0 rows in set. Elapsed: 0.737 sec. Processed 20.48 million rows, 183.88 MB (27.79 million rows/s., 249.43 MB/s.)

  1. then try modify table TTL
    ALTER TABLE test.test_modify_ttl_local MODIFY TTL event_day + toIntervalDay(500)

Query id: 9ebfbb00-0482-4b18-9df8-f3461ef056e9

0 rows in set. Elapsed: 0.007 sec.
Received exception from server (version 23.3.7):
Code: 341. DB::Exception: Received from localhost:2036. DB::Exception: Exception happened during execution of mutation 'mutation_33.txt' with part '20230822_23_32_2' reason: 'Code: 47. DB::Exception: Missing columns: 'cnt' while processing query: 'SELECT event_day, sum(cnt) GROUP BY event_day', required columns: 'event_day' 'cnt' 'event_day' 'cnt'. (UNKNOWN_IDENTIFIER) (version 23.3.7.1)'. This error maybe retryable or not. In case of unretryable error, mutation can be killed with KILL MUTATION query. (UNFINISHED)

** Which ClickHouse server version to use**
version 23.3.7

Expected behavior
expect the TTL to be materialized normally

Additional context
After resetting materialize_ttl_recalculate_only to zero, TTL can be materialized successfully.
Also, when the inserted data is fewer, say 1000000 (1/10 of the case above), TTL materialization is also executed successfully. Seems that fewer data leads to compact part, not sure if that's relevant
@Ravojay Ravojay changed the title TTL materialization fail when table conatains projection TTL materialization fail when table contains projection Aug 23, 2023
@Ravojay
Copy link
Author

Ravojay commented Aug 24, 2023

@amosbird boss, if you've got time, please have a look at this. Not sure if this is a bug in projection or a expected behavior that can be fixed with some settings. Thanks a lot!

@Ravojay
Copy link
Author

Ravojay commented Aug 24, 2023

production table error log looks like this (replaced the aggregated column with xxx):

2023.08.23 10:21:10.719443 [ 332 ] {} d098e71f-2ef5-4ca0-9360-25f4d50bbaae::20230120_1_5_2_6 (MutateFromLogEntryTask): virtual bool DB::ReplicatedMergeMutateTaskBase::executeStep(): Code: 47. DB::Exception: Missing columns: 'xxx' while processing query: 'SELECT event_day, sum(xxx) GROUP BY event_day', required columns: 'event_day' 'xxx' 'event_day' 'xxx'. (UNKNOWN_IDENTIFIER), Stack trace (when copying this message, always include the lines below):

  1. ./build/./contrib/llvm-project/libcxx/include/exception:134: Poco::Exception::Exception(String const&, int) @ 0x19393952 in /usr/bin/clickhouse
  2. ./build/./src/Common/Exception.cpp:91: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x108eb155 in /usr/bin/clickhouse
  3. DB::Exception::Exception(PreformattedMessage&&, int) @ 0xbe7fcec in /usr/bin/clickhouse
  4. ./build/./src/Interpreters/TreeRewriter.cpp:0: DB::TreeRewriterResult::collectUsedColumns(std::shared_ptrDB::IAST const&, bool, bool) @ 0x15fe08e6 in /usr/bin/clickhouse
  5. ./build/./src/Interpreters/TreeRewriter.cpp:1334: DB::TreeRewriter::analyzeSelect(std::shared_ptrDB::IAST&, DB::TreeRewriterResult&&, DB::SelectQueryOptions const&, std::vector<DB::TableWithColumnNamesAndTypes, std::allocatorDB::TableWithColumnNamesAndTypes> const&, std::vector<String, std::allocator> const&, std::shared_ptrDB::TableJoin, bool, std::unordered_map<String, String, std::hash, std::equal_to, std::allocator<std::pair<String const, String>>>, std::unordered_map<String, String, std::hash, std::equal_to, std::allocator<std::pair<String const, String>>>) const @ 0x15fe588e in /usr/bin/clickhouse
  6. ./build/./contrib/llvm-project/libcxx/include/__utility/swap.h:37: DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptrDB::IAST const&, std::shared_ptrDB::Context const&, std::optionalDB::Pipe, std::shared_ptrDB::IStorage const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptrDB::PreparedSets)::$_0::operator()(bool) const @ 0x15d38114 in /usr/bin/clickhouse
  7. ./build/./src/Interpreters/InterpreterSelectQuery.cpp:0: DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptrDB::IAST const&, std::shared_ptrDB::Context const&, std::optionalDB::Pipe, std::shared_ptrDB::IStorage const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptrDB::PreparedSets) @ 0x15d354f5 in /usr/bin/clickhouse
  8. ./build/./contrib/llvm-project/libcxx/include/optional:260: DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptrDB::IAST const&, std::shared_ptr<DB::Context const> const&, std::optionalDB::Pipe, std::shared_ptrDB::IStorage const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptrDB::PreparedSets) @ 0x15d3276a in /usr/bin/clickhouse
  9. ./build/./contrib/llvm-project/libcxx/include/vector:434: DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptrDB::IAST const&, std::shared_ptr<DB::Context const> const&, DB::Pipe, DB::SelectQueryOptions const&) @ 0x15d370ea in /usr/bin/clickhouse
  10. ./build/./src/Storages/ProjectionsDescription.cpp:0: DB::ProjectionDescription::calculate(DB::Block const&, std::shared_ptr<DB::Context const>) const @ 0x1640485e in /usr/bin/clickhouse
  11. ./build/./src/Storages/MergeTree/MutateTask.cpp:1089: DB::PartMergerWriter::mutateOriginalPartAndPrepareProjections() @ 0x16ac701a in /usr/bin/clickhouse
  12. ./build/./src/Storages/MergeTree/MutateTask.cpp:0: DB::MutateSomePartColumnsTask::executeStep() @ 0x16adaad8 in /usr/bin/clickhouse
  13. ./build/./src/Storages/MergeTree/MutateTask.cpp:1557: DB::MutateTask::execute() @ 0x16ac803c in /usr/bin/clickhouse
  14. ./build/./src/Storages/MergeTree/ReplicatedMergeMutateTaskBase.cpp:0: DB::ReplicatedMergeMutateTaskBase::executeImpl() @ 0x16b09f64 in /usr/bin/clickhouse
  15. ./build/./src/Storages/MergeTree/ReplicatedMergeMutateTaskBase.cpp:0: DB::ReplicatedMergeMutateTaskBase::executeStep() @ 0x16b09059 in /usr/bin/clickhouse
  16. ./build/./src/Storages/MergeTree/MergeTreeBackgroundExecutor.cpp:137: DB::MergeTreeBackgroundExecutorDB::DynamicRuntimeQueue::routine(std::shared_ptrDB::TaskRuntimeData) @ 0x168f81fb in /usr/bin/clickhouse
  17. ./build/./contrib/llvm-project/libcxx/include/__memory/shared_ptr.h:701: DB::MergeTreeBackgroundExecutorDB::DynamicRuntimeQueue::threadFunction() @ 0x168f936b in /usr/bin/clickhouse
  18. ./build/./base/base/../base/wide_integer_impl.h:789: ThreadPoolImpl<ThreadFromGlobalPoolImpl>::worker(std::__list_iterator<ThreadFromGlobalPoolImpl, void*>) @ 0x109a3de2 in /usr/bin/clickhouse
  19. ./build/./src/Common/ThreadPool.cpp:0: ThreadFromGlobalPoolImpl::ThreadFromGlobalPoolImpl<void ThreadPoolImpl<ThreadFromGlobalPoolImpl>::scheduleImpl(std::function<void ()>, long, std::optional, bool)::'lambda0'()>(void&&)::'lambda'()::operator()() @ 0x109a6a79 in /usr/bin/clickhouse
  20. ./build/./base/base/../base/wide_integer_impl.h:789: ThreadPoolImplstd::thread::worker(std::__list_iterator<std::thread, void*>) @ 0x109a18b2 in /usr/bin/clickhouse
  21. ./build/./contrib/llvm-project/libcxx/include/__memory/unique_ptr.h:302: void* std::__thread_proxy[abi:v15000]<std::tuple<std::unique_ptr<std::__thread_struct, std::default_deletestd::__thread_struct>, void ThreadPoolImplstd::thread::scheduleImpl(std::function<void ()>, long, std::optional, bool)::'lambda0'()>>(void*) @ 0x109a51ee in /usr/bin/clickhouse
  22. ? @ 0x7f2869d7c609 in ?
  23. __clone @ 0x7f2869ca1133 in ?
    (version 23.3.7.1)

@amosbird
Copy link
Collaborator

@Ravojay I cannot reproduce the issue using latest main branch.

@amosbird amosbird added the st-need-repro We were not able to reproduce the problem, please help us. label Aug 24, 2023
@Ravojay
Copy link
Author

Ravojay commented Aug 24, 2023

@amosbird
I tried latest lts version in v23.3: Release v23.3.10.5-lts
Still throws the same error.
However version 23.8 doesn't produce any error.
Will the changes that solved the problem be merged into the lts 23.3 versions ?

@amosbird
Copy link
Collaborator

Will the changes that solved the problem be merged into the lts 23.3 versions ?

Well, we need to find out what PR fixed this issue first.

@Ravojay
Copy link
Author

Ravojay commented Aug 25, 2023 •
edited

I think its somewhere between these two version:
v23.5.4.25-stable...v23.6.1.1524-stable
b3b1d0fd4abcc72efab20d6fdca0b0ac
maybe around these commits I suppose
@amosbird

@amosbird
Copy link
Collaborator

Should be this one #50104 . If we want to backport it, we may need two other PRs #52517 , #52530 .

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
st-need-repro We were not able to reproduce the problem, please help us. unexpected behaviour
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@amosbird @Ravojay