Issue with view() used with remoteSecure produces "UNKNOWN_TABLE" Error

[pratimapatel2008]

Company or project name

No response

Describe what's wrong

We have observed an issue with view() used with remoteSecure() which was working in 22.9.7.34 and fails in 25.1.3.
Below is the problematic query :

SELECT * FROM remoteSecure('xxx.us-east-1.aws.clickhouse.cloud:9440', view(SELECT * FROM default.test), 'default', 'xxxx');

Query id: 65b7e360-636f-41a5-bd7f-cd37e40067cc

Received exception from server (version 24.10.1):
Code: 60. DB::Exception: Received from xxx.us-east-1.aws.clickhouse.cloud:9440. DB::Exception: Unknown table expression identifier 'default.test' in scope SELECT * FROM default.test. (UNKNOWN_TABLE)

The query which does not uses view() in remoteSecure works fine, below is example

clickhouse-cloud :)SELECT * FROM remoteSecure('xxx.us-east-1.aws.clickhouse.cloud:9440', default.test, 'default', 'xxxx')

Query id: 53bfc3bf-dc46-4cbe-88b2-2a13d4735d65

   ┌─name────┬─user_id─┐
1. │ name100 │     100 │
2. │ name200 │     200 │
   └─────────┴─────────┘

2 rows in set. Elapsed: 0.067 sec.

Here is the DDL in destination service.

clickhouse-cloud :) CREATE TABLE default.test
(
    `name` String,
    `user_id` Int32
)
ENGINE = SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY user_id

CREATE TABLE default.test
(
    `name` String,
    `user_id` Int32
)
ENGINE = SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY user_id

Query id: 0ddd30a5-1bb5-4e1e-a628-78846bac3382

0 rows in set. Elapsed: 0.178 sec.

SELECT *
FROM remoteSecure('xxx.us-east-1.aws.clickhouse.cloud:9440', view(
    SELECT *
    FROM default.test
), 'default', 'xxxx')

Query id: d487a993-0bc7-4b5f-8eb8-592df42ad40e

   ┌─name────┬─user_id─┐
1. │ name100 │     100 │
2. │ name200 │     200 │
   └─────────┴─────────┘

2 rows in set. Elapsed: 0.055 sec.

When I create the same table in the source service the query works fine.

The issue appears to be with the new analyzer which is trying to search for the objects inside view() in current CH Service as opposed to fetch those from remote service, one of the workaround is to turn off the experimental analyzer.

Error Stack:

0. Poco::Exception::Exception(String const&, int) @ 0x000000010ca593bc
1. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x0000000105031204
2. DB::Exception::Exception(PreformattedMessage&&, int) @ 0x000000010097dcdc
3. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity<String>::type>, String const&, String&&) @ 0x0000000100c60ca0
4. DB::QueryAnalyzer::initializeQueryJoinTreeNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&) @ 0x0000000108f74944
5. DB::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::IdentifierResolveScope&) @ 0x0000000108f4e948
6. DB::QueryAnalyzer::resolve(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::IQueryTreeNode> const&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4dfa8
7. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4d840
8. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>) @ 0x0000000108ec263c
9. DB::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) @ 0x0000000109491888
10. DB::InterpreterSelectQueryAnalyzer::InterpreterSelectQueryAnalyzer(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x00000001094901d8
11. DB::InterpreterSelectQueryAnalyzer::getSampleBlock(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&) @ 0x0000000109491ca0
12. DB::TableFunctionView::getActualTableStructure(std::shared_ptr<DB::Context const>, bool) const @ 0x0000000108244a30
13. DB::TableFunctionView::executeImpl(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool) const @ 0x0000000108244d44
14. DB::ITableFunction::execute(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool, bool) const @ 0x0000000108240364
15. DB::Context::executeTableFunction(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::ITableFunction> const&) @ 0x00000001092d5ff4
16. DB::QueryAnalyzer::resolveTableFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&, bool) @ 0x0000000108f57014
17. DB::QueryAnalyzer::resolveTableFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&, bool) @ 0x0000000108f552c8
18. DB::QueryAnalyzer::resolveQueryJoinTreeNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&) @ 0x0000000108f7570c
19. DB::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::IdentifierResolveScope&) @ 0x0000000108f4e9e0
20. DB::QueryAnalyzer::resolve(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::IQueryTreeNode> const&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4dfa8
21. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4d840
22. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>) @ 0x0000000108ec263c
23. DB::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) @ 0x0000000109491888
24. DB::InterpreterSelectQueryAnalyzer::InterpreterSelectQueryAnalyzer(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x00000001094901d8
25. 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[abi:ne180100]<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&) @ 0x00000001094933c4
26. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x000000010943d46c
27. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*, std::shared_ptr<DB::IAST>&) @ 0x0000000109792450
28. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000010978f5c4
29. DB::LocalConnection::sendQuery(DB::ConnectionTimeouts const&, String const&, std::unordered_map<String, String, std::hash<String>, std::equal_to<String>, std::allocator<std::pair<String const, String>>> const&, String const&, unsigned long long, DB::Settings const*, DB::ClientInfo const*, bool, std::vector<String, std::allocator<String>> const&, std::function<void (DB::Progress const&)>) @ 0x000000010ad04e4c
30. DB::ClientBase::processOrdinaryQuery(String const&, std::shared_ptr<DB::IAST>) @ 0x000000010acb9754
31. DB::ClientBase::processParsedSingleQuery(String const&, String const&, std::shared_ptr<DB::IAST>, std::optional<bool>, bool) @ 0x000000010acb82ec

Does it reproduce on the most recent release?

Yes

How to reproduce

1. Create table in Remote CH Service with below DDL and Insert few records

CREATE TABLE default.test
(
    `name` String,
    `user_id` Int32
)
ENGINE = SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY user_id;

