You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by mj...@apache.org on 2017/02/22 18:12:05 UTC
[6/6] incubator-impala git commit: IMPALA-4821: Update AVG() for
DECIMAL_V2
IMPALA-4821: Update AVG() for DECIMAL_V2
This change implements the DECIMAL_V2's behavior for AVG().
The differences with DECIMAL_V1 are:
1. The output type has a minimum scale of 6. This is similar
to MS SQL's behavior which takes the max of 6 and the input
type's scale. We deviate from MS SQL in the output's precision
which is always set to 38. We use the smallest precision which
can store the output. A key insight is that the output of AVG()
is no wider than the inputs. Precision only needs to be adjusted
when the scale is augmented. Using a smaller precision avoids
potential loss of precision in subsequent decimal operations
(e.g. division) if AVG() is a subexpression. Please note that
the output type is different from SUM()/COUNT() as the latter
can have a much larger scale.
2. Due to a minimum of 6 decimal places for the output,
AVG() for decimal values whose whole number part exceeds 32
decimal places (e.g. DECIMAL(38,4), DECIMAL(33,0)) will
always overflow as the scale is augmented to 6. Certain
decimal types which work with AVG() in DECIMAL_V1 no longer
work in DECIMAL_V2.
Change-Id: I28f5ef0370938440eb5b1c6d29b2f24e6f88499f
Reviewed-on: http://gerrit.cloudera.org:8080/6038
Reviewed-by: Dan Hecht <dh...@cloudera.com>
Reviewed-by: Alex Behm <al...@cloudera.com>
Tested-by: Impala Public Jenkins
Project: http://git-wip-us.apache.org/repos/asf/incubator-impala/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-impala/commit/637cc3e4
Tree: http://git-wip-us.apache.org/repos/asf/incubator-impala/tree/637cc3e4
Diff: http://git-wip-us.apache.org/repos/asf/incubator-impala/diff/637cc3e4
Branch: refs/heads/master
Commit: 637cc3e447650a5c9bc4b8dd79ee74fd11459fa2
Parents: ed71133
Author: Michael Ho <kw...@cloudera.com>
Authored: Tue Feb 14 19:28:15 2017 -0800
Committer: Impala Public Jenkins <im...@gerrit.cloudera.org>
Committed: Wed Feb 22 06:31:14 2017 +0000
----------------------------------------------------------------------
be/src/exprs/aggregate-functions-ir.cc | 17 ++-
be/src/exprs/expr-test.cc | 77 ++++++++--
.../impala/analysis/FunctionCallExpr.java | 16 ++-
.../queries/QueryTest/decimal-exprs.test | 143 ++++++++++++++++++-
.../queries/QueryTest/decimal.test | 58 ++------
5 files changed, 235 insertions(+), 76 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/637cc3e4/be/src/exprs/aggregate-functions-ir.cc
----------------------------------------------------------------------
diff --git a/be/src/exprs/aggregate-functions-ir.cc b/be/src/exprs/aggregate-functions-ir.cc
index 531598c..6c8c781 100644
--- a/be/src/exprs/aggregate-functions-ir.cc
+++ b/be/src/exprs/aggregate-functions-ir.cc
@@ -437,19 +437,18 @@ DecimalVal AggregateFunctions::DecimalAvgGetValue(FunctionContext* ctx,
const StringVal& src) {
DecimalAvgState* val_struct = reinterpret_cast<DecimalAvgState*>(src.ptr);
if (val_struct->count == 0) return DecimalVal::null();
- const FunctionContext::TypeDesc& output_desc = ctx->GetReturnType();
- DCHECK_EQ(FunctionContext::TYPE_DECIMAL, output_desc.type);
Decimal16Value sum(val_struct->sum.val16);
Decimal16Value count(val_struct->count);
- // The scale of the accumulated sum must be the same as the scale of the return type.
- // TODO: Investigate whether this is always the right thing to do. Does the current
- // implementation result in an unacceptable loss of output precision?
- ColumnType sum_type = ColumnType::CreateDecimalType(38, output_desc.scale);
- ColumnType count_type = ColumnType::CreateDecimalType(38, 0);
+
+ int output_precision =
+ ctx->impl()->GetConstFnAttr(FunctionContextImpl::RETURN_TYPE_PRECISION);
+ int output_scale = ctx->impl()->GetConstFnAttr(FunctionContextImpl::RETURN_TYPE_SCALE);
+ // The scale of the accumulated sum is set to the scale of the input type.
+ int sum_scale = ctx->impl()->GetConstFnAttr(FunctionContextImpl::ARG_TYPE_SCALE, 0);
bool is_nan = false;
bool overflow = false;
- Decimal16Value result = sum.Divide<int128_t>(sum_type, count, count_type,
- output_desc.precision, output_desc.scale, &is_nan, &overflow);
+ Decimal16Value result = sum.Divide<int128_t>(sum_scale, count, 0 /* count's scale */,
+ output_precision, output_scale, &is_nan, &overflow);
if (UNLIKELY(is_nan)) return DecimalVal::null();
if (UNLIKELY(overflow)) {
ctx->AddWarning("Avg computation overflowed, returning NULL");
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/637cc3e4/be/src/exprs/expr-test.cc
----------------------------------------------------------------------
diff --git a/be/src/exprs/expr-test.cc b/be/src/exprs/expr-test.cc
index 5f3d1d0..442b64e 100644
--- a/be/src/exprs/expr-test.cc
+++ b/be/src/exprs/expr-test.cc
@@ -403,38 +403,50 @@ class ExprTest : public testing::Test {
"1970-01-01 00:00:00");
}
- // Verify that 'expr' has the same precision and scale as 'expected_type'.
- void TestDecimalResultType(const string& expr, const ColumnType& expected_type) {
- const string typeof_expr = "typeof(" + expr + ")";
- const string typeof_result = GetValue(typeof_expr, TYPE_STRING);
- EXPECT_EQ(expected_type.DebugString(), typeof_result) << typeof_expr;
+ // Verify that output of 'query' has the same precision and scale as 'expected_type'.
+ // 'query' is an expression, optionally followed by a from clause which is needed
+ // for testing aggregate expressions.
+ void TestDecimalResultType(const string& query, const ColumnType& expected_type) {
+ // For the case with from clause, we need to generate the "typeof query" by first
+ // extracting the select list.
+ size_t from_offset = query.find("from");
+ string typeof_query;
+ if (from_offset != string::npos) {
+ int query_len = query.length();
+ typeof_query = "typeof(" + query.substr(0, from_offset) + ")" +
+ query.substr(from_offset, query_len - from_offset);
+ } else {
+ typeof_query = "typeof(" + query + ")";
+ }
+ const string typeof_result = GetValue(typeof_query, TYPE_STRING);
+ EXPECT_EQ(expected_type.DebugString(), typeof_result) << typeof_query;
}
// Decimals don't work with TestValue.
// TODO: figure out what operators need to be implemented to work with EXPECT_EQ
template<typename T>
- void TestDecimalValue(const string& expr, const T& expected_result,
+ void TestDecimalValue(const string& query, const T& expected_result,
const ColumnType& expected_type) {
// Verify precision and scale of the expression match the expected type.
- TestDecimalResultType(expr, expected_type);
+ TestDecimalResultType(query, expected_type);
// Verify the expression result matches the expected result, for the given the
// precision and scale.
- const string value = GetValue(expr, expected_type);
+ const string value = GetValue(query, expected_type);
StringParser::ParseResult result;
// These require that we've passed the correct type to StringToDecimal(), so these
// results are valid only when TestDecimalResultType() succeeded.
switch (expected_type.GetByteSize()) {
case 4:
EXPECT_EQ(expected_result.value(), StringParser::StringToDecimal<int32_t>(
- &value[0], value.size(), expected_type, &result).value()) << expr;
+ &value[0], value.size(), expected_type, &result).value()) << query;
break;
case 8:
EXPECT_EQ(expected_result.value(), StringParser::StringToDecimal<int64_t>(
- &value[0], value.size(), expected_type, &result).value()) << expr;
+ &value[0], value.size(), expected_type, &result).value()) << query;
break;
case 16:
EXPECT_EQ(expected_result.value(), StringParser::StringToDecimal<int128_t>(
- &value[0], value.size(), expected_type, &result).value()) << expr;
+ &value[0], value.size(), expected_type, &result).value()) << query;
break;
default:
EXPECT_TRUE(false) << expected_type << " " << expected_type.GetByteSize();
@@ -1435,7 +1447,48 @@ DecimalTestCase decimal_cases[] = {
// The overload greatest(decimal(*,*)) is available and should be used.
{ "greatest(0, cast('99999.1111' as decimal(30,10)))",
{{ false, 999991111000000, 30, 10 },
- { false, 999991111000000, 30, 10 }}}
+ { false, 999991111000000, 30, 10 }}},
+ // Test AVG() with DECIMAL
+ { "avg(d) from (values((cast(100000000000000000000000000000000.00000 as DECIMAL(38,5)) "
+ "as d))) as t",
+ {{ false, static_cast<int128_t>(10000000ll) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 38, 5 },
+ { true, 0, 38, 6}}},
+ { "avg(d) from (values((cast(1234567890 as DECIMAL(10,0)) as d))) as t",
+ {{false, 1234567890, 10, 0},
+ {false, 1234567890000000, 16, 6}}},
+ { "avg(d) from (values((cast(1234567.89 as DECIMAL(10,2)) as d))) as t",
+ {{false, 123456789, 10, 2},
+ {false, 1234567890000, 14, 6}}},
+ { "avg(d) from (values((cast(10000000000000000000000000000000 as DECIMAL(32,0)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(10) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 32, 0},
+ {false, static_cast<int128_t>(10000000) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 38, 6}}},
+ { "avg(d) from (values((cast(100000000000000000000000000000000 as DECIMAL(33,0)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(100) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 33, 0},
+ {true, 0, 38, 6}}},
+ { "avg(d) from (values((cast(100000000000000000000000000000000.0 as DECIMAL(34,1)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(1000) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 34, 1},
+ {true, 0, 38, 6}}},
+ { "avg(d) from (values((cast(100000000000000000000000000000000.00000 as DECIMAL(38,5)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(10000000) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 38, 5},
+ {true, 0, 38, 6}}},
+ { "avg(d) from (values((cast(10000000000000000000000000000000.000000 as DECIMAL(38,6)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(10000000) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 38, 6}}},
+ { "avg(d) from (values((cast(0.10000000000000000000000000000000000000 as DECIMAL(38,38)) "
+ "as d))) as t",
+ {{false, static_cast<int128_t>(10000000) *
+ 10000000000ll * 10000000000ll * 10000000000ll, 38, 38}}}
};
TEST_F(ExprTest, DecimalArithmeticExprs) {
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/637cc3e4/fe/src/main/java/org/apache/impala/analysis/FunctionCallExpr.java
----------------------------------------------------------------------
diff --git a/fe/src/main/java/org/apache/impala/analysis/FunctionCallExpr.java b/fe/src/main/java/org/apache/impala/analysis/FunctionCallExpr.java
index 482a31e..a747af8 100644
--- a/fe/src/main/java/org/apache/impala/analysis/FunctionCallExpr.java
+++ b/fe/src/main/java/org/apache/impala/analysis/FunctionCallExpr.java
@@ -331,7 +331,21 @@ public class FunctionCallExpr extends Expr {
int digitsBefore = childType.decimalPrecision() - childType.decimalScale();
int digitsAfter = childType.decimalScale();
- if (fnName_.getFunction().equalsIgnoreCase("ceil") ||
+ if (fnName_.getFunction().equalsIgnoreCase("avg") &&
+ analyzer.getQueryOptions().isDecimal_v2()) {
+ // AVG() always gets at least MIN_ADJUSTED_SCALE decimal places since it performs
+ // an implicit divide. The output type isn't always the same as SUM()/COUNT().
+ // Scale is set the same as MS SQL Server, which takes the max of the input scale
+ // and MIN_ADJUST_SCALE. For precision, MS SQL always sets it to 38. We choose to
+ // trim it down to the size that's needed because the absolute value of the result
+ // is less than the absolute value of the largest input. Using a smaller precision
+ // allows for better DECIMAL types to be chosen for the overall expression when
+ // AVG() is a subexpression. For DECIMAL_V1, we set the output type to be the same
+ // as the input type.
+ int resultScale = Math.max(ScalarType.MIN_ADJUSTED_SCALE, digitsAfter);
+ int resultPrecision = digitsBefore + resultScale;
+ return ScalarType.createAdjustedDecimalType(resultPrecision, resultScale);
+ } else if (fnName_.getFunction().equalsIgnoreCase("ceil") ||
fnName_.getFunction().equalsIgnoreCase("ceiling") ||
fnName_.getFunction().equals("floor") ||
fnName_.getFunction().equals("dfloor")) {
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/637cc3e4/testdata/workloads/functional-query/queries/QueryTest/decimal-exprs.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/functional-query/queries/QueryTest/decimal-exprs.test b/testdata/workloads/functional-query/queries/QueryTest/decimal-exprs.test
index 89385bd..1dff5f1 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/decimal-exprs.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/decimal-exprs.test
@@ -30,7 +30,7 @@ DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
---- QUERY
# Test casting behavior without decimal_v2 query option set.
set decimal_v2=false;
-select cast(d3 as decimal(20, 3)) from functional.decimal_tbl;
+select cast(d3 as decimal(20, 3)) from decimal_tbl;
---- RESULTS
1.234
12.345
@@ -43,7 +43,7 @@ DECIMAL
---- QUERY
# Test casting behavior with decimal_v2 query option set.
set decimal_v2=true;
-select cast(d3 as decimal(20, 3)) from functional.decimal_tbl;
+select cast(d3 as decimal(20, 3)) from decimal_tbl;
---- RESULTS
1.235
12.346
@@ -56,7 +56,7 @@ DECIMAL
---- QUERY
# Test casting behavior without decimal_v2 query option set.
set decimal_v2=false;
-select sum(cast(d3 as DECIMAL(20,2)) + cast(d5 as DECIMAL(20,4))) from functional.decimal_tbl;
+select sum(cast(d3 as DECIMAL(20,2)) + cast(d5 as DECIMAL(20,4))) from decimal_tbl;
---- RESULTS
26078.2788
---- TYPES
@@ -65,9 +65,144 @@ DECIMAL
---- QUERY
# Test casting behavior with decimal_v2 query option set.
set decimal_v2=true;
-select sum(cast(d3 as DECIMAL(20,2)) + cast(d5 as DECIMAL(20,4))) from functional.decimal_tbl;
+select sum(cast(d3 as DECIMAL(20,2)) + cast(d5 as DECIMAL(20,4))) from decimal_tbl;
---- RESULTS
26078.3189
---- TYPES
DECIMAL
====
+---- QUERY
+# Test AVG() with DECIMAL_V1
+set decimal_v2=false;
+select avg(d1), avg(d2), avg(d3), avg(d4), avg(d5), avg(d6) from decimal_tbl;
+---- RESULTS
+32222,666,2743.4567651580,0.12345678900000000000000000000000000000,2472.20577,1
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL
+====
+---- QUERY
+# Test AVG() with DECIMAL_V2
+set decimal_v2=true;
+select avg(d1), avg(d2), avg(d3), avg(d4), avg(d5), avg(d6) from decimal_tbl;
+---- RESULTS
+32222.200000,666.400000,2743.4567651580,0.12345678900000000000000000000000000000,2472.205778,1.000000
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL
+====
+---- QUERY
+# Test AVG() with DECIMAL_V1
+set decimal_v2=false;
+select l_tax, avg(cast(l_extendedprice as decimal(38,10))), avg(l_extendedprice)
+from tpch_parquet.lineitem group by l_tax order by 1;
+---- RESULTS
+0.00,38241.5984613546,38241.59
+0.01,38283.5417664599,38283.54
+0.02,38250.4873094187,38250.48
+0.03,38259.2810374789,38259.28
+0.04,38247.1967454731,38247.19
+0.05,38234.8480874721,38234.84
+0.06,38246.4342924027,38246.43
+0.07,38281.1963710003,38281.19
+0.08,38251.6233675941,38251.62
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL
+====
+---- QUERY
+# Test AVG() with DECIMAL_V2
+set decimal_v2=true;
+select l_tax, avg(cast(l_extendedprice as decimal(38,10))), avg(l_extendedprice)
+from tpch_parquet.lineitem group by l_tax order by 1;
+---- RESULTS
+0.00,38241.5984613546,38241.598461
+0.01,38283.5417664599,38283.541766
+0.02,38250.4873094187,38250.487309
+0.03,38259.2810374789,38259.281037
+0.04,38247.1967454731,38247.196745
+0.05,38234.8480874721,38234.848087
+0.06,38246.4342924027,38246.434292
+0.07,38281.1963710003,38281.196371
+0.08,38251.6233675941,38251.623367
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL
+====
+---- QUERY
+# Test AVG() with DECIMAL_V1
+set decimal_v2=false;
+select avg(l_extendedprice) as a from tpch_parquet.lineitem
+group by l_tax having a > 38247.190 order by 1;
+---- RESULTS
+38250.48
+38251.62
+38259.28
+38281.19
+38283.54
+---- TYPES
+DECIMAL
+====
+---- QUERY
+# Test AVG() with DECIMAL_V2
+set decimal_v2=true;
+select avg(l_extendedprice) as a from tpch_parquet.lineitem
+group by l_tax having a > 38247.190 order by 1;
+---- RESULTS
+38247.196745
+38250.487309
+38251.623367
+38259.281037
+38281.196371
+38283.541766
+---- TYPES
+DECIMAL
+====
+---- QUERY
+# Test sum() and avg() analytic fns with start bounds (tests Remove() for decimal)
+# with DECIMAL_V1
+set decimal_v2=false;
+select
+sum(c1) over (order by c1 rows between 5 preceding and current row),
+sum(c2) over (order by c1 rows between 5 preceding and 5 following),
+sum(c3) over (order by c1 rows between 5 preceding and 2 preceding),
+avg(c1) over (order by c1 rows between 5 preceding and current row),
+avg(c2) over (order by c1 rows between 5 preceding and 5 following),
+avg(c3) over (order by c1 rows between 5 preceding and 2 preceding)
+from decimal_tiny where c2 < 112
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0.0000,618.33330,NULL,0.0000,103.05555,NULL
+0.1111,725.66662,NULL,0.0555,103.66666,NULL
+0.3333,834.22216,0.0,0.1111,104.27777,0.0
+0.6666,943.99992,0.1,0.1666,104.88888,0.0
+1.1110,1054.99990,0.3,0.2222,105.49999,0.1
+1.6665,1054.99990,0.6,0.2777,105.49999,0.1
+2.3331,954.99990,1.0,0.3888,106.11110,0.2
+2.9997,853.77768,1.4,0.4999,106.72221,0.3
+3.6663,751.33324,1.8,0.6110,107.33332,0.4
+4.3329,647.66658,2.2,0.7221,107.94443,0.5
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL
+====
+---- QUERY
+# Test sum() and avg() analytic fns with start bounds (tests Remove() for decimal)
+# with DECIMAL_V2
+set decimal_v2=true;
+select
+sum(c1) over (order by c1 rows between 5 preceding and current row),
+sum(c2) over (order by c1 rows between 5 preceding and 5 following),
+sum(c3) over (order by c1 rows between 5 preceding and 2 preceding),
+avg(c1) over (order by c1 rows between 5 preceding and current row),
+avg(c2) over (order by c1 rows between 5 preceding and 5 following),
+avg(c3) over (order by c1 rows between 5 preceding and 2 preceding)
+from decimal_tiny where c2 < 112
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0.0000,618.33330,NULL,0.000000,103.055550,NULL
+0.1111,725.66662,NULL,0.055550,103.666660,NULL
+0.3333,834.22216,0.0,0.111100,104.277770,0.000000
+0.6666,943.99992,0.1,0.166650,104.888880,0.050000
+1.1110,1054.99990,0.3,0.222200,105.499990,0.100000
+1.6665,1054.99990,0.6,0.277750,105.499990,0.150000
+2.3331,954.99990,1.0,0.388850,106.111100,0.250000
+2.9997,853.77768,1.4,0.499950,106.722210,0.350000
+3.6663,751.33324,1.8,0.611050,107.333320,0.450000
+4.3329,647.66658,2.2,0.722150,107.944430,0.550000
+---- TYPES
+DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL,DECIMAL
+====
\ No newline at end of file
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/637cc3e4/testdata/workloads/functional-query/queries/QueryTest/decimal.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/functional-query/queries/QueryTest/decimal.test b/testdata/workloads/functional-query/queries/QueryTest/decimal.test
index 6e7cbae..1f0fa4c 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/decimal.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/decimal.test
@@ -134,23 +134,6 @@ order by t1.c1 desc limit 3
DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
====
---- QUERY
-select avg(d1), avg(d2), avg(d3), avg(d4), avg(d5), avg(d6) from decimal_tbl;
----- RESULTS
-32222,666,2743.4567651580,0.12345678900000000000000000000000000000,2472.20577,1
----- TYPES
-decimal,decimal,decimal,decimal,decimal,decimal
-====
----- QUERY
-select d1, avg(d2), avg(d3), avg(d4), avg(d5), avg(d6) from decimal_tbl group by d1;
----- RESULTS
-132842,333,12345.6789000000,0.12345678900000000000000000000000000000,0.77889,1
-2345,111,12.3456789000,0.12345678900000000000000000000000000000,3.14100,1
-1234,2222,1.2345678900,0.12345678900000000000000000000000000000,12345.78900,1
-12345,333,679.0123395000,0.12345678900000000000000000000000000000,5.66000,1
----- TYPES
-decimal,decimal,decimal,decimal,decimal,decimal
-====
----- QUERY
select count(d1), count(d2), count(d3), count(d4), count(d5), count(d6) from decimal_tbl;
---- RESULTS
5,5,5,5,5,5
@@ -219,18 +202,6 @@ select d1, ndv(d2), ndv(d3), ndv(d4), ndv(d5), ndv(d6) from decimal_tbl group by
decimal,bigint,bigint,bigint,bigint,bigint
====
---- QUERY
-select cast(avg(c1) as decimal(10,4)) as c from decimal_tiny
-group by c3 having c > 5.5 order by 1
----- RESULTS
-5.5550
-5.6661
-5.7772
-5.8883
-5.9994
----- TYPES
-decimal
-====
----- QUERY
select a.c1 from decimal_tiny a left semi join decimal_tiny b on a.c1=b.c3
---- RESULTS
0.0000
@@ -344,28 +315,15 @@ select * from decimal_tiny
DECIMAL, DECIMAL, DECIMAL
====
---- QUERY
-# Test sum() and avg() analytic fns with start bounds (tests Remove() for decimal)
-select
-sum(c1) over (order by c1 rows between 5 preceding and current row),
-sum(c2) over (order by c1 rows between 5 preceding and 5 following),
-sum(c3) over (order by c1 rows between 5 preceding and 2 preceding),
-avg(c1) over (order by c1 rows between 5 preceding and current row),
-avg(c2) over (order by c1 rows between 5 preceding and 5 following),
-avg(c3) over (order by c1 rows between 5 preceding and 2 preceding)
-from decimal_tiny where c2 < 112
----- RESULTS: VERIFY_IS_EQUAL_SORTED
-0.0000,618.33330,NULL,0.0000,103.05555,NULL
-0.1111,725.66662,NULL,0.0555,103.66666,NULL
-0.3333,834.22216,0.0,0.1111,104.27777,0.0
-0.6666,943.99992,0.1,0.1666,104.88888,0.0
-1.1110,1054.99990,0.3,0.2222,105.49999,0.1
-1.6665,1054.99990,0.6,0.2777,105.49999,0.1
-2.3331,954.99990,1.0,0.3888,106.11110,0.2
-2.9997,853.77768,1.4,0.4999,106.72221,0.3
-3.6663,751.33324,1.8,0.6110,107.33332,0.4
-4.3329,647.66658,2.2,0.7221,107.94443,0.5
+# Test group-by with decimal
+select d1, d2, sum(d3), sum(d4), sum(d5), sum(d6) from decimal_tbl group by d1,d2;
+---- RESULTS
+132842,333,12345.6789000000,0.12345678900000000000000000000000000000,0.77889,1
+12345,333,1358.0246790000,0.24691357800000000000000000000000000000,11.32000,2
+1234,2222,1.2345678900,0.12345678900000000000000000000000000000,12345.78900,1
+2345,111,12.3456789000,0.12345678900000000000000000000000000000,3.14100,1
---- TYPES
-DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
+decimal,decimal,decimal,decimal,decimal,decimal
====
---- QUERY
# IMPALA-1559: FIRST_VALUE rewrite function intermediate type not matching slot type