This is an automated email from the ASF dual-hosted git repository.
alexpl pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new 67e28313e9a IGNITE-23516 SQL Calcite: Fix rounding of numerics
(unification for DECIMAL and other floating point rounding) - Fixes #11644.
67e28313e9a is described below
commit 67e28313e9ad01033d747e79d0bd9e121b22ad49
Author: Vladimir Steshin <[email protected]>
AuthorDate: Fri Nov 8 10:57:03 2024 +0300
IGNITE-23516 SQL Calcite: Fix rounding of numerics (unification for DECIMAL
and other floating point rounding) - Fixes #11644.
Signed-off-by: Aleksey Plekhanov <[email protected]>
---
.../query/calcite/exec/exp/IgniteSqlFunctions.java | 25 +--
.../processors/query/calcite/util/IgniteMath.java | 123 ++++++++++---
.../query/calcite/integration/DataTypesTest.java | 203 +++++++++++++++++----
.../{test_floor_ceil.test => test_round_ceil.test} | 32 ++--
.../src/test/sql/function/numeric/test_trigo.test | 144 +++++++--------
5 files changed, 354 insertions(+), 173 deletions(-)
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
index d4157bf0972..63b27f3a8ac 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java
@@ -18,7 +18,6 @@ package
org.apache.ignite.internal.processors.query.calcite.exec.exp;
import java.math.BigDecimal;
import java.math.BigInteger;
-import java.math.RoundingMode;
import org.apache.calcite.DataContext;
import org.apache.calcite.avatica.util.ByteString;
import org.apache.calcite.config.CalciteConnectionConfig;
@@ -38,6 +37,9 @@ import
org.apache.ignite.internal.processors.query.calcite.type.IgniteTypeSystem
import org.apache.ignite.internal.processors.query.calcite.util.Commons;
import org.checkerframework.checker.nullness.qual.Nullable;
+import static
org.apache.ignite.internal.processors.query.calcite.util.IgniteMath.NUMERIC_ROUNDING_MODE;
+import static
org.apache.ignite.internal.processors.query.calcite.util.IgniteMath.convertToBigDecimal;
+
/**
* Ignite SQL functions.
*/
@@ -48,9 +50,6 @@ public class IgniteSqlFunctions {
/** */
private static final int DFLT_NUM_PRECISION =
IgniteTypeSystem.INSTANCE.getDefaultPrecision(SqlTypeName.DECIMAL);
- /** */
- private static final RoundingMode NUMERIC_ROUNDING_MODE =
RoundingMode.HALF_UP;
-
/**
* Default constructor.
*/
@@ -145,24 +144,6 @@ public class IgniteSqlFunctions {
return dec.setScale(scale, NUMERIC_ROUNDING_MODE);
}
- /** */
- private static BigDecimal convertToBigDecimal(Number value) {
- BigDecimal dec;
-
- if (value instanceof Float)
- dec = BigDecimal.valueOf(value.floatValue());
- else if (value instanceof Double)
- dec = BigDecimal.valueOf(value.doubleValue());
- else if (value instanceof BigDecimal)
- dec = (BigDecimal)value;
- else if (value instanceof BigInteger)
- dec = new BigDecimal((BigInteger)value);
- else
- dec = new BigDecimal(value.longValue());
-
- return dec;
- }
-
/** Cast object depending on type to DECIMAL. */
public static BigDecimal toBigDecimal(Object o, int precision, int scale) {
if (o == null)
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMath.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMath.java
index f5350d1c18c..020b747a45a 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMath.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/util/IgniteMath.java
@@ -18,6 +18,8 @@
package org.apache.ignite.internal.processors.query.calcite.util;
import java.math.BigDecimal;
+import java.math.BigInteger;
+import java.math.RoundingMode;
import org.apache.calcite.sql.type.SqlTypeName;
import static org.apache.calcite.sql.type.SqlTypeName.BIGINT;
@@ -45,6 +47,33 @@ public class IgniteMath {
/** */
private static final Float LOWER_LONG_FLOAT = (float)Long.MIN_VALUE;
+ /** */
+ private static final double LONG_MAX_EXT = Long.MAX_VALUE + 1.d;
+
+ /** */
+ private static final double LONG_MIN_EXT = Long.MIN_VALUE - 1d;
+
+ /** */
+ private static final double INT_MAX_EXT = Integer.MAX_VALUE + 1.d;
+
+ /** */
+ private static final double INT_MIN_EXT = Integer.MIN_VALUE - 1d;
+
+ /** */
+ private static final double SHORT_MAX_EXT = Short.MAX_VALUE + 1.d;
+
+ /** */
+ private static final double SHORT_MIN_EXT = Short.MIN_VALUE - 1d;
+
+ /** */
+ private static final double BYTE_MAX_EXT = Byte.MAX_VALUE + 1.d;
+
+ /** */
+ private static final double BYTE_MIN_EXT = Byte.MIN_VALUE - 1d;
+
+ /** */
+ public static final RoundingMode NUMERIC_ROUNDING_MODE =
RoundingMode.HALF_UP;
+
/** Returns the sum of its arguments, throwing an exception if the result
overflows an {@code long}. */
public static long addExact(long x, long y) {
long r = x + y;
@@ -236,28 +265,10 @@ public class IgniteMath {
return (byte)(x / y);
}
- /** */
- private static void checkNumberLongBounds(SqlTypeName type, Number x) {
- if (x instanceof BigDecimal) {
- if ((((BigDecimal)x).compareTo(UPPER_LONG_BIG_DECIMAL) < 0 &&
((BigDecimal)x).compareTo(LOWER_LONG_BIG_DECIMAL) > 0))
- return;
- }
- else if (x instanceof Double) {
- if ((((Double)x).compareTo(UPPER_LONG_DOUBLE) <= 0 &&
((Double)x).compareTo(LOWER_LONG_DOUBLE) >= 0))
- return;
- }
- else if (x instanceof Float) {
- if ((((Float)x).compareTo(UPPER_LONG_FLOAT) <= 0 &&
((Float)x).compareTo(LOWER_LONG_FLOAT) >= 0))
- return;
- }
- else
- return;
-
- throw new ArithmeticException(type.getName() + " overflow");
- }
-
/** Cast value to {@code long}, throwing an exception if the result
overflows an {@code long}. */
public static long convertToLongExact(Number x) {
+ x = round(x);
+
checkNumberLongBounds(BIGINT, x);
return x.longValue();
@@ -265,8 +276,10 @@ public class IgniteMath {
/** Cast value to {@code long}, throwing an exception if the result
overflows an {@code long}. */
public static long convertToLongExact(double x) {
- if (x > Long.MAX_VALUE || x < Long.MIN_VALUE)
- throw new ArithmeticException(BIGINT.getName() + " overflow");
+ x = extendToRound(x);
+
+ if (x <= LONG_MIN_EXT || x >= LONG_MAX_EXT)
+ throw new ArithmeticException(INTEGER.getName() + " overflow");
return (long)x;
}
@@ -283,7 +296,9 @@ public class IgniteMath {
/** Cast value to {@code int}, throwing an exception if the result
overflows an {@code int}. */
public static int convertToIntExact(double x) {
- if (x > Integer.MAX_VALUE || x < Integer.MIN_VALUE)
+ x = extendToRound(x);
+
+ if (x <= INT_MIN_EXT || x >= INT_MAX_EXT)
throw new ArithmeticException(INTEGER.getName() + " overflow");
return (int)x;
@@ -291,6 +306,8 @@ public class IgniteMath {
/** Cast value to {@code int}, throwing an exception if the result
overflows an {@code int}. */
public static int convertToIntExact(Number x) {
+ x = round(x);
+
checkNumberLongBounds(INTEGER, x);
return convertToIntExact(x.longValue());
@@ -308,7 +325,9 @@ public class IgniteMath {
/** Cast value to {@code short}, throwing an exception if the result
overflows an {@code short}. */
public static short convertToShortExact(double x) {
- if (x > Short.MAX_VALUE || x < Short.MIN_VALUE)
+ x = extendToRound(x);
+
+ if (x <= SHORT_MIN_EXT || x >= SHORT_MAX_EXT)
throw new ArithmeticException(SMALLINT.getName() + " overflow");
return (short)x;
@@ -316,6 +335,8 @@ public class IgniteMath {
/** Cast value to {@code short}, throwing an exception if the result
overflows an {@code short}. */
public static short convertToShortExact(Number x) {
+ x = round(x);
+
checkNumberLongBounds(SMALLINT, x);
return convertToShortExact(x.longValue());
@@ -333,7 +354,9 @@ public class IgniteMath {
/** Cast value to {@code byte}, throwing an exception if the result
overflows an {@code byte}. */
public static byte convertToByteExact(double x) {
- if (x > Byte.MAX_VALUE || x < Byte.MIN_VALUE)
+ x = extendToRound(x);
+
+ if (x <= BYTE_MIN_EXT || x >= BYTE_MAX_EXT)
throw new ArithmeticException(TINYINT.getName() + " overflow");
return (byte)x;
@@ -341,8 +364,58 @@ public class IgniteMath {
/** Cast value to {@code byte}, throwing an exception if the result
overflows an {@code byte}. */
public static byte convertToByteExact(Number x) {
+ x = round(x);
+
checkNumberLongBounds(TINYINT, x);
return convertToByteExact(x.longValue());
}
+
+ /** */
+ public static BigDecimal convertToBigDecimal(Number val) {
+ BigDecimal dec;
+
+ if (val instanceof Float)
+ dec = BigDecimal.valueOf(val.floatValue());
+ else if (val instanceof Double)
+ dec = BigDecimal.valueOf(val.doubleValue());
+ else if (val instanceof BigDecimal)
+ dec = (BigDecimal)val;
+ else if (val instanceof BigInteger)
+ dec = new BigDecimal((BigInteger)val);
+ else
+ dec = BigDecimal.valueOf(val.longValue());
+
+ return dec;
+ }
+
+ /** */
+ private static void checkNumberLongBounds(SqlTypeName type, Number x) {
+ if (x instanceof BigDecimal) {
+ if ((((BigDecimal)x).compareTo(UPPER_LONG_BIG_DECIMAL) < 0 &&
((BigDecimal)x).compareTo(LOWER_LONG_BIG_DECIMAL) > 0))
+ return;
+ }
+ else if (x instanceof Double) {
+ if ((((Double)x).compareTo(UPPER_LONG_DOUBLE) <= 0 &&
((Double)x).compareTo(LOWER_LONG_DOUBLE) >= 0))
+ return;
+ }
+ else if (x instanceof Float) {
+ if ((((Float)x).compareTo(UPPER_LONG_FLOAT) <= 0 &&
((Float)x).compareTo(LOWER_LONG_FLOAT) >= 0))
+ return;
+ }
+ else
+ return;
+
+ throw new ArithmeticException(type.getName() + " overflow");
+ }
+
+ /** */
+ private static double extendToRound(double x) {
+ return x < 0.0d ? x - 0.5d : x + 0.5d;
+ }
+
+ /** */
+ private static BigDecimal round(Number x) {
+ return convertToBigDecimal(x).setScale(0, NUMERIC_ROUNDING_MODE);
+ }
}
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java
index a577fa9f96e..1c8e95e852d 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java
@@ -18,6 +18,7 @@
package org.apache.ignite.internal.processors.query.calcite.integration;
import java.math.BigDecimal;
+import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.UUID;
@@ -43,6 +44,99 @@ import static org.junit.Assume.assumeTrue;
* Test SQL data types.
*/
public class DataTypesTest extends AbstractBasicIntegrationTransactionalTest {
+ /** */
+ @Test
+ public void testRoundingOfNumerics() {
+ doTestCoercionOfNumerics(numericsToRound(), false, false);
+ }
+
+ /** */
+ @Test
+ public void testRoundingOfNumericsPrecasted() {
+ doTestCoercionOfNumerics(numericsToRound(), false, true);
+ }
+
+ /** */
+ @Test
+ public void testRoundingOfDynamicNumerics() {
+ doTestCoercionOfNumerics(numericsToRound(), true, false);
+ }
+
+ /** */
+ @Test
+ public void testRoundingOfDynamicNumericsPrecasted() {
+ doTestCoercionOfNumerics(numericsToRound(), true, true);
+ }
+
+ /** @return input type, input value, target type, expected result. */
+ private static List<List<Object>> numericsToRound() {
+ List<List<Object>> lst = new ArrayList<>(50);
+
+ Exception overflowErr = new ArithmeticException("overflow");
+
+ lst.add(F.asList("DECIMAL(5,4)", BigDecimal.valueOf(1.4999d),
"DECIMAL(1)", new BigDecimal(1)));
+ lst.add(F.asList("DECIMAL(5,4)", BigDecimal.valueOf(-1.4999d),
"DECIMAL(1)", new BigDecimal(-1)));
+ lst.add(F.asList("DECIMAL(2,1)", BigDecimal.valueOf(1.5d),
"DECIMAL(1)", new BigDecimal(2)));
+ lst.add(F.asList("DECIMAL(2,1)", BigDecimal.valueOf(-1.5d),
"DECIMAL(1)", new BigDecimal(-2)));
+ lst.add(F.asList("DECIMAL(5,4)", BigDecimal.valueOf(1.4999d),
"DECIMAL(2,1)", BigDecimal.valueOf(1.5)));
+ lst.add(F.asList("DECIMAL(5,4)", BigDecimal.valueOf(-1.4999d),
"DECIMAL(2,1)", BigDecimal.valueOf(-1.5)));
+
+ lst.add(F.asList("DECIMAL", new BigDecimal("-9223372036854775808.4"),
"BIGINT", -9223372036854775808L));
+ lst.add(F.asList("DECIMAL", new BigDecimal("-9223372036854775808.5"),
"BIGINT", overflowErr));
+ lst.add(F.asList("DECIMAL", new BigDecimal("9223372036854775807.4"),
"BIGINT", 9223372036854775807L));
+ lst.add(F.asList("DECIMAL", new BigDecimal("9223372036854775807.5"),
"BIGINT", overflowErr));
+
+ lst.add(F.asList("DOUBLE", -2147483648.4d, "INT", -2147483648));
+ lst.add(F.asList("DOUBLE", -2147483648.5d, "INT", overflowErr));
+ lst.add(F.asList("DOUBLE", 2147483647.4d, "INT", 2147483647));
+ lst.add(F.asList("DOUBLE", 2147483647.5d, "INT", overflowErr));
+
+ for (String numTypeName : F.asList("DOUBLE", "FLOAT")) {
+ lst.add(F.asList(numTypeName, floatingVal(1.4999f, numTypeName),
"DECIMAL(1)", new BigDecimal(1)));
+ lst.add(F.asList(numTypeName, floatingVal(-1.4999f, numTypeName),
"DECIMAL(1)", new BigDecimal(-1)));
+ lst.add(F.asList(numTypeName, floatingVal(1.5f, numTypeName),
"DECIMAL(1)", new BigDecimal(2)));
+ lst.add(F.asList(numTypeName, floatingVal(-1.5f, numTypeName),
"DECIMAL(1)", new BigDecimal(-2)));
+
+ lst.add(F.asList(numTypeName, floatingVal(1.4999f, numTypeName),
"BIGINT", 1L));
+ lst.add(F.asList(numTypeName, floatingVal(-1.4999f, numTypeName),
"BIGINT", -1L));
+ lst.add(F.asList(numTypeName, floatingVal(1.5f, numTypeName),
"BIGINT", 2L));
+ lst.add(F.asList(numTypeName, floatingVal(-1.5f, numTypeName),
"BIGINT", -2L));
+
+ lst.add(F.asList(numTypeName, floatingVal(1.4999f, numTypeName),
"INT", 1));
+ lst.add(F.asList(numTypeName, floatingVal(-1.4999f, numTypeName),
"INT", -1));
+ lst.add(F.asList(numTypeName, floatingVal(1.5f, numTypeName),
"INT", 2));
+ lst.add(F.asList(numTypeName, floatingVal(-1.5f, numTypeName),
"INT", -2));
+
+ lst.add(F.asList(numTypeName, floatingVal(1.4999f, numTypeName),
"SMALLINT", (short)1));
+ lst.add(F.asList(numTypeName, floatingVal(-1.4999f, numTypeName),
"SMALLINT", (short)-1));
+ lst.add(F.asList(numTypeName, floatingVal(1.5f, numTypeName),
"SMALLINT", (short)2));
+ lst.add(F.asList(numTypeName, floatingVal(-1.5f, numTypeName),
"SMALLINT", (short)-2));
+ lst.add(F.asList(numTypeName, floatingVal(32767.4f, numTypeName),
"SMALLINT", (short)32767));
+ lst.add(F.asList(numTypeName, floatingVal(32767.5f, numTypeName),
"SMALLINT", overflowErr));
+ lst.add(F.asList(numTypeName, floatingVal(-32768.4f, numTypeName),
"SMALLINT", (short)-32768));
+ lst.add(F.asList(numTypeName, floatingVal(-32768.5f, numTypeName),
"SMALLINT", overflowErr));
+
+ lst.add(F.asList(numTypeName, floatingVal(1.4999f, numTypeName),
"TINYINT", (byte)1));
+ lst.add(F.asList(numTypeName, floatingVal(-1.4999f, numTypeName),
"TINYINT", (byte)-1));
+ lst.add(F.asList(numTypeName, floatingVal(1.5f, numTypeName),
"TINYINT", (byte)2));
+ lst.add(F.asList(numTypeName, floatingVal(-1.5f, numTypeName),
"TINYINT", (byte)-2));
+ lst.add(F.asList(numTypeName, floatingVal(127.4f, numTypeName),
"TINYINT", (byte)127));
+ lst.add(F.asList(numTypeName, floatingVal(127.5f, numTypeName),
"TINYINT", overflowErr));
+ lst.add(F.asList(numTypeName, floatingVal(-128.4f, numTypeName),
"TINYINT", (byte)-128));
+ lst.add(F.asList(numTypeName, floatingVal(-128.5f, numTypeName),
"TINYINT", overflowErr));
+ }
+
+ return lst;
+ }
+
+ /** */
+ private static Number floatingVal(float v, String typeName) {
+ if ("DOUBLE".equalsIgnoreCase(typeName))
+ return (double)v;
+
+ return v;
+ }
+
/** Tests Other type. */
@Test
public void testOtherType() {
@@ -619,38 +713,32 @@ public class DataTypesTest extends
AbstractBasicIntegrationTransactionalTest {
/** */
@Test
public void testCoercionOfNumericLiterals() {
- assumeNoTransactions();
-
- doTestCoercionOfNumerics(false, false);
+ doTestCoercionOfNumerics(numericsToCast(), false, false);
}
/** */
@Test
public void testCoercionOfNumericLiteralsPrecasted() {
- assumeNoTransactions();
-
- doTestCoercionOfNumerics(false, true);
+ doTestCoercionOfNumerics(numericsToCast(), false, true);
}
/** */
@Test
public void testCoercionOfNumericDynamicParameters() {
- assumeNoTransactions();
-
- doTestCoercionOfNumerics(true, false);
+ doTestCoercionOfNumerics(numericsToCast(), true, false);
}
/** */
@Test
public void testCoercionOfNumericDynamicParametersPrecasted() {
- assumeNoTransactions();
-
- doTestCoercionOfNumerics(true, true);
+ doTestCoercionOfNumerics(numericsToCast(), true, true);
}
/** */
- private void doTestCoercionOfNumerics(boolean dynamic, boolean precasted) {
- for (List<Object> params : numericsToCast()) {
+ private void doTestCoercionOfNumerics(List<List<Object>> testSuite,
boolean dynamic, boolean precasted) {
+ assumeNoTransactions();
+
+ for (List<Object> params : testSuite) {
assert params.size() == 4 : "Wrong params lenght: " +
params.size();
String inputType = params.get(0).toString();
@@ -686,7 +774,7 @@ public class DataTypesTest extends
AbstractBasicIntegrationTransactionalTest {
/** */
private static String asLiteral(Object val, String type) {
- return type.equalsIgnoreCase("VARCHAR") ? String.format("'%s'", val) :
String.valueOf(val);
+ return "VARCHAR".equalsIgnoreCase(type) ? String.format("'%s'", val) :
String.valueOf(val);
}
/** @return input type, input value, target type, expected result. */
@@ -863,80 +951,119 @@ public class DataTypesTest extends
AbstractBasicIntegrationTransactionalTest {
// BIGINT
assertQuery("SELECT CAST(9223372036854775807.1 AS
BIGINT)").returns(9223372036854775807L).check();
- assertQuery("SELECT CAST(9223372036854775807.9 AS
BIGINT)").returns(9223372036854775807L).check();
- assertQuery("SELECT CAST(9223372036854775808.9 - 1 AS
BIGINT)").returns(9223372036854775807L).check();
+ assertQuery("SELECT CAST(9223372036854775807.4 AS
BIGINT)").returns(9223372036854775807L).check();
+ assertQuery("SELECT CAST(9223372036854775806.9 AS
BIGINT)").returns(9223372036854775807L).check();
+ assertThrows("SELECT CAST(9223372036854775807.5 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
+ assertThrows("SELECT CAST(9223372036854775807.9 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
+ assertQuery("SELECT CAST(9223372036854775807.9 - 1 AS
BIGINT)").returns(9223372036854775807L).check();
assertThrows("SELECT CAST(9223372036854775808 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST(9223372036854775808.1 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST(-9223372036854775809 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST(-9223372036854775809.1 AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertQuery("SELECT CAST(-9223372036854775808.1 AS
BIGINT)").returns(-9223372036854775808L).check();
- assertQuery("SELECT CAST(-9223372036854775808.9 AS
BIGINT)").returns(-9223372036854775808L).check();
- assertQuery("SELECT CAST(-9223372036854775809.9 + 1 AS
BIGINT)").returns(-9223372036854775808L).check();
+ assertQuery("SELECT CAST(-9223372036854775807.9 AS
BIGINT)").returns(-9223372036854775808L).check();
+ assertQuery("SELECT CAST(-9223372036854775808.4 AS
BIGINT)").returns(-9223372036854775808L).check();
+ assertQuery("SELECT CAST(-9223372036854775808.9 + 1 AS
BIGINT)").returns(-9223372036854775808L).check();
assertQuery("SELECT CAST('9223372036854775807.1' AS
BIGINT)").returns(9223372036854775807L).check();
- assertQuery("SELECT CAST('9223372036854775807.9' AS
BIGINT)").returns(9223372036854775807L).check();
+ assertQuery("SELECT CAST('9223372036854775807.4' AS
BIGINT)").returns(9223372036854775807L).check();
+ assertThrows("SELECT CAST('9223372036854775807.5' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
+ assertThrows("SELECT CAST('9223372036854775807.9' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST('9223372036854775808' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST('9223372036854775808.1' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST('-9223372036854775809' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertThrows("SELECT CAST('-9223372036854775809.1' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
assertQuery("SELECT CAST('-9223372036854775808.1' AS
BIGINT)").returns(-9223372036854775808L).check();
- assertQuery("SELECT CAST('-9223372036854775808.9' AS
BIGINT)").returns(-9223372036854775808L).check();
+ assertThrows("SELECT CAST('-9223372036854775808.9' AS BIGINT)",
IgniteSQLException.class, "BIGINT overflow");
// INTEGER
- assertQuery("SELECT CAST(2147483647.1 AS
INTEGER)").returns(2147483647).check();
- assertQuery("SELECT CAST(2147483647.9 AS
INTEGER)").returns(2147483647).check();
- assertQuery("SELECT CAST(2147483648.9 - 1 AS
INTEGER)").returns(2147483647).check();
+ assertQuery("SELECT CAST(2147483647.4 AS
INTEGER)").returns(2147483647).check();
+ assertThrows("SELECT CAST(2147483647.5 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST(2147483647.9 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST(2147483648.5 - 1 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST(2147483648.9 - 1 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertQuery("SELECT CAST(2147483648.4 - 1 AS
INTEGER)").returns(2147483647).check();
+ assertQuery("SELECT CAST(2147483647.9 - 1 AS
INTEGER)").returns(2147483647).check();
assertThrows("SELECT CAST(2147483648 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST(2147483648.1 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST(-2147483649 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST(-2147483649.1 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertQuery("SELECT CAST(-2147483648.1 AS
INTEGER)").returns(-2147483648).check();
- assertQuery("SELECT CAST(-2147483648.9 AS
INTEGER)").returns(-2147483648).check();
+ assertQuery("SELECT CAST(-2147483648.4 AS
INTEGER)").returns(-2147483648).check();
+ assertThrows("SELECT CAST(-2147483648.5 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST(-2147483648.9 AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertQuery("SELECT CAST('2147483647.1' AS
INTEGER)").returns(2147483647).check();
- assertQuery("SELECT CAST('2147483647.9' AS
INTEGER)").returns(2147483647).check();
+ assertQuery("SELECT CAST('2147483647.4' AS
INTEGER)").returns(2147483647).check();
+ assertThrows("SELECT CAST('2147483647.5' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST('2147483647.9' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST('2147483648' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST('2147483648.1' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST('2147483648.4' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST('2147483648.5' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertThrows("SELECT CAST('-2147483649' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
- assertThrows("SELECT CAST('-2147483649.1' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST('-2147483649.4' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
assertQuery("SELECT CAST('-2147483648.1' AS
INTEGER)").returns(-2147483648).check();
- assertQuery("SELECT CAST('-2147483648.9' AS
INTEGER)").returns(-2147483648).check();
+ assertQuery("SELECT CAST('-2147483648.4' AS
INTEGER)").returns(-2147483648).check();
+ assertThrows("SELECT CAST('-2147483648.5' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
+ assertThrows("SELECT CAST('-2147483648.9' AS INTEGER)",
IgniteSQLException.class, "INTEGER overflow");
// SMALLINT
assertQuery("SELECT CAST(32767.1 AS
SMALLINT)").returns((short)32767).check();
- assertQuery("SELECT CAST(32767.9 AS
SMALLINT)").returns((short)32767).check();
- assertQuery("SELECT CAST(32768.9 - 1 AS
SMALLINT)").returns((short)32767).check();
+ assertQuery("SELECT CAST(32767.4 AS
SMALLINT)").returns((short)32767).check();
+ assertThrows("SELECT CAST(32767.5 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
+ assertThrows("SELECT CAST(32767.9 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
+ assertQuery("SELECT CAST(32767.9 - 1 AS
SMALLINT)").returns((short)32767).check();
+ assertQuery("SELECT CAST(32768.4 - 1 AS
SMALLINT)").returns((short)32767).check();
+ assertThrows("SELECT CAST(32768.5 - 1 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
+ assertThrows("SELECT CAST(32768.9 - 1 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST(32768 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST(32768.1 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST(-32769 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST(-32769.1 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertQuery("SELECT CAST(-32768.1 AS
SMALLINT)").returns((short)-32768).check();
- assertQuery("SELECT CAST(-32768.9 AS
SMALLINT)").returns((short)-32768).check();
+ assertQuery("SELECT CAST(-32768.4 AS
SMALLINT)").returns((short)-32768).check();
+ assertThrows("SELECT CAST(-32768.5 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
+ assertThrows("SELECT CAST(-32768.9 AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertQuery("SELECT CAST('32767.1' AS
SMALLINT)").returns((short)32767).check();
- assertQuery("SELECT CAST('32767.9' AS
SMALLINT)").returns((short)32767).check();
+ assertQuery("SELECT CAST('32767.4' AS
SMALLINT)").returns((short)32767).check();
+ assertThrows("SELECT CAST('32767.9' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST('32768' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST('32768.1' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST('-32769' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertThrows("SELECT CAST('-32769.1' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
assertQuery("SELECT CAST('-32768.1' AS
SMALLINT)").returns((short)-32768).check();
- assertQuery("SELECT CAST('-32768.9' AS
SMALLINT)").returns((short)-32768).check();
+ assertQuery("SELECT CAST('-32768.4' AS
SMALLINT)").returns((short)-32768).check();
+ assertThrows("SELECT CAST('-32768.5' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
+ assertThrows("SELECT CAST('-32768.9' AS SMALLINT)",
IgniteSQLException.class, "SMALLINT overflow");
// TINYINT
assertQuery("SELECT CAST(127.1 AS
TINYINT)").returns((byte)127).check();
- assertQuery("SELECT CAST(127.9 AS
TINYINT)").returns((byte)127).check();
- assertQuery("SELECT CAST(128.9 - 1 AS
TINYINT)").returns((byte)127).check();
+ assertQuery("SELECT CAST(127.4 AS
TINYINT)").returns((byte)127).check();
+ assertThrows("SELECT CAST(127.5 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertThrows("SELECT CAST(127.9 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertQuery("SELECT CAST(127.9 - 1 AS
TINYINT)").returns((byte)127).check();
+ assertQuery("SELECT CAST(128.4 - 1 AS
TINYINT)").returns((byte)127).check();
+ assertThrows("SELECT CAST(128.9 - 1 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertThrows("SELECT CAST(128.5 - 1 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST(128 AS TINYINT)", IgniteSQLException.class,
"TINYINT overflow");
assertThrows("SELECT CAST(128.1 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST(-129 AS TINYINT)", IgniteSQLException.class,
"TINYINT overflow");
assertThrows("SELECT CAST(-129.1 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertQuery("SELECT CAST(-128.1 AS
TINYINT)").returns((byte)-128).check();
- assertQuery("SELECT CAST(-128.9 AS
TINYINT)").returns((byte)-128).check();
+ assertQuery("SELECT CAST(-128.4 AS
TINYINT)").returns((byte)-128).check();
+ assertThrows("SELECT CAST(-128.5 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertThrows("SELECT CAST(-128.9 AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertQuery("SELECT CAST('127.1' AS
TINYINT)").returns((byte)127).check();
- assertQuery("SELECT CAST('127.9' AS
TINYINT)").returns((byte)127).check();
+ assertQuery("SELECT CAST('127.4' AS
TINYINT)").returns((byte)127).check();
+ assertThrows("SELECT CAST('127.5' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertThrows("SELECT CAST('127.9' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST('128' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST('128.1' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST('-129' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertThrows("SELECT CAST('-129.1' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
assertQuery("SELECT CAST('-128.1' AS
TINYINT)").returns((byte)-128).check();
- assertQuery("SELECT CAST('-128.9' AS
TINYINT)").returns((byte)-128).check();
+ assertQuery("SELECT CAST('-128.4' AS
TINYINT)").returns((byte)-128).check();
+ assertThrows("SELECT CAST('-128.5' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
+ assertThrows("SELECT CAST('-128.9' AS TINYINT)",
IgniteSQLException.class, "TINYINT overflow");
}
/** */
diff --git a/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
b/modules/calcite/src/test/sql/function/numeric/test_round_ceil.test
similarity index 96%
rename from modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
rename to modules/calcite/src/test/sql/function/numeric/test_round_ceil.test
index fc0c0107a6f..2d009414bc4 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_round_ceil.test
@@ -11,41 +11,41 @@ INSERT INTO numbers VALUES (NULL),(-42.8),(-42.2),(0),
(42.2), (42.8)
query I
SELECT cast(CEIL(n::tinyint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(CEIL(n::smallint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(CEIL(n::integer) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(CEIL(n::bigint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
@@ -81,41 +81,41 @@ NULL
query I
SELECT cast(FLOOR(n::tinyint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(FLOOR(n::smallint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(FLOOR(n::integer) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
SELECT cast(FLOOR(n::bigint) as bigint) FROM numbers ORDER BY n NULLS LAST
----
--42
+-43
-42
0
42
-42
+43
NULL
query I
diff --git a/modules/calcite/src/test/sql/function/numeric/test_trigo.test
b/modules/calcite/src/test/sql/function/numeric/test_trigo.test
index 628b30214a7..68836b276a2 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_trigo.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_trigo.test
@@ -11,121 +11,121 @@ INSERT INTO numbers VALUES (-42),(-1),(0), (1), (42),
(NULL)
query I
SELECT cast(SIN(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(SIN(n::smallint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(SIN(n::integer)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(SIN(n::bigint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(SIN(n::float)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(SIN(n::double)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
-916
+917
-841
0
841
--916
+-917
NULL
query I
SELECT cast(COS(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
SELECT cast(COS(n::smallint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
SELECT cast(COS(n::integer)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
SELECT cast(COS(n::bigint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
SELECT cast(COS(n::float)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
SELECT cast(COS(n::double)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--399
+-400
540
1000
540
--399
+-400
NULL
query I
@@ -191,145 +191,145 @@ NULL
query I
SELECT cast(ATAN(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ATAN(n::smallint)*1000 as bigint) FROM numbers ORDER BY n NULLS
LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ATAN(n::integer)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ATAN(n::bigint)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ATAN(n::float)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ATAN(n::double)*1000 as bigint) FROM numbers ORDER BY n NULLS LAST
----
--1546
+-1547
-785
0
785
-1546
+1547
NULL
query I
SELECT cast(ASIN(n::tinyint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ASIN(n::smallint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ASIN(n::integer)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ASIN(n::bigint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ASIN(n::float)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ASIN(n::double)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
--1570
+-1571
0
-1570
+1571
query I
SELECT cast(ACOS(n::tinyint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
query I
SELECT cast(ACOS(n::smallint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
query I
SELECT cast(ACOS(n::integer)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
query I
SELECT cast(ACOS(n::bigint)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
query I
SELECT cast(ACOS(n::float)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
query I
SELECT cast(ACOS(n::double)*1000 as bigint) FROM numbers WHERE n between -1
and 1 ORDER BY n
----
-3141
-1570
+3142
+1571
0
statement error
@@ -387,9 +387,9 @@ query I
SELECT cast(ATAN2(n::tinyint, 42)*1000 as bigint) FROM numbers ORDER BY n
NULLS LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
@@ -397,9 +397,9 @@ query I
SELECT cast(ATAN2(n::smallint, 42)*1000 as bigint) FROM numbers ORDER BY n
NULLS LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
@@ -407,9 +407,9 @@ query I
SELECT cast(ATAN2(n::integer, 42)*1000 as bigint) FROM numbers ORDER BY n
NULLS LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
@@ -417,9 +417,9 @@ query I
SELECT cast(ATAN2(n::bigint, 42)*1000 as bigint) FROM numbers ORDER BY n NULLS
LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
@@ -427,9 +427,9 @@ query I
SELECT cast(ATAN2(n::float, 42)*1000 as bigint) FROM numbers ORDER BY n NULLS
LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
@@ -437,9 +437,9 @@ query I
SELECT cast(ATAN2(n::double, 42)*1000 as bigint) FROM numbers ORDER BY n NULLS
LAST
----
-785
--23
+-24
0
-23
+24
785
NULL
