WITH Clause CTE expressions causes exception when used in CTE subquery on latest version. #62946
Description
Describe the issue
A CTE expression used in a CTE subquery causes an exception on latest version.
How to reproduce
ClickHouse versions tested:
- Docker Image
clickhouse/clickhouse-server:latest - Docker Image
clickhouse/clickhouse-server:24.2
OS: Ubuntu 22.04 LTS
Query in concern:
WITH
(`database` LIKE 'system') AS `$condition`,
`$main` AS (SELECT DISTINCT table FROM system.tables WHERE `$condition`)
SELECT * FROM `$main`;The above query can be executed perfectly on clickhouse/clickhouse-server:24.2 using ClickHouse's command line client.
However, in the latest image, the following exception is received:
Received exception from server (version 24.3.2):
Code: 47. DB::Exception: Received from localhost:9000. DB::Exception: Unknown expression or function identifier 'database' in scope WITH database LIKE 'system' AS `$condition`, `$main` AS (SELECT DISTINCT `table` FROM system.tables WHERE `$condition`) SELECT * FROM `$main`. (UNKNOWN_IDENTIFIER)
Error message and/or stacktrace
See clickhouse-server.err.log below
2024.04.25 03:35:28.986946 [ 48 ] {4ec0d7db-bc36-4bcb-8056-bf805f8de9b6} <Error> TCPHandler: Code: 47. DB::Exception: Unknown expression or function identifier 'database' in scope WITH database LIKE 'system' AS `$condition`, `$main` AS (SELECT DISTINCT `table` FROM system.tables WHERE `$condition`) SELECT * FROM `$main`. (UNKNOWN_IDENTIFIER), Stack trace (when copying this message, always include the lines below):
0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000cbcedbb
1. DB::Exception::Exception<char const*, String&, String const&, String, String>(int, FormatStringHelperImpl<std::type_identity<char const*>::type, std::type_identity<String&>::type, std::type_identity<String const&>::type, std::type_identity<String>::type, std::type_identity<String>::type>, char const*&&, String&, String const&, String&&, String&&) @ 0x0000000010c1a54b
2. DB::(anonymous namespace)::QueryAnalyzer::resolveExpressionNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, bool, bool) @ 0x0000000010bd3eee
3. DB::(anonymous namespace)::QueryAnalyzer::resolveExpressionNodeList(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, bool, bool) @ 0x0000000010bcf54d
4. DB::(anonymous namespace)::QueryAnalyzer::resolveFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&) @ 0x0000000010be9237
5. DB::(anonymous namespace)::QueryAnalyzer::resolveExpressionNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, bool, bool) @ 0x0000000010bd09a0
6. DB::(anonymous namespace)::QueryAnalyzer::tryResolveIdentifierFromAliases(DB::(anonymous namespace)::IdentifierLookup const&, DB::(anonymous namespace)::IdentifierResolveScope&, DB::(anonymous namespace)::IdentifierResolveSettings) @ 0x0000000010be3f94
7. DB::(anonymous namespace)::QueryAnalyzer::tryResolveIdentifier(DB::(anonymous namespace)::IdentifierLookup const&, DB::(anonymous namespace)::IdentifierResolveScope&, DB::(anonymous namespace)::IdentifierResolveSettings) @ 0x0000000010be0efc
8. DB::(anonymous namespace)::QueryAnalyzer::tryResolveIdentifier(DB::(anonymous namespace)::IdentifierLookup const&, DB::(anonymous namespace)::IdentifierResolveScope&, DB::(anonymous namespace)::IdentifierResolveSettings) @ 0x0000000010be177f
9. DB::(anonymous namespace)::QueryAnalyzer::resolveExpressionNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, bool, bool) @ 0x0000000010bd1507
10. DB::(anonymous namespace)::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::(anonymous namespace)::IdentifierResolveScope&) @ 0x0000000010bc753c
11. DB::(anonymous namespace)::QueryAnalyzer::resolveExpressionNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, bool, bool) @ 0x0000000010bd0f6d
12. DB::(anonymous namespace)::QueryAnalyzer::resolveQueryJoinTreeNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::(anonymous namespace)::IdentifierResolveScope&, DB::(anonymous namespace)::QueryExpressionsAliasVisitor&) @ 0x0000000010bd9200
13. DB::(anonymous namespace)::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::(anonymous namespace)::IdentifierResolveScope&) @ 0x0000000010bc7304
14. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x0000000010bc4d25
15. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>) @ 0x0000000010bc35e3
16. DB::(anonymous namespace)::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) (.llvm.17547086829861056443) @ 0x0000000010e5421d
17. DB::InterpreterSelectQueryAnalyzer::InterpreterSelectQueryAnalyzer(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&) @ 0x0000000010e52fb1
18. std::unique_ptr<DB::IInterpreter, std::default_delete<DB::IInterpreter>> std::__function::__policy_invoker<std::unique_ptr<DB::IInterpreter, std::default_delete<DB::IInterpreter>> (DB::InterpreterFactory::Arguments const&)>::__call_impl<std::__function::__default_alloc_func<DB::registerInterpreterSelectQueryAnalyzer(DB::InterpreterFactory&)::$_0, std::unique_ptr<DB::IInterpreter, std::default_delete<DB::IInterpreter>> (DB::InterpreterFactory::Arguments const&)>>(std::__function::__policy_storage const*, DB::InterpreterFactory::Arguments const&) (.llvm.17547086829861056443) @ 0x0000000010e55f8c
19. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x0000000010de7ef9
20. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x000000001126f9c8
21. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000001126bf9a
22. DB::TCPHandler::runImpl() @ 0x0000000012351d84
23. DB::TCPHandler::run() @ 0x000000001236d099
24. Poco::Net::TCPServerConnection::start() @ 0x0000000014c9bef2
25. Poco::Net::TCPServerDispatcher::run() @ 0x0000000014c9cd39
26. Poco::PooledThread::run() @ 0x0000000014d954a1
27. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000014d93a3d
28. ? @ 0x0000758715368609
29. ? @ 0x000075871528d353
Additional Information
The following query is also tested:
WITH
(`database` LIKE 'system') AS `$cond`
SELECT DISTINCT table FROM system.tables WHERE `$cond`;This query works perfectly on both versions.