You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@impala.apache.org by ta...@apache.org on 2016/07/18 15:28:16 UTC
incubator-impala git commit: IMPALA-3210: last/first_value() support
for IGNORE NULLS
Repository: incubator-impala
Updated Branches:
refs/heads/master 609b80410 -> 343bdad86
IMPALA-3210: last/first_value() support for IGNORE NULLS
Added support for the 'ignore nulls' keyword to the last_value and
first_value analytic functions, eg. 'last_value(col ignore nulls)',
which would return the last value from the window that is not null,
or null if all of the values in the window are null.
We handle 'ignore nulls' in the FE in the same way that we handle
'distinct' - by adding isIgnoreNulls as a field in FunctionParams.
To avoid affecting performance when 'ignore nulls' is not used, and
to avoid having to special case 'ignore nulls' on the backend, this
patch adds 'last_value_ignore_nulls' and 'first_value_ignore_nulls'
builtin analytic functions that wrap 'last_value' and 'first_value'
respectively.
Change-Id: Ic27525e2237fb54318549d2674f1610884208e9b
Reviewed-on: http://gerrit.cloudera.org:8080/3328
Reviewed-by: Thomas Tauber-Marshall <tm...@cloudera.com>
Tested-by: Internal 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/343bdad8
Tree: http://git-wip-us.apache.org/repos/asf/incubator-impala/tree/343bdad8
Diff: http://git-wip-us.apache.org/repos/asf/incubator-impala/diff/343bdad8
Branch: refs/heads/master
Commit: 343bdad866e08e5ceb55d2d66db2953c279fd2c0
Parents: 609b804
Author: Thomas Tauber-Marshall <tm...@cloudera.com>
Authored: Mon Jun 6 09:39:52 2016 -0700
Committer: Tim Armstrong <ta...@cloudera.com>
Committed: Mon Jul 18 08:28:09 2016 -0700
----------------------------------------------------------------------
be/src/exprs/aggregate-functions-ir.cc | 345 ++++++++++++++++---
be/src/exprs/aggregate-functions.h | 23 +-
fe/src/main/cup/sql-parser.cup | 2 +
.../cloudera/impala/analysis/AnalyticExpr.java | 117 +++++--
.../impala/analysis/FunctionCallExpr.java | 5 +
.../impala/analysis/FunctionParams.java | 17 +-
.../com/cloudera/impala/catalog/BuiltinsDb.java | 211 ++++++++++--
.../impala/analysis/AnalyzeExprsTest.java | 18 +
.../queries/PlannerTest/analytic-fns.test | 70 ++--
.../queries/QueryTest/analytic-fns.test | 259 +++++++++++++-
.../queries/QueryTest/decimal.test | 6 +-
11 files changed, 937 insertions(+), 136 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/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 853661c..1da530a 100644
--- a/be/src/exprs/aggregate-functions-ir.cc
+++ b/be/src/exprs/aggregate-functions-ir.cc
@@ -188,6 +188,34 @@ void AggregateFunctions::InitZero(FunctionContext*, DecimalVal* dst) {
dst->val16 = 0;
}
+template <typename T>
+void AggregateFunctions::UpdateVal(FunctionContext* ctx, const T& src, T* dst) {
+ *dst = src;
+}
+
+template <>
+void AggregateFunctions::UpdateVal(FunctionContext* ctx, const StringVal& src,
+ StringVal* dst) {
+ if (src.is_null) {
+ if (!dst->is_null) ctx->Free(dst->ptr);
+ *dst = StringVal::null();
+ return;
+ }
+
+ uint8_t* new_ptr;
+ if (dst->is_null) {
+ new_ptr = ctx->Allocate(src.len);
+ } else {
+ new_ptr = ctx->Reallocate(dst->ptr, src.len);
+ }
+ // Note that a zero-length string is not the same as StringVal::null().
+ RETURN_IF_NULL(ctx, new_ptr);
+ dst->ptr = new_ptr;
+ memcpy(dst->ptr, src.ptr, src.len);
+ dst->is_null = false;
+ dst->len = src.len;
+}
+
StringVal AggregateFunctions::StringValGetValue(
FunctionContext* ctx, const StringVal& src) {
if (src.is_null) return src;
@@ -1369,48 +1397,133 @@ BigIntVal AggregateFunctions::RankFinalize(FunctionContext* ctx,
}
template <typename T>
-void AggregateFunctions::LastValUpdate(FunctionContext* ctx, const T& src, T* dst) {
- *dst = src;
+void AggregateFunctions::LastValRemove(FunctionContext* ctx, const T& src, T* dst) {
+ if (ctx->impl()->num_removes() >= ctx->impl()->num_updates()) *dst = T::null();
}
template <>
-void AggregateFunctions::LastValUpdate(FunctionContext* ctx, const StringVal& src,
+void AggregateFunctions::LastValRemove(FunctionContext* ctx, const StringVal& src,
StringVal* dst) {
- if (src.is_null) {
+ if (ctx->impl()->num_removes() >= ctx->impl()->num_updates()) {
if (!dst->is_null) ctx->Free(dst->ptr);
*dst = StringVal::null();
- return;
}
+}
- uint8_t* new_ptr;
- if (dst->is_null) {
- new_ptr = ctx->Allocate(src.len);
+// Returns the current size of the window.
+inline int GetWindowSize(FunctionContext* ctx) {
+ return ctx->impl()->num_updates() - ctx->impl()->num_removes();
+}
+
+// LastValIgnoreNulls is a wrapper around LastVal. It works by not calling UpdateVal
+// if the value being added to the window is null, so that we will return the most
+// recently seen non-null value.
+// The one special case to consider is when all of the values in the window are null
+// and we therefore need to return null. To handle this, we track the number of nulls
+// currently in the window, and set the value to be returned to null if the number of
+// nulls is the same as the window size.
+template <typename T>
+struct LastValIgnoreNullsState {
+ T last_val;
+ // Number of nulls currently in the window, to detect when the window only has nulls.
+ int64_t num_nulls;
+};
+
+template <typename T>
+void AggregateFunctions::LastValIgnoreNullsInit(FunctionContext* ctx, StringVal* dst) {
+ AllocBuffer(ctx, dst, sizeof(LastValIgnoreNullsState<T>));
+ LastValIgnoreNullsState<T>* state =
+ reinterpret_cast<LastValIgnoreNullsState<T>*>(dst->ptr);
+ state->last_val = T::null();
+ state->num_nulls = 0;
+}
+
+template <typename T>
+void AggregateFunctions::LastValIgnoreNullsUpdate(FunctionContext* ctx, const T& src,
+ StringVal* dst) {
+ DCHECK(dst->ptr != NULL);
+ DCHECK_EQ(sizeof(LastValIgnoreNullsState<T>), dst->len);
+ LastValIgnoreNullsState<T>* state =
+ reinterpret_cast<LastValIgnoreNullsState<T>*>(dst->ptr);
+
+ if (!src.is_null) {
+ UpdateVal(ctx, src, &state->last_val);
} else {
- new_ptr = ctx->Reallocate(dst->ptr, src.len);
+ ++state->num_nulls;
+ DCHECK_LE(state->num_nulls, GetWindowSize(ctx));
+ if (GetWindowSize(ctx) == state->num_nulls) {
+ // Call UpdateVal here to set the value to null because it handles deallocation
+ // of StringVals correctly.
+ UpdateVal(ctx, T::null(), &state->last_val);
+ }
}
- // Note that a zero-length string is not the same as StringVal::null().
- RETURN_IF_NULL(ctx, new_ptr);
- dst->ptr = new_ptr;
- memcpy(dst->ptr, src.ptr, src.len);
- dst->is_null = false;
- dst->len = src.len;
}
template <typename T>
-void AggregateFunctions::LastValRemove(FunctionContext* ctx, const T& src, T* dst) {
- if (ctx->impl()->num_removes() >= ctx->impl()->num_updates()) *dst = T::null();
+void AggregateFunctions::LastValIgnoreNullsRemove(FunctionContext* ctx, const T& src,
+ StringVal* dst) {
+ DCHECK(dst->ptr != NULL);
+ DCHECK_EQ(sizeof(LastValIgnoreNullsState<T>), dst->len);
+ LastValIgnoreNullsState<T>* state =
+ reinterpret_cast<LastValIgnoreNullsState<T>*>(dst->ptr);
+ LastValRemove(ctx, src, &state->last_val);
+
+ if (src.is_null) --state->num_nulls;
+ DCHECK_GE(state->num_nulls, 0);
+ if (GetWindowSize(ctx) == state->num_nulls) {
+ // Call UpdateVal here to set the value to null because it handles deallocation
+ // of StringVals correctly.
+ UpdateVal(ctx, T::null(), &state->last_val);
+ }
+}
+
+template <typename T>
+T AggregateFunctions::LastValIgnoreNullsGetValue(FunctionContext* ctx,
+ const StringVal& src) {
+ DCHECK(!src.is_null);
+ DCHECK_EQ(sizeof(LastValIgnoreNullsState<T>), src.len);
+ LastValIgnoreNullsState<T>* state =
+ reinterpret_cast<LastValIgnoreNullsState<T>*>(src.ptr);
+ return state->last_val;
}
template <>
-void AggregateFunctions::LastValRemove(FunctionContext* ctx, const StringVal& src,
- StringVal* dst) {
- if (ctx->impl()->num_removes() >= ctx->impl()->num_updates()) {
- if (!dst->is_null) ctx->Free(dst->ptr);
- *dst = StringVal::null();
+StringVal AggregateFunctions::LastValIgnoreNullsGetValue(FunctionContext* ctx,
+ const StringVal& src) {
+ DCHECK(!src.is_null);
+ DCHECK_EQ(sizeof(LastValIgnoreNullsState<StringVal>), src.len);
+ LastValIgnoreNullsState<StringVal>* state =
+ reinterpret_cast<LastValIgnoreNullsState<StringVal>*>(src.ptr);
+
+ if (state->last_val.is_null) {
+ return StringVal::null();
+ } else {
+ return StringVal::CopyFrom(ctx, state->last_val.ptr, state->last_val.len);
}
}
template <typename T>
+T AggregateFunctions::LastValIgnoreNullsFinalize(FunctionContext* ctx,
+ const StringVal& src) {
+ DCHECK(!src.is_null);
+ T result = LastValIgnoreNullsGetValue<T>(ctx, src);
+ ctx->Free(src.ptr);
+ return result;
+}
+
+template <>
+StringVal AggregateFunctions::LastValIgnoreNullsFinalize(FunctionContext* ctx,
+ const StringVal& src) {
+ DCHECK(!src.is_null);
+ LastValIgnoreNullsState<StringVal>* state =
+ reinterpret_cast<LastValIgnoreNullsState<StringVal>*>(src.ptr);
+ StringVal result = LastValIgnoreNullsGetValue<StringVal>(ctx, src);
+ if (!state->last_val.is_null) ctx->Free(state->last_val.ptr);
+ ctx->Free(src.ptr);
+ return result;
+}
+
+template <typename T>
void AggregateFunctions::FirstValUpdate(FunctionContext* ctx, const T& src, T* dst) {
// The first call to FirstValUpdate sets the value of dst.
if (ctx->impl()->num_updates() > 1) return;
@@ -1437,7 +1550,25 @@ void AggregateFunctions::FirstValUpdate(FunctionContext* ctx, const StringVal& s
template <typename T>
void AggregateFunctions::FirstValRewriteUpdate(FunctionContext* ctx, const T& src,
const BigIntVal&, T* dst) {
- LastValUpdate<T>(ctx, src, dst);
+ UpdateVal<T>(ctx, src, dst);
+}
+
+template <typename T>
+void AggregateFunctions::FirstValIgnoreNullsUpdate(FunctionContext*, const T& src,
+ T* dst) {
+ // Store the first non-null value encountered, unlike FirstValUpdate which always stores
+ // the first value even if it is null.
+ if (!dst->is_null || src.is_null) return;
+ *dst = src;
+}
+
+template <>
+void AggregateFunctions::FirstValIgnoreNullsUpdate(FunctionContext* ctx,
+ const StringVal& src, StringVal* dst) {
+ // Store the first non-null value encountered, unlike FirstValUpdate which always stores
+ // the first value even if it is null.
+ if (!dst->is_null || src.is_null) return;
+ CopyStringVal(ctx, src, dst);
}
template <typename T>
@@ -1458,6 +1589,27 @@ void AggregateFunctions::OffsetFnUpdate(FunctionContext* ctx, const T& src,
// Stamp out the templates for the types we need.
template void AggregateFunctions::InitZero<BigIntVal>(FunctionContext*, BigIntVal* dst);
+template void AggregateFunctions::UpdateVal<BooleanVal>(
+ FunctionContext*, const BooleanVal& src, BooleanVal* dst);
+template void AggregateFunctions::UpdateVal<TinyIntVal>(
+ FunctionContext*, const TinyIntVal& src, TinyIntVal* dst);
+template void AggregateFunctions::UpdateVal<SmallIntVal>(
+ FunctionContext*, const SmallIntVal& src, SmallIntVal* dst);
+template void AggregateFunctions::UpdateVal<IntVal>(
+ FunctionContext*, const IntVal& src, IntVal* dst);
+template void AggregateFunctions::UpdateVal<BigIntVal>(
+ FunctionContext*, const BigIntVal& src, BigIntVal* dst);
+template void AggregateFunctions::UpdateVal<FloatVal>(
+ FunctionContext*, const FloatVal& src, FloatVal* dst);
+template void AggregateFunctions::UpdateVal<DoubleVal>(
+ FunctionContext*, const DoubleVal& src, DoubleVal* dst);
+template void AggregateFunctions::UpdateVal<StringVal>(
+ FunctionContext*, const StringVal& src, StringVal* dst);
+template void AggregateFunctions::UpdateVal<TimestampVal>(
+ FunctionContext*, const TimestampVal& src, TimestampVal* dst);
+template void AggregateFunctions::UpdateVal<DecimalVal>(
+ FunctionContext*, const DecimalVal& src, DecimalVal* dst);
+
template void AggregateFunctions::AvgUpdate<BigIntVal>(
FunctionContext* ctx, const BigIntVal& input, StringVal* dst);
template void AggregateFunctions::AvgUpdate<DoubleVal>(
@@ -1754,27 +1906,6 @@ template void AggregateFunctions::KnuthVarUpdate(
template void AggregateFunctions::KnuthVarUpdate(
FunctionContext*, const DoubleVal&, StringVal*);
-template void AggregateFunctions::LastValUpdate<BooleanVal>(
- FunctionContext*, const BooleanVal& src, BooleanVal* dst);
-template void AggregateFunctions::LastValUpdate<TinyIntVal>(
- FunctionContext*, const TinyIntVal& src, TinyIntVal* dst);
-template void AggregateFunctions::LastValUpdate<SmallIntVal>(
- FunctionContext*, const SmallIntVal& src, SmallIntVal* dst);
-template void AggregateFunctions::LastValUpdate<IntVal>(
- FunctionContext*, const IntVal& src, IntVal* dst);
-template void AggregateFunctions::LastValUpdate<BigIntVal>(
- FunctionContext*, const BigIntVal& src, BigIntVal* dst);
-template void AggregateFunctions::LastValUpdate<FloatVal>(
- FunctionContext*, const FloatVal& src, FloatVal* dst);
-template void AggregateFunctions::LastValUpdate<DoubleVal>(
- FunctionContext*, const DoubleVal& src, DoubleVal* dst);
-template void AggregateFunctions::LastValUpdate<StringVal>(
- FunctionContext*, const StringVal& src, StringVal* dst);
-template void AggregateFunctions::LastValUpdate<TimestampVal>(
- FunctionContext*, const TimestampVal& src, TimestampVal* dst);
-template void AggregateFunctions::LastValUpdate<DecimalVal>(
- FunctionContext*, const DecimalVal& src, DecimalVal* dst);
-
template void AggregateFunctions::LastValRemove<BooleanVal>(
FunctionContext*, const BooleanVal& src, BooleanVal* dst);
template void AggregateFunctions::LastValRemove<TinyIntVal>(
@@ -1796,6 +1927,111 @@ template void AggregateFunctions::LastValRemove<TimestampVal>(
template void AggregateFunctions::LastValRemove<DecimalVal>(
FunctionContext*, const DecimalVal& src, DecimalVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsInit<BooleanVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<TinyIntVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<SmallIntVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<IntVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<BigIntVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<FloatVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<DoubleVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<StringVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<TimestampVal>(
+ FunctionContext*, StringVal*);
+template void AggregateFunctions::LastValIgnoreNullsInit<DecimalVal>(
+ FunctionContext*, StringVal*);
+
+template void AggregateFunctions::LastValIgnoreNullsUpdate<BooleanVal>(
+ FunctionContext*, const BooleanVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<TinyIntVal>(
+ FunctionContext*, const TinyIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<SmallIntVal>(
+ FunctionContext*, const SmallIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<IntVal>(
+ FunctionContext*, const IntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<BigIntVal>(
+ FunctionContext*, const BigIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<FloatVal>(
+ FunctionContext*, const FloatVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<DoubleVal>(
+ FunctionContext*, const DoubleVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<StringVal>(
+ FunctionContext*, const StringVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<TimestampVal>(
+ FunctionContext*, const TimestampVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsUpdate<DecimalVal>(
+ FunctionContext*, const DecimalVal& src, StringVal* dst);
+
+template void AggregateFunctions::LastValIgnoreNullsRemove<BooleanVal>(
+ FunctionContext*, const BooleanVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<TinyIntVal>(
+ FunctionContext*, const TinyIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<SmallIntVal>(
+ FunctionContext*, const SmallIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<IntVal>(
+ FunctionContext*, const IntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<BigIntVal>(
+ FunctionContext*, const BigIntVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<FloatVal>(
+ FunctionContext*, const FloatVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<DoubleVal>(
+ FunctionContext*, const DoubleVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<StringVal>(
+ FunctionContext*, const StringVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<TimestampVal>(
+ FunctionContext*, const TimestampVal& src, StringVal* dst);
+template void AggregateFunctions::LastValIgnoreNullsRemove<DecimalVal>(
+ FunctionContext*, const DecimalVal& src, StringVal* dst);
+
+template BooleanVal AggregateFunctions::LastValIgnoreNullsGetValue<BooleanVal>(
+ FunctionContext*, const StringVal&);
+template TinyIntVal AggregateFunctions::LastValIgnoreNullsGetValue<TinyIntVal>(
+ FunctionContext*, const StringVal&);
+template SmallIntVal AggregateFunctions::LastValIgnoreNullsGetValue<SmallIntVal>(
+ FunctionContext*, const StringVal&);
+template IntVal AggregateFunctions::LastValIgnoreNullsGetValue<IntVal>(
+ FunctionContext*, const StringVal&);
+template BigIntVal AggregateFunctions::LastValIgnoreNullsGetValue<BigIntVal>(
+ FunctionContext*, const StringVal&);
+template FloatVal AggregateFunctions::LastValIgnoreNullsGetValue<FloatVal>(
+ FunctionContext*, const StringVal&);
+template DoubleVal AggregateFunctions::LastValIgnoreNullsGetValue<DoubleVal>(
+ FunctionContext*, const StringVal&);
+template StringVal AggregateFunctions::LastValIgnoreNullsGetValue<StringVal>(
+ FunctionContext*, const StringVal&);
+template TimestampVal AggregateFunctions::LastValIgnoreNullsGetValue<TimestampVal>(
+ FunctionContext*, const StringVal&);
+template DecimalVal AggregateFunctions::LastValIgnoreNullsGetValue<DecimalVal>(
+ FunctionContext*, const StringVal&);
+
+template BooleanVal AggregateFunctions::LastValIgnoreNullsFinalize<BooleanVal>(
+ FunctionContext*, const StringVal&);
+template TinyIntVal AggregateFunctions::LastValIgnoreNullsFinalize<TinyIntVal>(
+ FunctionContext*, const StringVal&);
+template SmallIntVal AggregateFunctions::LastValIgnoreNullsFinalize<SmallIntVal>(
+ FunctionContext*, const StringVal&);
+template IntVal AggregateFunctions::LastValIgnoreNullsFinalize<IntVal>(
+ FunctionContext*, const StringVal&);
+template BigIntVal AggregateFunctions::LastValIgnoreNullsFinalize<BigIntVal>(
+ FunctionContext*, const StringVal&);
+template FloatVal AggregateFunctions::LastValIgnoreNullsFinalize<FloatVal>(
+ FunctionContext*, const StringVal&);
+template DoubleVal AggregateFunctions::LastValIgnoreNullsFinalize<DoubleVal>(
+ FunctionContext*, const StringVal&);
+template StringVal AggregateFunctions::LastValIgnoreNullsFinalize<StringVal>(
+ FunctionContext*, const StringVal&);
+template TimestampVal AggregateFunctions::LastValIgnoreNullsFinalize<TimestampVal>(
+ FunctionContext*, const StringVal&);
+template DecimalVal AggregateFunctions::LastValIgnoreNullsFinalize<DecimalVal>(
+ FunctionContext*, const StringVal&);
+
template void AggregateFunctions::FirstValUpdate<BooleanVal>(
FunctionContext*, const BooleanVal& src, BooleanVal* dst);
template void AggregateFunctions::FirstValUpdate<TinyIntVal>(
@@ -1838,6 +2074,27 @@ template void AggregateFunctions::FirstValRewriteUpdate<TimestampVal>(
template void AggregateFunctions::FirstValRewriteUpdate<DecimalVal>(
FunctionContext*, const DecimalVal& src, const BigIntVal&, DecimalVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<BooleanVal>(
+ FunctionContext*, const BooleanVal& src, BooleanVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<TinyIntVal>(
+ FunctionContext*, const TinyIntVal& src, TinyIntVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<SmallIntVal>(
+ FunctionContext*, const SmallIntVal& src, SmallIntVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<IntVal>(
+ FunctionContext*, const IntVal& src, IntVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<BigIntVal>(
+ FunctionContext*, const BigIntVal& src, BigIntVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<FloatVal>(
+ FunctionContext*, const FloatVal& src, FloatVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<DoubleVal>(
+ FunctionContext*, const DoubleVal& src, DoubleVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<StringVal>(
+ FunctionContext*, const StringVal& src, StringVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<TimestampVal>(
+ FunctionContext*, const TimestampVal& src, TimestampVal* dst);
+template void AggregateFunctions::FirstValIgnoreNullsUpdate<DecimalVal>(
+ FunctionContext*, const DecimalVal& src, DecimalVal* dst);
+
template void AggregateFunctions::OffsetFnInit<BooleanVal>(
FunctionContext*, BooleanVal*);
template void AggregateFunctions::OffsetFnInit<TinyIntVal>(
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/be/src/exprs/aggregate-functions.h
----------------------------------------------------------------------
diff --git a/be/src/exprs/aggregate-functions.h b/be/src/exprs/aggregate-functions.h
index cf0161e..c4c18d6 100644
--- a/be/src/exprs/aggregate-functions.h
+++ b/be/src/exprs/aggregate-functions.h
@@ -38,6 +38,10 @@ class AggregateFunctions {
template <typename T>
static void InitZero(FunctionContext*, T* dst);
+ // Sets dst's value to src. Handles deallocation if src and dst are StringVals.
+ template <typename T>
+ static void UpdateVal(FunctionContext*, const T& src, T* dst);
+
/// StringVal GetValue() function that returns a copy of src
static StringVal StringValGetValue(FunctionContext* ctx, const StringVal& src);
@@ -232,11 +236,21 @@ class AggregateFunctions {
/// Implements LAST_VALUE.
template <typename T>
- static void LastValUpdate(FunctionContext*, const T& src, T* dst);
- template <typename T>
static void LastValRemove(FunctionContext*, const T& src, T* dst);
- /// Implements FIRST_VALUE.
+ // Implements LAST_VALUE_IGNORE_NULLS
+ template <typename T>
+ static void LastValIgnoreNullsInit(FunctionContext*, StringVal* dst);
+ template <typename T>
+ static void LastValIgnoreNullsUpdate(FunctionContext*, const T& src, StringVal* dst);
+ template <typename T>
+ static void LastValIgnoreNullsRemove(FunctionContext*, const T& src, StringVal* dst);
+ template <typename T>
+ static T LastValIgnoreNullsGetValue(FunctionContext* ctx, const StringVal& src);
+ template <typename T>
+ static T LastValIgnoreNullsFinalize(FunctionContext* ctx, const StringVal& src);
+
+ /// Implements FIRST_VALUE. Requires a start bound of UNBOUNDED PRECEDING.
template <typename T>
static void FirstValUpdate(FunctionContext*, const T& src, T* dst);
/// Implements FIRST_VALUE for some windows that require rewrites during planning.
@@ -245,6 +259,9 @@ class AggregateFunctions {
template <typename T>
static void FirstValRewriteUpdate(FunctionContext*, const T& src, const BigIntVal&,
T* dst);
+ /// Implements FIRST_VALUE_IGNORE_NULLS. Requires a start bound of UNBOUNDED PRECEDING.
+ template <typename T>
+ static void FirstValIgnoreNullsUpdate(FunctionContext*, const T& src, T* dst);
/// OffsetFn*() implement LAG and LEAD. Init() sets the default value (the last
/// constant parameter) as dst.
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/main/cup/sql-parser.cup
----------------------------------------------------------------------
diff --git a/fe/src/main/cup/sql-parser.cup b/fe/src/main/cup/sql-parser.cup
index 62a2b80..e0f86c9 100644
--- a/fe/src/main/cup/sql-parser.cup
+++ b/fe/src/main/cup/sql-parser.cup
@@ -2592,6 +2592,8 @@ function_params ::=
{: RESULT = new FunctionParams(false, exprs); :}
| KW_DISTINCT:distinct expr_list:exprs
{: RESULT = new FunctionParams(true, exprs); :}
+ | expr_list:exprs KW_IGNORE KW_NULLS
+ {: RESULT = new FunctionParams(false, true, exprs); :}
;
predicate ::=
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/main/java/com/cloudera/impala/analysis/AnalyticExpr.java
----------------------------------------------------------------------
diff --git a/fe/src/main/java/com/cloudera/impala/analysis/AnalyticExpr.java b/fe/src/main/java/com/cloudera/impala/analysis/AnalyticExpr.java
index 8da58bd..063a9e6 100644
--- a/fe/src/main/java/com/cloudera/impala/analysis/AnalyticExpr.java
+++ b/fe/src/main/java/com/cloudera/impala/analysis/AnalyticExpr.java
@@ -78,8 +78,10 @@ public class AnalyticExpr extends Expr {
private static String LEAD = "lead";
private static String LAG = "lag";
- private static String FIRSTVALUE = "first_value";
- private static String LASTVALUE = "last_value";
+ private static String FIRST_VALUE = "first_value";
+ private static String LAST_VALUE = "last_value";
+ private static String FIRST_VALUE_IGNORE_NULLS = "first_value_ignore_nulls";
+ private static String LAST_VALUE_IGNORE_NULLS = "last_value_ignore_nulls";
private static String RANK = "rank";
private static String DENSERANK = "dense_rank";
private static String ROWNUMBER = "row_number";
@@ -446,6 +448,14 @@ public class AnalyticExpr extends Expr {
"DISTINCT not allowed in analytic function: " + getFnCall().toSql());
}
+ if (getFnCall().getParams().isIgnoreNulls()) {
+ String fnName = getFnCall().getFnName().getFunction();
+ if (!fnName.equals(LAST_VALUE) && !fnName.equals(FIRST_VALUE)) {
+ throw new AnalysisException("Function " + fnName.toUpperCase()
+ + " does not accept the keyword IGNORE NULLS.");
+ }
+ }
+
// check for correct composition of analytic expr
Function fn = getFnCall().getFn();
if (!(fn instanceof AggregateFunction)) {
@@ -551,19 +561,8 @@ public class AnalyticExpr extends Expr {
* Explicitly set the default arguments to for BE simplicity.
* Set a window for lead(): UNBOUNDED PRECEDING to OFFSET FOLLOWING.
* Set a window for lag(): UNBOUNDED PRECEDING to OFFSET PRECEDING.
- * 3. UNBOUNDED FOLLOWING windows:
- * Reverse the ordering and window if the start bound is not UNBOUNDED PRECEDING.
- * Flip first_value() and last_value().
- * 4. first_value():
- * Set the upper boundary to CURRENT_ROW if the lower boundary is
- * UNBOUNDED_PRECEDING.
- * 5. Explicitly set the default window if no window was given but there
- * are order-by elements.
- * 6. FIRST_VALUE without UNBOUNDED PRECEDING gets rewritten to use a different window
- * and change the function to return the last value. We either set the fn to be
- * 'last_value' or 'first_value_rewrite', which simply wraps the 'last_value'
- * implementation but allows us to handle the first rows in a partition in a special
- * way in the backend. There are a few cases:
+ * 3. FIRST_VALUE without UNBOUNDED PRECEDING or IGNORE NULLS gets rewritten to use a
+ * different window and function. There are a few cases:
* a) Start bound is X FOLLOWING or CURRENT ROW (X=0):
* Use 'last_value' with a window where both bounds are X FOLLOWING (or
* CURRENT ROW). Setting the start bound to X following is necessary because the
@@ -579,11 +578,28 @@ public class AnalyticExpr extends Expr {
* first Y rows in a partition have empty windows and should be NULL. An extra
* parameter with the integer constant Y is added to indicate to the backend
* that NULLs should be added for the first Y rows.
+ * The performance optimization here and in 5. below cannot be applied in the case of
+ * IGNORE NULLS because they change what values appear in the window, which in the
+ * IGNORE NULLS case could mean the correct value to return isn't even in the window,
+ * eg. if all of the values in the rewritten window are NULL but one of the values in
+ * the original window isn't.
+ * 4. Start bound is not UNBOUNDED PRECEDING and either the end bound is UNBOUNDED
+ * FOLLOWING or the function is first_value(... ignore nulls):
+ * Reverse the ordering and window, and flip first_value() and last_value().
+ * 5. first_value() with UNBOUNDED PRECEDING and not IGNORE NULLS:
+ * Set the end boundary to CURRENT_ROW.
+ * 6. Rewrite IGNORE NULLS as regular FunctionCallExprs with '_ignore_nulls'
+ * appended to the function name, because the BE implements them as different
+ * functions.
+ * 7. Explicitly set the default window if no window was given but there
+ * are order-by elements.
+ * 8. first/last_value() with RANGE window:
+ * Rewrite as a ROWS window.
*/
private void standardize(Analyzer analyzer) {
FunctionName analyticFnName = getFnCall().getFnName();
- // Set a window from UNBOUNDED PRECEDING to CURRENT_ROW for row_number().
+ // 1. Set a window from UNBOUNDED PRECEDING to CURRENT_ROW for row_number().
if (analyticFnName.getFunction().equals(ROWNUMBER)) {
Preconditions.checkState(window_ == null, "Unexpected window set for row_numer()");
window_ = new AnalyticWindow(AnalyticWindow.Type.ROWS,
@@ -593,7 +609,7 @@ public class AnalyticExpr extends Expr {
return;
}
- // Explicitly set the default arguments to lead()/lag() for BE simplicity.
+ // 2. Explicitly set the default arguments to lead()/lag() for BE simplicity.
// Set a window for lead(): UNBOUNDED PRECEDING to OFFSET FOLLOWING,
// Set a window for lag(): UNBOUNDED PRECEDING to OFFSET PRECEDING.
if (isOffsetFn(getFnCall().getFn())) {
@@ -640,13 +656,15 @@ public class AnalyticExpr extends Expr {
return;
}
- if (analyticFnName.getFunction().equals(FIRSTVALUE)
+ // 3.
+ if (analyticFnName.getFunction().equals(FIRST_VALUE)
&& window_ != null
- && window_.getLeftBoundary().getType() != BoundaryType.UNBOUNDED_PRECEDING) {
+ && window_.getLeftBoundary().getType() != BoundaryType.UNBOUNDED_PRECEDING
+ && !getFnCall().getParams().isIgnoreNulls()) {
if (window_.getLeftBoundary().getType() != BoundaryType.PRECEDING) {
window_ = new AnalyticWindow(window_.getType(), window_.getLeftBoundary(),
window_.getLeftBoundary());
- fnCall_ = new FunctionCallExpr(new FunctionName("last_value"),
+ fnCall_ = new FunctionCallExpr(new FunctionName(LAST_VALUE),
getFnCall().getParams());
} else {
List<Expr> paramExprs = Expr.cloneList(getFnCall().getParams().exprs());
@@ -665,7 +683,7 @@ public class AnalyticExpr extends Expr {
window_ = new AnalyticWindow(window_.getType(),
new Boundary(BoundaryType.UNBOUNDED_PRECEDING, null),
window_.getLeftBoundary());
- fnCall_ = new FunctionCallExpr(new FunctionName("first_value_rewrite"),
+ fnCall_ = new FunctionCallExpr(new FunctionName(FIRST_VALUE_REWRITE),
new FunctionParams(paramExprs));
fnCall_.setIsInternalFnCall(true);
}
@@ -677,21 +695,24 @@ public class AnalyticExpr extends Expr {
analyticFnName = getFnCall().getFnName();
}
- // Reverse the ordering and window for windows ending with UNBOUNDED FOLLOWING,
- // and and not starting with UNBOUNDED PRECEDING.
+ // 4. Reverse the ordering and window for windows not starting with UNBOUNDED
+ // PRECEDING and either: ending with UNBOUNDED FOLLOWING or
+ // first_value(... ignore nulls)
if (window_ != null
- && window_.getRightBoundary().getType() == BoundaryType.UNBOUNDED_FOLLOWING
- && window_.getLeftBoundary().getType() != BoundaryType.UNBOUNDED_PRECEDING) {
+ && window_.getLeftBoundary().getType() != BoundaryType.UNBOUNDED_PRECEDING
+ && (window_.getRightBoundary().getType() == BoundaryType.UNBOUNDED_FOLLOWING
+ || (analyticFnName.getFunction().equals(FIRST_VALUE)
+ && getFnCall().getParams().isIgnoreNulls()))) {
orderByElements_ = OrderByElement.reverse(orderByElements_);
window_ = window_.reverse();
// Also flip first_value()/last_value(). For other analytic functions there is no
// need to also change the function.
FunctionName reversedFnName = null;
- if (analyticFnName.getFunction().equals(FIRSTVALUE)) {
- reversedFnName = new FunctionName(LASTVALUE);
- } else if (analyticFnName.getFunction().equals(LASTVALUE)) {
- reversedFnName = new FunctionName(FIRSTVALUE);
+ if (analyticFnName.getFunction().equals(FIRST_VALUE)) {
+ reversedFnName = new FunctionName(LAST_VALUE);
+ } else if (analyticFnName.getFunction().equals(LAST_VALUE)) {
+ reversedFnName = new FunctionName(FIRST_VALUE);
}
if (reversedFnName != null) {
fnCall_ = new FunctionCallExpr(reversedFnName, getFnCall().getParams());
@@ -701,20 +722,48 @@ public class AnalyticExpr extends Expr {
analyticFnName = getFnCall().getFnName();
}
- // Set the upper boundary to CURRENT_ROW for first_value() if the lower boundary
- // is UNBOUNDED_PRECEDING.
- if (window_ != null
+ // 5. Set the start boundary to CURRENT_ROW for first_value() if the end boundary
+ // is UNBOUNDED_PRECEDING and IGNORE NULLS is not set.
+ if (analyticFnName.getFunction().equals(FIRST_VALUE)
+ && window_ != null
&& window_.getLeftBoundary().getType() == BoundaryType.UNBOUNDED_PRECEDING
&& window_.getRightBoundary().getType() != BoundaryType.PRECEDING
- && analyticFnName.getFunction().equals(FIRSTVALUE)) {
+ && !getFnCall().getParams().isIgnoreNulls()) {
window_.setRightBoundary(new Boundary(BoundaryType.CURRENT_ROW, null));
}
- // Set the default window.
+ // 6. Set the default window.
if (!orderByElements_.isEmpty() && window_ == null) {
window_ = AnalyticWindow.DEFAULT_WINDOW;
resetWindow_ = true;
}
+
+ // 7. Change first_value/last_value RANGE windows to ROWS.
+ if ((analyticFnName.getFunction().equals(FIRST_VALUE)
+ || analyticFnName.getFunction().equals(LAST_VALUE))
+ && window_ != null
+ && window_.getType() == AnalyticWindow.Type.RANGE) {
+ window_ = new AnalyticWindow(AnalyticWindow.Type.ROWS, window_.getLeftBoundary(),
+ window_.getRightBoundary());
+ }
+
+ // 8. Append IGNORE NULLS to fn name if set.
+ if (getFnCall().getParams().isIgnoreNulls()) {
+ if (analyticFnName.getFunction().equals(LAST_VALUE)) {
+ fnCall_ = new FunctionCallExpr(new FunctionName(LAST_VALUE_IGNORE_NULLS),
+ getFnCall().getParams());
+ } else {
+ Preconditions.checkState(analyticFnName.getFunction().equals(FIRST_VALUE));
+ fnCall_ = new FunctionCallExpr(new FunctionName(FIRST_VALUE_IGNORE_NULLS),
+ getFnCall().getParams());
+ }
+
+ fnCall_.setIsAnalyticFnCall(true);
+ fnCall_.setIsInternalFnCall(true);
+ fnCall_.analyzeNoThrow(analyzer);
+ analyticFnName = getFnCall().getFnName();
+ Preconditions.checkState(type_.equals(fnCall_.getType()));
+ }
}
/**
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/main/java/com/cloudera/impala/analysis/FunctionCallExpr.java
----------------------------------------------------------------------
diff --git a/fe/src/main/java/com/cloudera/impala/analysis/FunctionCallExpr.java b/fe/src/main/java/com/cloudera/impala/analysis/FunctionCallExpr.java
index ca4097b..13fb93e 100644
--- a/fe/src/main/java/com/cloudera/impala/analysis/FunctionCallExpr.java
+++ b/fe/src/main/java/com/cloudera/impala/analysis/FunctionCallExpr.java
@@ -463,6 +463,11 @@ public class FunctionCallExpr extends Expr {
if (aggFn.ignoresDistinct()) params_.setIsDistinct(false);
}
+ if (params_.isIgnoreNulls() && !isAnalyticFnCall_) {
+ throw new AnalysisException("Function " + fnName_.getFunction().toUpperCase()
+ + " does not accept the keyword IGNORE NULLS.");
+ }
+
if (isScalarFunction()) validateScalarFnParams(params_);
if (fn_ instanceof AggregateFunction
&& ((AggregateFunction) fn_).isAnalyticFn()
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/main/java/com/cloudera/impala/analysis/FunctionParams.java
----------------------------------------------------------------------
diff --git a/fe/src/main/java/com/cloudera/impala/analysis/FunctionParams.java b/fe/src/main/java/com/cloudera/impala/analysis/FunctionParams.java
index 0d1882e..7c47256 100644
--- a/fe/src/main/java/com/cloudera/impala/analysis/FunctionParams.java
+++ b/fe/src/main/java/com/cloudera/impala/analysis/FunctionParams.java
@@ -23,18 +23,25 @@ import java.util.List;
class FunctionParams implements Cloneable {
private final boolean isStar_;
private boolean isDistinct_;
+ private boolean isIgnoreNulls_;
private final List<Expr> exprs_;
// c'tor for non-star params
- public FunctionParams(boolean isDistinct, List<Expr> exprs) {
- isStar_ = false;
+ public FunctionParams(boolean isDistinct, boolean isIgnoreNulls, List<Expr> exprs) {
+ this.isStar_ = false;
this.isDistinct_ = isDistinct;
+ this.isIgnoreNulls_ = isIgnoreNulls;
this.exprs_ = exprs;
}
- // c'tor for non-star, non-distinct params
+ // c'tor for non-star, non-ignore-nulls params
+ public FunctionParams(boolean isDistinct, List<Expr> exprs) {
+ this(isDistinct, false, exprs);
+ }
+
+ // c'tor for non-star, non-distinct, non-ignore-nulls params
public FunctionParams(List<Expr> exprs) {
- this(false, exprs);
+ this(false, false, exprs);
}
static public FunctionParams createStarParam() {
@@ -43,6 +50,7 @@ class FunctionParams implements Cloneable {
public boolean isStar() { return isStar_; }
public boolean isDistinct() { return isDistinct_; }
+ public boolean isIgnoreNulls() { return isIgnoreNulls_; }
public List<Expr> exprs() { return exprs_; }
public void setIsDistinct(boolean v) { isDistinct_ = v; }
public int size() { return exprs_ == null ? 0 : exprs_.size(); }
@@ -52,5 +60,6 @@ class FunctionParams implements Cloneable {
exprs_ = null;
isStar_ = true;
isDistinct_ = false;
+ isIgnoreNulls_ = false;
}
}
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/main/java/com/cloudera/impala/catalog/BuiltinsDb.java
----------------------------------------------------------------------
diff --git a/fe/src/main/java/com/cloudera/impala/catalog/BuiltinsDb.java b/fe/src/main/java/com/cloudera/impala/catalog/BuiltinsDb.java
index 2ca3dc1..eab02f5 100644
--- a/fe/src/main/java/com/cloudera/impala/catalog/BuiltinsDb.java
+++ b/fe/src/main/java/com/cloudera/impala/catalog/BuiltinsDb.java
@@ -185,6 +185,30 @@ public class BuiltinsDb extends Db {
"23ReservoirSampleFinalizeIN10impala_udf10DecimalValEEENS2_9StringValEPNS2_15FunctionContextERKS4_")
.build();
+ private static final Map<Type, String> UPDATE_VAL_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "9UpdateValIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.DECIMAL,
+ "9UpdateValIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.TINYINT,
+ "9UpdateValIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.SMALLINT,
+ "9UpdateValIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.TIMESTAMP,
+ "9UpdateValIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.INT,
+ "9UpdateValIN10impala_udf6IntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.FLOAT,
+ "9UpdateValIN10impala_udf8FloatValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.BIGINT,
+ "9UpdateValIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.DOUBLE,
+ "9UpdateValIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.STRING,
+ "9UpdateValIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .build();
+
private static final Map<Type, String> APPX_MEDIAN_FINALIZE_SYMBOL =
ImmutableMap.<Type, String>builder()
.put(Type.BOOLEAN,
@@ -416,30 +440,6 @@ public class BuiltinsDb extends Db {
"14OffsetFnUpdateIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_RKNS2_9BigIntValES8_PS6_")
.build();
- private static final Map<Type, String> LAST_VALUE_UPDATE_SYMBOL =
- ImmutableMap.<Type, String>builder()
- .put(Type.BOOLEAN,
- "13LastValUpdateIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.DECIMAL,
- "13LastValUpdateIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.TINYINT,
- "13LastValUpdateIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.SMALLINT,
- "13LastValUpdateIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.TIMESTAMP,
- "13LastValUpdateIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.INT,
- "13LastValUpdateIN10impala_udf6IntValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.FLOAT,
- "13LastValUpdateIN10impala_udf8FloatValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.BIGINT,
- "13LastValUpdateIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.DOUBLE,
- "13LastValUpdateIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PS6_")
- .put(Type.STRING,
- "13LastValUpdateIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS6_")
- .build();
-
private static final Map<Type, String> FIRST_VALUE_REWRITE_UPDATE_SYMBOL =
ImmutableMap.<Type, String>builder()
.put(Type.BOOLEAN,
@@ -488,6 +488,126 @@ public class BuiltinsDb extends Db {
"13LastValRemoveIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS6_")
.build();
+ private static final Map<Type, String> LAST_VALUE_IGNORE_NULLS_INIT_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "22LastValIgnoreNullsInitIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.TINYINT,
+ "22LastValIgnoreNullsInitIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.SMALLINT,
+ "22LastValIgnoreNullsInitIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.INT,
+ "22LastValIgnoreNullsInitIN10impala_udf6IntValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.BIGINT,
+ "22LastValIgnoreNullsInitIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.FLOAT,
+ "22LastValIgnoreNullsInitIN10impala_udf8FloatValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.DOUBLE,
+ "22LastValIgnoreNullsInitIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.STRING,
+ "22LastValIgnoreNullsInitIN10impala_udf9StringValEEEvPNS2_15FunctionContextEPS3_")
+ .put(Type.TIMESTAMP,
+ "22LastValIgnoreNullsInitIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .put(Type.DECIMAL,
+ "22LastValIgnoreNullsInitIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextEPNS2_9StringValE")
+ .build();
+
+ private static final Map<Type, String> LAST_VALUE_IGNORE_NULLS_UPDATE_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "24LastValIgnoreNullsUpdateIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.DECIMAL,
+ "24LastValIgnoreNullsUpdateIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.TINYINT,
+ "24LastValIgnoreNullsUpdateIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.SMALLINT,
+ "24LastValIgnoreNullsUpdateIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.TIMESTAMP,
+ "24LastValIgnoreNullsUpdateIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.INT,
+ "24LastValIgnoreNullsUpdateIN10impala_udf6IntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.FLOAT,
+ "24LastValIgnoreNullsUpdateIN10impala_udf8FloatValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.BIGINT,
+ "24LastValIgnoreNullsUpdateIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.DOUBLE,
+ "24LastValIgnoreNullsUpdateIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.STRING,
+ "24LastValIgnoreNullsUpdateIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS3_")
+ .build();
+
+ private static final Map<Type, String> LAST_VALUE_IGNORE_NULLS_REMOVE_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "24LastValIgnoreNullsRemoveIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.DECIMAL,
+ "24LastValIgnoreNullsRemoveIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.TINYINT,
+ "24LastValIgnoreNullsRemoveIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.SMALLINT,
+ "24LastValIgnoreNullsRemoveIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.TIMESTAMP,
+ "24LastValIgnoreNullsRemoveIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.INT,
+ "24LastValIgnoreNullsRemoveIN10impala_udf6IntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.FLOAT,
+ "24LastValIgnoreNullsRemoveIN10impala_udf8FloatValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.BIGINT,
+ "24LastValIgnoreNullsRemoveIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.DOUBLE,
+ "24LastValIgnoreNullsRemoveIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PNS2_9StringValE")
+ .put(Type.STRING,
+ "24LastValIgnoreNullsRemoveIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS3_")
+ .build();
+
+ private static final Map<Type, String> LAST_VALUE_IGNORE_NULLS_GET_VALUE_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "26LastValIgnoreNullsGetValueIN10impala_udf10BooleanValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.TINYINT,
+ "26LastValIgnoreNullsGetValueIN10impala_udf10TinyIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.SMALLINT,
+ "26LastValIgnoreNullsGetValueIN10impala_udf11SmallIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.INT,
+ "26LastValIgnoreNullsGetValueIN10impala_udf6IntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.BIGINT,
+ "26LastValIgnoreNullsGetValueIN10impala_udf9BigIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.FLOAT,
+ "26LastValIgnoreNullsGetValueIN10impala_udf8FloatValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.DOUBLE,
+ "26LastValIgnoreNullsGetValueIN10impala_udf9DoubleValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.STRING,
+ "26LastValIgnoreNullsGetValueIN10impala_udf9StringValEEET_PNS2_15FunctionContextERKS3_")
+ .put(Type.TIMESTAMP,
+ "26LastValIgnoreNullsGetValueIN10impala_udf12TimestampValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.DECIMAL,
+ "26LastValIgnoreNullsGetValueIN10impala_udf10DecimalValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .build();
+
+ private static final Map<Type, String> LAST_VALUE_IGNORE_NULLS_FINALIZE_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf10BooleanValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.TINYINT,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf10TinyIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.SMALLINT,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf11SmallIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.INT,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf6IntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.BIGINT,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf9BigIntValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.FLOAT,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf8FloatValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.DOUBLE,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf9DoubleValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.STRING,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf9StringValEEET_PNS2_15FunctionContextERKS3_")
+ .put(Type.TIMESTAMP,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf12TimestampValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .put(Type.DECIMAL,
+ "26LastValIgnoreNullsFinalizeIN10impala_udf10DecimalValEEET_PNS2_15FunctionContextERKNS2_9StringValE")
+ .build();
+
private static final Map<Type, String> FIRST_VALUE_UPDATE_SYMBOL =
ImmutableMap.<Type, String>builder()
.put(Type.BOOLEAN,
@@ -512,6 +632,30 @@ public class BuiltinsDb extends Db {
"14FirstValUpdateIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS6_")
.build();
+ private static final Map<Type, String> FIRST_VALUE_IGNORE_NULLS_UPDATE_SYMBOL =
+ ImmutableMap.<Type, String>builder()
+ .put(Type.BOOLEAN,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf10BooleanValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.DECIMAL,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf10DecimalValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.TINYINT,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf10TinyIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.SMALLINT,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf11SmallIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.TIMESTAMP,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf12TimestampValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.INT,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf6IntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.FLOAT,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf8FloatValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.BIGINT,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf9BigIntValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.DOUBLE,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf9DoubleValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .put(Type.STRING,
+ "25FirstValIgnoreNullsUpdateIN10impala_udf9StringValEEEvPNS2_15FunctionContextERKT_PS6_")
+ .build();
+
// Populate all the aggregate builtins in the catalog.
// null symbols indicate the function does not need that step of the evaluation.
// An empty symbol indicates a TODO for the BE to implement the function.
@@ -853,16 +997,33 @@ public class BuiltinsDb extends Db {
t == Type.STRING ? stringValGetValue : null,
t == Type.STRING ? stringValSerializeOrFinalize : null,
false));
+ db.addBuiltin(AggregateFunction.createAnalyticBuiltin(
+ db, "first_value_ignore_nulls", Lists.newArrayList(t), t, t,
+ t.isStringType() ? initNullString : initNull,
+ prefix + FIRST_VALUE_IGNORE_NULLS_UPDATE_SYMBOL.get(t),
+ null,
+ t == Type.STRING ? stringValGetValue : null,
+ t == Type.STRING ? stringValSerializeOrFinalize : null,
+ false));
db.addBuiltin(AggregateFunction.createAnalyticBuiltin(
db, "last_value", Lists.newArrayList(t), t, t,
t.isStringType() ? initNullString : initNull,
- prefix + LAST_VALUE_UPDATE_SYMBOL.get(t),
+ prefix + UPDATE_VAL_SYMBOL.get(t),
prefix + LAST_VALUE_REMOVE_SYMBOL.get(t),
t == Type.STRING ? stringValGetValue : null,
t == Type.STRING ? stringValSerializeOrFinalize : null));
db.addBuiltin(AggregateFunction.createAnalyticBuiltin(
+ db, "last_value_ignore_nulls", Lists.newArrayList(t), t, Type.STRING,
+ prefix + LAST_VALUE_IGNORE_NULLS_INIT_SYMBOL.get(t),
+ prefix + LAST_VALUE_IGNORE_NULLS_UPDATE_SYMBOL.get(t),
+ prefix + LAST_VALUE_IGNORE_NULLS_REMOVE_SYMBOL.get(t),
+ prefix + LAST_VALUE_IGNORE_NULLS_GET_VALUE_SYMBOL.get(t),
+ prefix + LAST_VALUE_IGNORE_NULLS_FINALIZE_SYMBOL.get(t),
+ false));
+
+ db.addBuiltin(AggregateFunction.createAnalyticBuiltin(
db, "lag", Lists.newArrayList(t, Type.BIGINT, t), t, t,
prefix + OFFSET_FN_INIT_SYMBOL.get(t),
prefix + OFFSET_FN_UPDATE_SYMBOL.get(t),
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/fe/src/test/java/com/cloudera/impala/analysis/AnalyzeExprsTest.java
----------------------------------------------------------------------
diff --git a/fe/src/test/java/com/cloudera/impala/analysis/AnalyzeExprsTest.java b/fe/src/test/java/com/cloudera/impala/analysis/AnalyzeExprsTest.java
index 7305a68..d0cda29 100644
--- a/fe/src/test/java/com/cloudera/impala/analysis/AnalyzeExprsTest.java
+++ b/fe/src/test/java/com/cloudera/impala/analysis/AnalyzeExprsTest.java
@@ -717,6 +717,16 @@ public class AnalyzeExprsTest extends AnalyzerTest {
AnalyzesOk(
"select id from functional.alltypes order by rank() over (order by tinyint_col)");
+ // last_value/first_value
+ AnalyzesOk(
+ "select first_value(tinyint_col) over (order by id) from functional.alltypesagg");
+ AnalyzesOk(
+ "select last_value(tinyint_col) over (order by id) from functional.alltypesagg");
+ AnalyzesOk("select first_value(tinyint_col ignore nulls) over (order by id) from "
+ + "functional.alltypesagg");
+ AnalyzesOk("select last_value(tinyint_col ignore nulls) over (order by id) from "
+ + "functional.alltypesagg");
+
// legal combinations of analytic and agg functions
AnalyzesOk("select sum(count(id)) over (partition by min(int_col) "
+ "order by max(bigint_col)) from functional.alltypes group by id, tinyint_col "
@@ -840,6 +850,10 @@ public class AnalyzeExprsTest extends AnalyzerTest {
"select min(id) over (order by tinyint_col) as X from functional.alltypes "
+ "group by id, tinyint_col order by rank() over (order by X)",
"Nesting of analytic expressions is not allowed");
+ // IGNORE NULLS may only be used with first_value/last_value
+ AnalysisError(
+ "select sum(id ignore nulls) over (order by id) from functional.alltypes",
+ "Function SUM does not accept the keyword IGNORE NULLS.");
// IMPALA-1256: AnalyticExpr.resetAnalysisState() didn't sync up w/ orderByElements_
AnalyzesOk("with t as ("
+ "select * from (select sum(t1.year) over ("
@@ -1576,6 +1590,10 @@ public class AnalyzeExprsTest extends AnalyzerTest {
"Function functional.extract conflicts with the EXTRACT builtin");
AnalysisError("select date_part(year from now())",
"Function DATE_PART does not accept the keyword FROM");
+
+ // IGNORE NULLS may only be used with first_value/last_value
+ AnalysisError("select lower('FOO' ignore nulls)",
+ "Function LOWER does not accept the keyword IGNORE NULLS.");
}
@Test
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/testdata/workloads/functional-planner/queries/PlannerTest/analytic-fns.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/functional-planner/queries/PlannerTest/analytic-fns.test b/testdata/workloads/functional-planner/queries/PlannerTest/analytic-fns.test
index 0f9b2d0..a62e21d 100644
--- a/testdata/workloads/functional-planner/queries/PlannerTest/analytic-fns.test
+++ b/testdata/workloads/functional-planner/queries/PlannerTest/analytic-fns.test
@@ -687,7 +687,7 @@ first_value(int_col)
over(partition by double_col, tinyint_col order by int_col desc
rows between 2 preceding and 2 following),
# sort group 2
-last_value(int_col)
+last_value(int_col ignore nulls)
over(partition by double_col, tinyint_col order by int_col asc
rows between 2 preceding and 2 following),
# different partition
@@ -734,7 +734,7 @@ from functional.alltypesagg
| window: ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
|
04:ANALYTIC
-| functions: last_value(int_col)
+| functions: last_value_ignore_nulls(int_col)
| partition by: double_col, tinyint_col
| order by: int_col ASC
| window: ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
@@ -790,7 +790,7 @@ from functional.alltypesagg
| window: ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
|
04:ANALYTIC
-| functions: last_value(int_col)
+| functions: last_value_ignore_nulls(int_col)
| partition by: double_col, tinyint_col
| order by: int_col ASC
| window: ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
@@ -1521,9 +1521,10 @@ from functional.alltypesagg
partitions=11/11 files=11 size=814.73KB
====
# Test canonical function/window/order: Reverse windows ending in UNBOUNDED FOLLOWING
-# and not starting with UNBOUNDED PRECEDING, and change first_value() to last_value().
-# Set the upper boundary to CURRENT_ROW for first_value() if the lower boundary is
-# UNBOUNDED_PRECEDING.
+# and either not starting with UNBOUNDED PRECEDING or first_value(... IGNORE NULLS), and
+# change first_value() to last_value().
+# Set the end boundary to CURRENT_ROW for first_value() if the start boundary is
+# UNBOUNDED_PRECEDING and not IGNORE NULLS.
select
# windows, sort order and function should be reversed
last_value(int_col) over(partition by tinyint_col order by id desc nulls first, bool_col asc nulls last
@@ -1531,22 +1532,31 @@ last_value(int_col) over(partition by tinyint_col order by id desc nulls first,
# TODO: Revert window when RANGE offsets are supported
last_value(int_col) over(partition by tinyint_col order by id asc nulls last
range between current row and unbounded following),
-# range between 4 preceding and unbounded following),
# windows, sort order and function should remain unchanged
-first_value(bigint_col) over(partition by tinyint_col order by id),
+first_value(bigint_col ignore nulls) over(partition by tinyint_col order by id),
first_value(bigint_col) over(partition by tinyint_col order by id, int_col
rows between 6 preceding and 8 following),
# TODO: Revert window when RANGE offsets are supported
first_value(bigint_col) over(partition by tinyint_col order by id
- range between unbounded preceding and unbounded following)
-# range between unbounded preceding and 10 following)
+ range between unbounded preceding and unbounded following),
+# window, order, and function should be reversed
+first_value(tinyint_col ignore nulls) over (order by id
+ rows between 1 following and 2 following)
from functional.alltypesagg
---- PLAN
+09:ANALYTIC
+| functions: last_value_ignore_nulls(tinyint_col)
+| order by: id DESC
+| window: ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
+|
+08:SORT
+| order by: id DESC NULLS FIRST
+|
07:ANALYTIC
-| functions: first_value(bigint_col)
+| functions: first_value_ignore_nulls(bigint_col), first_value(bigint_col)
| partition by: tinyint_col
| order by: id ASC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
06:ANALYTIC
| functions: first_value(int_col)
@@ -1570,7 +1580,7 @@ from functional.alltypesagg
| functions: first_value(int_col)
| partition by: tinyint_col
| order by: id DESC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
01:SORT
| order by: tinyint_col ASC NULLS FIRST, id DESC NULLS FIRST
@@ -1582,12 +1592,12 @@ from functional.alltypesagg
# and not starting with UNBOUNDED PRECEDING, and change last_value() to first_value()
select
# windows, sort order and function should be reversed
-last_value(int_col) over(partition by tinyint_col order by id desc nulls first, bool_col asc nulls last
- rows between 2 preceding and unbounded following),
+last_value(int_col) over(partition by tinyint_col order by id desc nulls first,
+ bool_col asc nulls last
+ rows between 2 preceding and unbounded following),
# TODO: Revert window when RANGE offsets are supported
last_value(int_col) over(partition by tinyint_col order by id asc nulls last
range between current row and unbounded following),
-# range between 4 preceding and unbounded following),
# windows, sort order and function should remain unchanged
last_value(bigint_col) over(partition by tinyint_col order by id),
last_value(bigint_col) over(partition by tinyint_col order by id, int_col
@@ -1595,20 +1605,19 @@ last_value(bigint_col) over(partition by tinyint_col order by id, int_col
# TODO: Revert window when RANGE offsets are supported
last_value(bigint_col) over(partition by tinyint_col order by id
range between unbounded preceding and unbounded following)
-# range between unbounded preceding and 10 following)
from functional.alltypesagg
---- PLAN
08:ANALYTIC
| functions: last_value(bigint_col)
| partition by: tinyint_col
| order by: id ASC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
|
07:ANALYTIC
| functions: last_value(bigint_col)
| partition by: tinyint_col
| order by: id ASC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
06:ANALYTIC
| functions: first_value(int_col)
@@ -1632,7 +1641,7 @@ from functional.alltypesagg
| functions: first_value(int_col)
| partition by: tinyint_col
| order by: id DESC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
|
01:SORT
| order by: tinyint_col ASC NULLS FIRST, id DESC NULLS FIRST
@@ -1798,7 +1807,7 @@ where year = 2009 and tinyint_col + 1 = 1
| functions: last_value(tinyint_col)
| partition by: id, year
| order by: int_col ASC
-| window: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
|
01:SORT
| order by: id ASC NULLS FIRST, year ASC NULLS FIRST, int_col ASC
@@ -2181,3 +2190,22 @@ WRITE TO HDFS [default.impala_2832, OVERWRITE=false]
00:SCAN HDFS [functional.alltypes]
partitions=24/24 files=24 size=478.45KB
====
+# For first/last_value(), ranges windows get rewritten as rows windows,
+# so these should be grouped.
+select last_value(int_col) over (order by bigint_col
+ range between unbounded preceding and current row),
+first_value(int_col) over (order by bigint_col
+ rows between unbounded preceding and current row)
+from functional.alltypes
+---- PLAN
+02:ANALYTIC
+| functions: last_value(int_col), first_value(int_col)
+| order by: bigint_col ASC
+| window: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+|
+01:SORT
+| order by: bigint_col ASC
+|
+00:SCAN HDFS [functional.alltypes]
+ partitions=24/24 files=24 size=478.45KB
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/testdata/workloads/functional-query/queries/QueryTest/analytic-fns.test
----------------------------------------------------------------------
diff --git a/testdata/workloads/functional-query/queries/QueryTest/analytic-fns.test b/testdata/workloads/functional-query/queries/QueryTest/analytic-fns.test
index b31620f..93431bf 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/analytic-fns.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/analytic-fns.test
@@ -1108,7 +1108,7 @@ select
first_value(-32.9) over (order by id rows between 92 preceding and unbounded following),
first_value(1.1) over (order by id rows between 92 preceding and 1 preceding)
from alltypestiny
----- RESULTS
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
-32.9,NULL
-32.9,1.1
-32.9,1.1
@@ -1127,7 +1127,7 @@ select id, date_string_col,
first_value(id) over (partition by date_string_col order by id
rows between 10 preceding and 3 preceding)
from alltypes where id < 15 order by id
----- RESULTS
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
0,'01/01/09',NULL
1,'01/01/09',NULL
2,'01/01/09',NULL
@@ -1638,3 +1638,258 @@ SMALLINT
NULL
NULL
====
+---- QUERY
+# Start IGNORE NULLS
+# Test all possible combinations of bound types for last_value that don't get rewritten.
+select id, tinyint_col,
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and 1 preceding),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and current row),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and 1 following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and unbounded following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between 2 preceding and 1 preceding),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between 2 preceding and current row),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 preceding and 2 following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between current row and current row),
+ last_value(cast(tinyint_col as string) ignore nulls) over
+ (order by id rows between current row and 1 following),
+ last_value(cast(tinyint_col as string) ignore nulls) over
+ (order by id rows between 1 following and 2 following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id range between unbounded preceding and current row),
+ last_value(tinyint_col ignore nulls) over
+ (order by id range between unbounded preceding and unbounded following)
+from functional.alltypesagg where id < 21
+---- TYPES
+INT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, STRING, STRING, TINYINT, TINYINT
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,NULL,NULL,NULL,NULL,9,NULL,NULL,1,NULL,'NULL','1',NULL,9
+0,NULL,NULL,NULL,1,9,NULL,NULL,2,NULL,'1','2',NULL,9
+1,1,NULL,1,2,9,NULL,1,3,1,'2','3',1,9
+2,2,1,2,3,9,1,2,4,2,'3','4',2,9
+3,3,2,3,4,9,2,3,5,3,'4','5',3,9
+4,4,3,4,5,9,3,4,6,4,'5','6',4,9
+5,5,4,5,6,9,4,5,7,5,'6','7',5,9
+6,6,5,6,7,9,5,6,8,6,'7','8',6,9
+7,7,6,7,8,9,6,7,9,7,'8','9',7,9
+8,8,7,8,9,9,7,8,9,8,'9','9',8,9
+9,9,8,9,9,9,8,9,9,9,'9','NULL',9,9
+10,NULL,9,9,9,9,9,9,1,NULL,'NULL','1',9,9
+10,NULL,9,9,1,9,9,9,2,NULL,'1','2',9,9
+11,1,9,1,2,9,NULL,1,3,1,'2','3',1,9
+12,2,1,2,3,9,1,2,4,2,'3','4',2,9
+13,3,2,3,4,9,2,3,5,3,'4','5',3,9
+14,4,3,4,5,9,3,4,6,4,'5','6',4,9
+15,5,4,5,6,9,4,5,7,5,'6','7',5,9
+16,6,5,6,7,9,5,6,8,6,'7','8',6,9
+17,7,6,7,8,9,6,7,9,7,'8','9',7,9
+18,8,7,8,9,9,7,8,9,8,'9','9',8,9
+19,9,8,9,9,9,8,9,9,9,'9','NULL',9,9
+20,NULL,9,9,9,9,9,9,9,NULL,'NULL','NULL',9,9
+20,NULL,9,9,9,9,9,9,NULL,NULL,'NULL','NULL',9,9
+====
+---- QUERY
+# Test the remaining combinations of bound types. These are handled separately from above
+# because they are rewritten as first_value() functions, so their results can be returned
+# in a different order, since there isn't a total ordering on id.
+select id, tinyint_col,
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 preceding and unbounded following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between current row and unbounded following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id rows between 2 following and unbounded following),
+ last_value(tinyint_col ignore nulls) over
+ (order by id range between current row and unbounded following)
+from functional.alltypesagg where id < 21 order by id
+---- TYPES
+INT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,NULL,9,9,9,9
+0,NULL,9,9,9,9
+1,1,9,9,9,9
+2,2,9,9,9,9
+3,3,9,9,9,9
+4,4,9,9,9,9
+5,5,9,9,9,9
+6,6,9,9,9,9
+7,7,9,9,9,9
+8,8,9,9,9,9
+9,9,9,9,9,9
+10,NULL,9,9,9,9
+10,NULL,9,9,9,9
+11,1,9,9,9,9
+12,2,9,9,9,9
+13,3,9,9,9,9
+14,4,9,9,9,9
+15,5,9,9,9,9
+16,6,9,9,9,9
+17,7,9,9,9,9
+18,8,9,9,NULL,9
+19,9,9,9,NULL,9
+20,NULL,9,NULL,NULL,NULL
+20,NULL,NULL,NULL,NULL,NULL
+====
+---- QUERY
+# Test all combinations of bound types for first_value that don't get rewritten.
+select id, tinyint_col,
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and 1 preceding),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between unbounded preceding and current row),
+ first_value(cast(tinyint_col as string) ignore nulls) over
+ (order by id rows between unbounded preceding and 1 following),
+ first_value(cast(tinyint_col as string) ignore nulls) over
+ (order by id rows between unbounded preceding and unbounded following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id range between unbounded preceding and current row),
+ first_value(tinyint_col ignore nulls) over
+ (order by id range between unbounded preceding and unbounded following)
+from functional.alltypesagg where id < 21
+---- TYPES
+INT, TINYINT, TINYINT, TINYINT, STRING, STRING, TINYINT, TINYINT
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,NULL,NULL,NULL,'NULL','1',NULL,1
+0,NULL,NULL,NULL,'1','1',NULL,1
+1,1,NULL,1,'1','1',1,1
+2,2,1,1,'1','1',1,1
+3,3,1,1,'1','1',1,1
+4,4,1,1,'1','1',1,1
+5,5,1,1,'1','1',1,1
+6,6,1,1,'1','1',1,1
+7,7,1,1,'1','1',1,1
+8,8,1,1,'1','1',1,1
+9,9,1,1,'1','1',1,1
+10,NULL,1,1,'1','1',1,1
+10,NULL,1,1,'1','1',1,1
+11,1,1,1,'1','1',1,1
+12,2,1,1,'1','1',1,1
+13,3,1,1,'1','1',1,1
+14,4,1,1,'1','1',1,1
+15,5,1,1,'1','1',1,1
+16,6,1,1,'1','1',1,1
+17,7,1,1,'1','1',1,1
+18,8,1,1,'1','1',1,1
+19,9,1,1,'1','1',1,1
+20,NULL,1,1,'1','1',1,1
+20,NULL,1,1,'1','1',1,1
+====
+---- QUERY
+# Test combinations of bound types for first_value that get rewritten as last_value.
+select id, tinyint_col,
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 2 preceding and 1 preceding),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 preceding and current row),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 preceding and 1 following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 preceding and unbounded following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between current row and current row),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between current row and 1 following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between current row and unbounded following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 1 following and 2 following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id rows between 2 following and unbounded following),
+ first_value(tinyint_col ignore nulls) over
+ (order by id range between current row and unbounded following)
+from functional.alltypesagg where id < 21
+---- TYPES
+INT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT, TINYINT
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,NULL,NULL,NULL,NULL,1,NULL,NULL,1,1,1,1
+0,NULL,NULL,NULL,1,1,NULL,1,1,1,2,1
+1,1,NULL,1,1,1,1,1,1,2,3,1
+2,2,1,1,1,1,2,2,2,3,4,2
+3,3,1,2,2,2,3,3,3,4,5,3
+4,4,2,3,3,3,4,4,4,5,6,4
+5,5,3,4,4,4,5,5,5,6,7,5
+6,6,4,5,5,5,6,6,6,7,8,6
+7,7,5,6,6,6,7,7,7,8,9,7
+8,8,6,7,7,7,8,8,8,9,1,8
+9,9,7,8,8,8,9,9,9,NULL,1,9
+10,NULL,8,9,9,9,NULL,NULL,1,1,1,1
+10,NULL,9,NULL,1,1,NULL,1,1,1,2,1
+11,1,NULL,1,1,1,1,1,1,2,3,1
+12,2,1,1,1,1,2,2,2,3,4,2
+13,3,1,2,2,2,3,3,3,4,5,3
+14,4,2,3,3,3,4,4,4,5,6,4
+15,5,3,4,4,4,5,5,5,6,7,5
+16,6,4,5,5,5,6,6,6,7,8,6
+17,7,5,6,6,6,7,7,7,8,9,7
+18,8,6,7,7,7,8,8,8,9,NULL,8
+19,9,7,8,8,8,9,9,9,NULL,NULL,9
+20,NULL,8,9,9,9,NULL,NULL,NULL,NULL,NULL,NULL
+20,NULL,9,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
+====
+---- QUERY
+# Test all possible types for last_value (except decimal).
+select id,
+ last_value(bool_col ignore nulls) over (order by id),
+ last_value(tinyint_col ignore nulls) over (order by id),
+ last_value(smallint_col ignore nulls) over (order by id),
+ last_value(int_col ignore nulls) over (order by id),
+ last_value(bigint_col ignore nulls) over (order by id),
+ last_value(float_col ignore nulls) over (order by id),
+ last_value(double_col ignore nulls) over (order by id),
+ last_value(string_col ignore nulls) over (order by id),
+ last_value(timestamp_col ignore nulls) over (order by id)
+from functional.alltypesagg where id < 5
+---- TYPES
+INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, TIMESTAMP
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,true,NULL,NULL,NULL,NULL,NULL,NULL,'0',2010-01-01 00:00:00
+0,true,NULL,NULL,NULL,NULL,NULL,NULL,'0',2010-01-01 00:00:00
+1,false,1,1,1,10,1.100000023841858,10.1,'1',2010-01-01 00:01:00
+2,true,2,2,2,20,2.200000047683716,20.2,'2',2010-01-01 00:02:00.100000000
+3,false,3,3,3,30,3.299999952316284,30.3,'3',2010-01-01 00:03:00.300000000
+4,true,4,4,4,40,4.400000095367432,40.4,'4',2010-01-01 00:04:00.600000000
+====
+---- QUERY
+# Test all possible types for first_value (except decimal).
+select id,
+ first_value(bool_col ignore nulls) over (order by id),
+ first_value(tinyint_col ignore nulls) over (order by id),
+ first_value(smallint_col ignore nulls) over (order by id),
+ first_value(int_col ignore nulls) over (order by id),
+ first_value(bigint_col ignore nulls) over (order by id),
+ first_value(float_col ignore nulls) over (order by id),
+ first_value(double_col ignore nulls) over (order by id),
+ first_value(string_col ignore nulls) over (order by id),
+ first_value(timestamp_col ignore nulls) over (order by id)
+from functional.alltypesagg where id < 5
+---- TYPES
+INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, TIMESTAMP
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+0,true,NULL,NULL,NULL,NULL,NULL,NULL,'0',2010-01-01 00:00:00
+0,true,NULL,NULL,NULL,NULL,NULL,NULL,'0',2010-01-01 00:00:00
+1,true,1,1,1,10,1.100000023841858,10.1,'0',2010-01-01 00:00:00
+2,true,1,1,1,10,1.100000023841858,10.1,'0',2010-01-01 00:00:00
+3,true,1,1,1,10,1.100000023841858,10.1,'0',2010-01-01 00:00:00
+4,true,1,1,1,10,1.100000023841858,10.1,'0',2010-01-01 00:00:00
+====
+---- QUERY
+select
+last_value(d1 ignore nulls) over (order by d1),
+first_value(d1 ignore nulls) over (order by d1)
+from functional.decimal_tbl
+---- TYPES
+DECIMAL, DECIMAL
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
+1234,1234
+2345,1234
+12345,1234
+12345,1234
+132842,1234
+====
http://git-wip-us.apache.org/repos/asf/incubator-impala/blob/343bdad8/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 18f397b..6e7cbae 100644
--- a/testdata/workloads/functional-query/queries/QueryTest/decimal.test
+++ b/testdata/workloads/functional-query/queries/QueryTest/decimal.test
@@ -370,10 +370,10 @@ DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL
---- QUERY
# IMPALA-1559: FIRST_VALUE rewrite function intermediate type not matching slot type
select
-first_value(c3) over (order by c3 rows between 92 preceding and current row),
-first_value(c2) over (order by c3 rows between 92 preceding and 1 preceding)
+first_value(c3) over (order by c1 rows between 92 preceding and current row),
+first_value(c2) over (order by c1 rows between 92 preceding and 1 preceding)
from decimal_tiny where c3 = 0.0
----- RESULTS
+---- RESULTS: VERIFY_IS_EQUAL_SORTED
0.0,NULL
0.0,100.00000
0.0,100.00000