jiangxintong created SPARK-57926:
------------------------------------
Summary: Add IPv4 address functions (inet_aton, inet_ntoa,
try_inet_aton)
Key: SPARK-57926
URL: https://issues.apache.org/jira/browse/SPARK-57926
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 4.2.0
Reporter: jiangxintong
h2. Problem
Spark SQL has no built-in functions for IP address manipulation. This is a
common need in network log analysis, security auditing, and IP geolocation
scenarios.
Other databases have this capability:
* MySQL: INET_ATON() / INET_NTOA()
* ClickHouse: IPv4StringToNum() / IPv4NumToString()
* Doris: inet_aton() / inet_ntoa()
* Databend: inet_aton() / inet_ntoa() / try_inet_aton()
* VoltDB: INET_ATON() / INET_NTOA()
* SingleStore: INET_ATON() / INET_NTOA()
h2. Proposed Functions
||Function||Signature||Description||
|inet_aton|inet_aton(STRING) -> LONG|Convert IPv4 address string to 32-bit
integer. Throws on invalid input when ANSI mode is on; returns null when ANSI
mode is off.|
|inet_ntoa|inet_ntoa(LONG) -> STRING|Convert 32-bit integer to IPv4 address
string. Throws on out-of-range input ([0, 2^32-1]) when ANSI mode is on;
returns null when ANSI mode is off.|
|try_inet_aton|try_inet_aton(STRING) -> LONG|Same as inet_aton, but always
returns null on invalid input regardless of ANSI mode.|
h2. Examples
{code:sql}
> SELECT inet_aton('192.168.1.1');
3232235777
> SELECT inet_ntoa(3232235777);
192.168.1.1
> SELECT try_inet_aton('not_an_ip');
null
{code}
h2. ANSI Semantics
inet_aton and inet_ntoa follow Spark's ANSI mode convention:
* ANSI mode ON: throws QueryExecutionErrors on invalid/out-of-range input
* ANSI mode OFF: returns null on invalid/out-of-range input
try_inet_aton always returns null on error, following the try_* family pattern
(25 existing try_ functions in the registry).
This is consistent with cast/to_number/to_timestamp behavior.
h2. Notes
* IPv4 only (32-bit). IPv6 (128-bit) does not fit in LONG and requires
BINARY/STRING representation — deferred to a separate issue.
* Naming follows MySQL convention (inet_aton/inet_ntoa).
* Return type is LONG (not INT) because 3232235777 > 2^31-1.
h2. Why Built-in Instead of UDF
IP address functions are:
* Standard in major databases (MySQL, ClickHouse, Doris, Databend, VoltDB,
SingleStore)
* Performance-critical for network log analysis (millions of rows)
* Used in WHERE clauses (benefit from whole-stage codegen)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
