alex-plekhanov commented on code in PR #11581: URL: https://github.com/apache/ignite/pull/11581#discussion_r1805967068
########## modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java: ########## @@ -596,6 +600,214 @@ public void testNumericConversion() { .check(); } + /** */ + @Test + public void testCoercionOfVarcharLiterals() { + doTestCoercionOfVarchars(false); + } + + /** */ + @Test + public void testCoercionOfVarcharDynamicParameters() { + doTestCoercionOfVarchars(true); + } + + /** */ + private void doTestCoercionOfVarchars(boolean dynamics) { + for (List<Object> params : varcharsToCoerce()) { + String val = params.get(0).toString(); + RelDataType type = (RelDataType)params.get(1); + String result = params.get(2).toString(); + + if (dynamics) + assertQuery(String.format("SELECT CAST(? AS %s)", type)).withParams(val).returns(result).check(); + else + assertQuery(String.format("SELECT CAST('%s' AS %s)", val, type)).returns(result).check(); + } + } + + /** */ + private static List<List<Object>> varcharsToCoerce() { + IgniteTypeFactory tf = Commons.typeFactory(); + + return F.asList( + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 3), "abc"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 5), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 6), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR), "a"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR, 3), "abc") + ); + } + + /** */ + @Test + public void testCoercionOfNumericLiterals() { + doTestCoercionOfNumerics(false, false); + } + + /** */ + @Test + public void testCoercionOfNumericLiteralsPrecasted() { + doTestCoercionOfNumerics(false, true); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParameters() { + doTestCoercionOfNumerics(true, false); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParametersPrecasted() { + doTestCoercionOfNumerics(true, true); + } + + /** */ + private void doTestCoercionOfNumerics(boolean dynamic, boolean precasted) { + for (List<Object> params : numericsToCast()) { + assert params.size() == 4 : "Wrong params lenght: " + params.size(); + + RelDataType inputType = (RelDataType)params.get(0); + Object inputVal = params.get(1); + RelDataType targetType = (RelDataType)params.get(2); + Object expectedRes = params.get(3); + + log.info("Params: inputType=" + inputType + ", inputValue=" + inputVal + ", targetType=" + targetType + + ", expectedResult=" + expectedRes); + + if (dynamic) { + String qry = precasted + ? String.format("SELECT CAST(?::%s AS %s)", inputType, targetType) + : String.format("SELECT CAST(? AS %s)", targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage(), inputVal); + else + assertQuery(qry).withParams(inputType).returns(expectedRes); + } + else { + String qry = precasted + ? String.format("SELECT CAST(%s::%s AS %s)", asLiteral(inputVal, inputType), inputType, targetType) + : String.format("SELECT CAST(%s AS %s)", asLiteral(inputVal, inputType), targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage()); + else + assertQuery(qry).returns(expectedRes); + } + } + } + + /** */ + private static String asLiteral(Object val, RelDataType type) { + return SqlTypeUtil.isCharacter(type) ? String.format("'%s'", val) : String.valueOf(val); + } + + /** */ + private static List<List<Object>> numericsToCast() { + IgniteTypeFactory tf = Commons.typeFactory(); + + RelDataType varcharType = tf.createSqlType(SqlTypeName.VARCHAR); + RelDataType tinyIntType = tf.createSqlType(SqlTypeName.TINYINT); + RelDataType smallIntType = tf.createSqlType(SqlTypeName.SMALLINT); + RelDataType integerType = tf.createSqlType(SqlTypeName.INTEGER); + RelDataType bigintType = tf.createSqlType(SqlTypeName.BIGINT); + RelDataType realType = tf.createSqlType(SqlTypeName.REAL); + RelDataType doubleType = tf.createSqlType(SqlTypeName.DOUBLE); + + Exception overflowErr = new IllegalArgumentException(IgniteSqlFunctions.NUMERIC_OVERFLOW_ERROR); + Exception numFormatErr = new NumberFormatException("is neither a decimal digit number"); + + //noinspection RedundantTypeArguments (explicit type arguments speedup compilation and analysis time) + return F.<List<Object>>asList( + // String + F.asList(varcharType, "100", decimalType(3), new BigDecimal("100")), Review Comment: Type here is no needed too, strings can be inlined (`VARCHAR`, `DECIMAL(3)`, etc) ########## modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java: ########## @@ -596,6 +600,214 @@ public void testNumericConversion() { .check(); } + /** */ + @Test + public void testCoercionOfVarcharLiterals() { + doTestCoercionOfVarchars(false); + } + + /** */ + @Test + public void testCoercionOfVarcharDynamicParameters() { + doTestCoercionOfVarchars(true); + } + + /** */ + private void doTestCoercionOfVarchars(boolean dynamics) { + for (List<Object> params : varcharsToCoerce()) { + String val = params.get(0).toString(); + RelDataType type = (RelDataType)params.get(1); + String result = params.get(2).toString(); + + if (dynamics) + assertQuery(String.format("SELECT CAST(? AS %s)", type)).withParams(val).returns(result).check(); + else + assertQuery(String.format("SELECT CAST('%s' AS %s)", val, type)).returns(result).check(); + } + } + + /** */ + private static List<List<Object>> varcharsToCoerce() { + IgniteTypeFactory tf = Commons.typeFactory(); + + return F.asList( + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 3), "abc"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 5), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 6), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR), "a"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR, 3), "abc") + ); + } + + /** */ + @Test + public void testCoercionOfNumericLiterals() { Review Comment: New tests don't work in transactional mode (test now is a child of AbstractBasicIntegrationTransactionalTest). Lets add `assumeTrue("Test use queries that doesn't touch any data. Skip for tx modes", sqlTxMode == SqlTransactionMode.NONE);` to skip tests in tx modes. Or indroduce some method with this assumption (there are already two usages of this assumption in test). ########## modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java: ########## @@ -596,6 +600,214 @@ public void testNumericConversion() { .check(); } + /** */ + @Test + public void testCoercionOfVarcharLiterals() { + doTestCoercionOfVarchars(false); + } + + /** */ + @Test + public void testCoercionOfVarcharDynamicParameters() { + doTestCoercionOfVarchars(true); + } + + /** */ + private void doTestCoercionOfVarchars(boolean dynamics) { + for (List<Object> params : varcharsToCoerce()) { + String val = params.get(0).toString(); + RelDataType type = (RelDataType)params.get(1); + String result = params.get(2).toString(); + + if (dynamics) + assertQuery(String.format("SELECT CAST(? AS %s)", type)).withParams(val).returns(result).check(); + else + assertQuery(String.format("SELECT CAST('%s' AS %s)", val, type)).returns(result).check(); + } + } + + /** */ + private static List<List<Object>> varcharsToCoerce() { + IgniteTypeFactory tf = Commons.typeFactory(); + + return F.asList( + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 3), "abc"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 5), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 6), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR), "a"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR, 3), "abc") + ); + } + + /** */ + @Test + public void testCoercionOfNumericLiterals() { + doTestCoercionOfNumerics(false, false); + } + + /** */ + @Test + public void testCoercionOfNumericLiteralsPrecasted() { + doTestCoercionOfNumerics(false, true); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParameters() { + doTestCoercionOfNumerics(true, false); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParametersPrecasted() { + doTestCoercionOfNumerics(true, true); + } + + /** */ + private void doTestCoercionOfNumerics(boolean dynamic, boolean precasted) { + for (List<Object> params : numericsToCast()) { + assert params.size() == 4 : "Wrong params lenght: " + params.size(); + + RelDataType inputType = (RelDataType)params.get(0); + Object inputVal = params.get(1); + RelDataType targetType = (RelDataType)params.get(2); + Object expectedRes = params.get(3); + + log.info("Params: inputType=" + inputType + ", inputValue=" + inputVal + ", targetType=" + targetType + + ", expectedResult=" + expectedRes); + + if (dynamic) { + String qry = precasted + ? String.format("SELECT CAST(?::%s AS %s)", inputType, targetType) + : String.format("SELECT CAST(? AS %s)", targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage(), inputVal); + else + assertQuery(qry).withParams(inputType).returns(expectedRes); + } + else { + String qry = precasted + ? String.format("SELECT CAST(%s::%s AS %s)", asLiteral(inputVal, inputType), inputType, targetType) + : String.format("SELECT CAST(%s AS %s)", asLiteral(inputVal, inputType), targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage()); + else + assertQuery(qry).returns(expectedRes); + } + } + } + + /** */ + private static String asLiteral(Object val, RelDataType type) { + return SqlTypeUtil.isCharacter(type) ? String.format("'%s'", val) : String.valueOf(val); + } + + /** */ + private static List<List<Object>> numericsToCast() { + IgniteTypeFactory tf = Commons.typeFactory(); + + RelDataType varcharType = tf.createSqlType(SqlTypeName.VARCHAR); + RelDataType tinyIntType = tf.createSqlType(SqlTypeName.TINYINT); + RelDataType smallIntType = tf.createSqlType(SqlTypeName.SMALLINT); + RelDataType integerType = tf.createSqlType(SqlTypeName.INTEGER); + RelDataType bigintType = tf.createSqlType(SqlTypeName.BIGINT); + RelDataType realType = tf.createSqlType(SqlTypeName.REAL); + RelDataType doubleType = tf.createSqlType(SqlTypeName.DOUBLE); + + Exception overflowErr = new IllegalArgumentException(IgniteSqlFunctions.NUMERIC_OVERFLOW_ERROR); + Exception numFormatErr = new NumberFormatException("is neither a decimal digit number"); + + //noinspection RedundantTypeArguments (explicit type arguments speedup compilation and analysis time) + return F.<List<Object>>asList( + // String + F.asList(varcharType, "100", decimalType(3), new BigDecimal("100")), + F.asList(varcharType, "100", decimalType(3, 0), new BigDecimal("100")), + F.asList(varcharType, "100", decimalType(4, 1), new BigDecimal("100.0")), + F.asList(varcharType, "100.12", decimalType(5, 1), new BigDecimal("100.1")), + F.asList(varcharType, "lame", decimalType(5, 1), numFormatErr), + F.asList(varcharType, "12345", decimalType(5, 1), overflowErr), + F.asList(varcharType, "1234", decimalType(5, 1), new BigDecimal("1234.0")), + F.asList(varcharType, "100.12", decimalType(1, 0), overflowErr), + F.asList(varcharType, "100", decimalType(2, 0), overflowErr), + + // Numeric + F.asList(decimalType(4), "100", decimalType(3), new BigDecimal("100")), + F.asList(decimalType(4), "100", decimalType(3, 0), new BigDecimal("100")), + F.asList(decimalType(4), "100.12", decimalType(5, 1), new BigDecimal("100.1")), + F.asList(decimalType(4), "100.12", decimalType(5, 0), new BigDecimal("100")), Review Comment: Let's add: ``` F.asList(decimalType(4), "100.16", decimalType(5, 1), new BigDecimal("100.1")), F.asList(decimalType(4), "-100.16", decimalType(5, 1), new BigDecimal("-100.1")), ``` To check correct rounding ########## modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java: ########## @@ -42,6 +42,15 @@ * Ignite SQL functions. */ public class IgniteSqlFunctions { + /** */ + public static final String NUMERIC_OVERFLOW_ERROR = "Numeric field overflow."; + + /** */ + private static final int DFLT_NUM_PRECISION = IgniteTypeSystem.INSTANCE.getDefaultPrecision(SqlTypeName.DECIMAL); + + /** */ + private static final RoundingMode NUMERIC_ROUNDING_MODE = RoundingMode.HALF_UP; Review Comment: We use CEILING rounding mode, but for some reason tests are passed with this rounding mode when I add cases like convertation from 100.16 to 100.1. ########## modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java: ########## @@ -596,6 +600,214 @@ public void testNumericConversion() { .check(); } + /** */ + @Test + public void testCoercionOfVarcharLiterals() { + doTestCoercionOfVarchars(false); + } + + /** */ + @Test + public void testCoercionOfVarcharDynamicParameters() { + doTestCoercionOfVarchars(true); + } + + /** */ + private void doTestCoercionOfVarchars(boolean dynamics) { + for (List<Object> params : varcharsToCoerce()) { + String val = params.get(0).toString(); + RelDataType type = (RelDataType)params.get(1); + String result = params.get(2).toString(); + + if (dynamics) + assertQuery(String.format("SELECT CAST(? AS %s)", type)).withParams(val).returns(result).check(); + else + assertQuery(String.format("SELECT CAST('%s' AS %s)", val, type)).returns(result).check(); + } + } + + /** */ + private static List<List<Object>> varcharsToCoerce() { + IgniteTypeFactory tf = Commons.typeFactory(); + + return F.asList( + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 3), "abc"), Review Comment: No need to create type, this type only used to be converted to string, so here string `VARCHAR(3)` can be used. ########## modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/DataTypesTest.java: ########## @@ -596,6 +600,214 @@ public void testNumericConversion() { .check(); } + /** */ + @Test + public void testCoercionOfVarcharLiterals() { + doTestCoercionOfVarchars(false); + } + + /** */ + @Test + public void testCoercionOfVarcharDynamicParameters() { + doTestCoercionOfVarchars(true); + } + + /** */ + private void doTestCoercionOfVarchars(boolean dynamics) { + for (List<Object> params : varcharsToCoerce()) { + String val = params.get(0).toString(); + RelDataType type = (RelDataType)params.get(1); + String result = params.get(2).toString(); + + if (dynamics) + assertQuery(String.format("SELECT CAST(? AS %s)", type)).withParams(val).returns(result).check(); + else + assertQuery(String.format("SELECT CAST('%s' AS %s)", val, type)).returns(result).check(); + } + } + + /** */ + private static List<List<Object>> varcharsToCoerce() { + IgniteTypeFactory tf = Commons.typeFactory(); + + return F.asList( + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 3), "abc"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 5), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR, 6), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.VARCHAR), "abcde"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR), "a"), + F.asList("abcde", tf.createSqlType(SqlTypeName.CHAR, 3), "abc") + ); + } + + /** */ + @Test + public void testCoercionOfNumericLiterals() { + doTestCoercionOfNumerics(false, false); + } + + /** */ + @Test + public void testCoercionOfNumericLiteralsPrecasted() { + doTestCoercionOfNumerics(false, true); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParameters() { + doTestCoercionOfNumerics(true, false); + } + + /** */ + @Test + public void testCoercionOfNumericDynamicParametersPrecasted() { + doTestCoercionOfNumerics(true, true); + } + + /** */ + private void doTestCoercionOfNumerics(boolean dynamic, boolean precasted) { + for (List<Object> params : numericsToCast()) { + assert params.size() == 4 : "Wrong params lenght: " + params.size(); + + RelDataType inputType = (RelDataType)params.get(0); + Object inputVal = params.get(1); + RelDataType targetType = (RelDataType)params.get(2); + Object expectedRes = params.get(3); + + log.info("Params: inputType=" + inputType + ", inputValue=" + inputVal + ", targetType=" + targetType + + ", expectedResult=" + expectedRes); + + if (dynamic) { + String qry = precasted + ? String.format("SELECT CAST(?::%s AS %s)", inputType, targetType) + : String.format("SELECT CAST(? AS %s)", targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage(), inputVal); + else + assertQuery(qry).withParams(inputType).returns(expectedRes); + } + else { + String qry = precasted + ? String.format("SELECT CAST(%s::%s AS %s)", asLiteral(inputVal, inputType), inputType, targetType) + : String.format("SELECT CAST(%s AS %s)", asLiteral(inputVal, inputType), targetType); + + if (expectedRes instanceof Exception) + assertThrows(qry, (Class<? extends Exception>)expectedRes.getClass(), ((Throwable)expectedRes).getMessage()); + else + assertQuery(qry).returns(expectedRes); + } + } + } + + /** */ + private static String asLiteral(Object val, RelDataType type) { + return SqlTypeUtil.isCharacter(type) ? String.format("'%s'", val) : String.valueOf(val); + } + + /** */ + private static List<List<Object>> numericsToCast() { + IgniteTypeFactory tf = Commons.typeFactory(); + + RelDataType varcharType = tf.createSqlType(SqlTypeName.VARCHAR); + RelDataType tinyIntType = tf.createSqlType(SqlTypeName.TINYINT); + RelDataType smallIntType = tf.createSqlType(SqlTypeName.SMALLINT); + RelDataType integerType = tf.createSqlType(SqlTypeName.INTEGER); + RelDataType bigintType = tf.createSqlType(SqlTypeName.BIGINT); + RelDataType realType = tf.createSqlType(SqlTypeName.REAL); + RelDataType doubleType = tf.createSqlType(SqlTypeName.DOUBLE); + + Exception overflowErr = new IllegalArgumentException(IgniteSqlFunctions.NUMERIC_OVERFLOW_ERROR); + Exception numFormatErr = new NumberFormatException("is neither a decimal digit number"); + + //noinspection RedundantTypeArguments (explicit type arguments speedup compilation and analysis time) + return F.<List<Object>>asList( + // String + F.asList(varcharType, "100", decimalType(3), new BigDecimal("100")), + F.asList(varcharType, "100", decimalType(3, 0), new BigDecimal("100")), + F.asList(varcharType, "100", decimalType(4, 1), new BigDecimal("100.0")), + F.asList(varcharType, "100.12", decimalType(5, 1), new BigDecimal("100.1")), Review Comment: Let's add: ``` F.asList(varcharType, "100.16", decimalType(5, 1), new BigDecimal("100.1")), F.asList(varcharType, "-100.16", decimalType(5, 1), new BigDecimal("-100.1")), ``` To check correct rounding. ########## modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/exec/exp/IgniteSqlFunctions.java: ########## @@ -64,46 +73,42 @@ public static String toString(BigDecimal x) { return x == null ? null : x.toPlainString(); } - /** */ - private static BigDecimal setScale(int precision, int scale, BigDecimal decimal) { - return precision == IgniteTypeSystem.INSTANCE.getDefaultPrecision(SqlTypeName.DECIMAL) - ? decimal : decimal.setScale(scale, RoundingMode.HALF_UP); - } - /** CAST(DOUBLE AS DECIMAL). */ public static BigDecimal toBigDecimal(double val, int precision, int scale) { - BigDecimal decimal = BigDecimal.valueOf(val); - return setScale(precision, scale, decimal); + return removeDefaultScale(precision, scale, toBigDecimal(BigDecimal.valueOf(val), precision, scale)); } /** CAST(FLOAT AS DECIMAL). */ public static BigDecimal toBigDecimal(float val, int precision, int scale) { - BigDecimal decimal = new BigDecimal(String.valueOf(val)); - return setScale(precision, scale, decimal); + return removeDefaultScale(precision, scale, toBigDecimal(BigDecimal.valueOf(val), precision, scale)); + } + + /** Removes redundant scale in case of default DECIMAL (without passed precision and scale). */ + private static BigDecimal removeDefaultScale(int precision, int scale, BigDecimal val) { + if (precision == DFLT_NUM_PRECISION && scale == 0 && val.compareTo(val.setScale(0, NUMERIC_ROUNDING_MODE)) == 0) + return val.setScale(0, NUMERIC_ROUNDING_MODE); Review Comment: `val.setScale(0, NUMERIC_ROUNDING_MODE)` executed twice, we can cache first value. Can we somehow define that BigDecimal has a scale but fraction is 0? Do we need this method at all? Can you provide examples when double/float is converted to BigDecimal without fraction but with scale? I've commented this method, but tests are passed. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@ignite.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org