[
https://issues.apache.org/jira/browse/SPARK-57926?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
jiangxintong updated SPARK-57926:
---------------------------------
Description:
h2. Problem
Spark SQL currently has no built-in functions for IPv4 address manipulation.
This is a common
requirement in network log analysis, security auditing, and IP geolocation
workloads.
Users currently need database-specific functions, UDFs, or application-side
conversion. A native
Spark SQL implementation would keep the operation visible to analysis and
whole-stage codegen,
and would provide consistent SQL, Scala, PySpark, and Spark Connect APIs.
h2. Proposed Functions
||Function||Signature||Description||
|inet_aton|inet_aton(STRING) -> LONG|Converts IPv4 text to an unsigned 32-bit
value represented as LONG. Invalid input returns NULL when ANSI mode is
disabled and raises a structured error when ANSI mode is enabled.|
|inet_ntoa|inet_ntoa(LONG) -> STRING|Converts an unsigned 32-bit value
represented as LONG to canonical four-part IPv4 text. Values outside [0,
4294967295] return NULL when ANSI mode is disabled and raise a structured error
when ANSI mode is enabled.|
|try_inet_aton|try_inet_aton(STRING) -> LONG|The non-throwing variant of
inet_aton. Invalid IPv4 text always produces NULL.|
|try_inet_ntoa|try_inet_ntoa(LONG) -> STRING|The non-throwing variant of
inet_ntoa. Values outside the valid IPv4 range always produce NULL.|
h2. IPv4 Semantics
* IPv4 text accepts one to four dot-separated parts.
* Each part must contain decimal digits and have a value between 0 and 255.
* Leading zeroes are interpreted as decimal digits.
* Short forms follow the existing IPv4 conversion convention: {{a}} maps to
{{0.0.0.a}},
{{a.b}} maps to {{a.0.0.b}}, and {{a.b.c}} maps to {{a.b.0.c}}.
* Empty parts, whitespace, signs, non-ASCII characters, more than four parts,
and out-of-range
parts are invalid.
* inet_ntoa always returns canonical four-part text.
* The implementation supports IPv4 only. IPv6, CIDR operations, host name
resolution, and new
binary IP types are outside the scope of this issue.
h2. ANSI, NULL, and Type Conversion Behavior
inet_aton and inet_ntoa follow Spark's ANSI mode convention. The try_* variants
always return NULL
for invalid IPv4 content or out-of-range values, regardless of ANSI mode. SQL
NULL inputs follow
Spark's normal nullable expression semantics.
The documented argument types are STRING for inet_aton and LONG for inet_ntoa.
As with other Spark
expressions using implicit input types, analyzer coercion may accept additional
coercible inputs.
For example, inet_aton(1.5D) is analyzed through a STRING cast, while
inet_ntoa(1.5D) is analyzed
through a BIGINT cast. Cast failures retain Spark's CAST_INVALID_INPUT behavior
and precedence;
they are not reported as IPv4 range errors.
h2. Examples
{code:sql}
SELECT inet_aton('192.168.1.1');
-- 3232235777
SELECT inet_aton('127.1');
-- 2130706433
SELECT inet_ntoa(3232235777);
-- 192.168.1.1
SELECT try_inet_aton('not_an_ip');
-- NULL
SELECT try_inet_ntoa(-1);
-- NULL
{code}
h2. Why Built-in Functions Instead of UDFs
IPv4 conversion is a scalar operation used in projections, filters, conditional
expressions, and
joins. Implementing it as native functions provides analyzer-visible types and
errors, whole-stage
codegen support, and consistent behavior across Spark SQL, Scala, PySpark, and
Spark Connect without
requiring users to maintain UDFs.
The function names follow established database conventions, but the behavior is
defined by the
Spark contract above rather than by a promise of complete compatibility with
any other database.
was:
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)
Summary: Add IPv4 address functions (inet_aton, inet_ntoa,
try_inet_aton, try_inet_ntoa) (was: Add IPv4 address functions (inet_aton,
inet_ntoa, try_inet_aton))
> Add IPv4 address functions (inet_aton, inet_ntoa, try_inet_aton,
> try_inet_ntoa)
> -------------------------------------------------------------------------------
>
> 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
> Priority: Major
> Labels: pull-request-available
>
> h2. Problem
> Spark SQL currently has no built-in functions for IPv4 address manipulation.
> This is a common
> requirement in network log analysis, security auditing, and IP geolocation
> workloads.
> Users currently need database-specific functions, UDFs, or application-side
> conversion. A native
> Spark SQL implementation would keep the operation visible to analysis and
> whole-stage codegen,
> and would provide consistent SQL, Scala, PySpark, and Spark Connect APIs.
> h2. Proposed Functions
> ||Function||Signature||Description||
> |inet_aton|inet_aton(STRING) -> LONG|Converts IPv4 text to an unsigned 32-bit
> value represented as LONG. Invalid input returns NULL when ANSI mode is
> disabled and raises a structured error when ANSI mode is enabled.|
> |inet_ntoa|inet_ntoa(LONG) -> STRING|Converts an unsigned 32-bit value
> represented as LONG to canonical four-part IPv4 text. Values outside [0,
> 4294967295] return NULL when ANSI mode is disabled and raise a structured
> error when ANSI mode is enabled.|
> |try_inet_aton|try_inet_aton(STRING) -> LONG|The non-throwing variant of
> inet_aton. Invalid IPv4 text always produces NULL.|
> |try_inet_ntoa|try_inet_ntoa(LONG) -> STRING|The non-throwing variant of
> inet_ntoa. Values outside the valid IPv4 range always produce NULL.|
> h2. IPv4 Semantics
> * IPv4 text accepts one to four dot-separated parts.
> * Each part must contain decimal digits and have a value between 0 and 255.
> * Leading zeroes are interpreted as decimal digits.
> * Short forms follow the existing IPv4 conversion convention: {{a}} maps to
> {{0.0.0.a}},
> {{a.b}} maps to {{a.0.0.b}}, and {{a.b.c}} maps to {{a.b.0.c}}.
> * Empty parts, whitespace, signs, non-ASCII characters, more than four parts,
> and out-of-range
> parts are invalid.
> * inet_ntoa always returns canonical four-part text.
> * The implementation supports IPv4 only. IPv6, CIDR operations, host name
> resolution, and new
> binary IP types are outside the scope of this issue.
> h2. ANSI, NULL, and Type Conversion Behavior
> inet_aton and inet_ntoa follow Spark's ANSI mode convention. The try_*
> variants always return NULL
> for invalid IPv4 content or out-of-range values, regardless of ANSI mode. SQL
> NULL inputs follow
> Spark's normal nullable expression semantics.
> The documented argument types are STRING for inet_aton and LONG for
> inet_ntoa. As with other Spark
> expressions using implicit input types, analyzer coercion may accept
> additional coercible inputs.
> For example, inet_aton(1.5D) is analyzed through a STRING cast, while
> inet_ntoa(1.5D) is analyzed
> through a BIGINT cast. Cast failures retain Spark's CAST_INVALID_INPUT
> behavior and precedence;
> they are not reported as IPv4 range errors.
> h2. Examples
> {code:sql}
> SELECT inet_aton('192.168.1.1');
> -- 3232235777
> SELECT inet_aton('127.1');
> -- 2130706433
> SELECT inet_ntoa(3232235777);
> -- 192.168.1.1
> SELECT try_inet_aton('not_an_ip');
> -- NULL
> SELECT try_inet_ntoa(-1);
> -- NULL
> {code}
> h2. Why Built-in Functions Instead of UDFs
> IPv4 conversion is a scalar operation used in projections, filters,
> conditional expressions, and
> joins. Implementing it as native functions provides analyzer-visible types
> and errors, whole-stage
> codegen support, and consistent behavior across Spark SQL, Scala, PySpark,
> and Spark Connect without
> requiring users to maintain UDFs.
> The function names follow established database conventions, but the behavior
> is defined by the
> Spark contract above rather than by a promise of complete compatibility with
> any other database.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
