Vivek Gangavarapu created SPARK-58965:
-----------------------------------------
Summary: Add gcd and lcm math functions
Key: SPARK-58965
URL: https://issues.apache.org/jira/browse/SPARK-58965
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 5.0.0
Reporter: Vivek Gangavarapu
Spark SQL has no way to compute the greatest common divisor or the least
common multiple of two integers. There is no expression, and no combination
of existing built-ins that produces the result, so users must fall back to a
UDF -- which for PySpark means a Python round trip per row and no whole-stage
codegen.
Both functions are standard in comparable engines:
* PostgreSQL 13+: gcd(a, b), lcm(a, b) for integer, bigint and numeric
* DuckDB: gcd(a, b), lcm(a, b) (aliases greatest_common_divisor,
least_common_multiple)
Common uses: reducing fractions and ratios to lowest terms, aligning batch or
partition sizes, computing the repeat period of overlapping schedules, and
normalizing denominators before aggregation.
h3. Proposed signature
{code:sql}
gcd(a, b) -- returns BIGINT
lcm(a, b) -- returns BIGINT
{code}
Both arguments are implicitly cast to BIGINT, matching the existing
{{factorial}} expression, which also takes an integral argument and returns
BIGINT.
h3. Semantics
|| Case || gcd || lcm || Rationale ||
| either argument is NULL | NULL | NULL | null-intolerant, as for all math
functions |
| gcd(0, 0) / lcm(0, 0) | 0 | 0 | PostgreSQL |
| one argument is 0 | abs(other) | 0 | PostgreSQL |
| negative arguments | result is non-negative | result is non-negative |
PostgreSQL, DuckDB |
| result not representable as BIGINT | ARITHMETIC_OVERFLOW |
ARITHMETIC_OVERFLOW | see below |
Overflow arises in exactly two places, and in both PostgreSQL raises an error
as well:
* {{gcd(-9223372036854775808, 0)}} -- the result is abs(Long.MinValue), which
is not representable.
* {{lcm(a, b)}} where {{abs(a) / gcd(a, b) * abs(b)}} exceeds Long.MaxValue.
Both are raised as the existing ARITHMETIC_OVERFLOW error condition through
{{MathUtils.withOverflow}}. No new error condition is introduced.
h3. Examples
{code:sql}
SELECT gcd(24, 36); -- 12
SELECT gcd(-24, 36); -- 12
SELECT gcd(0, 0); -- 0
SELECT lcm(4, 6); -- 12
SELECT lcm(0, 5); -- 0
SELECT gcd(NULL, 5); -- NULL
-- reduce a ratio to lowest terms
SELECT num / gcd(num, den) AS n,
den / gcd(num, den) AS d
FROM ratios;
{code}
h3. Scope
* Two new expressions in mathExpressions.scala, with codegen.
* Registration in FunctionRegistry.
* Scala/Java API in functions.scala, plus PySpark and Spark Connect.
* Unit tests, SQL golden-file tests, and documentation.
No existing behavior changes; the change is purely additive.
h3. Follow-ups deliberately left out of scope
* DECIMAL support (PostgreSQL supports gcd/lcm on numeric). Can be added
later without breaking the BIGINT signature.
* A {{try_lcm}} variant returning NULL instead of raising on overflow, if the
community wants one.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
