This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 91cd2b137d [CALCITE-6322] Casts to DECIMAL types are ignored
91cd2b137d is described below
commit 91cd2b137d706dbfea7ba22733857021232c0aeb
Author: Mihai Budiu <[email protected]>
AuthorDate: Thu Aug 1 13:36:23 2024 -0700
[CALCITE-6322] Casts to DECIMAL types are ignored
Signed-off-by: Mihai Budiu <[email protected]>
---
babel/src/test/resources/sql/big-query.iq | 8 +
babel/src/test/resources/sql/redshift.iq | 100 ++++-----
babel/src/test/resources/sql/spark.iq | 2 +-
.../adapter/enumerable/RexToLixTranslator.java | 21 ++
.../java/org/apache/calcite/rex/RexBuilder.java | 6 +
.../src/main/java/org/apache/calcite/util/Bug.java | 16 ++
.../org/apache/calcite/util/BuiltInMethod.java | 6 +
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 3 +-
.../org/apache/calcite/rex/RexProgramTest.java | 3 +-
.../org/apache/calcite/test/InterpreterTest.java | 2 +-
.../org/apache/calcite/test/JdbcAdapterTest.java | 12 +-
.../java/org/apache/calcite/test/JdbcTest.java | 2 +-
.../calcite/test/TypeCoercionConverterTest.xml | 10 +-
core/src/test/resources/sql/agg.iq | 66 +++---
core/src/test/resources/sql/measure-paper.iq | 26 +--
core/src/test/resources/sql/measure.iq | 148 ++++++------
core/src/test/resources/sql/misc.iq | 12 +-
core/src/test/resources/sql/sub-query.iq | 14 +-
core/src/test/resources/sql/winagg.iq | 82 +++----
core/src/test/resources/sql/within-distinct.iq | 107 ++++-----
.../org/apache/calcite/test/DruidAdapter2IT.java | 8 +-
.../org/apache/calcite/test/DruidAdapterIT.java | 8 +-
.../calcite/adapter/innodb/InnodbAdapterTest.java | 4 +-
.../org/apache/calcite/linq4j/tree/Primitive.java | 30 +++
site/_docs/history.md | 5 +
.../org/apache/calcite/test/SqlOperatorTest.java | 247 +++++++++++----------
26 files changed, 522 insertions(+), 426 deletions(-)
diff --git a/babel/src/test/resources/sql/big-query.iq
b/babel/src/test/resources/sql/big-query.iq
index 5ffa0bdf66..a158bd16ee 100755
--- a/babel/src/test/resources/sql/big-query.iq
+++ b/babel/src/test/resources/sql/big-query.iq
@@ -660,6 +660,7 @@ SELECT SAFE_ADD(CAST(1.7e308 as DOUBLE), CAST(1.7e308 as
DOUBLE)) as double_over
!ok
+!if (fixed.calcite6328) {
SELECT SAFE_ADD(9, cast(9.999999999999999999e75 as DECIMAL(38, 19))) as
decimal_overflow;
+------------------+
| decimal_overflow |
@@ -669,6 +670,7 @@ SELECT SAFE_ADD(9, cast(9.999999999999999999e75 as
DECIMAL(38, 19))) as decimal_
(1 row)
!ok
+!}
# NaN arguments should return NaN
SELECT SAFE_ADD(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
@@ -720,6 +722,7 @@ SELECT SAFE_DIVIDE(CAST(1.7e308 as DOUBLE),
!ok
+!if (fixed.calcite6328) {
SELECT SAFE_DIVIDE(CAST(-3.5e75 AS DECIMAL(76, 0)),
CAST(3.5e-75 AS DECIMAL(76, 0))) as decimal_overflow;
+------------------+
@@ -730,6 +733,7 @@ SELECT SAFE_DIVIDE(CAST(-3.5e75 AS DECIMAL(76, 0)),
(1 row)
!ok
+!}
# NaN arguments should return NaN
SELECT SAFE_DIVIDE(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
@@ -801,6 +805,7 @@ SELECT SAFE_MULTIPLY(CAST(1.7e308 as DOUBLE), CAST(3 as
BIGINT)) as double_overf
!ok
+!if (fixed.calcite6328) {
SELECT SAFE_MULTIPLY(CAST(-3.5e75 AS DECIMAL(76, 0)), CAST(10 AS BIGINT)) as
decimal_overflow;
+------------------+
| decimal_overflow |
@@ -810,6 +815,7 @@ SELECT SAFE_MULTIPLY(CAST(-3.5e75 AS DECIMAL(76, 0)),
CAST(10 AS BIGINT)) as dec
(1 row)
!ok
+!}
# NaN arguments should return NaN
SELECT SAFE_MULTIPLY(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
@@ -916,6 +922,7 @@ SELECT SAFE_SUBTRACT(CAST(1.7e308 as DOUBLE), CAST(-1.7e308
as DOUBLE)) as doubl
!ok
+!if (fixed.calcite6328) {
SELECT SAFE_SUBTRACT(9, cast(-9.999999999999999999e75 as DECIMAL(38, 19))) as
decimal_overflow;
+------------------+
| decimal_overflow |
@@ -925,6 +932,7 @@ SELECT SAFE_SUBTRACT(9, cast(-9.999999999999999999e75 as
DECIMAL(38, 19))) as de
(1 row)
!ok
+!}
# NaN arguments should return NaN
SELECT SAFE_SUBTRACT(CAST('NaN' AS DOUBLE), CAST(3 as BIGINT)) as NaN_result;
diff --git a/babel/src/test/resources/sql/redshift.iq
b/babel/src/test/resources/sql/redshift.iq
index 6104222746..d5d9f255f3 100755
--- a/babel/src/test/resources/sql/redshift.iq
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -184,7 +184,7 @@ select approximate percentile_disc(0.5) within group (order
by sal) from emp gro
# AVG
select avg(sal) from emp;
EXPR$0
-2073.214285714286
+2073.21
!ok
# COUNT
@@ -288,12 +288,12 @@ select percentile_disc(0.6) within group (order by sal)
from emp group by deptno
# STDDEV_SAMP and STDDEV_POP
select stddev_samp(sal) from emp;
EXPR$0
-1182.503223516271873450023122131824493408203125
+1182.50
!ok
select stddev_pop(sal) from emp;
EXPR$0
-1139.488618295281639802851714193820953369140625
+1139.48
!ok
# SUM
@@ -308,24 +308,24 @@ EXPR$0
!ok
# VAR_SAMP and VAR_POP
-select var_samp(sal) from emp;
+select var_samp(CAST(sal AS DECIMAL(11, 4))) from emp;
EXPR$0
-1398313.873626374
+1398313.8736
!ok
-select var_samp(distinct sal) from emp;
+select var_samp(distinct CAST(sal AS DECIMAL(11, 4))) from emp;
EXPR$0
-1512779.356060606
+1512779.3560
!ok
-select var_samp(all sal) from emp;
+select var_samp(all CAST(sal AS DECIMAL(11, 4))) from emp;
EXPR$0
-1398313.873626374
+1398313.8736
!ok
-select var_pop(sal) from emp;
+select var_pop(CAST(sal AS DECIMAL(11, 4))) from emp;
EXPR$0
-1298434.31122449
+1298434.3112
!ok
# 4 Bit-Wise Aggregate Functions
@@ -378,10 +378,10 @@ select empno, avg(sal) over (order by empno rows
unbounded preceding) from emp w
EMPNO, EXPR$1
7499, 1600.00
7521, 1425.00
-7654, 1366.666666666667
+7654, 1366.66
7698, 1737.50
7844, 1690.00
-7900, 1566.666666666667
+7900, 1566.66
!ok
# COUNT
@@ -525,33 +525,33 @@ select deptno, ratio_to_report(sal) over (partition by
deptno) from emp;
# STDDEV_POP
select empno, stddev_pop(comm) over (order by empno rows unbounded preceding)
from emp where deptno = 30 order by 1;
EMPNO, EXPR$1
-7499, 0
-7521, 100
-7654, 478.42333648024424519462627358734607696533203125
-7698, 478.42333648024424519462627358734607696533203125
-7844, 522.0153254455275373402400873601436614990234375
-7900, 522.0153254455275373402400873601436614990234375
+7499, 0.00
+7521, 100.00
+7654, 478.42
+7698, 478.42
+7844, 522.01
+7900, 522.01
!ok
# STDDEV_SAMP (synonym for STDDEV)
select empno, stddev_samp(comm) over (order by empno rows unbounded preceding)
from emp where deptno = 30 order by 1;
EMPNO, EXPR$1
7499, null
-7521, 141.421356237309510106570087373256683349609375
-7654, 585.9465277082316561063635163009166717529296875
-7698, 585.9465277082316561063635163009166717529296875
-7844, 602.7713773341707792496890760958194732666015625
-7900, 602.7713773341707792496890760958194732666015625
+7521, 141.42
+7654, 585.94
+7698, 585.94
+7844, 602.77
+7900, 602.77
!ok
select empno, stddev(comm) over (order by empno rows unbounded preceding) from
emp where deptno = 30 order by 1;
EMPNO, EXPR$1
7499, null
-7521, 141.421356237309510106570087373256683349609375
-7654, 585.9465277082316561063635163009166717529296875
-7698, 585.9465277082316561063635163009166717529296875
-7844, 602.7713773341707792496890760958194732666015625
-7900, 602.7713773341707792496890760958194732666015625
+7521, 141.42
+7654, 585.94
+7698, 585.94
+7844, 602.77
+7900, 602.77
!ok
# SUM
@@ -566,35 +566,35 @@ EMPNO, EXPR$1
!ok
# VAR_POP
-select empno, var_pop(comm) over (order by empno rows unbounded preceding)
from emp where deptno = 30 order by 1;
+select empno, var_pop(CAST(comm AS DECIMAL(10, 2))) over (order by empno rows
unbounded preceding) from emp where deptno = 30 order by 1;
EMPNO, EXPR$1
-7499, 0.0000
-7521, 10000.0000
-7654, 228888.888888889
-7698, 228888.888888889
-7844, 272500.0000
-7900, 272500.0000
+7499, 0.00
+7521, 10000.00
+7654, 228888.88
+7698, 228888.88
+7844, 272500.00
+7900, 272500.00
!ok
# VAR_SAMP (synonym for VARIANCE)
-select empno, var_samp(comm) over (order by empno rows unbounded preceding)
from emp where deptno = 30 order by 1;
+select empno, var_samp(CAST(comm AS DECIMAL(10, 2))) over (order by empno rows
unbounded preceding) from emp where deptno = 30 order by 1;
EMPNO, EXPR$1
7499, null
-7521, 20000.0000
-7654, 343333.3333333335
-7698, 343333.3333333335
-7844, 363333.3333333333
-7900, 363333.3333333333
+7521, 20000.00
+7654, 343333.33
+7698, 343333.33
+7844, 363333.33
+7900, 363333.33
!ok
-select empno, variance(comm) over (order by empno rows unbounded preceding)
from emp where deptno = 30 order by 1;
+select empno, variance(CAST(comm AS DECIMAL(10, 2))) over (order by empno rows
unbounded preceding) from emp where deptno = 30 order by 1;
EMPNO, EXPR$1
7499, null
-7521, 20000.0000
-7654, 343333.3333333335
-7698, 343333.3333333335
-7844, 363333.3333333333
-7900, 363333.3333333333
+7521, 20000.00
+7654, 343333.33
+7698, 343333.33
+7844, 363333.33
+7900, 363333.33
!ok
# 5.2 Ranking functions
@@ -2030,12 +2030,12 @@ SELECT
"JSON_EXTRACT_PATH_TEXT"('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}', 'f
# CAST and CONVERT
select cast(stddev_samp(sal) as dec(14, 2)) from emp;
EXPR$0
-1182.503223516271873450023122131824493408203125
+1182.50
!ok
select 123.456::decimal(8,4);
EXPR$0
-123.456
+123.4560
!ok
!if (position) {
diff --git a/babel/src/test/resources/sql/spark.iq
b/babel/src/test/resources/sql/spark.iq
index 668d15409e..d4d512489b 100644
--- a/babel/src/test/resources/sql/spark.iq
+++ b/babel/src/test/resources/sql/spark.iq
@@ -440,7 +440,7 @@ EXPR$0
SELECT REVERSE(array(CAST(2.1 as decimal(17)), 2.1111111111111119));
EXPR$0
-[2.1111111111111119, 2.1]
+[2.11, 2.00]
!ok
SELECT REVERSE(array(CAST(2.1 as double), 2.1111111111111119));
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
index 64db857bb5..39014391da 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
@@ -474,6 +474,27 @@ public class RexToLixTranslator implements
RexVisitor<RexToLixTranslator.Result>
Expressions.constant(precision),
Expressions.constant(scale),
Expressions.constant(sourceType.getSqlTypeName().getEndUnit().multiplier));
+ } else if (sourceType.getSqlTypeName() == SqlTypeName.DECIMAL) {
+ // Cast from DECIMAL to DECIMAL, may adjust scale and precision.
+ return Expressions.call(
+ BuiltInMethod.DECIMAL_DECIMAL_CAST.method,
+ operand,
+ Expressions.constant(precision),
+ Expressions.constant(scale));
+ } else if
(SqlTypeName.INT_TYPES.contains(sourceType.getSqlTypeName())) {
+ // Cast from INTEGER to DECIMAL, check for overflow
+ return Expressions.call(
+ BuiltInMethod.INTEGER_DECIMAL_CAST.method,
+ operand,
+ Expressions.constant(precision),
+ Expressions.constant(scale));
+ } else if
(SqlTypeName.APPROX_TYPES.contains(sourceType.getSqlTypeName())) {
+ // Cast from FLOAT/DOUBLE to DECIMAL
+ return Expressions.call(
+ BuiltInMethod.FP_DECIMAL_CAST.method,
+ operand,
+ Expressions.constant(precision),
+ Expressions.constant(scale));
}
}
return defaultExpression.get();
diff --git a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
index c3d2c8153c..4d40f2be03 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexBuilder.java
@@ -1140,6 +1140,12 @@ public class RexBuilder {
}
o = ((TimestampWithTimeZoneString) o).round(p);
break;
+ case DECIMAL:
+ if (o != null && type.getScale() != RelDataType.SCALE_NOT_SPECIFIED) {
+ assert o instanceof BigDecimal;
+ o = ((BigDecimal) o).setScale(type.getScale(), RoundingMode.DOWN);
+ }
+ break;
default:
break;
}
diff --git a/core/src/main/java/org/apache/calcite/util/Bug.java
b/core/src/main/java/org/apache/calcite/util/Bug.java
index 521da4d29d..989df00fdc 100644
--- a/core/src/main/java/org/apache/calcite/util/Bug.java
+++ b/core/src/main/java/org/apache/calcite/util/Bug.java
@@ -226,6 +226,22 @@ public abstract class Bug {
* [CALCITE-6294] Support IN filter in Arrow adapter</a> is fixed. */
public static final boolean CALCITE_6294_FIXED = false;
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE/issues/CALCITE-6295";>
+ * [CALCITE-6295] Support IS NOT NULL in Arrow adapter</a> is fixed. */
+ public static final boolean CALCITE_6295_FIXED = false;
+
+ /** Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE/issues/CALCITE-6296";>
+ * [CALCITE-6296] Support IS NULL in Arrow adapter</a> is fixed. */
+ public static final boolean CALCITE_6296_FIXED = false;
+
+ /* Whether
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6328";>[CALCITE-6328]
+ * The BigQuery functions SAFE_* do not match the BigQuery specification</a>
+ * is fixed. */
+ public static final boolean CALCITE_6328_FIXED = false;
+
/**
* Use this to flag temporary code.
*/
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index bc5ca1d61a..57d1f25a11 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -301,6 +301,12 @@ public enum BuiltInMethod {
ENUMERABLE_TO_LIST(ExtendedEnumerable.class, "toList"),
ENUMERABLE_TO_MAP(ExtendedEnumerable.class, "toMap", Function1.class,
Function1.class),
AS_LIST(Primitive.class, "asList", Object.class),
+ DECIMAL_DECIMAL_CAST(Primitive.class, "decimalDecimalCast",
+ BigDecimal.class, int.class, int.class),
+ INTEGER_DECIMAL_CAST(Primitive.class, "integerDecimalCast",
+ Number.class, int.class, int.class),
+ FP_DECIMAL_CAST(Primitive.class, "fpDecimalCast",
+ Number.class, int.class, int.class),
INTEGER_CAST(Primitive.class, "integerCast", Primitive.class, Object.class),
MEMORY_GET0(MemoryFactory.Memory.class, "get"),
MEMORY_GET1(MemoryFactory.Memory.class, "get", int.class),
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index af3e1bea6a..811427d809 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -5393,7 +5393,8 @@ class RelToSqlConverterTest {
* is greater than maximum numeric scale</a>. */
@Test void testNumericScaleMod() {
final String sql = "SELECT MOD(CAST(2 AS DECIMAL(39, 20)), 2)";
- final String expected = "SELECT MOD(2, 2)\nFROM (VALUES (0)) AS \"t\"
(\"ZERO\")";
+ final String expected =
+ "SELECT MOD(2.00000000000000000000, 2)\nFROM (VALUES (0)) AS \"t\"
(\"ZERO\")";
sql(sql).withPostgresqlModifiedDecimalTypeSystem()
.ok(expected);
}
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index a312e19211..a0b983fdd7 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -3851,7 +3851,7 @@ class RexProgramTest extends RexProgramTestBase {
checkSimplify(div(a, one), "?0.notNullInt1");
checkSimplify(div(a, nullInt), "null:INTEGER");
- checkSimplifyUnchanged(add(b, half));
+ checkSimplify(add(b, half), "?0.notNullDecimal2");
checkSimplify(add(zero, sub(nullInt, nullInt)), "null:INTEGER");
}
@@ -3863,5 +3863,4 @@ class RexProgramTest extends RexProgramTestBase {
RexNode cast = rexBuilder.makeCast(nullableDateType, dateStr);
checkSimplify(cast, "2020-10-30");
}
-
}
diff --git a/core/src/test/java/org/apache/calcite/test/InterpreterTest.java
b/core/src/test/java/org/apache/calcite/test/InterpreterTest.java
index a98a19625b..d241b2a116 100644
--- a/core/src/test/java/org/apache/calcite/test/InterpreterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/InterpreterTest.java
@@ -579,7 +579,7 @@ class InterpreterTest {
final String sql = "select x, min(y), max(y), sum(y), avg(y)\n"
+ "from (values ('a', -1.2), ('a', 2.3), ('a', 15)) as t(x, y)\n"
+ "group by x";
- sql(sql).returnsRows("[a, -1.2, 15.0, 16.1, 5.366666666666667]");
+ sql(sql).returnsRows("[a, -1.2, 15.0, 16.1, 5.3]");
}
@Test void testInterpretUnnest() {
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index c7f0815bfd..2b7aa4b9b9 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -1110,12 +1110,12 @@ class JdbcAdapterTest {
+ " JdbcTableModify(table=[[foodmart, expense_fact]], "
+ "operation=[INSERT], flattened=[false])\n"
+ " JdbcValues(tuples=[[{ 666, 666, 1997-01-01 00:00:00, 666, "
- + "'666', 666, 666 }]])\n\n";
+ + "'666', 666, 666.0000 }]])\n\n";
final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\"
(\"store_id\", "
+ "\"account_id\", \"exp_date\", \"time_id\", \"category_id\",
\"currency_id\", "
+ "\"amount\")\n"
+ "VALUES (666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', "
- + "666, 666)";
+ + "666, 666.0000)";
final AssertThat that =
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.enable(CalciteAssert.DB == DatabaseInstance.HSQLDB
@@ -1143,14 +1143,14 @@ class JdbcAdapterTest {
+ " JdbcTableModify(table=[[foodmart, expense_fact]], "
+ "operation=[INSERT], flattened=[false])\n"
+ " JdbcValues(tuples=[["
- + "{ 666, 666, 1997-01-01 00:00:00, 666, '666', 666, 666 }, "
- + "{ 666, 777, 1997-01-01 00:00:00, 666, '666', 666, 666 }]])\n\n";
+ + "{ 666, 666, 1997-01-01 00:00:00, 666, '666', 666, 666.0000 }, "
+ + "{ 666, 777, 1997-01-01 00:00:00, 666, '666', 666, 666.0000
}]])\n\n";
final String jdbcSql = "INSERT INTO \"foodmart\".\"expense_fact\""
+ " (\"store_id\", \"account_id\", \"exp_date\", \"time_id\", "
+ "\"category_id\", \"currency_id\", \"amount\")\n"
+ "VALUES "
- + "(666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666,
666),\n"
- + "(666, 777, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666, 666)";
+ + "(666, 666, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666,
666.0000),\n"
+ + "(666, 777, TIMESTAMP '1997-01-01 00:00:00', 666, '666', 666,
666.0000)";
final AssertThat that =
CalciteAssert.model(FoodmartSchema.FOODMART_MODEL)
.enable(CalciteAssert.DB == DatabaseInstance.HSQLDB
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 736d1b478f..3a2352b7a5 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -8349,7 +8349,7 @@ public class JdbcTest {
@Test void testIntAndBigDecimalInArray() {
CalciteAssert.that()
.query("select array[1, 1.1]")
- .returns("EXPR$0=[1, 1.1]\n");
+ .returns("EXPR$0=[1.0, 1.1]\n");
}
/** Test case for
diff --git
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
index 2d945b6da3..c88d38118c 100644
---
a/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
+++
b/core/src/test/resources/org/apache/calcite/test/TypeCoercionConverterTest.xml
@@ -158,11 +158,11 @@ LogicalTableModify(table=[[CATALOG, SALES, T1]],
operation=[INSERT], flattened=[
LogicalUnion(all=[false])
LogicalUnion(all=[false])
LogicalUnion(all=[false])
- LogicalValues(tuples=[[{ 'a', 1, 1.0, 0, 0, 0, 0, 2021-11-28
00:00:00, 2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'a', 1, 1, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
LogicalValues(tuples=[[{ 'b', 2, 2, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
- LogicalValues(tuples=[[{ 'c', 3, 3.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
- LogicalValues(tuples=[[{ 'd', 4, 4.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
- LogicalValues(tuples=[[{ 'e', 5, 5.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'c', 3, 3, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'd', 4, 4, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'e', 5, 5, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
]]>
</Resource>
</TestCase>
@@ -173,7 +173,7 @@ LogicalTableModify(table=[[CATALOG, SALES, T1]],
operation=[INSERT], flattened=[
<Resource name="plan">
<![CDATA[
LogicalTableModify(table=[[CATALOG, SALES, T1]], operation=[INSERT],
flattened=[false])
- LogicalValues(tuples=[[{ 'a', 1, 1.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'c', 3, 3.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'd', 4, 4.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'e', 5, 5.0, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
+ LogicalValues(tuples=[[{ 'a', 1, 1, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'b', 2, 2, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'c', 3, 3, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'd', 4, 4, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }, { 'e', 5, 5, 0, 0, 0, 0, 2021-11-28 00:00:00,
2021-11-28, X'0a', false }]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 8541f46ba5..feeaf9035e 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -1888,11 +1888,11 @@ from "scott".emp;
# Previously threw "java.lang.ArithmeticException: Non-terminating decimal
# expansion; no exact representable decimal result"
select avg(comm) as a, count(comm) as c from "scott".emp where empno < 7844;
-+-------------------+---+
-| A | C |
-+-------------------+---+
-| 733.3333333333333 | 3 |
-+-------------------+---+
++--------+---+
+| A | C |
++--------+---+
+| 733.33 | 3 |
++--------+---+
(1 row)
!ok
@@ -2913,10 +2913,10 @@ EnumerableAggregate(group=[{}], REGR_COUNT(COMM,
SAL)=[REGR_COUNT($6, $5)], REGR
# [CALCITE-1776, CALCITE-2402] REGR_SXX, REGR_SXY, REGR_SYY
SELECT
- regr_sxx(COMM, SAL) as "REGR_SXX(COMM, SAL)",
- regr_syy(COMM, SAL) as "REGR_SYY(COMM, SAL)",
- regr_sxx(SAL, COMM) as "REGR_SXX(SAL, COMM)",
- regr_syy(SAL, COMM) as "REGR_SYY(SAL, COMM)"
+ regr_sxx(CAST(COMM AS DECIMAL(12, 4)), CAST(SAL AS DECIMAL(12, 4))) as
"REGR_SXX(COMM, SAL)",
+ regr_syy(CAST(COMM AS DECIMAL(12, 4)), CAST(SAL AS DECIMAL(12, 4))) as
"REGR_SYY(COMM, SAL)",
+ regr_sxx(CAST(SAL AS DECIMAL(12, 4)), CAST(COMM AS DECIMAL(12, 4))) as
"REGR_SXX(SAL, COMM)",
+ regr_syy(CAST(SAL AS DECIMAL(12, 4)), CAST(COMM AS DECIMAL(12, 4))) as
"REGR_SYY(SAL, COMM)"
from "scott".emp;
+---------------------+---------------------+---------------------+---------------------+
| REGR_SXX(COMM, SAL) | REGR_SYY(COMM, SAL) | REGR_SXX(SAL, COMM) |
REGR_SYY(SAL, COMM) |
@@ -2929,16 +2929,16 @@ from "scott".emp;
# [CALCITE-1776, CALCITE-2402] COVAR_POP, COVAR_SAMP, VAR_SAMP, VAR_POP
SELECT
- covar_pop(COMM, COMM) as "COVAR_POP(COMM, COMM)",
- covar_samp(SAL, SAL) as "COVAR_SAMP(SAL, SAL)",
- var_pop(COMM) as "VAR_POP(COMM)",
- var_samp(SAL) as "VAR_SAMP(SAL)"
+ covar_pop(CAST(COMM AS DECIMAL(12, 4)), CAST(COMM AS DECIMAL(12, 4))) as
"COVAR_POP(COMM, COMM)",
+ covar_samp(CAST(SAL AS DECIMAL(12, 4)), CAST(SAL AS DECIMAL(12, 4))) as
"COVAR_SAMP(SAL, SAL)",
+ var_pop(CAST(COMM AS DECIMAL(12, 4))) as "VAR_POP(COMM)",
+ var_samp(CAST(SAL AS DECIMAL(12, 4))) as "VAR_SAMP(SAL)"
from "scott".emp;
-+-----------------------+----------------------+---------------+-------------------+
-| COVAR_POP(COMM, COMM) | COVAR_SAMP(SAL, SAL) | VAR_POP(COMM) | VAR_SAMP(SAL)
|
-+-----------------------+----------------------+---------------+-------------------+
-| 272500.0000 | 1398313.873626374 | 272500.0000 |
1398313.873626374 |
-+-----------------------+----------------------+---------------+-------------------+
++-----------------------+----------------------+---------------+---------------+
+| COVAR_POP(COMM, COMM) | COVAR_SAMP(SAL, SAL) | VAR_POP(COMM) | VAR_SAMP(SAL)
|
++-----------------------+----------------------+---------------+---------------+
+| 272500.0000 | 1398313.8736 | 272500.0000 | 1398313.8736
|
++-----------------------+----------------------+---------------+---------------+
(1 row)
!ok
@@ -2970,23 +2970,23 @@ from "scott".emp group by SAL;
# [CALCITE-1776, CALCITE-2402] COVAR_POP, COVAR_SAMP, VAR_SAMP, VAR_POP with
group by
SELECT
MONTH(HIREDATE) as "MONTH",
- covar_samp(SAL, COMM) as "COVAR_SAMP(SAL, COMM)",
- var_pop(COMM) as "VAR_POP(COMM)",
- var_samp(SAL) as "VAR_SAMP(SAL)"
+ covar_samp(CAST(SAL AS DECIMAL(12, 4)), CAST(COMM AS DECIMAL(12, 4))) as
"COVAR_SAMP(SAL, COMM)",
+ var_pop(CAST(COMM AS DECIMAL(12, 4))) as "VAR_POP(COMM)",
+ var_samp(CAST(SAL AS DECIMAL(12, 4))) as "VAR_SAMP(SAL)"
from "scott".emp
group by MONTH(HIREDATE);
-+-------+-----------------------+---------------+-------------------+
-| MONTH | COVAR_SAMP(SAL, COMM) | VAR_POP(COMM) | VAR_SAMP(SAL) |
-+-------+-----------------------+---------------+-------------------+
-| 1 | | | 1201250.0000 |
-| 11 | | | |
-| 12 | | | 1510833.333333334 |
-| 2 | -35000.0000 | 10000.0000 | 831458.333333335 |
-| 4 | | | |
-| 5 | | | |
-| 6 | | | |
-| 9 | -175000.0000 | 490000.0000 | 31250.0000 |
-+-------+-----------------------+---------------+-------------------+
++-------+-----------------------+---------------+---------------+
+| MONTH | COVAR_SAMP(SAL, COMM) | VAR_POP(COMM) | VAR_SAMP(SAL) |
++-------+-----------------------+---------------+---------------+
+| 1 | | | 1201250.0000 |
+| 11 | | | |
+| 12 | | | 1510833.3333 |
+| 2 | -35000.0000 | 10000.0000 | 831458.3333 |
+| 4 | | | |
+| 5 | | | |
+| 6 | | | |
+| 9 | -175000.0000 | 490000.0000 | 31250.0000 |
++-------+-----------------------+---------------+---------------+
(8 rows)
!ok
diff --git a/core/src/test/resources/sql/measure-paper.iq
b/core/src/test/resources/sql/measure-paper.iq
index f74c920d2b..59b03be9e5 100644
--- a/core/src/test/resources/sql/measure-paper.iq
+++ b/core/src/test/resources/sql/measure-paper.iq
@@ -56,7 +56,7 @@ GROUP BY "prodName";
+----------+---+--------------------+
| prodName | C | profitMargin |
+----------+---+--------------------+
-| Acme | 1 | 0.6 |
+| Acme | 1 | 0.60 |
| Happy | 3 | 0.4705882352941176 |
| Whizz | 1 | 0.6666666666666667 |
+----------+---+--------------------+
@@ -79,13 +79,13 @@ CREATE VIEW "SummarizedOrders" AS
SELECT "prodName", AVG("profitMargin") AS "m"
FROM "SummarizedOrders"
GROUP BY "prodName";
-+----------+--------------------+
-| prodName | m |
-+----------+--------------------+
-| Acme | 0.6 |
-| Happy | 0.5039682539682540 |
-| Whizz | 0.6666666666666667 |
-+----------+--------------------+
++----------+-----------------+
+| prodName | m |
++----------+-----------------+
+| Acme | 0.6000000000000 |
+| Happy | 0.5039682539682 |
+| Whizz | 0.6666666666666 |
++----------+-----------------+
(3 rows)
!ok
@@ -96,7 +96,7 @@ FROM "SummarizedOrders";
+----------+------------+--------------------+
| prodName | orderDate | profitMargin |
+----------+------------+--------------------+
-| Acme | 2023-11-27 | 0.6 |
+| Acme | 2023-11-27 | 0.60 |
| Happy | 2022-11-27 | 0.75 |
| Happy | 2023-11-28 | 0.3333333333333333 |
| Happy | 2024-11-28 | 0.4285714285714286 |
@@ -115,7 +115,7 @@ GROUP BY "prodName", "custName";
+----------+----------+--------------------+
| prodName | custName | profitMargin |
+----------+----------+--------------------+
-| Acme | Bob | 0.6 |
+| Acme | Bob | 0.60 |
| Happy | Alice | 0.3846153846153846 |
| Happy | Bob | 0.75 |
| Whizz | Celia | 0.6666666666666667 |
@@ -133,7 +133,7 @@ GROUP BY "prodName";
+----------+--------------------+
| prodName | profitMargin |
+----------+--------------------+
-| Acme | 0.6 |
+| Acme | 0.60 |
| Happy | 0.4705882352941176 |
| Whizz | 0.6666666666666667 |
+----------+--------------------+
@@ -150,7 +150,7 @@ GROUP BY "prodName", "orderDate", "custName";
+----------+------------+----------+--------------------+
| prodName | orderDate | custName | profitMargin |
+----------+------------+----------+--------------------+
-| Acme | 2023-11-27 | Bob | 0.6 |
+| Acme | 2023-11-27 | Bob | 0.60 |
| Happy | 2022-11-27 | Bob | 0.75 |
| Happy | 2023-11-28 | Alice | 0.3333333333333333 |
| Happy | 2024-11-28 | Alice | 0.4285714285714286 |
@@ -227,7 +227,7 @@ GROUP BY "prodName";
+----------+--------------------+-------+
| prodName | profitMargin | count |
+----------+--------------------+-------+
-| Acme | 0.6 | 1 |
+| Acme | 0.60 | 1 |
| Happy | 0.4705882352941176 | 3 |
| Whizz | 0.6666666666666667 | 1 |
+----------+--------------------+-------+
diff --git a/core/src/test/resources/sql/measure.iq
b/core/src/test/resources/sql/measure.iq
index 0d5ccf190c..056537d1f7 100644
--- a/core/src/test/resources/sql/measure.iq
+++ b/core/src/test/resources/sql/measure.iq
@@ -30,15 +30,15 @@ from emp;
select job, avg_sal as a
from empm
group by job;
-+-----------+-------------------+
-| JOB | A |
-+-----------+-------------------+
-| ANALYST | 3000.00 |
-| CLERK | 1037.50 |
-| MANAGER | 2758.333333333333 |
-| PRESIDENT | 5000.00 |
-| SALESMAN | 1400.00 |
-+-----------+-------------------+
++-----------+---------+
+| JOB | A |
++-----------+---------+
+| ANALYST | 3000.00 |
+| CLERK | 1037.50 |
+| MANAGER | 2758.33 |
+| PRESIDENT | 5000.00 |
+| SALESMAN | 1400.00 |
++-----------+---------+
(5 rows)
!ok
@@ -50,15 +50,15 @@ from (
from "scott".emp
) as empm
group by job;
-+-----------+-------------------+
-| JOB | A |
-+-----------+-------------------+
-| ANALYST | 3000.00 |
-| CLERK | 1037.50 |
-| MANAGER | 2758.333333333333 |
-| PRESIDENT | 5000.00 |
-| SALESMAN | 1400.00 |
-+-----------+-------------------+
++-----------+---------+
+| JOB | A |
++-----------+---------+
+| ANALYST | 3000.00 |
+| CLERK | 1037.50 |
+| MANAGER | 2758.33 |
+| PRESIDENT | 5000.00 |
+| SALESMAN | 1400.00 |
++-----------+---------+
(5 rows)
!ok
@@ -71,15 +71,15 @@ with empm as (
select job, avg_sal as a
from empm
group by job;
-+-----------+-------------------+
-| JOB | A |
-+-----------+-------------------+
-| ANALYST | 3000.00 |
-| CLERK | 1037.50 |
-| MANAGER | 2758.333333333333 |
-| PRESIDENT | 5000.00 |
-| SALESMAN | 1400.00 |
-+-----------+-------------------+
++-----------+---------+
+| JOB | A |
++-----------+---------+
+| ANALYST | 3000.00 |
+| CLERK | 1037.50 |
+| MANAGER | 2758.33 |
+| PRESIDENT | 5000.00 |
+| SALESMAN | 1400.00 |
++-----------+---------+
(5 rows)
!ok
@@ -88,15 +88,15 @@ group by job;
select job, aggregate(avg_sal) as a
from empm
group by job;
-+-----------+-------------------+
-| JOB | A |
-+-----------+-------------------+
-| ANALYST | 3000.00 |
-| CLERK | 1037.50 |
-| MANAGER | 2758.333333333333 |
-| PRESIDENT | 5000.00 |
-| SALESMAN | 1400.00 |
-+-----------+-------------------+
++-----------+---------+
+| JOB | A |
++-----------+---------+
+| ANALYST | 3000.00 |
+| CLERK | 1037.50 |
+| MANAGER | 2758.33 |
+| PRESIDENT | 5000.00 |
+| SALESMAN | 1400.00 |
++-----------+---------+
(5 rows)
!ok
@@ -108,15 +108,15 @@ with empm as (
select job, aggregate(avg_sal) as a
from empm
group by job;
-+-----------+-------------------+
-| JOB | A |
-+-----------+-------------------+
-| ANALYST | 3000.00 |
-| CLERK | 1037.50 |
-| MANAGER | 2758.333333333333 |
-| PRESIDENT | 5000.00 |
-| SALESMAN | 1400.00 |
-+-----------+-------------------+
++-----------+---------+
+| JOB | A |
++-----------+---------+
+| ANALYST | 3000.00 |
+| CLERK | 1037.50 |
+| MANAGER | 2758.33 |
+| PRESIDENT | 5000.00 |
+| SALESMAN | 1400.00 |
++-----------+---------+
(5 rows)
!ok
@@ -125,11 +125,11 @@ group by job;
select avg_sal as a
from empm
group by ();
-+-------------------+
-| A |
-+-------------------+
-| 2073.214285714286 |
-+-------------------+
++---------+
+| A |
++---------+
+| 2073.21 |
++---------+
(1 row)
!ok
@@ -137,11 +137,11 @@ group by ();
# Same as previous; 'group by ()' is implicit when we use AGGREGATE.
select AGGREGATE(avg_sal) as a
from empm;
-+-------------------+
-| A |
-+-------------------+
-| 2073.214285714286 |
-+-------------------+
++---------+
+| A |
++---------+
+| 2073.21 |
++---------+
(1 row)
!ok
@@ -149,11 +149,11 @@ from empm;
# Similar to previous; 'group by ()' is implicit when we use COUNT.
select avg_sal as a, COUNT(*) AS c
from empm;
-+-------------------+----+
-| A | C |
-+-------------------+----+
-| 2073.214285714286 | 14 |
-+-------------------+----+
++---------+----+
+| A | C |
++---------+----+
+| 2073.21 | 14 |
++---------+----+
(1 row)
!ok
@@ -468,13 +468,13 @@ select deptno,
avg(sal) filter (where job in ('ANALYST', 'SALESMAN')) as avg_sal1
from "scott".emp
group by deptno;
-+--------+---+-------------------+----+----------+
-| DEPTNO | C | AVG_SAL | C1 | AVG_SAL1 |
-+--------+---+-------------------+----+----------+
-| 10 | 3 | 2916.666666666667 | 0 | |
-| 20 | 5 | 2175.00 | 2 | 3000.00 |
-| 30 | 6 | 1566.666666666667 | 4 | 1400.00 |
-+--------+---+-------------------+----+----------+
++--------+---+---------+----+----------+
+| DEPTNO | C | AVG_SAL | C1 | AVG_SAL1 |
++--------+---+---------+----+----------+
+| 10 | 3 | 2916.66 | 0 | |
+| 20 | 5 | 2175.00 | 2 | 3000.00 |
+| 30 | 6 | 1566.66 | 4 | 1400.00 |
++--------+---+---------+----+----------+
(3 rows)
!ok
@@ -519,13 +519,13 @@ from (
where job <> 'PRESIDENT')
where deptno < 25
group by job;
-+---------+---+-------------------+----+----------+
-| JOB | C | AVG_SAL | C2 | AVG_SAL2 |
-+---------+---+-------------------+----+----------+
-| ANALYST | 2 | 3000.00 | 2 | 3000.00 |
-| CLERK | 4 | 1037.50 | 2 | 950.00 |
-| MANAGER | 3 | 2758.333333333333 | 1 | 2975.00 |
-+---------+---+-------------------+----+----------+
++---------+---+---------+----+----------+
+| JOB | C | AVG_SAL | C2 | AVG_SAL2 |
++---------+---+---------+----+----------+
+| ANALYST | 2 | 3000.00 | 2 | 3000.00 |
+| CLERK | 4 | 1037.50 | 2 | 950.00 |
+| MANAGER | 3 | 2758.33 | 1 | 2975.00 |
++---------+---+---------+----+----------+
(3 rows)
!ok
diff --git a/core/src/test/resources/sql/misc.iq
b/core/src/test/resources/sql/misc.iq
index 235581d388..224d6a1d13 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -2439,13 +2439,13 @@ FROM (VALUES (0, 2, 4, 8),
(1, 2, 4, 8),
(CAST(null as int), CAST(null as int), CAST(null as int), CAST(null as
int))) AS T(A,B,C,D);
V
-13.0
-9.5
-1.75
-1.875
+13.00000000
+9.50000000
+1.75000000
+1.87500000
null
-0
-14
+0E-8
+14.00000000
!ok
# TIMESTAMP literals without a time part are OK.
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index e65eaf0a84..2201dea8de 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -2847,7 +2847,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS
NULL($t1)], DEPTNO=[$t0], $condi
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -2873,7 +2873,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS
NULL($t1)], DEPTNO=[$t0], U=[$t2
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -2899,7 +2899,7 @@ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT
NULL($t1)], DEPTNO=[$t0], U=
EnumerableAggregate(group=[{0}])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[1],
expr#5=[>($t2, $t4)], i=[$t3], $condition=[$t5])
EnumerableAggregate(group=[{5, 7}], c=[COUNT()])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t5):DECIMAL(12,
2)], expr#9=[3000.00:DECIMAL(12, 2)], expr#10=[=($t8, $t9)], expr#11=[IS NOT
NULL($t7)], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}], $condition=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3147,7 +3147,7 @@ select * from "scott".emp where comm in (300, 500, null);
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300:DECIMAL(12, 2), 500:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], proj#0..7=[{exprs}], $condition=[$t10])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3175,7 +3175,7 @@ select *, comm in (300, 500, null) as i from "scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300:DECIMAL(12, 2), 500:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[OR($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[300.00:DECIMAL(12, 2), 500.00:DECIMAL(12, 2)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[OR($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3216,7 +3216,7 @@ select *, comm not in (300, 500, null) as i from
"scott".emp;
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300:DECIMAL(12, 2)), (300:DECIMAL(12, 2)..500:DECIMAL(12,
2)), (500:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)], expr#10=[SEARCH($t8, $t9)],
expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}],
I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
@@ -3243,7 +3243,7 @@ select *, (comm <> 300 and comm <> 500 and comm <> null)
as i from "scott".emp;
(14 rows)
!ok
-EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300:DECIMAL(12, 2)), (300:DECIMAL(12, 2)..500:DECIMAL(12,
2)), (500:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)], expr#10=[SEARCH($t8, $t9)],
expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)], proj#0..7=[{exprs}],
I=[$t12])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t6):DECIMAL(12, 2)],
expr#9=[Sarg[(-∞..300.00:DECIMAL(12, 2)), (300.00:DECIMAL(12,
2)..500.00:DECIMAL(12, 2)), (500.00:DECIMAL(12, 2)..+∞)]:DECIMAL(12, 2)],
expr#10=[SEARCH($t8, $t9)], expr#11=[null:BOOLEAN], expr#12=[AND($t10, $t11)],
proj#0..7=[{exprs}], I=[$t12])
EnumerableTableScan(table=[[scott, EMP]])
!plan
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index 963c38eb67..ddd6dfaae1 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -80,41 +80,41 @@ from emp;
# STDDEV applied to nullable column
select empno,
- stddev(comm) over (order by empno rows unbounded preceding) as stdev
+ stddev(CAST(comm AS DECIMAL(12, 4))) over (order by empno rows unbounded
preceding) as stdev
from emp
where deptno = 30
order by 1;
-+-------+-------------------------------------------------+
-| EMPNO | STDEV |
-+-------+-------------------------------------------------+
-| 7499 | |
-| 7521 | 141.421356237309510106570087373256683349609375 |
-| 7654 | 585.9465277082316561063635163009166717529296875 |
-| 7698 | 585.9465277082316561063635163009166717529296875 |
-| 7844 | 602.7713773341707792496890760958194732666015625 |
-| 7900 | 602.7713773341707792496890760958194732666015625 |
-+-------+-------------------------------------------------+
++-------+----------+
+| EMPNO | STDEV |
++-------+----------+
+| 7499 | |
+| 7521 | 141.4213 |
+| 7654 | 585.9465 |
+| 7698 | 585.9465 |
+| 7844 | 602.7713 |
+| 7900 | 602.7713 |
++-------+----------+
(6 rows)
!ok
# [CALCITE-5931] Allow integers like 1.00 in window frame
select empno,
- stddev(comm) over (order by empno rows 2 preceding) as stdev_2int,
- stddev(comm) over (order by empno rows 2.00 preceding) as stdev_2double
+ stddev(CAST(comm AS DECIMAL(12, 4))) over (order by empno rows 2 preceding)
as stdev_2int,
+ stddev(CAST(comm AS DECIMAL(12, 4))) over (order by empno rows 2.00
preceding) as stdev_2double
from emp
where deptno = 30
order by 1;
-+-------+-------------------------------------------------+-------------------------------------------------+
-| EMPNO | STDEV_2INT | STDEV_2DOUBLE
|
-+-------+-------------------------------------------------+-------------------------------------------------+
-| 7499 | |
|
-| 7521 | 141.421356237309510106570087373256683349609375 |
141.421356237309510106570087373256683349609375 |
-| 7654 | 585.9465277082316561063635163009166717529296875 |
585.9465277082316561063635163009166717529296875 |
-| 7698 | 636.3961030678927954795653931796550750732421875 |
636.3961030678927954795653931796550750732421875 |
-| 7844 | 989.949493661166570745990611612796783447265625 |
989.949493661166570745990611612796783447265625 |
-| 7900 | |
|
-+-------+-------------------------------------------------+-------------------------------------------------+
++-------+------------+---------------+
+| EMPNO | STDEV_2INT | STDEV_2DOUBLE |
++-------+------------+---------------+
+| 7499 | | |
+| 7521 | 141.4213 | 141.4213 |
+| 7654 | 585.9465 | 585.9465 |
+| 7698 | 636.3961 | 636.3961 |
+| 7844 | 989.9494 | 989.9494 |
+| 7900 | | |
++-------+------------+---------------+
(6 rows)
!ok
@@ -883,24 +883,24 @@ select empno,
sum(sal) over (order by empno rows between unbounded preceding and unbounded
following) as e5
from emp
order by 1;
-+-------+-------------------------------------------------+----+----------+----------+----------+
-| EMPNO | E1 | E2 | E3 | E4
| E5 |
-+-------+-------------------------------------------------+----+----------+----------+----------+
-| 7369 | 585.9465277082316561063635163009166717529296875 | 5 | 29025.00 |
29025.00 | 29025.00 |
-| 7499 | 636.3961030678927954795653931796550750732421875 | 6 | 29025.00 |
29025.00 | 29025.00 |
-| 7521 | 777.8174593052023055861354805529117584228515625 | 7 | 29025.00 |
29025.00 | 29025.00 |
-| 7566 | 585.9465277082316561063635163009166717529296875 | 7 | 28225.00 |
29025.00 | 29025.00 |
-| 7654 | 353.5533905932737752664252184331417083740234375 | 7 | 26625.00 |
29025.00 | 29025.00 |
-| 7698 | 989.949493661166570745990611612796783447265625 | 7 | 25375.00 |
29025.00 | 29025.00 |
-| 7782 | 989.949493661166570745990611612796783447265625 | 7 | 22400.00 |
29025.00 | 29025.00 |
-| 7788 | | 7 | 21150.00 |
29025.00 | 29025.00 |
-| 7839 | | 7 | 18300.00 |
29025.00 | 29025.00 |
-| 7844 | | 6 | 15850.00 |
29025.00 | 29025.00 |
-| 7876 | | 5 | 12850.00 |
29025.00 | 29025.00 |
-| 7900 | | 4 | 7850.00 |
29025.00 | 29025.00 |
-| 7902 | | 3 | 6350.00 |
29025.00 | 29025.00 |
-| 7934 | | 2 | 5250.00 |
29025.00 | 29025.00 |
-+-------+-------------------------------------------------+----+----------+----------+----------+
++-------+--------+----+----------+----------+----------+
+| EMPNO | E1 | E2 | E3 | E4 | E5 |
++-------+--------+----+----------+----------+----------+
+| 7369 | 585.94 | 5 | 29025.00 | 29025.00 | 29025.00 |
+| 7499 | 636.39 | 6 | 29025.00 | 29025.00 | 29025.00 |
+| 7521 | 777.81 | 7 | 29025.00 | 29025.00 | 29025.00 |
+| 7566 | 585.94 | 7 | 28225.00 | 29025.00 | 29025.00 |
+| 7654 | 353.55 | 7 | 26625.00 | 29025.00 | 29025.00 |
+| 7698 | 989.94 | 7 | 25375.00 | 29025.00 | 29025.00 |
+| 7782 | 989.94 | 7 | 22400.00 | 29025.00 | 29025.00 |
+| 7788 | | 7 | 21150.00 | 29025.00 | 29025.00 |
+| 7839 | | 7 | 18300.00 | 29025.00 | 29025.00 |
+| 7844 | | 6 | 15850.00 | 29025.00 | 29025.00 |
+| 7876 | | 5 | 12850.00 | 29025.00 | 29025.00 |
+| 7900 | | 4 | 7850.00 | 29025.00 | 29025.00 |
+| 7902 | | 3 | 6350.00 | 29025.00 | 29025.00 |
+| 7934 | | 2 | 5250.00 | 29025.00 | 29025.00 |
++-------+--------+----+----------+----------+----------+
(14 rows)
!ok
diff --git a/core/src/test/resources/sql/within-distinct.iq
b/core/src/test/resources/sql/within-distinct.iq
index ea8c5ec554..63264060be 100644
--- a/core/src/test/resources/sql/within-distinct.iq
+++ b/core/src/test/resources/sql/within-distinct.iq
@@ -304,18 +304,18 @@ ORDER BY 1, 2;
!}
SELECT deptno,
- avg(sal) AS avg_sal,
- avg(sal) WITHIN DISTINCT (job) AS avg_job_sal
+ avg(CAST(sal AS DECIMAL(10, 2))) AS avg_sal,
+ avg(CAST(sal AS DECIMAL(10, 2))) WITHIN DISTINCT (job) AS avg_job_sal
from emp3
group by deptno
order by deptno;
-+--------+---------+-------------------+
-| DEPTNO | AVG_SAL | AVG_JOB_SAL |
-+--------+---------+-------------------+
-| 10 | 2750.00 | 2750.00 |
-| 20 | 2010.00 | 2083.333333333333 |
-| 30 | 1375.00 | 1500.00 |
-+--------+---------+-------------------+
++--------+---------+-------------+
+| DEPTNO | AVG_SAL | AVG_JOB_SAL |
++--------+---------+-------------+
+| 10 | 2750.00 | 2750.00 |
+| 20 | 2010.00 | 2083.33 |
+| 30 | 1375.00 | 1500.00 |
++--------+---------+-------------+
(3 rows)
!ok
@@ -344,17 +344,18 @@ select * from job_salary order by job;
# Query on the normalized (emp, job_salary) tables give same result as the
query on the
# denormalized emp table. Of course.
-select e.deptno, avg(j.sal) as avg_sal, avg(j.sal) within distinct (j.job) as
avg_job_sal
+select e.deptno, avg(CAST(j.sal AS DECIMAL(10, 2))) as avg_sal,
+ avg(CAST(j.sal AS DECIMAL(10, 2))) within distinct (j.job) as
avg_job_sal
from emp as e join job_salary as j on e.job = j.job
group by deptno
order by deptno;
-+--------+---------+-------------------+
-| DEPTNO | AVG_SAL | AVG_JOB_SAL |
-+--------+---------+-------------------+
-| 10 | 2750.00 | 2750.00 |
-| 20 | 2010.00 | 2083.333333333333 |
-| 30 | 1375.00 | 1500.00 |
-+--------+---------+-------------------+
++--------+---------+-------------+
+| DEPTNO | AVG_SAL | AVG_JOB_SAL |
++--------+---------+-------------+
+| 10 | 2750.00 | 2750.00 |
+| 20 | 2010.00 | 2083.33 |
+| 30 | 1375.00 | 1500.00 |
++--------+---------+-------------+
(3 rows)
!ok
@@ -428,9 +429,9 @@ select * from orders;
+----------+-------------+---------+----------+
| ORDER_ID | CUSTOMER_ID | PAYMENT | SHIPPING |
+----------+-------------+---------+----------+
-| 1 | 100 | cash | 10 |
-| 2 | 100 | visa | 20 |
-| 3 | 101 | cash | 12 |
+| 1 | 100 | cash | 10.00 |
+| 2 | 100 | visa | 20.00 |
+| 3 | 101 | cash | 12.00 |
+----------+-------------+---------+----------+
(3 rows)
@@ -445,12 +446,12 @@ order by order_id, product, units;
+-------------+----------+---------+----------+---------+-------+-------+-----+-------+
| CUSTOMER_ID | ORDER_ID | PAYMENT | SHIPPING | PRODUCT | UNITS | NAME | AGE
| STATE |
+-------------+----------+---------+----------+---------+-------+-------+-----+-------+
-| 100 | 1 | cash | 10 | apple | 3 | Fred | 25
| CA |
-| 100 | 1 | cash | 10 | orange | 1 | Fred | 25
| CA |
-| 100 | 2 | visa | 20 | banana | 2 | Fred | 25
| CA |
-| 100 | 2 | visa | 20 | banana | 6 | Fred | 25
| CA |
-| 100 | 2 | visa | 20 | orange | 5 | Fred | 25
| CA |
-| 101 | 3 | cash | 12 | mango | 7 | Velma | 17
| NV |
+| 100 | 1 | cash | 10.00 | apple | 3 | Fred | 25
| CA |
+| 100 | 1 | cash | 10.00 | orange | 1 | Fred | 25
| CA |
+| 100 | 2 | visa | 20.00 | banana | 2 | Fred | 25
| CA |
+| 100 | 2 | visa | 20.00 | banana | 6 | Fred | 25
| CA |
+| 100 | 2 | visa | 20.00 | orange | 5 | Fred | 25
| CA |
+| 101 | 3 | cash | 12.00 | mango | 7 | Velma | 17
| NV |
+-------------+----------+---------+----------+---------+-------+-------+-----+-------+
(6 rows)
@@ -466,7 +467,7 @@ join order_items using (order_id);
+-------+--------------+-----------+
| count | sum_shipping | sum_units |
+-------+--------------+-----------+
-| 6 | 92 | 24 |
+| 6 | 92.00 | 24 |
+-------+--------------+-----------+
(1 row)
@@ -486,10 +487,10 @@ order by product;
+---------+-------+--------------+-----------+
| PRODUCT | count | sum_shipping | sum_units |
+---------+-------+--------------+-----------+
-| apple | 1 | 10 | 3 |
-| banana | 2 | 40 | 8 |
-| mango | 1 | 12 | 7 |
-| orange | 2 | 30 | 6 |
+| apple | 1 | 10.00 | 3 |
+| banana | 2 | 40.00 | 8 |
+| mango | 1 | 12.00 | 7 |
+| orange | 2 | 30.00 | 6 |
+---------+-------+--------------+-----------+
(4 rows)
@@ -507,10 +508,10 @@ order by product;
+---------+-------+--------------+-----------+
| PRODUCT | count | sum_shipping | sum_units |
+---------+-------+--------------+-----------+
-| apple | 1 | 10 | 3 |
-| banana | 2 | 20 | 8 |
-| mango | 1 | 12 | 7 |
-| orange | 2 | 30 | 6 |
+| apple | 1 | 10.00 | 3 |
+| banana | 2 | 20.00 | 8 |
+| mango | 1 | 12.00 | 7 |
+| orange | 2 | 30.00 | 6 |
+---------+-------+--------------+-----------+
(4 rows)
@@ -535,10 +536,10 @@ order by product;
+---------+-------+--------------+-----------+
| PRODUCT | count | sum_shipping | sum_units |
+---------+-------+--------------+-----------+
-| apple | 1 | 10 | 3 |
-| banana | 2 | 20 | 8 |
-| mango | 1 | 12 | 7 |
-| orange | 2 | 30 | 6 |
+| apple | 1 | 10.00 | 3 |
+| banana | 2 | 20.00 | 8 |
+| mango | 1 | 12.00 | 7 |
+| orange | 2 | 30.00 | 6 |
+---------+-------+--------------+-----------+
(4 rows)
@@ -558,8 +559,8 @@ order by payment;
+---------+-------+-------------+--------------+-----------+
| PAYMENT | count | order_count | sum_shipping | sum_units |
+---------+-------+-------------+--------------+-----------+
-| cash | 3 | 2 | 32 | 11 |
-| visa | 3 | 1 | 60 | 13 |
+| cash | 3 | 2 | 32.00 | 11 |
+| visa | 3 | 1 | 60.00 | 13 |
+---------+-------+-------------+--------------+-----------+
(2 rows)
@@ -606,8 +607,8 @@ group by payment;
+---------+-------+-------------+--------------+-----------+
| PAYMENT | count | order_count | sum_shipping | sum_units |
+---------+-------+-------------+--------------+-----------+
-| cash | 3 | 2 | 22 | 11 |
-| visa | 2 | 1 | 20 | 13 |
+| cash | 3 | 2 | 22.00 | 11 |
+| visa | 2 | 1 | 20.00 | 13 |
+---------+-------+-------------+--------------+-----------+
(2 rows)
@@ -639,8 +640,8 @@ group by payment;
+---------+-------+-------------+--------------+-----------+
| PAYMENT | count | order_count | sum_shipping | sum_units |
+---------+-------+-------------+--------------+-----------+
-| cash | 3 | 2 | 22 | 11 |
-| visa | 2 | 1 | 20 | 13 |
+| cash | 3 | 2 | 22.00 | 11 |
+| visa | 2 | 1 | 20.00 | 13 |
+---------+-------+-------------+--------------+-----------+
(2 rows)
@@ -691,8 +692,8 @@ group by payment;
+---------+-------+-------------+--------------+-----------+
| PAYMENT | count | order_count | sum_shipping | sum_units |
+---------+-------+-------------+--------------+-----------+
-| cash | 3 | 2 | 22 | 11 |
-| visa | 2 | 1 | 20 | 13 |
+| cash | 3 | 2 | 22.00 | 11 |
+| visa | 2 | 1 | 20.00 | 13 |
+---------+-------+-------------+--------------+-----------+
(2 rows)
@@ -743,10 +744,10 @@ order by product;
+---------+-------+-------------+--------------+-----------+
| PRODUCT | count | order_count | sum_shipping | sum_units |
+---------+-------+-------------+--------------+-----------+
-| apple | 1 | 1 | 10 | 3 |
-| banana | 2 | 1 | 20 | 8 |
-| mango | 1 | 1 | 12 | 7 |
-| orange | 2 | 2 | 30 | 6 |
+| apple | 1 | 1 | 10.00 | 3 |
+| banana | 2 | 1 | 20.00 | 8 |
+| mango | 1 | 1 | 12.00 | 7 |
+| orange | 2 | 2 | 30.00 | 6 |
+---------+-------+-------------+--------------+-----------+
(4 rows)
@@ -792,8 +793,8 @@ order by o.payment;
+---------+----------------+-------------+------------------+---------+--------------+-----------+
| PAYMENT | customer_count | order_count | order_item_count | sum_age |
sum_shipping | sum_units |
+---------+----------------+-------------+------------------+---------+--------------+-----------+
-| cash | 2 | 2 | 3 | 67 |
32 | 11 |
-| visa | 1 | 1 | 3 | 75 |
60 | 13 |
+| cash | 2 | 2 | 3 | 67 |
32.00 | 11 |
+| visa | 1 | 1 | 3 | 75 |
60.00 | 13 |
+---------+----------------+-------------+------------------+---------+--------------+-----------+
(2 rows)
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
b/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
index 7bf53579db..8c522b06a0 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapter2IT.java
@@ -1909,10 +1909,10 @@ public class DruidAdapter2IT {
+ "end as b from \"foodmart\" group by \"store_state\" order by a
desc";
final String postAggString =
"'postAggregations':[{'type':'expression','name':'A',"
+ "'expression':'(\\'$f1\\' /
\\'$f2\\')'},{'type':'expression','name':'B',"
- + "'expression':'case_searched((\\'$f3\\' == 0),1.0,CAST(\\'$f3\\'";
+ + "'expression':'case_searched((\\'$f3\\' == 0),1,CAST(\\'$f3\\'";
final String plan = "PLAN="
+ "EnumerableInterpreter\n"
- + " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]],
projects=[[$63, $90, $91, $89]], groups=[{0}], aggs=[[SUM($1), SUM($2),
SUM($3)]], post_projects=[[$0, /($1, $2), CASE(=($3, 0), 1.0:DECIMAL(19, 0),
CAST($3):DECIMAL(19, 0))]], sort0=[1], dir0=[DESC])\n";
+ + " DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/2992-01-10T00:00:00.000Z]],
projects=[[$63, $90, $91, $89]], groups=[{0}], aggs=[[SUM($1), SUM($2),
SUM($3)]], post_projects=[[$0, /($1, $2), CASE(=($3, 0), 1:DECIMAL(19, 0),
CAST($3):DECIMAL(19, 0))]], sort0=[1], dir0=[DESC])\n";
CalciteAssert.AssertQuery q = sql(sqlQuery)
.explainContains(plan)
.queryContains(new DruidChecker(postAggString));
@@ -3285,8 +3285,8 @@ public class DruidAdapter2IT {
sql(sql).runs().queryContains(
new DruidChecker(
false,
-
"\"filter\":{\"type\":\"bound\",\"dimension\":\"product_id\",\"lower\":\"16.0\","
- + "\"lowerStrict\":false,\"upper\":\"16.0\","
+ "\"filter\":{\"type\":\"bound\",\"dimension\":\"product_id\","
+ +
"\"lower\":\"16.000000000\",\"lowerStrict\":false,\"upper\":\"16.000000000\","
+ "\"upperStrict\":false,\"ordering\":\"numeric\"}"));
}
diff --git a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
index b3d62632c0..200c93efe5 100644
--- a/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
+++ b/druid/src/test/java/org/apache/calcite/test/DruidAdapterIT.java
@@ -2215,12 +2215,12 @@ public class DruidAdapterIT {
+ "from \"foodmart\" group by \"store_state\" order by a desc";
final String postAggString =
"'postAggregations':[{'type':'expression','name':'A',"
+ "'expression':'(\\'$f1\\' /
\\'$f2\\')'},{'type':'expression','name':'B',"
- + "'expression':'case_searched((\\'$f3\\' == 0),1.0,CAST(\\'$f3\\'";
+ + "'expression':'case_searched((\\'$f3\\' == 0),1,CAST(\\'$f3\\'";
final String plan =
"DruidQuery(table=[[foodmart, foodmart]],
intervals=[[1900-01-09T00:00:00.000Z/"
+ "2992-01-10T00:00:00.000Z]], projects=[[$63, $90, $91, $89]],
groups=[{0}], "
+ "aggs=[[SUM($1), SUM($2), SUM($3)]], post_projects=[[$0, /($1,
$2), "
- + "CASE(=($3, 0), 1.0:DECIMAL(19, 0), CAST($3):DECIMAL(19, 0))]],
sort0=[1], dir0=[DESC])";
+ + "CASE(=($3, 0), 1:DECIMAL(19, 0), CAST($3):DECIMAL(19, 0))]],
sort0=[1], dir0=[DESC])";
CalciteAssert.AssertQuery q = sql(sqlQuery, FOODMART)
.explainContains(plan)
.queryContains(new DruidChecker(postAggString));
@@ -3942,8 +3942,8 @@ public class DruidAdapterIT {
sql(sql, FOODMART).runs().queryContains(
new DruidChecker(
false,
-
"\"filter\":{\"type\":\"bound\",\"dimension\":\"product_id\",\"lower\":\"16.0\","
- + "\"lowerStrict\":false,\"upper\":\"16.0\","
+ "\"filter\":{\"type\":\"bound\",\"dimension\":\"product_id\","
+ +
"\"lower\":\"16.000000000\",\"lowerStrict\":false,\"upper\":\"16.000000000\","
+ "\"upperStrict\":false,\"ordering\":\"numeric\"}"));
}
diff --git
a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
index f3a889c122..61de692755 100644
---
a/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
+++
b/innodb/src/test/java/org/apache/calcite/adapter/innodb/InnodbAdapterTest.java
@@ -913,7 +913,7 @@ public class InnodbAdapterTest {
sql("SELECT * FROM \"EMP\" WHERE DEPTNO = 30 AND SAL = 1250 AND COMM =
500.00")
.explainContains("PLAN=InnodbToEnumerableConverter\n"
+ " InnodbFilter(condition=[(SK_POINT_QUERY,
index=DEPTNO_SAL_COMM_KEY, "
- + "DEPTNO=30,SAL=1250,COMM=500.00)])\n"
+ + "DEPTNO=30,SAL=1250.00,COMM=500.00)])\n"
+ " InnodbTableScan(table=[[test, EMP]])\n")
.returns(some(7521));
}
@@ -923,7 +923,7 @@ public class InnodbAdapterTest {
.explainContains("PLAN=InnodbToEnumerableConverter\n"
+ " InnodbProject(EMPNO=[$0], ENAME=[$1])\n"
+ " InnodbFilter(condition=[(SK_POINT_QUERY,
index=DEPTNO_SAL_COMM_KEY, "
- + "DEPTNO=30,SAL=1250,COMM=500.00)])\n"
+ + "DEPTNO=30,SAL=1250.00,COMM=500.00)])\n"
+ " InnodbTableScan(table=[[test, EMP]])")
.returns("EMPNO=7521; ENAME=WARD\n");
}
diff --git a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Primitive.java
b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Primitive.java
index 94578647e3..71e7d5aae1 100644
--- a/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Primitive.java
+++ b/linq4j/src/main/java/org/apache/calcite/linq4j/tree/Primitive.java
@@ -384,6 +384,7 @@ public enum Primitive {
}
}
+ /** Called from BuiltInMethod.INTEGER_CAST */
public static @Nullable Object integerCast(Primitive primitive, final Object
value) {
return requireNonNull(primitive, "primitive").numberValue((Number) value);
}
@@ -447,6 +448,35 @@ public enum Primitive {
return checkOverflow(result, precision, scale);
}
+ /** Called from BuiltInMethod.DECIMAL_DECIMAL_CAST */
+ public static @Nullable Object decimalDecimalCast(
+ @Nullable BigDecimal value, int precision, int scale) {
+ if (value == null) {
+ return null;
+ }
+ return checkOverflow(value, precision, scale);
+ }
+
+ /** Called from BuiltInMethod.INTEGER_DECIMAL_CAST */
+ public static @Nullable Object integerDecimalCast(
+ @Nullable Number value, int precision, int scale) {
+ if (value == null) {
+ return null;
+ }
+ final BigDecimal decimal = new BigDecimal(value.longValue());
+ return checkOverflow(decimal, precision, scale);
+ }
+
+ /** Called from BuiltInMethod.FP_DECIMAL_CAST */
+ public static @Nullable Object fpDecimalCast(
+ @Nullable Number value, int precision, int scale) {
+ if (value == null) {
+ return null;
+ }
+ final BigDecimal decimal = BigDecimal.valueOf(value.doubleValue());
+ return checkOverflow(decimal, precision, scale);
+ }
+
/**
* Converts a number into a value of the type specified by this primitive
* using the SQL CAST rules. If the value conversion causes loss of
significant digits,
diff --git a/site/_docs/history.md b/site/_docs/history.md
index d2ae580fdb..b9440ae2c5 100644
--- a/site/_docs/history.md
+++ b/site/_docs/history.md
@@ -43,6 +43,11 @@ z.
#### Breaking Changes
{: #breaking-1-38-0}
+In previous versions of Calcite the casts to DECIMAL types were
+treated as no-ops [CALCITE-6322]. Fixing this bug causes all
+calculations that use DECIMAL values to produce slightly different
+results.
+
Compatibility: This release is tested on Linux, macOS, Microsoft Windows;
using JDK/OpenJDK versions 8 to 19;
Guava versions 21.0 to 32.1.3-jre;
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 72b3559336..12d3961289 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -352,11 +352,6 @@ public class SqlOperatorTest {
Pattern.compile("(?s).*could not calculate results for the following "
+ "row.*PC=5 Code=2201F.*");
- /**
- * Whether DECIMAL type is implemented.
- */
- public static final boolean DECIMAL = false;
-
/** Function object that returns a string with 2 copies of each character.
* For example, {@code DOUBLER.apply("xy")} returns {@code "xxyy"}. */
private static final UnaryOperator<String> DOUBLER =
@@ -743,6 +738,29 @@ public class SqlOperatorTest {
"654342432412312");
}
+ /** Test cases for <a
href="https://issues.apache.org/jira/projects/CALCITE/issues/CALCITE-6322";>
+ * [CALCITE-6322] Casts to DECIMAL types are ignored</a>. */
+ @Test public void testIssue6322() {
+ SqlOperatorFixture f = fixture();
+ f.checkScalar("CAST(1.123 AS DECIMAL(4, 0))", "1", "DECIMAL(4, 0) NOT
NULL");
+ f.checkScalar("CAST(100 AS DECIMAL(3, 0))", "100", "DECIMAL(3, 0) NOT
NULL");
+ f.checkScalar("CAST(-100 AS DECIMAL(3, 0))", "-100", "DECIMAL(3, 0) NOT
NULL");
+ f.checkScalar("CAST(100 AS DECIMAL(5, 2))", "100.00", "DECIMAL(5, 2) NOT
NULL");
+ f.checkScalar("CAST(-100 AS DECIMAL(5, 2))", "-100.00", "DECIMAL(5, 2) NOT
NULL");
+ f.checkFails("CAST(1000 AS DECIMAL(2, 0))",
+ "Value 1000 cannot be represented as a DECIMAL\\(2, 0\\)", true);
+ f.checkFails("CAST(-1000 AS DECIMAL(2, 0))",
+ "Value -1000 cannot be represented as a DECIMAL\\(2, 0\\)", true);
+ f.checkScalar("CAST(100.5e0 AS DECIMAL(4, 1))", "100.5", "DECIMAL(4, 1)
NOT NULL");
+ f.checkScalar("CAST(-100.5e0 AS DECIMAL(4, 1))", "-100.5", "DECIMAL(4, 1)
NOT NULL");
+ f.checkScalar("CAST(100.55e0 AS DECIMAL(4, 1))", "100.5", "DECIMAL(4, 1)
NOT NULL");
+ f.checkScalar("CAST(-100.55e0 AS DECIMAL(4, 1))", "-100.5", "DECIMAL(4, 1)
NOT NULL");
+ f.checkFails("CAST(100.5e0 AS DECIMAL(4, 2))",
+ "Value 100.5 cannot be represented as a DECIMAL\\(4, 2\\)", true);
+ f.checkFails("CAST(-100.5e0 AS DECIMAL(4, 2))",
+ "Value -100.5 cannot be represented as a DECIMAL\\(4, 2\\)", true);
+ }
+
/**
* Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-5843";>
* Constant expression with nested casts causes a compiler crash</a>. */
@@ -939,23 +957,18 @@ public class SqlOperatorTest {
"cast(5 as interval year)",
"+5",
"INTERVAL YEAR NOT NULL");
- if (DECIMAL) {
- // Due to DECIMAL rounding bugs, currently returns "+5"
- f.checkScalar(
- "cast(5.7 as interval day)",
- "+6",
- "INTERVAL DAY NOT NULL");
- f.checkScalar(
- "cast(-5.7 as interval day)",
- "-6",
- "INTERVAL DAY NOT NULL");
- } else {
- // An easier case
- f.checkScalar(
- "cast(6.2 as interval day)",
- "+6",
- "INTERVAL DAY NOT NULL");
- }
+ f.checkScalar(
+ "cast(5.7 as interval day)",
+ "+6",
+ "INTERVAL DAY NOT NULL");
+ f.checkScalar(
+ "cast(-5.7 as interval day)",
+ "-6",
+ "INTERVAL DAY NOT NULL");
+ f.checkScalar(
+ "cast(6.2 as interval day)",
+ "+6",
+ "INTERVAL DAY NOT NULL");
f.checkScalar(
"cast(3456 as interval month(4))",
"+3456",
@@ -1159,9 +1172,7 @@ public class SqlOperatorTest {
// null
f.checkNull("cast(null as integer)");
- if (DECIMAL) {
- f.checkNull("cast(null as decimal(4,3))");
- }
+ f.checkNull("cast(null as decimal(4,3))");
f.checkNull("cast(null as double)");
f.checkNull("cast(null as varchar(10))");
f.checkNull("cast(null as char(10))");
@@ -1759,14 +1770,12 @@ public class SqlOperatorTest {
f.checkString("case 1 when 1 then cast('a' as varchar(1)) "
+ "when 2 then cast('bcd' as varchar(3)) end",
"a", "VARCHAR(3)");
- if (DECIMAL) {
- f.checkScalarExact("case 2 when 1 then 11.2 "
- + "when 2 then 4.543 else null end",
- "DECIMAL(5, 3)", "4.543");
- f.checkScalarExact("case 1 when 1 then 11.2 "
- + "when 2 then 4.543 else null end",
- "DECIMAL(5, 3)", "11.200");
- }
+ f.checkScalarExact("case 2 when 1 then 11.2 "
+ + "when 2 then 4.543 else null end",
+ "DECIMAL(5, 3)", "4.543");
+ f.checkScalarExact("case 1 when 1 then 11.2 "
+ + "when 2 then 4.543 else null end",
+ "DECIMAL(5, 3)", "11.200");
f.checkScalarExact("case 'a' when 'a' then 1 end", 1);
f.checkScalarApprox("case 1 when 1 then 11.2e0 "
+ "when 2 then cast(4 as bigint) else 3 end",
@@ -2593,9 +2602,6 @@ public class SqlOperatorTest {
f.checkScalarExact("12%-7", 5);
f.checkScalarExact("cast(12 as tinyint) % cast(-7 as tinyint)",
"TINYINT NOT NULL", "5");
- if (!DECIMAL) {
- return;
- }
f.checkScalarExact("cast(9 as decimal(2, 0)) % 7",
"INTEGER NOT NULL", "2");
f.checkScalarExact("7 % cast(9 as decimal(2, 0))",
@@ -2612,9 +2618,6 @@ public class SqlOperatorTest {
void checkModOperatorNull(SqlOperatorFixture f) {
f.checkNull("cast(null as integer) % 2");
f.checkNull("4 % cast(null as tinyint)");
- if (!DECIMAL) {
- return;
- }
f.checkNull("4 % cast(null as decimal(12,0))");
}
@@ -2642,15 +2645,12 @@ public class SqlOperatorTest {
f.checkScalarApprox(" 6.0 / cast(10.0 as real) ", "DOUBLE NOT NULL",
isExactly("0.6"));
f.checkScalarExact("10.0 / 5.0", "DECIMAL(9, 6) NOT NULL", "2");
- if (DECIMAL) {
- f.checkScalarExact("1.0 / 3.0", "DECIMAL(8, 6) NOT NULL", "0.333333");
- f.checkScalarExact("100.1 / 0.0001", "DECIMAL(14, 7) NOT NULL",
- "1001000.0000000");
- f.checkScalarExact("100.1 / 0.00000001", "DECIMAL(19, 8) NOT NULL",
- "10010000000.00000000");
- }
+ f.checkScalarExact("1.0 / 3.0", "DECIMAL(8, 6) NOT NULL",
"0.3333333333333333");
+ f.checkScalarExact("100.1 / 0.0001", "DECIMAL(14, 7) NOT NULL",
+ "1.001E+6");
+ f.checkScalarExact("100.1 / 0.00000001", "DECIMAL(19, 8) NOT NULL",
+ "1.001E+10");
f.checkNull("1e1 / cast(null as float)");
-
f.checkScalarExact("100.1 / 0.00000000000000001", "DECIMAL(19, 0) NOT
NULL",
"1.001E+19");
}
@@ -3116,9 +3116,6 @@ public class SqlOperatorTest {
}
@Test void testLessThanOperatorInterval() {
- if (!DECIMAL) {
- return;
- }
final SqlOperatorFixture f = fixture();
f.checkBoolean("interval '2' day < interval '1' day", false);
f.checkBoolean("interval '2' day < interval '5' day", true);
@@ -7169,9 +7166,6 @@ public class SqlOperatorTest {
f.checkScalarExact("mod(cast(12 as tinyint), cast(-7 as tinyint))",
"TINYINT NOT NULL", "5");
- if (!DECIMAL) {
- return;
- }
f.checkScalarExact("mod(cast(9 as decimal(2, 0)), 7)",
"INTEGER NOT NULL", "2");
f.checkScalarExact("mod(7, cast(9 as decimal(2, 0)))",
@@ -7185,16 +7179,13 @@ public class SqlOperatorTest {
final SqlOperatorFixture f = fixture();
f.checkNull("mod(cast(null as integer),2)");
f.checkNull("mod(4,cast(null as tinyint))");
- if (!DECIMAL) {
- return;
- }
f.checkNull("mod(4,cast(null as decimal(12,0)))");
}
@Test void testModFuncDivByZero() {
// The extra CASE expression is to fool Janino. It does constant
// reduction and will throw the divide by zero exception while
- // compiling the expression. The test frame work would then issue
+ // compiling the expression. The test framework would then issue
// unexpected exception occurred during "validation". You cannot
// submit as non-runtime because the janino exception does not have
// error position information and the framework is unhappy with that.
@@ -7904,7 +7895,7 @@ public class SqlOperatorTest {
f.checkScalar(fn + "(array[null, 1, cast(2 as decimal)])", "[2, 1,
null]",
"DECIMAL(19, 0) ARRAY NOT NULL");
f.checkScalar(fn + "(array[CAST(2.1 as decimal(17)),
2.1111111111111119])",
- "[2.1111111111111119, 2.1]", "DECIMAL(19, 2) NOT NULL ARRAY NOT
NULL");
+ "[2.11, 2.00]", "DECIMAL(19, 2) NOT NULL ARRAY NOT NULL");
f.checkScalar(fn + "(array[CAST(2.1 as double), 2.1111111111111119])",
"[2.111111111111112, 2.1]", "DOUBLE NOT NULL ARRAY NOT NULL");
f.checkScalar(fn + "(array[null])", "[null]", "NULL ARRAY NOT NULL");
@@ -9034,7 +9025,7 @@ public class SqlOperatorTest {
f.checkType("atanh('abc')", "DOUBLE NOT NULL");
f.checkScalarApprox("atanh(0.76159416)", "DOUBLE NOT NULL",
isWithin(1d, 0.0001d));
- f.checkScalarApprox("atanh(cast(-0.1 as decimal))", "DOUBLE NOT NULL",
+ f.checkScalarApprox("atanh(cast(-0.1 as decimal(2,1)))", "DOUBLE NOT
NULL",
isWithin(-0.1003d, 0.0001d));
f.checkNull("atanh(cast(null as integer))");
f.checkNull("atanh(cast(null as double))");
@@ -9112,7 +9103,7 @@ public class SqlOperatorTest {
isWithin(0.5d, 0.01d));
f.checkScalarApprox("cosd(-60)", "DOUBLE NOT NULL",
isWithin(0.5d, 0.01d));
- f.checkScalarApprox("cosd(cast(60 as decimal(1, 0)))", "DOUBLE NOT NULL",
+ f.checkScalarApprox("cosd(cast(60 as decimal(2, 0)))", "DOUBLE NOT NULL",
isWithin(0.5d, 0.01d));
f.checkScalarExact("cosd(cast('NaN' as double))", "DOUBLE NOT NULL",
"NaN");
@@ -9404,18 +9395,18 @@ public class SqlOperatorTest {
false);
f.checkType("round('abc', 'def')", "DECIMAL(19, 9) NOT NULL");
f.checkScalar("round(42, -1)", 40, "INTEGER NOT NULL");
- f.checkScalar("round(cast(42.346 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(4235, 2), "DECIMAL(2, 3) NOT NULL");
- f.checkScalar("round(cast(-42.346 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(-4235, 2), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("round(cast(42.346 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(4235, 2), "DECIMAL(5, 3) NOT NULL");
+ f.checkScalar("round(cast(-42.346 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(-4235, 2), "DECIMAL(5, 3) NOT NULL");
f.checkNull("round(cast(null as integer), 1)");
f.checkNull("round(cast(null as double), 1)");
f.checkNull("round(43.21, cast(null as integer))");
f.checkNull("round(cast(null as double))");
f.checkScalar("round(42)", 42, "INTEGER NOT NULL");
- f.checkScalar("round(cast(42.346 as decimal(2, 3)))",
- BigDecimal.valueOf(42, 0), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("round(cast(42.346 as decimal(5, 3)))",
+ BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkScalar("round(42.324)",
BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkScalar("round(42.724)",
@@ -9533,7 +9524,7 @@ public class SqlOperatorTest {
isWithin(0.5d, 0.01d));
f.checkScalarApprox("sind(-30)", "DOUBLE NOT NULL",
isWithin(-0.5d, 0.01d));
- f.checkScalarApprox("sind(cast(30 as decimal(1, 0)))", "DOUBLE NOT NULL",
+ f.checkScalarApprox("sind(cast(30 as decimal(2, 0)))", "DOUBLE NOT NULL",
isWithin(0.5d, 0.01d));
f.checkScalarExact("sin(cast('NaN' as double))", "DOUBLE NOT NULL",
"NaN");
@@ -9603,7 +9594,7 @@ public class SqlOperatorTest {
isWithin(1.73d, 1.74d));
f.checkScalarApprox("cosd(-60)", "DOUBLE NOT NULL",
isWithin(1.73d, 1.74d));
- f.checkScalarApprox("tand(cast(60 as decimal(1, 0)))", "DOUBLE NOT NULL",
+ f.checkScalarApprox("tand(cast(60 as decimal(3, 0)))", "DOUBLE NOT NULL",
isWithin(1.73d, 1.74d));
f.checkScalarExact("tand(cast('NaN' as double))", "DOUBLE NOT NULL",
"NaN");
@@ -9648,10 +9639,10 @@ public class SqlOperatorTest {
false);
f.checkType("trunc('abc', 'def')", "DECIMAL(19, 9) NOT NULL");
f.checkScalar("trunc(42, -1)", 40.0, "DOUBLE NOT NULL");
- f.checkScalar("trunc(cast(42.345 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(4234, 2), "DECIMAL(2, 3) NOT NULL");
- f.checkScalar("trunc(cast(-42.345 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(-4234, 2), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("trunc(cast(42.345 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(4234, 2), "DECIMAL(5, 3) NOT NULL");
+ f.checkScalar("trunc(cast(-42.345 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(-4234, 2), "DECIMAL(5, 3) NOT NULL");
f.checkNull("trunc(cast(null as integer), 1)");
f.checkNull("trunc(cast(null as double), 1)");
f.checkNull("trunc(43.21, cast(null as integer))");
@@ -9661,8 +9652,8 @@ public class SqlOperatorTest {
BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkScalar("trunc(cast(42.324 as float))", 42F,
"FLOAT NOT NULL");
- f.checkScalar("trunc(cast(42.345 as decimal(2, 3)))",
- BigDecimal.valueOf(42, 0), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("trunc(cast(42.345 as decimal(5, 3)))",
+ BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkNull("trunc(cast(null as integer))");
f.checkNull("trunc(cast(null as double))");
}
@@ -9683,10 +9674,10 @@ public class SqlOperatorTest {
false);
f.checkType("truncate('abc', 'def')", "DECIMAL(19, 9) NOT NULL");
f.checkScalar("truncate(42, -1)", 40, "INTEGER NOT NULL");
- f.checkScalar("truncate(cast(42.345 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(4234, 2), "DECIMAL(2, 3) NOT NULL");
- f.checkScalar("truncate(cast(-42.345 as decimal(2, 3)), 2)",
- BigDecimal.valueOf(-4234, 2), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("truncate(cast(42.345 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(4234, 2), "DECIMAL(5, 3) NOT NULL");
+ f.checkScalar("truncate(cast(-42.345 as decimal(5, 3)), 2)",
+ BigDecimal.valueOf(-4234, 2), "DECIMAL(5, 3) NOT NULL");
f.checkNull("truncate(cast(null as integer), 1)");
f.checkNull("truncate(cast(null as double), 1)");
f.checkNull("truncate(43.21, cast(null as integer))");
@@ -9696,8 +9687,8 @@ public class SqlOperatorTest {
BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkScalar("truncate(cast(42.324 as float))", 42F,
"FLOAT NOT NULL");
- f.checkScalar("truncate(cast(42.345 as decimal(2, 3)))",
- BigDecimal.valueOf(42, 0), "DECIMAL(2, 3) NOT NULL");
+ f.checkScalar("truncate(cast(42.345 as decimal(5, 3)))",
+ BigDecimal.valueOf(42, 0), "DECIMAL(5, 3) NOT NULL");
f.checkNull("truncate(cast(null as integer))");
f.checkNull("truncate(cast(null as double))");
}
@@ -9750,14 +9741,16 @@ public class SqlOperatorTest {
+ "cast(9223372036854775807 as bigint))");
f.checkNull("safe_add(cast(-20 as bigint), "
+ "cast(-9223372036854775807 as bigint))");
- f.checkNull("safe_add(9, cast(9.999999999999999999e75 as DECIMAL(38,
19)))");
- f.checkNull("safe_add(-9, cast(-9.999999999999999999e75 as DECIMAL(38,
19)))");
- f.checkNull("safe_add(cast(9.999999999999999999e75 as DECIMAL(38, 19)),
9)");
- f.checkNull("safe_add(cast(-9.999999999999999999e75 as DECIMAL(38, 19)),
-9)");
- f.checkNull("safe_add(cast(9.9e75 as DECIMAL(76, 0)), "
- + "cast(9.9e75 as DECIMAL(76, 0)))");
- f.checkNull("safe_add(cast(-9.9e75 as DECIMAL(76, 0)), "
- + "cast(-9.9e75 as DECIMAL(76, 0)))");
+ if (Bug.CALCITE_6328_FIXED) {
+ f.checkNull("safe_add(9, cast(9.999999999999999999e75 as DECIMAL(38,
19)))");
+ f.checkNull("safe_add(-9, cast(-9.999999999999999999e75 as DECIMAL(38,
19)))");
+ f.checkNull("safe_add(cast(9.999999999999999999e75 as DECIMAL(38, 19)),
9)");
+ f.checkNull("safe_add(cast(-9.999999999999999999e75 as DECIMAL(38, 19)),
-9)");
+ f.checkNull("safe_add(cast(9.9e75 as DECIMAL(76, 0)), "
+ + "cast(9.9e75 as DECIMAL(76, 0)))");
+ f.checkNull("safe_add(cast(-9.9e75 as DECIMAL(76, 0)), "
+ + "cast(-9.9e75 as DECIMAL(76, 0)))");
+ }
f.checkNull("safe_add(cast(1.7976931348623157e308 as double), "
+ "cast(9.9e7 as decimal(76, 0)))");
f.checkNull("safe_add(cast(-1.7976931348623157e308 as double), "
@@ -9829,20 +9822,22 @@ public class SqlOperatorTest {
f.checkNull("safe_divide(cast(0 as double), cast(0 as bigint))");
f.checkNull("safe_divide(cast(0 as double), cast(0 as double))");
f.checkNull("safe_divide(cast(0 as double), cast(0 as decimal(1, 0)))");
- f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as bigint))");
- f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as double))");
- f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as decimal(1,
0)))");
- // Overflow test for each pairing
- f.checkNull("safe_divide(cast(10 as bigint), cast(3.5e-75 as DECIMAL(76,
0)))");
- f.checkNull("safe_divide(cast(10 as bigint), cast(-3.5e75 as DECIMAL(76,
0)))");
- f.checkNull("safe_divide(cast(3.5e75 as DECIMAL(76, 0)), "
- + "cast(1.5 as DECIMAL(2, 1)))");
- f.checkNull("safe_divide(cast(-3.5e75 as DECIMAL(76, 0)), "
- + "cast(1.5 as DECIMAL(2, 1)))");
+ if (Bug.CALCITE_6328_FIXED) {
+ f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as
bigint))");
+ f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as
double))");
+ f.checkNull("safe_divide(cast(1.5 as decimal(2, 1)), cast(0 as
decimal(1, 0)))");
+ // Overflow test for each pairing
+ f.checkNull("safe_divide(cast(10 as bigint), cast(3.5e-75 as DECIMAL(76,
0)))");
+ f.checkNull("safe_divide(cast(10 as bigint), cast(-3.5e75 as DECIMAL(76,
0)))");
+ f.checkNull("safe_divide(cast(3.5e75 as DECIMAL(76, 0)), "
+ + "cast(1.5 as DECIMAL(2, 1)))");
+ f.checkNull("safe_divide(cast(-3.5e75 as DECIMAL(76, 0)), "
+ + "cast(1.5 as DECIMAL(2, 1)))");
+ f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(1.7e-309 as
double))");
+ f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(-1.7e-309 as
double))");
+ }
f.checkNull("safe_divide(cast(1.7e308 as double), cast(0.5 as decimal(3,
2)))");
f.checkNull("safe_divide(cast(-1.7e308 as double), cast(0.5 as decimal(2,
1)))");
- f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(1.7e-309 as
double))");
- f.checkNull("safe_divide(cast(5e20 as decimal(1, 0)), cast(-1.7e-309 as
double))");
f.checkNull("safe_divide(cast(3 as bigint), cast(1.7e-309 as double))");
f.checkNull("safe_divide(cast(3 as bigint), cast(-1.7e-309 as double))");
f.checkNull("safe_divide(cast(3 as double), cast(1.7e-309 as double))");
@@ -9900,14 +9895,16 @@ public class SqlOperatorTest {
+ "cast(9223372036854775807 as bigint))");
f.checkNull("safe_multiply(cast(20 as bigint), "
+ "cast(-9223372036854775807 as bigint))");
- f.checkNull("safe_multiply(cast(10 as bigint), cast(3.5e75 as DECIMAL(76,
0)))");
- f.checkNull("safe_multiply(cast(10 as bigint), cast(-3.5e75 as DECIMAL(76,
0)))");
- f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), cast(10 as
bigint))");
- f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), cast(10 as
bigint))");
- f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), "
- + "cast(1.5 as DECIMAL(2, 1)))");
- f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), "
- + "cast(1.5 as DECIMAL(2, 1)))");
+ if (Bug.CALCITE_6328_FIXED) {
+ f.checkNull("safe_multiply(cast(10 as bigint), cast(3.5e75 as
DECIMAL(76, 0)))");
+ f.checkNull("safe_multiply(cast(10 as bigint), cast(-3.5e75 as
DECIMAL(76, 0)))");
+ f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), cast(10 as
bigint))");
+ f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), cast(10 as
bigint))");
+ f.checkNull("safe_multiply(cast(3.5e75 as DECIMAL(76, 0)), "
+ + "cast(1.5 as DECIMAL(2, 1)))");
+ f.checkNull("safe_multiply(cast(-3.5e75 as DECIMAL(76, 0)), "
+ + "cast(1.5 as DECIMAL(2, 1)))");
+ }
f.checkNull("safe_multiply(cast(1.7e308 as double), cast(1.23 as
decimal(3, 2)))");
f.checkNull("safe_multiply(cast(-1.7e308 as double), cast(1.2 as
decimal(2, 1)))");
f.checkNull("safe_multiply(cast(1.2 as decimal(2, 1)), cast(1.7e308 as
double))");
@@ -10010,14 +10007,16 @@ public class SqlOperatorTest {
+ "cast(-9223372036854775807 as bigint))");
f.checkNull("safe_subtract(cast(-20 as bigint), "
+ "cast(9223372036854775807 as bigint))");
- f.checkNull("safe_subtract(9, cast(-9.999999999999999999e75 as DECIMAL(38,
19)))");
- f.checkNull("safe_subtract(-9, cast(9.999999999999999999e75 as DECIMAL(38,
19)))");
- f.checkNull("safe_subtract(cast(-9.999999999999999999e75 as DECIMAL(38,
19)), 9)");
- f.checkNull("safe_subtract(cast(9.999999999999999999e75 as DECIMAL(38,
19)), -9)");
- f.checkNull("safe_subtract(cast(-9.9e75 as DECIMAL(76, 0)), "
- + "cast(9.9e75 as DECIMAL(76, 0)))");
- f.checkNull("safe_subtract(cast(9.9e75 as DECIMAL(76, 0)), "
- + "cast(-9.9e75 as DECIMAL(76, 0)))");
+ if (Bug.CALCITE_6328_FIXED) {
+ f.checkNull("safe_subtract(9, cast(-9.999999999999999999e75 as
DECIMAL(38, 19)))");
+ f.checkNull("safe_subtract(-9, cast(9.999999999999999999e75 as
DECIMAL(38, 19)))");
+ f.checkNull("safe_subtract(cast(-9.999999999999999999e75 as DECIMAL(38,
19)), 9)");
+ f.checkNull("safe_subtract(cast(9.999999999999999999e75 as DECIMAL(38,
19)), -9)");
+ f.checkNull("safe_subtract(cast(-9.9e75 as DECIMAL(76, 0)), "
+ + "cast(9.9e75 as DECIMAL(76, 0)))");
+ f.checkNull("safe_subtract(cast(9.9e75 as DECIMAL(76, 0)), "
+ + "cast(-9.9e75 as DECIMAL(76, 0)))");
+ }
f.checkNull("safe_subtract(cast(1.7976931348623157e308 as double), "
+ "cast(-9.9e7 as decimal(76, 0)))");
f.checkNull("safe_subtract(cast(-1.7976931348623157e308 as double), "
@@ -11380,7 +11379,7 @@ public class SqlOperatorTest {
f12.checkScalar("nvl2('a', 3, 2)", "3", "INTEGER NOT NULL");
f12.checkScalar("NVL2(NULL, 3.0, 4.0)", "4.0", "DECIMAL(2, 1) NOT NULL");
f12.checkScalar("NVL2('abc', 3.0, 4.0)", "3.0", "DECIMAL(2, 1) NOT
NULL");
- f12.checkScalar("NVL2(1, 3.0, 2.111)", "3.0", "DECIMAL(4, 3) NOT NULL");
+ f12.checkScalar("NVL2(1, 3.0, 2.111)", "3.000", "DECIMAL(4, 3) NOT
NULL");
f12.checkScalar("NVL2(NULL, 3.0, 2.111)", "2.111", "DECIMAL(4, 3) NOT
NULL");
f12.checkScalar("NVL2(3.111, 3.1415926, 2.111)", "3.1415926",
"DECIMAL(8, 7) NOT NULL");
@@ -12854,8 +12853,8 @@ public class SqlOperatorTest {
f.checkScalarExact("ceil(cast(3 as integer))", "DOUBLE NOT NULL", "3.0");
f.checkScalarExact("ceil(cast(3 as bigint))", "DOUBLE NOT NULL", "3.0");
f.checkScalarExact("ceil(cast(3.5 as double))", "DOUBLE NOT NULL", "4.0");
- f.checkScalarExact("ceil(cast(3.45 as decimal))",
- "DECIMAL(19, 0) NOT NULL", "4");
+ f.checkScalarExact("ceil(cast(3.45 as decimal(19, 1)))",
+ "DECIMAL(19, 1) NOT NULL", "4");
f.checkScalarExact("ceil(cast(3.45 as float))", "FLOAT NOT NULL", "4.0");
f.checkNull("ceil(cast(null as tinyint))");
}
@@ -15598,9 +15597,13 @@ public class SqlOperatorTest {
} else {
// Value outside legal bound should fail at runtime (not
// validate time).
- f.checkFails("CAST(" + literalString + " AS " + type + ")",
- OUT_OF_RANGE_MESSAGE,
- true);
+ String expected;
+ if (type.getSqlTypeName() == SqlTypeName.DECIMAL) {
+ expected = "Value .* cannot be represented as .*";
+ } else {
+ expected = "Value .* out of range";
+ }
+ f.checkFails("CAST(" + literalString + " AS " + type + ")",
expected, true);
}
}
}
