[
https://issues.apache.org/jira/browse/SPARK-58965?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Vivek Gangavarapu updated SPARK-58965:
--------------------------------------
Description:
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 under ANSI mode,
NULL otherwise | ARITHMETIC_OVERFLOW under ANSI mode, NULL otherwise | 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 reported with the existing ARITHMETIC_OVERFLOW error condition; no new
error
condition is introduced. Overflow is gated on ANSI mode, as it is everywhere
else in Spark
(compare {{conv}}, which takes the same approach): under ANSI mode the error is
raised, and in
non-ANSI mode the result is NULL. Raising unconditionally would make these the
only functions in
mathExpressions.scala that do so.
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.
was:
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.
> 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: 4.4.0
> Reporter: Vivek Gangavarapu
> Priority: Major
>
> 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 under ANSI mode,
> NULL otherwise | ARITHMETIC_OVERFLOW under ANSI mode, NULL otherwise | 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 reported with the existing ARITHMETIC_OVERFLOW error condition; no
> new error
> condition is introduced. Overflow is gated on ANSI mode, as it is everywhere
> else in Spark
> (compare {{conv}}, which takes the same approach): under ANSI mode the error
> is raised, and in
> non-ANSI mode the result is NULL. Raising unconditionally would make these
> the only functions in
> mathExpressions.scala that do so.
> 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]
