Skip to content

Commit 4a1eba5

Browse files
asifabasharasifabashar
authored
Feature tonumber : issue #4514 tonumber function as part of roadmap #4287
Signed-off-by: Asif Bashar <asif.bashar@gmail.com>
1 parent 71813bf commit 4a1eba5

11 files changed

Lines changed: 686 additions & 2 deletions

File tree

‎core/src/main/java/org/opensearch/sql/calcite/utils/PPLOperandTypes.java‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,13 @@ private PPLOperandTypes() {}
104104
UDFOperandMetadata.wrap(
105105
OperandTypes.family(
106106
SqlTypeFamily.CHARACTER, SqlTypeFamily.CHARACTER, SqlTypeFamily.INTEGER));
107+
108+
public static final UDFOperandMetadata STRING_OR_STRING_INTEGER =
109+
UDFOperandMetadata.wrap(
110+
(CompositeOperandTypeChecker)
111+
OperandTypes.family(SqlTypeFamily.CHARACTER)
112+
.or(OperandTypes.family(SqlTypeFamily.CHARACTER, SqlTypeFamily.INTEGER)));
113+
107114
public static final UDFOperandMetadata STRING_STRING_INTEGER_INTEGER =
108115
UDFOperandMetadata.wrap(
109116
OperandTypes.family(

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -160,6 +160,7 @@ public enum BuiltinFunctionName {
160160

161161
/** Text Functions. */
162162
TOSTRING(FunctionName.of("tostring")),
163+
TONUMBER(FunctionName.of("tonumber")),
163164

164165
/** IP Functions. */
165166
CIDRMATCH(FunctionName.of("cidrmatch")),

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -67,6 +67,7 @@
6767
import org.opensearch.sql.expression.function.udf.RexExtractMultiFunction;
6868
import org.opensearch.sql.expression.function.udf.RexOffsetFunction;
6969
import org.opensearch.sql.expression.function.udf.SpanFunction;
70+
import org.opensearch.sql.expression.function.udf.ToNumberFunction;
7071
import org.opensearch.sql.expression.function.udf.ToStringFunction;
7172
import org.opensearch.sql.expression.function.udf.condition.EarliestFunction;
7273
import org.opensearch.sql.expression.function.udf.condition.EnhancedCoalesceFunction;
@@ -412,6 +413,7 @@ public class PPLBuiltinOperators extends ReflectiveSqlOperatorTable {
412413
RELEVANCE_QUERY_FUNCTION_INSTANCE.toUDF("multi_match", false);
413414
public static final SqlOperator NUMBER_TO_STRING =
414415
new NumberToStringFunction().toUDF("NUMBER_TO_STRING");
416+
public static final SqlOperator TONUMBER = new ToNumberFunction().toUDF("TONUMBER");
415417
public static final SqlOperator TOSTRING = new ToStringFunction().toUDF("TOSTRING");
416418
public static final SqlOperator WIDTH_BUCKET =
417419
new org.opensearch.sql.expression.function.udf.binning.WidthBucketFunction()

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -218,6 +218,7 @@
218218
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TIMESTAMPDIFF;
219219
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TIME_FORMAT;
220220
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TIME_TO_SEC;
221+
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TONUMBER;
221222
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TOSTRING;
222223
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TO_DAYS;
223224
import static org.opensearch.sql.expression.function.BuiltinFunctionName.TO_SECONDS;
@@ -974,6 +975,7 @@ void populate() {
974975
registerOperator(WEEKOFYEAR, PPLBuiltinOperators.WEEK);
975976

976977
registerOperator(INTERNAL_PATTERN_PARSER, PPLBuiltinOperators.PATTERN_PARSER);
978+
registerOperator(TONUMBER, PPLBuiltinOperators.TONUMBER);
977979
registerOperator(TOSTRING, PPLBuiltinOperators.TOSTRING);
978980
register(
979981
TOSTRING,
@@ -1191,7 +1193,6 @@ void populate() {
11911193
SqlTypeFamily.INTEGER,
11921194
SqlTypeFamily.INTEGER)),
11931195
false));
1194-
11951196
register(
11961197
LOG,
11971198
(FunctionImp2)

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

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.expression.function.udf;
7+
8+
import java.math.BigInteger;
9+
import java.util.List;
10+
import org.apache.calcite.adapter.enumerable.NotNullImplementor;
11+
import org.apache.calcite.adapter.enumerable.NullPolicy;
12+
import org.apache.calcite.adapter.enumerable.RexToLixTranslator;
13+
import org.apache.calcite.linq4j.function.Strict;
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.ReturnTypes;
18+
import org.apache.calcite.sql.type.SqlReturnTypeInference;
19+
import org.opensearch.sql.calcite.utils.PPLOperandTypes;
20+
import org.opensearch.sql.expression.function.ImplementorUDF;
21+
import org.opensearch.sql.expression.function.UDFOperandMetadata;
22+
23+
/**
24+
* The following usage options are available, depending on the parameter types and the number of
25+
* parameters.
26+
*
27+
* <p><b>Usage:</b> {@code tonumber(string, [base])} converts the value in the first argument. The
28+
* second argument describes the base of the first argument. If the second argument is not provided,
29+
* the value is converted using base 10.
30+
*
31+
* <p><b>Return type:</b> Number
32+
*
33+
* <p>You can use this function with the eval commands and as part of eval expressions.
34+
*
35+
* <p>Base values can range from 2 to 36. The maximum value supported for base 10 is {@code +(2 −
36+
* 2^-52) · 2^1023} and the minimum is {@code −(2 − 2^-52) · 2^1023}.
37+
*
38+
* <p>The maximum for other supported bases is {@code 2^63 − 1} (or {@code 7FFFFFFFFFFFFFFF}) and
39+
* the minimum is {@code -2^63} (or {@code -7FFFFFFFFFFFFFFF}).
40+
*/
41+
public class ToNumberFunction extends ImplementorUDF {
42+
public ToNumberFunction() {
43+
super(
44+
new org.opensearch.sql.expression.function.udf.ToNumberFunction.ToNumberImplementor(),
45+
NullPolicy.ANY);
46+
}
47+
48+
@Override
49+
public SqlReturnTypeInference getReturnTypeInference() {
50+
51+
return ReturnTypes.DOUBLE_FORCE_NULLABLE;
52+
}
53+
54+
@Override
55+
public UDFOperandMetadata getOperandMetadata() {
56+
return PPLOperandTypes.STRING_OR_STRING_INTEGER;
57+
}
58+
59+
public static class ToNumberImplementor implements NotNullImplementor {
60+
61+
@Override
62+
public Expression implement(
63+
RexToLixTranslator translator, RexCall call, List<Expression> translatedOperands) {
64+
Expression fieldValue = translatedOperands.get(0);
65+
int base = 10;
66+
if (translatedOperands.size() > 1) {
67+
Expression baseExpr = translatedOperands.get(1);
68+
return Expressions.call(ToNumberFunction.class, "toNumber", fieldValue, baseExpr);
69+
} else {
70+
return Expressions.call(ToNumberFunction.class, "toNumber", fieldValue);
71+
}
72+
}
73+
}
74+
75+
@Strict
76+
public static Number toNumber(String numStr) {
77+
return toNumber(numStr, 10);
78+
}
79+
80+
@Strict
81+
public static Number toNumber(String numStr, int base) {
82+
if (base < 2 || base > 36) {
83+
throw new IllegalArgumentException("Base has to be between 2 and 36.");
84+
}
85+
Number result = null;
86+
try {
87+
if (base == 10) {
88+
if (numStr.contains(".")) {
89+
result = Double.parseDouble(numStr);
90+
} else {
91+
result = Long.parseLong(numStr);
92+
}
93+
} else {
94+
BigInteger bigInteger = new BigInteger(numStr, base);
95+
result = bigInteger.longValue();
96+
}
97+
} catch (Exception e) {
98+
99+
}
100+
return result;
101+
}
102+
}

‎core/src/test/java/org/opensearch/sql/expression/function/udf/ToNumberFunctionTest.java‎

Lines changed: 176 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,176 @@
1+
/*
2+
* Copyright OpenSearch Contributors
3+
* SPDX-License-Identifier: Apache-2.0
4+
*/
5+
6+
package org.opensearch.sql.expression.function.udf;
7+
8+
import static org.junit.jupiter.api.Assertions.*;
9+
10+
import org.apache.calcite.sql.type.ReturnTypes;
11+
import org.junit.jupiter.api.Test;
12+
import org.opensearch.sql.calcite.utils.PPLOperandTypes;
13+
14+
public class ToNumberFunctionTest {
15+
16+
private final ToNumberFunction function = new ToNumberFunction();
17+
18+
@Test
19+
void testGetReturnTypeInference() {
20+
assertEquals(ReturnTypes.DOUBLE_FORCE_NULLABLE, function.getReturnTypeInference());
21+
}
22+
23+
@Test
24+
void testGetOperandMetadata() {
25+
assertEquals(PPLOperandTypes.STRING_OR_STRING_INTEGER, function.getOperandMetadata());
26+
}
27+
28+
@Test
29+
void testToNumberWithDefaultBase() {
30+
assertEquals(123L, ToNumberFunction.toNumber("123"));
31+
assertEquals(0L, ToNumberFunction.toNumber("0"));
32+
assertEquals(-456L, ToNumberFunction.toNumber("-456"));
33+
assertEquals(123.45, ToNumberFunction.toNumber("123.45"));
34+
assertEquals(-123.45, ToNumberFunction.toNumber("-123.45"));
35+
assertEquals(0.5, ToNumberFunction.toNumber("0.5"));
36+
assertEquals(-0.5, ToNumberFunction.toNumber("-0.5"));
37+
}
38+
39+
@Test
40+
void testToNumberWithBase10() {
41+
assertEquals(123L, ToNumberFunction.toNumber("123", 10));
42+
assertEquals(0L, ToNumberFunction.toNumber("0", 10));
43+
assertEquals(-456L, ToNumberFunction.toNumber("-456", 10));
44+
assertEquals(123.45, ToNumberFunction.toNumber("123.45", 10));
45+
assertEquals(-123.45, ToNumberFunction.toNumber("-123.45", 10));
46+
}
47+
48+
@Test
49+
void testToNumberWithBase2() {
50+
assertEquals(5L, ToNumberFunction.toNumber("101", 2));
51+
assertEquals(0L, ToNumberFunction.toNumber("0", 2));
52+
assertEquals(1L, ToNumberFunction.toNumber("1", 2));
53+
assertEquals(7L, ToNumberFunction.toNumber("111", 2));
54+
assertEquals(10L, ToNumberFunction.toNumber("1010", 2));
55+
}
56+
57+
@Test
58+
void testToNumberWithBase8() {
59+
assertEquals(64L, ToNumberFunction.toNumber("100", 8));
60+
assertEquals(8L, ToNumberFunction.toNumber("10", 8));
61+
assertEquals(83L, ToNumberFunction.toNumber("123", 8));
62+
assertEquals(511L, ToNumberFunction.toNumber("777", 8));
63+
}
64+
65+
@Test
66+
void testToNumberWithBase16() {
67+
assertEquals(255L, ToNumberFunction.toNumber("FF", 16));
68+
assertEquals(16L, ToNumberFunction.toNumber("10", 16));
69+
assertEquals(171L, ToNumberFunction.toNumber("AB", 16));
70+
assertEquals(291L, ToNumberFunction.toNumber("123", 16));
71+
assertEquals(4095L, ToNumberFunction.toNumber("FFF", 16));
72+
}
73+
74+
@Test
75+
void testToNumberWithBase36() {
76+
assertEquals(35L, ToNumberFunction.toNumber("Z", 36));
77+
assertEquals(1295L, ToNumberFunction.toNumber("ZZ", 36));
78+
assertEquals(46655L, ToNumberFunction.toNumber("ZZZ", 36));
79+
}
80+
81+
@Test
82+
void testToNumberWithDecimalBase2() {
83+
assertEquals(2L, ToNumberFunction.toNumber("10", 2));
84+
assertEquals(1L, ToNumberFunction.toNumber("1", 2));
85+
assertEquals(3L, ToNumberFunction.toNumber("11", 2));
86+
}
87+
88+
@Test
89+
void testToNumberWithDecimalBase16() {
90+
assertEquals(255L, ToNumberFunction.toNumber("FF", 16));
91+
assertEquals(16L, ToNumberFunction.toNumber("10", 16));
92+
assertEquals(171L, ToNumberFunction.toNumber("AB", 16));
93+
}
94+
95+
@Test
96+
void testToNumberWithNegativeDecimal() {
97+
assertEquals(-2L, ToNumberFunction.toNumber("-10", 2));
98+
assertEquals(-255L, ToNumberFunction.toNumber("-FF", 16));
99+
assertEquals(-123.45, ToNumberFunction.toNumber("-123.45", 10));
100+
}
101+
102+
@Test
103+
void testToNumberWithEmptyFractionalPart() {
104+
assertEquals(123.0, ToNumberFunction.toNumber("123.", 10));
105+
assertEquals(255L, ToNumberFunction.toNumber("FF", 16));
106+
assertEquals(5L, ToNumberFunction.toNumber("101", 2));
107+
}
108+
109+
@Test
110+
void testToNumberWithZeroIntegerPart() {
111+
assertEquals(0.5, ToNumberFunction.toNumber("0.5", 10));
112+
assertEquals(0L, ToNumberFunction.toNumber("0", 2));
113+
}
114+
115+
@Test
116+
void testToNumberInvalidBase() {
117+
assertThrows(
118+
IllegalArgumentException.class,
119+
() -> {
120+
ToNumberFunction.toNumber("123", 1);
121+
});
122+
123+
assertThrows(
124+
IllegalArgumentException.class,
125+
() -> {
126+
ToNumberFunction.toNumber("123", 37);
127+
});
128+
129+
assertThrows(
130+
IllegalArgumentException.class,
131+
() -> {
132+
ToNumberFunction.toNumber("123", 0);
133+
});
134+
135+
assertThrows(
136+
IllegalArgumentException.class,
137+
() -> {
138+
ToNumberFunction.toNumber("123", -1);
139+
});
140+
}
141+
142+
@Test
143+
void testToNumberInvalidDigits() {
144+
assertEquals(null, ToNumberFunction.toNumber("12A", 10));
145+
assertEquals(null, ToNumberFunction.toNumber("102", 2));
146+
assertEquals(null, ToNumberFunction.toNumber("101.101", 2));
147+
assertEquals(null, ToNumberFunction.toNumber("189", 8));
148+
assertEquals(null, ToNumberFunction.toNumber("GHI", 16));
149+
assertEquals(null, ToNumberFunction.toNumber("FF.8", 16));
150+
}
151+
152+
@Test
153+
void testToNumberEdgeCases() {
154+
assertEquals(0L, ToNumberFunction.toNumber("0", 2));
155+
assertEquals(0L, ToNumberFunction.toNumber("0", 36));
156+
assertEquals(0.0, ToNumberFunction.toNumber("0.0", 10));
157+
assertEquals(0.0, ToNumberFunction.toNumber("0.000", 10));
158+
}
159+
160+
@Test
161+
void testToNumberLargeNumbers() {
162+
assertEquals(
163+
(long) Integer.MAX_VALUE, ToNumberFunction.toNumber(String.valueOf(Integer.MAX_VALUE), 10));
164+
assertEquals(
165+
(long) Integer.MIN_VALUE, ToNumberFunction.toNumber(String.valueOf(Integer.MIN_VALUE), 10));
166+
}
167+
168+
@Test
169+
void testToNumberCaseInsensitivity() {
170+
assertEquals(255L, ToNumberFunction.toNumber("ff", 16));
171+
assertEquals(255L, ToNumberFunction.toNumber("FF", 16));
172+
assertEquals(255L, ToNumberFunction.toNumber("fF", 16));
173+
assertEquals(171L, ToNumberFunction.toNumber("ab", 16));
174+
assertEquals(171L, ToNumberFunction.toNumber("AB", 16));
175+
}
176+
}

0 commit comments

Comments
 (0)