More inconsistent AST formatting

[PedroTadim]

Describe the bug

Easy to reproduce, cc @alexey-milovidov

How to reproduce

Run:

SELECT position(1 = ANY(SELECT 1), 1);

Error message and/or stacktrace

Stack trace:

<Fatal> : Logical error: 'Inconsistent AST formatting: the query:
SELECT position(1 IN (SELECT 1), 1)
cannot parse query back from SELECT position(1 = ANY(SELECT 1), 1);'.
<Fatal> : Stack trace (when copying this message, always include the lines below):

0. contrib/llvm-project/libcxx/include/__exception/exception.h:113: Poco::Exception::Exception(String const&, int) @ 0x00000000265ff8f2
1. src/Common/Exception.cpp:116: DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x00000000140b8866
2. src/Common/Exception.h:119: DB::Exception::Exception(PreformattedMessage&&, int) @ 0x000000000cda15ac
3. src/Common/Exception.h:137: DB::Exception::Exception<String&, std::basic_string_view<char, std::char_traits<char>>>(int, FormatStringHelperImpl<std::type_identity<String&>::type, std::type_identity<std::basic_string_view<char, std::char_traits<char>>>::type>, String&, std::basic_string_view<char, std::char_traits<char>>&&) @ 0x000000001b33b276
4. src/Interpreters/executeQuery.cpp:1096: DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, std::unique_ptr<DB::ReadBuffer, std::default_delete<DB::ReadBuffer>>&, std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::ImplicitTransactionControlExecutor>) @ 0x000000001b332daf
5. src/Interpreters/executeQuery.cpp:1770: DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000001b32a385
6. src/Server/TCPHandler.cpp:739: DB::TCPHandler::runImpl() @ 0x000000001e5c380f
7. src/Server/TCPHandler.cpp:2740: DB::TCPHandler::run() @ 0x000000001e5dd6a4
8. base/poco/Net/src/TCPServerConnection.cpp:40: Poco::Net::TCPServerConnection::start() @ 0x00000000266b61c7
9. base/poco/Net/src/TCPServerDispatcher.cpp:115: Poco::Net::TCPServerDispatcher::run() @ 0x00000000266b67a5
10. base/poco/Foundation/src/ThreadPool.cpp:205: Poco::PooledThread::run() @ 0x000000002665597f
11. base/poco/Foundation/src/Thread_POSIX.cpp:341: Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000026653051
12. start_thread @ 0x00000000000a27f1
13. __clone3 @ 0x0000000000133c9c

[Dolaxom]

By the way, if you run query:

SELECT position(toString(1 = ANY(SELECT 1)),'1');

it works fine and returns 1:

bash programs % ./clickhouse-client --query="SELECT position(toString(1 = ANY(SELECT 1)),'1');"
1

[Dolaxom]

As I can understand, ClickHouse requires consistency of transformations at the top expression level such that format(parse(query)) = format(query). In this case, ANY is transformed into 1 IN (...), which changes the top level AST - producing Function_in instead of Function_equals + Function_any. Wrapping the expression in a toString (look at up commen) function preserves the top-level structure despite internal optimizations (ANY -> 1 IN).

bash programs % ./clickhouse-format --query="SELECT position(1 = ANY(SELECT 1), 1);"
SELECT position(1 IN (
        SELECT 1
    ), 1)
bash programs % ./clickhouse-format --query="SELECT position(toString(1 = ANY(SELECT 1)), '1')"
SELECT position(toString(1 IN (
        SELECT 1
    )), '1')

@PedroTadim I have no idea where exactly ClickHouse handles this, but the issue is (I think) that ClickHouse cannot match ANY and 1 IN (...) at the top expression level AST and treats them as different

[Felixoid]

In the recent versions, it changed to a proper type cheking:

SELECT position(1 IN (
        SELECT 1
    ), 1)

Query id: 693b7629-5780-4226-99db-a64fd1ad33a7


Elapsed: 0.001 sec.

Received exception:
Code: 43. DB::Exception: Illegal type UInt8 of argument of function position: In scope SELECT position(1 IN (SELECT 1), 1). (ILLEGAL_TYPE_OF_ARGUMENT)

[Felixoid]

I checked versions from 25.3.1 to the head, checked both clickhouse-local and client+server.

It produces a different stack trace:

