Skip to content

Commit fae0687

Browse files
ritvibhattritvibhatt
authored
Add max/min eval functions (#4333)
1 parent e468513 commit fae0687

16 files changed

Lines changed: 622 additions & 0 deletions

File tree

‎core/src/main/java/org/opensearch/sql/data/utils/MixedTypeComparator.java‎

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,38 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.data.utils;
7+
8+
import java.util.Comparator;
9+
10+
/** Comparator for mixed-type values. */
11+
public class MixedTypeComparator implements Comparator<Object> {
12+
13+
public static final MixedTypeComparator INSTANCE = new MixedTypeComparator();
14+
15+
private MixedTypeComparator() {}
16+
17+
@Override
18+
public int compare(Object a, Object b) {
19+
boolean aIsNumeric = isNumeric(a);
20+
boolean bIsNumeric = isNumeric(b);
21+
22+
// For same types compare directly
23+
if (aIsNumeric == bIsNumeric) {
24+
if (aIsNumeric) {
25+
return Double.compare(((Number) a).doubleValue(), ((Number) b).doubleValue());
26+
} else {
27+
return Integer.compare(a.toString().compareTo(b.toString()), 0);
28+
}
29+
}
30+
// For mixed types, strings are considered larger than numbers (non-numeric values are treated
31+
// as strings)
32+
return aIsNumeric ? -1 : 1;
33+
}
34+
35+
private static boolean isNumeric(Object obj) {
36+
return obj instanceof Number;
37+
}
38+
}

‎core/src/main/java/org/opensearch/sql/expression/function/PPLBuiltinOperators.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -94,6 +94,8 @@
9494
import org.opensearch.sql.expression.function.udf.math.ConvFunction;
9595
import org.opensearch.sql.expression.function.udf.math.DivideFunction;
9696
import org.opensearch.sql.expression.function.udf.math.EulerFunction;
97+
import org.opensearch.sql.expression.function.udf.math.MaxFunction;
98+
import org.opensearch.sql.expression.function.udf.math.MinFunction;
9799
import org.opensearch.sql.expression.function.udf.math.ModFunction;
98100
import org.opensearch.sql.expression.function.udf.math.NumberToStringFunction;
99101

@@ -124,6 +126,8 @@ public class PPLBuiltinOperators extends ReflectiveSqlOperatorTable {
124126
public static final SqlOperator DIVIDE = new DivideFunction().toUDF("DIVIDE");
125127
public static final SqlOperator SHA2 = CryptographicFunction.sha2().toUDF("SHA2");
126128
public static final SqlOperator CIDRMATCH = new CidrMatchFunction().toUDF("CIDRMATCH");
129+
public static final SqlOperator MAX = new MaxFunction().toUDF("MAX");
130+
public static final SqlOperator MIN = new MinFunction().toUDF("MIN");
127131

128132
public static final SqlOperator COSH =
129133
adaptMathFunctionToUDF(

‎core/src/main/java/org/opensearch/sql/expression/function/PPLFuncImpTable.java‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -717,6 +717,10 @@ void populate() {
717717
registerOperator(INTERNAL_REGEXP_REPLACE_5, SqlLibraryOperators.REGEXP_REPLACE_5);
718718
registerOperator(INTERNAL_TRANSLATE3, SqlLibraryOperators.TRANSLATE3);
719719

720+
// Register eval functions for PPL max() and min() calls
721+
registerOperator(MAX, PPLBuiltinOperators.MAX);
722+
registerOperator(MIN, PPLBuiltinOperators.MIN);
723+
720724
// Register PPL UDF operator
721725
registerOperator(COSH, PPLBuiltinOperators.COSH);
722726
registerOperator(SINH, PPLBuiltinOperators.SINH);

‎core/src/main/java/org/opensearch/sql/expression/function/udf/math/MaxFunction.java‎

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.expression.function.udf.math;
7+
8+
import java.util.Arrays;
9+
import java.util.List;
10+
import java.util.Objects;
11+
import org.apache.calcite.adapter.enumerable.NotNullImplementor;
12+
import org.apache.calcite.adapter.enumerable.NullPolicy;
13+
import org.apache.calcite.adapter.enumerable.RexToLixTranslator;
14+
import org.apache.calcite.linq4j.tree.Expression;
15+
import org.apache.calcite.linq4j.tree.Expressions;
16+
import org.apache.calcite.rex.RexCall;
17+
import org.apache.calcite.sql.type.SqlReturnTypeInference;
18+
import org.apache.calcite.sql.type.SqlTypeName;
19+
import org.opensearch.sql.data.utils.MixedTypeComparator;
20+
import org.opensearch.sql.expression.function.ImplementorUDF;
21+
import org.opensearch.sql.expression.function.UDFOperandMetadata;
22+
23+
/**
24+
* MAX(value1, value2, ...) returns the maximum value from the arguments. For mixed types, strings
25+
* have higher precedence than numbers.
26+
*/
27+
public class MaxFunction extends ImplementorUDF {
28+
29+
public MaxFunction() {
30+
super(new MaxImplementor(), NullPolicy.ALL);
31+
}
32+
33+
@Override
34+
public SqlReturnTypeInference getReturnTypeInference() {
35+
return opBinding -> opBinding.getTypeFactory().createSqlType(SqlTypeName.ANY);
36+
}
37+
38+
@Override
39+
public UDFOperandMetadata getOperandMetadata() {
40+
return null;
41+
}
42+
43+
public static class MaxImplementor implements NotNullImplementor {
44+
45+
@Override
46+
public Expression implement(
47+
RexToLixTranslator translator, RexCall call, List<Expression> translatedOperands) {
48+
return Expressions.call(
49+
MaxImplementor.class, "max", Expressions.newArrayInit(Object.class, translatedOperands));
50+
}
51+
52+
public static Object max(Object[] args) {
53+
return findMax(args);
54+
}
55+
56+
private static Object findMax(Object[] args) {
57+
if (args == null) {
58+
return null;
59+
}
60+
61+
return Arrays.stream(args)
62+
.filter(Objects::nonNull)
63+
.max(MixedTypeComparator.INSTANCE)
64+
.orElse(null);
65+
}
66+
}
67+
}

‎core/src/main/java/org/opensearch/sql/expression/function/udf/math/MinFunction.java‎

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.expression.function.udf.math;
7+
8+
import java.util.Arrays;
9+
import java.util.List;
10+
import java.util.Objects;
11+
import org.apache.calcite.adapter.enumerable.NotNullImplementor;
12+
import org.apache.calcite.adapter.enumerable.NullPolicy;
13+
import org.apache.calcite.adapter.enumerable.RexToLixTranslator;
14+
import org.apache.calcite.linq4j.tree.Expression;
15+
import org.apache.calcite.linq4j.tree.Expressions;
16+
import org.apache.calcite.rex.RexCall;
17+
import org.apache.calcite.sql.type.SqlReturnTypeInference;
18+
import org.apache.calcite.sql.type.SqlTypeName;
19+
import org.opensearch.sql.data.utils.MixedTypeComparator;
20+
import org.opensearch.sql.expression.function.ImplementorUDF;
21+
import org.opensearch.sql.expression.function.UDFOperandMetadata;
22+
23+
/**
24+
* MIN(value1, value2, ...) returns the minimum value from the arguments. For mixed types, numbers
25+
* have higher precedence than strings.
26+
*/
27+
public class MinFunction extends ImplementorUDF {
28+
29+
public MinFunction() {
30+
super(new MinImplementor(), NullPolicy.ALL);
31+
}
32+
33+
@Override
34+
public SqlReturnTypeInference getReturnTypeInference() {
35+
return opBinding -> opBinding.getTypeFactory().createSqlType(SqlTypeName.ANY);
36+
}
37+
38+
@Override
39+
public UDFOperandMetadata getOperandMetadata() {
40+
return null;
41+
}
42+
43+
public static class MinImplementor implements NotNullImplementor {
44+
45+
@Override
46+
public Expression implement(
47+
RexToLixTranslator translator, RexCall call, List<Expression> translatedOperands) {
48+
return Expressions.call(
49+
MinImplementor.class, "min", Expressions.newArrayInit(Object.class, translatedOperands));
50+
}
51+
52+
public static Object min(Object[] args) {
53+
return findMin(args);
54+
}
55+
56+
private static Object findMin(Object[] args) {
57+
if (args == null) {
58+
return null;
59+
}
60+
61+
return Arrays.stream(args)
62+
.filter(Objects::nonNull)
63+
.min(MixedTypeComparator.INSTANCE)
64+
.orElse(null);
65+
}
66+
}
67+
}

‎core/src/test/java/org/opensearch/sql/data/utils/MixedTypeComparatorTest.java‎

Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.data.utils;
7+
8+
import static org.junit.jupiter.api.Assertions.assertEquals;
9+
10+
import java.math.BigDecimal;
11+
import org.junit.jupiter.api.Test;
12+
13+
class MixedTypeComparatorTest {
14+
15+
private final MixedTypeComparator comparator = MixedTypeComparator.INSTANCE;
16+
17+
@Test
18+
public void testNumericComparison() {
19+
assertEquals(-1, comparator.compare(1, 2));
20+
assertEquals(1, comparator.compare(2, 1));
21+
assertEquals(0, comparator.compare(5, 5));
22+
23+
// Different numeric types
24+
assertEquals(-1, comparator.compare(1, 2.5));
25+
assertEquals(1, comparator.compare(3.14, 2));
26+
assertEquals(0, comparator.compare(4, 4.0));
27+
assertEquals(-1, comparator.compare(10L, new BigDecimal("20")));
28+
}
29+
30+
@Test
31+
public void testStringComparison() {
32+
assertEquals(-1, comparator.compare("apple", "banana"));
33+
assertEquals(1, comparator.compare("zebra", "apple"));
34+
assertEquals(0, comparator.compare("test", "test"));
35+
assertEquals(-1, comparator.compare("ABC", "abc")); //
36+
assertEquals(1, comparator.compare("hello", "HELLO"));
37+
}
38+
39+
@Test
40+
public void testMixedTypeComparison() {
41+
assertEquals(-1, comparator.compare(42, "apple"));
42+
assertEquals(1, comparator.compare("apple", 42));
43+
assertEquals(-1, comparator.compare(3.14, "hello"));
44+
assertEquals(1, comparator.compare("world", 100L));
45+
assertEquals(-1, comparator.compare(0, "0"));
46+
assertEquals(1, comparator.compare("123", 456));
47+
}
48+
}

‎docs/category.json‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,8 @@
4949
"user/ppl/cmd/subquery.rst",
5050
"user/ppl/cmd/syntax.rst",
5151
"user/ppl/cmd/timechart.rst",
52+
"user/ppl/cmd/search.rst",
53+
"user/ppl/functions/statistical.rst",
5254
"user/ppl/cmd/top.rst",
5355
"user/ppl/cmd/trendline.rst",
5456
"user/ppl/cmd/where.rst",

‎docs/user/ppl/functions/statistical.rst‎

Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,109 @@
1+
======================
2+
Statistical Functions
3+
======================
4+
5+
.. rubric:: Table of contents
6+
7+
.. contents::
8+
:local:
9+
:depth: 1
10+
11+
12+
MAX
13+
---
14+
15+
Description
16+
>>>>>>>>>>>
17+
18+
Usage: max(x, y, ...) returns the maximum value from all provided arguments. Strings are treated as greater than numbers, so if provided both strings and numbers, it will return the maximum string value (lexicographically ordered)
19+
20+
Note: This function is only available in the eval command context and requires Calcite engine to be enabled.
21+
22+
Argument type: Variable number of INTEGER/LONG/FLOAT/DOUBLE/STRING arguments
23+
24+
Return type: Type of the selected argument
25+
26+
Example::
27+
28+
os> source=accounts | eval max_val = MAX(age, 30) | fields age, max_val
29+
fetched rows / total rows = 4/4
30+
+-----+---------+
31+
| age | max_val |
32+
|-----+---------|
33+
| 32 | 32 |
34+
| 36 | 36 |
35+
| 28 | 30 |
36+
| 33 | 33 |
37+
+-----+---------+
38+
39+
os> source=accounts | eval result = MAX(firstname, 'John') | fields firstname, result
40+
fetched rows / total rows = 4/4
41+
+-----------+---------+
42+
| firstname | result |
43+
|-----------+---------|
44+
| Amber | John |
45+
| Hattie | John |
46+
| Nanette | Nanette |
47+
| Dale | John |
48+
+-----------+---------+
49+
50+
os> source=accounts | eval result = MAX(age, 35, 'John', firstname) | fields age, firstname, result
51+
fetched rows / total rows = 4/4
52+
+-----+-----------+---------+
53+
| age | firstname | result |
54+
|-----+-----------+---------|
55+
| 32 | Amber | John |
56+
| 36 | Hattie | John |
57+
| 28 | Nanette | Nanette |
58+
| 33 | Dale | John |
59+
+-----+-----------+---------+
60+
61+
62+
MIN
63+
---
64+
65+
Description
66+
>>>>>>>>>>>
67+
68+
Usage: min(x, y, ...) returns the minimum value from all provided arguments. Strings are treated as greater than numbers, so if provided both strings and numbers, it will return the minimum numeric value.
69+
70+
Note: This function is only available in the eval command context and requires Calcite engine to be enabled.
71+
72+
Argument type: Variable number of INTEGER/LONG/FLOAT/DOUBLE/STRING arguments
73+
74+
Return type: Type of the selected argument
75+
76+
Example::
77+
78+
os> source=accounts | eval min_val = MIN(age, 30) | fields age, min_val
79+
fetched rows / total rows = 4/4
80+
+-----+---------+
81+
| age | min_val |
82+
|-----+---------|
83+
| 32 | 30 |
84+
| 36 | 30 |
85+
| 28 | 28 |
86+
| 33 | 30 |
87+
+-----+---------+
88+
89+
os> source=accounts | eval result = MIN(firstname, 'John') | fields firstname, result
90+
fetched rows / total rows = 4/4
91+
+-----------+--------+
92+
| firstname | result |
93+
|-----------+--------|
94+
| Amber | Amber |
95+
| Hattie | Hattie |
96+
| Nanette | John |
97+
| Dale | Dale |
98+
+-----------+--------+
99+
100+
os> source=accounts | eval result = MIN(age, 35, firstname) | fields age, firstname, result
101+
fetched rows / total rows = 4/4
102+
+-----+-----------+--------+
103+
| age | firstname | result |
104+
|-----+-----------+--------|
105+
| 32 | Amber | 32 |
106+
| 36 | Hattie | 35 |
107+
| 28 | Nanette | 28 |
108+
| 33 | Dale | 33 |
109+
+-----+-----------+--------+

‎integ-test/src/test/java/org/opensearch/sql/calcite/remote/CalciteExplainIT.java‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -843,6 +843,24 @@ public void testExplainSortOnMetricsNoBucketNullable() throws IOException {
843843
+ " gender, state | sort `count()`"));
844844
}
845845

846+
@Test
847+
public void testExplainEvalMax() throws IOException {
848+
String expected = loadExpectedPlan("explain_eval_max.json");
849+
assertJsonEqualsIgnoreId(
850+
expected,
851+
explainQueryToString(
852+
"source=opensearch-sql_test_index_account | eval new = max(1, 2, 3, age, 'banana')"));
853+
}
854+
855+
@Test
856+
public void testExplainEvalMin() throws IOException {
857+
String expected = loadExpectedPlan("explain_eval_min.json");
858+
assertJsonEqualsIgnoreId(
859+
expected,
860+
explainQueryToString(
861+
"source=opensearch-sql_test_index_account | eval new = min(1, 2, 3, age, 'banana')"));
862+
}
863+
846864
/**
847865
* Executes the PPL query and returns the result as a string with windows-style line breaks
848866
* replaced with Unix-style ones.

0 commit comments

Comments
 (0)