Invalid IPv4 value: while executing FUNCTION... when casting and User Defined Function

[liuzhiguo630]

Describe what's wrong
SQL:

SELECT
    strings['clientIp'] AS clientIp,
    ip_to_provider(clientIp) AS provider
FROM
(
    SELECT CAST((string.keys, string.values), 'Map(String,String)') AS strings
    FROM unified_log_common_all
    WHERE project = 'client' AND dt >= '2022-08-22 23:25:00' AND dt <= '2022-08-22 23:50:00'
)
GROUP BY clientIp HAVING provider = 'Chinanet'

Result:
Received exception from server (version 22.7.3): Code: 441. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse-datanode-0.clickhouse-datanode.svc.cluster.local:9000. DB::Exception: Invalid IPv4 value: while executing 'FUNCTION IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp') :: 7) -> IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp')) UInt32 : 8'. (CANNOT_PARSE_DOMAIN_VALUE_FROM_STRING)

A clear and concise description of what works not as it is supposed to.

I have defined function ip_to_provider: CREATE FUNCTION if not exists ip_to_provider AS (x) -> dictGetString('geoip_asn_blocks_ipv4', 'autonomous_system_organization', tuple(IPv4StringToNum(x)));

When executing SQL without having provider = 'Chinanet', it works:

┌─clientIp────────┬─provider────────────────────────────────────┐
│ 171.212.121.33  │ Chinanet                                    │
│ 125.70.125.19   │ Chinanet                                    │
│ 171.223.95.84   │ Chinanet                                    │
│ 117.173.227.180 │ China Mobile Communications Group Co., Ltd. │
└─────────────────┴─────────────────────────────────────────────┘

They are all valid IP, but if I add having, or use sub query with where provider = 'Chinanet', it produced exception.

Does it reproduce on recent release?
22.3.x ~ 22.7.x

How to reproduce
First you need have a dictionary geoip_asn_blocks_ipv4, refer to this project https://github.com/AlexeyKupershtokh/clickhouse-maxmind-geoip , create function ip_to_provider, create a table with strings array, write some ip.

Otherwise if you replace table, it can't reproduce this problem, like:

SELECT
    strings['clientIp'] AS clientIp,
    ip_to_provider(clientIp) AS provider
FROM
( SELECT CAST((['clientIp'], ['125.70.125.19']), 'Map(String,String)') AS strings )
GROUP BY clientIp HAVING provider = 'Chinanet'

┌─clientIp──────┬─provider─┐
│ 125.70.125.19 │ Chinanet │
└───────────────┴──────────┘

Error message and/or stacktrace