INSERT INTO default.test VALUES (1,'User100'),(2,'User101');

2. Query from local CH using remoteSecure and view() such as below.

SELECT *
FROM remoteSecure('xxxx.xx.aws.clickhouse.cloud:9440', view(
    SELECT *
    FROM default.test
), 'xxxx', 'xxxx')

Workaround:

SELECT *
FROM remoteSecure('xxxx.xx.aws.clickhouse.cloud:9440', view(
    SELECT *
    FROM default.test
), 'xxxx', 'xxxx')  settings allow_experimental_analyzer=0;

Expected behavior

Should not fail with error.

Error message and/or stacktrace

2025.04.21 16:28:21.922240 [ 418306 ] {6a32568f-0327-4374-9743-d7a9a05a9afb} <Error> executeQuery: Code: 60. DB::Exception: Unknown table expression identifier 'default.test' in scope SELECT * FROM default.test. (UNKNOWN_TABLE) (version 25.2.1.2115 (official build)) (from 0.0.0.0:0) (query 1, line 1) (in query: SELECT * FROM remoteSecure('xxxx.xxxx.aws.clickhouse.cloud:9440', view(SELECT * FROM default.test), 'default', '[HIDDEN]')), Stack trace (when copying this message, always include the lines below):


0. Poco::Exception::Exception(String const&, int) @ 0x000000010ca593bc
1. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x0000000105031204
2. DB::Exception::Exception(PreformattedMessage&&, int) @ 0x000000010097dcdc
3. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity<String>::type>, String const&, String&&) @ 0x0000000100c60ca0
4. DB::QueryAnalyzer::initializeQueryJoinTreeNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&) @ 0x0000000108f74944
5. DB::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::IdentifierResolveScope&) @ 0x0000000108f4e948
6. DB::QueryAnalyzer::resolve(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::IQueryTreeNode> const&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4dfa8
7. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4d840
8. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>) @ 0x0000000108ec263c
9. DB::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) @ 0x0000000109491888
10. DB::InterpreterSelectQueryAnalyzer::InterpreterSelectQueryAnalyzer(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x00000001094901d8
11. DB::InterpreterSelectQueryAnalyzer::getSampleBlock(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&) @ 0x0000000109491ca0
12. DB::TableFunctionView::getActualTableStructure(std::shared_ptr<DB::Context const>, bool) const @ 0x0000000108244a30
13. DB::TableFunctionView::executeImpl(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool) const @ 0x0000000108244d44
14. DB::ITableFunction::execute(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const>, String const&, DB::ColumnsDescription, bool, bool) const @ 0x0000000108240364
15. DB::Context::executeTableFunction(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::ITableFunction> const&) @ 0x00000001092d5ff4
16. DB::QueryAnalyzer::resolveTableFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&, bool) @ 0x0000000108f57014
17. DB::QueryAnalyzer::resolveTableFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&, bool) @ 0x0000000108f552c8
18. DB::QueryAnalyzer::resolveQueryJoinTreeNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, DB::QueryExpressionsAliasVisitor&) @ 0x0000000108f7570c
19. DB::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::IdentifierResolveScope&) @ 0x0000000108f4e9e0
20. DB::QueryAnalyzer::resolve(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::IQueryTreeNode> const&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4dfa8
21. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x0000000108f4d840
22. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>) @ 0x0000000108ec263c
23. DB::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) @ 0x0000000109491888
24. DB::InterpreterSelectQueryAnalyzer::InterpreterSelectQueryAnalyzer(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context const> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x00000001094901d8
25. 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[abi:ne180100]<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&) @ 0x00000001094933c4
26. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x000000010943d46c
27. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*, std::shared_ptr<DB::IAST>&) @ 0x0000000109792450
28. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000010978f5c4
29. DB::LocalConnection::sendQuery(DB::ConnectionTimeouts const&, String const&, std::unordered_map<String, String, std::hash<String>, std::equal_to<String>, std::allocator<std::pair<String const, String>>> const&, String const&, unsigned long long, DB::Settings const*, DB::ClientInfo const*, bool, std::vector<String, std::allocator<String>> const&, std::function<void (DB::Progress const&)>) @ 0x000000010ad04e4c
30. DB::ClientBase::processOrdinaryQuery(String const&, std::shared_ptr<DB::IAST>) @ 0x000000010acb9754
31. DB::ClientBase::processParsedSingleQuery(String const&, String const&, std::shared_ptr<DB::IAST>, std::optional<bool>, bool) @ 0x000000010acb82ec

Additional context

No response

[SaltTan]

related #78717

[novikd]

I suspect it could be supported by handling the UNKNOWN_TABLE exception here:

ClickHouse/src/Analyzer/Resolve/QueryAnalyzer.cpp

Lines 4929 to 4949 in 4929c20

	/** Table functions arguments can contain expressions with invalid identifiers.
	* We cannot skip analysis for such arguments, because some table functions cannot provide
	* information if analysis for argument should be skipped until other arguments will be resolved.
	*
	* Example: SELECT key from remote('127.0.0.{1,2}', view(select number AS key from numbers(2)), cityHash64(key));
	* Example: SELECT id from remote('127.0.0.{1,2}', 'default', 'test_table', cityHash64(id));
	*/
	try
	{
	resolveExpressionNode(table_function_argument, scope, false /*allow_lambda_expression*/, false /*allow_table_expression*/);
	}
	catch (const Exception & exception)
	{
	if (exception.code() == ErrorCodes::UNKNOWN_IDENTIFIER)
	{
	result_table_function_arguments.push_back(table_function_argument);
	continue;
	}
	throw;
	}

The problem is that it would start to work like that for all the table functions (not only for remoteSecure), and I'm currently not 100% sure that it won't break anything.