2025.12.05 11:08:05.686685 [ 86 ] {} <Error> TCPHandler: Code: 43. DB::Exception: Illegal type UInt8 of argument of function position: In scope SELECT position(1 IN (SELECT 1), 1). (ILLEGAL_TYPE_OF_ARGUMENT), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x00000000164f4daa
1. DB::Exception::Exception(String&&, int, String, bool) @ 0x000000000deccb0e
2. DB::Exception::Exception(PreformattedMessage&&, int) @ 0x000000000decc500
3. DB::Exception::Exception<String, String>(int, FormatStringHelperImpl<std::type_identity<String>::type, std::type_identity<String>::type>, String&&, String&&) @ 0x000000000decbab6
4. DB::FunctionsStringSearch<DB::PositionImpl<DB::(anonymous namespace)::NamePosition, DB::PositionCaseSensitiveASCII>, (DB::ExecutionErrorPolicy)1, (DB::HaystackNeedleOrderIsConfigurable)0>::getReturnTypeImpl(std::vector<std::shared_ptr<DB::IDataType const>, std::allocator<std::shared_ptr<DB::IDataType const>>> const&) const @ 0x0000000014c5f0a7
5. DB::IFunction::getReturnTypeImpl(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x000000000decb2bf
6. DB::IFunctionOverloadResolver::getReturnType(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x00000000193f0071
7. DB::IFunctionOverloadResolver::build(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&) const @ 0x00000000193f0986
8. DB::QueryAnalyzer::resolveFunction(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&) @ 0x000000001ab93401
9. DB::QueryAnalyzer::resolveExpressionNode(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, bool, bool, bool) @ 0x000000001a8e1940
10. DB::QueryAnalyzer::resolveExpressionNodeList(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&, bool, bool) @ 0x000000001a8e0e0e
11. DB::QueryAnalyzer::resolveProjectionExpressionNodeList(std::shared_ptr<DB::IQueryTreeNode>&, DB::IdentifierResolveScope&) @ 0x000000001a906d32
12. DB::QueryAnalyzer::resolveQuery(std::shared_ptr<DB::IQueryTreeNode> const&, DB::IdentifierResolveScope&) @ 0x000000001a8d726e
13. DB::QueryAnalyzer::resolve(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::IQueryTreeNode> const&, std::shared_ptr<DB::Context const>) @ 0x000000001a8d4a7a
14. DB::QueryAnalysisPass::run(std::shared_ptr<DB::IQueryTreeNode>&, std::shared_ptr<DB::Context const>) @ 0x000000001a8d405c
15. DB::QueryTreePassManager::run(std::shared_ptr<DB::IQueryTreeNode>&) @ 0x000000001a92c5b6
16. DB::buildQueryTreeAndRunPasses(std::shared_ptr<DB::IAST> const&, DB::SelectQueryOptions const&, std::shared_ptr<DB::Context const> const&, std::shared_ptr<DB::IStorage> const&) (.llvm.6201911839381799294) @ 0x000000001b478d5c
17. 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&) @ 0x000000001b476c2a
18. std::unique_ptr<DB::IInterpreter, std::default_delete<DB::IInterpreter>> std::__function::__policy_func<std::unique_ptr<DB::IInterpreter, std::default_delete<DB::IInterpreter>> (DB::InterpreterFactory::Arguments const&)>::__call_func[abi:ne210105]<DB::registerInterpreterSelectQueryAnalyzer(DB::InterpreterFactory&)::$_0>(std::__function::__policy_storage const*, DB::InterpreterFactory::Arguments const&) (.llvm.6201911839381799294) @ 0x000000001b47a2e2
19. DB::InterpreterFactory::get(std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&) @ 0x000000001b3e8ebc
20. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, std::unique_ptr<DB::ReadBuffer, std::default_delete<DB::ReadBuffer>>&, std::shared_ptr<DB::IAST>&, std::shared_ptr<DB::ImplicitTransactionControlExecutor>, std::function<void ()>, DB::QueryResultDetails&) @ 0x000000001b85dbca
21. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000001b85888a
22. DB::TCPHandler::runImpl() @ 0x000000001d036ec0
23. DB::TCPHandler::run() @ 0x000000001d058aa4
24. Poco::Net::TCPServerConnection::start() @ 0x0000000022389cc7
25. Poco::Net::TCPServerDispatcher::run() @ 0x000000002238a1b1
26. Poco::PooledThread::run() @ 0x000000002234c21c
27. Poco::ThreadImpl::runnableEntry(void*) @ 0x000000002234a6cf
28. ? @ 0x0000000000094ac3
29. clone @ 0x0000000000125a74

Here's how I checked it clickhouse-client --query 'SELECT position(1 = ANY(SELECT 1), 1);'

https://fiddle.clickhouse.com/a96c40d6-44f1-4460-9447-69eb37b83260

[Felixoid]

It's the same for w/ and w/o analyzer

> clickhouse-client --query 'SELECT position(1 = ANY(SELECT 1), 1);' --enable_analyzer=0
Received exception from server (version 25.12.1):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type UInt8 of argument of function position: While processing position(1 IN ((SELECT 1) AS _subquery2), 1). (ILLEGAL_TYPE_OF_ARGUMENT)
(query: SELECT position(1 = ANY(SELECT 1), 1);)

> clickhouse-client --query 'SELECT position(1 = ANY(SELECT 1), 1);' --enable_analyzer=1
Received exception from server (version 25.12.1):
Code: 43. DB::Exception: Received from localhost:9000. DB::Exception: Illegal type UInt8 of argument of function position: In scope SELECT position(1 IN (SELECT 1), 1). (ILLEGAL_TYPE_OF_ARGUMENT)
(query: SELECT position(1 = ANY(SELECT 1), 1);)

[Felixoid]

Is it reproducible anywhere, somehow?

[PedroTadim]

I still reproduce it in a debug build

[Felixoid]

after some debugging time, here's another example of the same failing:

Inconsistent AST formatting: the query:
SELECT position(1 IN (SELECT 2), 1)
cannot parse query back from SELECT position((1 IN (SELECT 2)), 1)

Because the formatted query causes syntax error

:) SELECT position(1 IN (SELECT 2), 1)

Syntax error: failed at position 32 (,):

SELECT position(1 IN (SELECT 2), 1)

Expected one of: token, ClosingRoundBracket, OR, AND, IS NOT DISTINCT FROM, IS DISTINCT FROM, IS NULL, IS NOT NULL, BETWEEN, NOT BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, REGEXP, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, MOD, DIV, alias, AS, identifier

Looks like it's either a wrong SYNTAX_ERROR thrown the second time or a non-thrown on the first parse.

[Felixoid]

Looks fixed in the PR, waiting for the rest of CI and another approval https://github.com/ClickHouse/ClickHouse/pull/92225/files#diff-fa9a77e9e0306fa8a7e75caf550c8bf0d92015b8ddc6612e3ffcf5505004d017R1