[clickhouse-readnode-0] 2022.08.23 11:32:16.622264 [ 1664 ] {f705f257-3136-49b6-9123-42efcaddeb90} <Error> executeQuery: Code: 441. DB::Exception: Received from clickhouse-datanode-new-0.clickhouse-datanode-new.bs-logging.svc.cluster.local:9000. DB::Exception: Invalid IPv4 value: while executing 'FUNCTION IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp') :: 7) -> IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp')) UInt32 : 8'. Stack trace:

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xba37dda in /usr/bin/clickhouse
1. COW<DB::IColumn>::immutable_ptr<DB::IColumn> DB::convertToIPv4<(DB::IPStringToNumExceptionMode)0>(COW<DB::IColumn>::immutable_ptr<DB::IColumn>, DB::PODArray<char8_t, 4096ul, Allocator<false, false>, 15ul, 16ul> const*) @ 0xe20cb34 in /usr/bin/clickhouse
2. DB::FunctionIPv4StringToNum<(DB::IPStringToNumExceptionMode)0>::executeImpl(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName> > const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0xe73095b in /usr/bin/clickhouse
3. DB::FunctionToExecutableFunctionAdaptor::executeImpl(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName> > const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0xe1f158e in /usr/bin/clickhouse
4. DB::IExecutableFunction::executeWithoutLowCardinalityColumns(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName> > const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x15bfece7 in /usr/bin/clickhouse
5. DB::IExecutableFunction::executeWithoutSparseColumns(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName> > const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x15bff721 in /usr/bin/clickhouse
6. DB::IExecutableFunction::execute(std::__1::vector<DB::ColumnWithTypeAndName, std::__1::allocator<DB::ColumnWithTypeAndName> > const&, std::__1::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x15c00c2c in /usr/bin/clickhouse
7. DB::ExpressionActions::execute(DB::Block&, unsigned long&, bool) const @ 0x166ab295 in /usr/bin/clickhouse
8. DB::FilterTransform::transform(DB::Chunk&) @ 0x17d8025f in /usr/bin/clickhouse
9. DB::ISimpleTransform::transform(DB::Chunk&, DB::Chunk&) @ 0x1329bbf0 in /usr/bin/clickhouse
10. DB::ISimpleTransform::work() @ 0x17b8c469 in /usr/bin/clickhouse
11. DB::ExecutionThreadContext::executeTask() @ 0x17babe4a in /usr/bin/clickhouse
12. DB::PipelineExecutor::executeStepImpl(unsigned long, std::__1::atomic<bool>*) @ 0x17ba0d30 in /usr/bin/clickhouse
13. ? @ 0x17ba2501 in /usr/bin/clickhouse
14. ThreadPoolImpl<std::__1::thread>::worker(std::__1::__list_iterator<std::__1::thread, void*>) @ 0xbb046e8 in /usr/bin/clickhouse
15. ? @ 0xbb07a7d in /usr/bin/clickhouse
16. ? @ 0x7fa160543609 in ?
17. __clone @ 0x7fa160468133 in ?
: While executing Remote. (CANNOT_PARSE_DOMAIN_VALUE_FROM_STRING) (version 22.7.3.5 (official build)) (from 127.0.0.1:58506) (in query: SELECT strings['clientIp'] AS clientIp, ip_to_provider(clientIp) AS provider FROM ( SELECT CAST((string.keys, string.values), 'Map(String,String)') AS strings FROM unified_log_common_all WHERE project = 'client' AND dt >= '2022-08-22 23:25:00' AND dt <= '2022-08-22 23:50:00' ) GROUP BY clientIp HAVING provider = 'Chinanet'), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int, bool) @ 0xba37dda in /usr/bin/clickhouse
1. DB::readException(DB::ReadBuffer&, std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, bool) @ 0xbab19de in /usr/bin/clickhouse
2. DB::Connection::receiveException() const @ 0x178540a1 in /usr/bin/clickhouse
3. DB::Connection::receivePacket() @ 0x1785bf91 in /usr/bin/clickhouse
4. DB::PacketReceiver::Routine::operator()(boost::context::fiber&&) @ 0x1787d9b5 in /usr/bin/clickhouse
5. void boost::context::detail::fiber_entry<boost::context::detail::fiber_record<boost::context::fiber, FiberStack&, DB::PacketReceiver::Routine> >(boost::context::detail::transfer_t) @ 0x1787d7ee in /usr/bin/clickhouse


0 rows in set. Elapsed: 0.035 sec.

Received exception from server (version 22.7.3):
Code: 441. DB::Exception: Received from localhost:9000. DB::Exception: Received from clickhouse-datanode-new-0.clickhouse-datanode-new.bs-logging.svc.cluster.local:9000. DB::Exception: Invalid IPv4 value: while executing 'FUNCTION IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp') :: 7) -> IPv4StringToNum(arrayElement(CAST(tuple(string.keys, string.values), 'Map(String,String)'), 'clientIp')) UInt32 : 8'. (CANNOT_PARSE_DOMAIN_VALUE_FROM_STRING)

[liuzhiguo630]

Now I fixed with enable_optimize_predicate_expression = 0 for temporarily