This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 57391671424 [feature](window_function) support to secondary argument
to ignore null values in first_value/last_value (#27623)
57391671424 is described below
commit 57391671424c4cf3ad9866ae1c7079eaef78f055
Author: Jerry Hu <[email protected]>
AuthorDate: Thu Nov 30 09:56:43 2023 +0800
[feature](window_function) support to secondary argument to ignore null
values in first_value/last_value (#27623)
---
.../aggregate_function_window.cpp | 27 +++-
.../aggregate_function_window.h | 37 ++++-
.../java/org/apache/doris/catalog/FunctionSet.java | 20 ++-
.../rules/analysis/WindowFunctionChecker.java | 2 +-
.../functions/window/FirstOrLastValue.java | 23 ++-
.../expressions/functions/window/FirstValue.java | 14 +-
.../expressions/functions/window/LastValue.java | 12 +-
...ays_nullable_window_function_legacy_planner.out | 97 +++++++++++++
.../correctness_p0/test_first_value_window.out | 21 +++
... => test_first_value_window_legacy_planner.out} | 21 +++
.../data/correctness_p0/test_last_value_window.out | 15 ++
...t => test_last_value_window_legacy_planner.out} | 15 ++
.../test_always_nullable_window_function.groovy | 2 +
...nullable_window_function_legacy_planner.groovy} | 24 +--
.../correctness_p0/test_first_value_window.groovy | 80 +++++++++-
.../test_first_value_window_legacy_planner.groovy | 161 +++++++++++++++++++++
.../correctness_p0/test_last_value_window.groovy | 42 +++++-
...> test_last_value_window_legacy_planner.groovy} | 47 +++++-
18 files changed, 618 insertions(+), 42 deletions(-)
diff --git a/be/src/vec/aggregate_functions/aggregate_function_window.cpp
b/be/src/vec/aggregate_functions/aggregate_function_window.cpp
index fdc8a0bf861..2b260e6baee 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_window.cpp
+++ b/be/src/vec/aggregate_functions/aggregate_function_window.cpp
@@ -32,17 +32,32 @@
namespace doris::vectorized {
template <template <typename> class AggregateFunctionTemplate,
- template <typename ColVecType, bool, bool> class Data, template
<typename> class Impl,
- bool result_is_nullable, bool arg_is_nullable>
+ template <typename ColVecType, bool, bool> class Data,
+ template <typename, bool> class Impl, bool result_is_nullable, bool
arg_is_nullable>
AggregateFunctionPtr create_function_lead_lag_first_last(const String& name,
const DataTypes&
argument_types) {
auto type = remove_nullable(argument_types[0]);
WhichDataType which(*type);
-#define DISPATCH(TYPE, COLUMN_TYPE) \
- if (which.idx == TypeIndex::TYPE) \
- return std::make_shared<AggregateFunctionTemplate< \
- Impl<Data<COLUMN_TYPE, result_is_nullable,
arg_is_nullable>>>>(argument_types);
+ bool arg_ignore_null_value = false;
+ if (argument_types.size() == 2) {
+ DCHECK(name == "first_value" || name == "last_value") << "invalid
function name: " << name;
+ arg_ignore_null_value = true;
+ }
+
+#define DISPATCH(TYPE, COLUMN_TYPE)
\
+ if (which.idx == TypeIndex::TYPE) {
\
+ if (arg_ignore_null_value) {
\
+ return std::make_shared<AggregateFunctionTemplate<
\
+ Impl<Data<COLUMN_TYPE, result_is_nullable,
arg_is_nullable>, true>>>( \
+ argument_types);
\
+ } else {
\
+ return std::make_shared<AggregateFunctionTemplate<
\
+ Impl<Data<COLUMN_TYPE, result_is_nullable,
arg_is_nullable>, false>>>( \
+ argument_types);
\
+ }
\
+ }
+
TYPE_TO_COLUMN_TYPE(DISPATCH)
#undef DISPATCH
diff --git a/be/src/vec/aggregate_functions/aggregate_function_window.h
b/be/src/vec/aggregate_functions/aggregate_function_window.h
index a8fb89100db..808b48c4e36 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_window.h
+++ b/be/src/vec/aggregate_functions/aggregate_function_window.h
@@ -309,7 +309,7 @@ private:
bool _is_inited = false;
};
-template <typename Data>
+template <typename Data, bool = false>
struct WindowFunctionLeadImpl : Data {
void add_range_single_place(int64_t partition_start, int64_t
partition_end, int64_t frame_start,
int64_t frame_end, const IColumn** columns) {
@@ -328,7 +328,7 @@ struct WindowFunctionLeadImpl : Data {
static const char* name() { return "lead"; }
};
-template <typename Data>
+template <typename Data, bool = false>
struct WindowFunctionLagImpl : Data {
void add_range_single_place(int64_t partition_start, int64_t
partition_end, int64_t frame_start,
int64_t frame_end, const IColumn** columns) {
@@ -350,7 +350,7 @@ struct WindowFunctionLagImpl : Data {
// TODO: first_value && last_value in some corner case will be core,
// if need to simply change it, should set them to always nullable insert into
null value, and register in cpp maybe be change
// But it's may be another better way to handle it
-template <typename Data>
+template <typename Data, bool arg_ignore_null = false>
struct WindowFunctionFirstImpl : Data {
void add_range_single_place(int64_t partition_start, int64_t
partition_end, int64_t frame_start,
int64_t frame_end, const IColumn** columns) {
@@ -363,13 +363,27 @@ struct WindowFunctionFirstImpl : Data {
return;
}
frame_start = std::max<int64_t>(frame_start, partition_start);
+
+ if constexpr (arg_ignore_null) {
+ frame_end = std::min<int64_t>(frame_end, partition_end);
+
+ auto& second_arg = assert_cast<const
ColumnVector<UInt8>&>(*columns[1]);
+ auto ignore_null_value = second_arg.get_data()[0];
+
+ if (ignore_null_value && columns[0]->is_nullable()) {
+ auto& arg_nullable = assert_cast<const
ColumnNullable&>(*columns[0]);
+ while (frame_start < frame_end - 1 &&
arg_nullable.is_null_at(frame_start)) {
+ frame_start++;
+ }
+ }
+ }
this->set_value(columns, frame_start);
}
static const char* name() { return "first_value"; }
};
-template <typename Data>
+template <typename Data, bool arg_ignore_null = false>
struct WindowFunctionLastImpl : Data {
void add_range_single_place(int64_t partition_start, int64_t
partition_end, int64_t frame_start,
int64_t frame_end, const IColumn** columns) {
@@ -380,6 +394,21 @@ struct WindowFunctionLastImpl : Data {
return;
}
frame_end = std::min<int64_t>(frame_end, partition_end);
+
+ if constexpr (arg_ignore_null) {
+ frame_start = std::max<int64_t>(frame_start, partition_start);
+
+ auto& second_arg = assert_cast<const
ColumnVector<UInt8>&>(*columns[1]);
+ auto ignore_null_value = second_arg.get_data()[0];
+
+ if (ignore_null_value && columns[0]->is_nullable()) {
+ auto& arg_nullable = assert_cast<const
ColumnNullable&>(*columns[0]);
+ while (frame_start < (frame_end - 1) &&
arg_nullable.is_null_at(frame_end - 1)) {
+ frame_end--;
+ }
+ }
+ }
+
this->set_value(columns, frame_end - 1);
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
b/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
index 70e974c40cd..e18370560c3 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/FunctionSet.java
@@ -1426,6 +1426,22 @@ public class FunctionSet<T> {
null,
"",
"", true));
+
+ addBuiltin(AggregateFunction.createAnalyticBuiltin("first_value",
+ Lists.newArrayList(new ArrayType(t), Type.BOOLEAN), new
ArrayType(t), Type.ARRAY,
+ "",
+ "",
+ null,
+ "",
+ "", true));
+
+ addBuiltin(AggregateFunction.createAnalyticBuiltin("last_value",
+ Lists.newArrayList(new ArrayType(t), Type.BOOLEAN), new
ArrayType(t), Type.ARRAY,
+ "",
+ "",
+ null,
+ "",
+ "", true));
}
// Avg
@@ -1584,7 +1600,7 @@ public class FunctionSet<T> {
//vec first_value
addBuiltin(AggregateFunction.createAnalyticBuiltin(
- "first_value", Lists.newArrayList(t), t, t,
+ "first_value", Lists.newArrayList(t, Type.BOOLEAN), t, t,
"",
"",
null,
@@ -1601,7 +1617,7 @@ public class FunctionSet<T> {
false, false));
//vec last_value
addBuiltin(AggregateFunction.createAnalyticBuiltin(
- "last_value", Lists.newArrayList(t), t, t,
+ "last_value", Lists.newArrayList(t, Type.BOOLEAN), t, t,
"",
"",
"",
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/WindowFunctionChecker.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/WindowFunctionChecker.java
index 9c98fba59aa..9f5de748694 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/WindowFunctionChecker.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/WindowFunctionChecker.java
@@ -320,7 +320,7 @@ public class WindowFunctionChecker extends
DefaultExpressionVisitor<Expression,
&& wf.getLeftBoundary().isNot(FrameBoundType.PRECEDING)) {
windowExpression = windowExpression.withWindowFrame(
wf.withFrameUnits(FrameUnitsType.ROWS).withRightBoundary(wf.getLeftBoundary()));
- LastValue lastValue = new LastValue(firstValue.child());
+ LastValue lastValue = new LastValue(firstValue.children());
windowExpression = windowExpression.withFunction(lastValue);
return lastValue;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstOrLastValue.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstOrLastValue.java
index 463fe41aab2..bb0a68eef6a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstOrLastValue.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstOrLastValue.java
@@ -21,30 +21,41 @@ import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.trees.expressions.Expression;
import org.apache.doris.nereids.trees.expressions.functions.AlwaysNullable;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
-import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+import org.apache.doris.nereids.types.BooleanType;
import org.apache.doris.nereids.types.coercion.AnyDataType;
import com.google.common.collect.ImmutableList;
import java.util.List;
-/** parent class for first_value() and last_value() */
+/**
+ * parent class for first_value() and last_value()
+ */
public abstract class FirstOrLastValue extends WindowFunction
- implements UnaryExpression, AlwaysNullable,
ExplicitlyCastableSignature {
+ implements AlwaysNullable, ExplicitlyCastableSignature {
private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.retArgType(0).args(AnyDataType.INSTANCE_WITHOUT_INDEX)
+
FunctionSignature.retArgType(0).args(AnyDataType.INSTANCE_WITHOUT_INDEX),
+
FunctionSignature.retArgType(0).args(AnyDataType.INSTANCE_WITHOUT_INDEX,
BooleanType.INSTANCE)
);
+ public FirstOrLastValue(String name, Expression child, Expression
ignoreNullValue) {
+ super(name, child, ignoreNullValue);
+ }
+
public FirstOrLastValue(String name, Expression child) {
super(name, child);
}
+ public FirstOrLastValue(String name, List<Expression> children) {
+ super(name, children);
+ }
+
public FirstOrLastValue reverse() {
if (this instanceof FirstValue) {
- return new LastValue(child());
+ return new LastValue(children);
} else {
- return new FirstValue(child());
+ return new FirstValue(children);
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstValue.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstValue.java
index 834f2f3a65a..5afacefc184 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstValue.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/FirstValue.java
@@ -34,10 +34,18 @@ public class FirstValue extends FirstOrLastValue {
super("first_value", child);
}
+ public FirstValue(Expression child, Expression ignoreNullValue) {
+ super("first_value", child, ignoreNullValue);
+ }
+
+ public FirstValue(List<Expression> children) {
+ super("first_value", children);
+ }
+
@Override
public FirstValue withChildren(List<Expression> children) {
- Preconditions.checkArgument(children.size() == 1);
- return new FirstValue(children.get(0));
+ Preconditions.checkArgument(children.size() == 1 || children.size() ==
2);
+ return new FirstValue(children);
}
@Override
@@ -47,6 +55,6 @@ public class FirstValue extends FirstOrLastValue {
@Override
public DataType getDataType() {
- return child().getDataType();
+ return child(0).getDataType();
}
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/LastValue.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/LastValue.java
index c2644b6ade3..a4759ba8d66 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/LastValue.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/window/LastValue.java
@@ -34,10 +34,18 @@ public class LastValue extends FirstOrLastValue {
super("last_value", child);
}
+ public LastValue(Expression child, Expression ignoreNullValue) {
+ super("last_value", child, ignoreNullValue);
+ }
+
+ public LastValue(List<Expression> children) {
+ super("last_value", children);
+ }
+
@Override
public LastValue withChildren(List<Expression> children) {
- Preconditions.checkArgument(children.size() == 1);
- return new LastValue(children.get(0));
+ Preconditions.checkArgument(children.size() == 1 || children.size() ==
2);
+ return new LastValue(children);
}
@Override
diff --git
a/regression-test/data/correctness_p0/test_always_nullable_window_function_legacy_planner.out
b/regression-test/data/correctness_p0/test_always_nullable_window_function_legacy_planner.out
new file mode 100644
index 00000000000..b94f3ae38bb
--- /dev/null
+++
b/regression-test/data/correctness_p0/test_always_nullable_window_function_legacy_planner.out
@@ -0,0 +1,97 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !select_default --
+21 04-21-11 1 1 1 2 1.0 1 1
\N \N
+21 04-21-11 1 1 1 2 1.0 1 1
\N \N
+22 04-22-10-21 0 0 1 1 0.5 1 0
\N \N
+22 04-22-10-21 0 1 1 2 0.6666666666666666
1 0 \N \N
+22 04-22-10-21 1 0 0 1 0.3333333333333333
1 0 \N \N
+22 04-22-10-21 1 0 1 1 0.5 1 0
\N \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N \N
+
+-- !select_empty_window --
+21 04-21-11 1 \N \N \N \N \N \N
\N \N
+21 04-21-11 1 1 1 1 1.0 1 1
\N \N
+22 04-22-10-21 0 \N \N \N \N \N \N
\N 0
+22 04-22-10-21 0 1 1 1 1.0 1 1
0 \N
+22 04-22-10-21 1 0 0 0 0.0 0 0
\N 1
+22 04-22-10-21 1 0 0 0 0.0 0 0
1 \N
+23 04-23-10 1 \N \N \N \N \N \N
\N \N
+23 04-23-10 1 1 1 1 1.0 1 1
\N \N
+24 02-24-10-21 1 \N \N \N \N \N \N
\N \N
+24 02-24-10-21 1 1 1 1 1.0 1 1
\N \N
+
+-- !select_default_nullable --
+21 04-21-11 1 1 1 2 1.0 1 1
\N \N
+21 04-21-11 1 1 1 2 1.0 1 1
\N \N
+22 04-22-10-21 0 0 1 1 0.5 1 0
\N \N
+22 04-22-10-21 0 1 1 2 0.6666666666666666
1 0 \N \N
+22 04-22-10-21 1 0 0 1 0.3333333333333333
1 0 \N \N
+22 04-22-10-21 1 0 1 1 0.5 1 0
\N \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N \N
+
+-- !select_empty_window_nullable --
+21 04-21-11 1 \N \N \N \N \N \N
\N \N
+21 04-21-11 1 1 1 1 1.0 1 1
\N \N
+22 04-22-10-21 0 \N \N \N \N \N \N
\N 0
+22 04-22-10-21 0 1 1 1 1.0 1 1
0 \N
+22 04-22-10-21 1 0 0 0 0.0 0 0
\N 1
+22 04-22-10-21 1 0 0 0 0.0 0 0
1 \N
+23 04-23-10 1 \N \N \N \N \N \N
\N \N
+23 04-23-10 1 1 1 1 1.0 1 1
\N \N
+24 02-24-10-21 1 \N \N \N \N \N \N
\N \N
+24 02-24-10-21 1 1 1 1 1.0 1 1
\N \N
+
+-- !select_default_old_planner --
+21 04-21-11 1 1 1 2 1.0 1 1
\N 1
+21 04-21-11 1 1 1 2 1.0 1 1
1 \N
+22 04-22-10-21 0 0 1 1 0.5 1 0
\N 1
+22 04-22-10-21 0 1 1 2 0.6666666666666666
1 0 1 1
+22 04-22-10-21 1 0 0 1 0.3333333333333333
1 0 0 0
+22 04-22-10-21 1 0 1 1 0.5 1 0
0 \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N 1
+23 04-23-10 1 1 1 2 1.0 1 1
1 \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N 1
+24 02-24-10-21 1 1 1 2 1.0 1 1
1 \N
+
+-- !select_empty_window_old_planner --
+21 04-21-11 1 \N \N \N \N \N \N
\N \N
+21 04-21-11 1 1 1 1 1.0 1 1
\N \N
+22 04-22-10-21 0 \N \N \N \N \N \N
\N 0
+22 04-22-10-21 0 1 1 1 1.0 1 1
0 \N
+22 04-22-10-21 1 0 0 0 0.0 0 0
\N 1
+22 04-22-10-21 1 0 0 0 0.0 0 0
1 \N
+23 04-23-10 1 \N \N \N \N \N \N
\N \N
+23 04-23-10 1 1 1 1 1.0 1 1
\N \N
+24 02-24-10-21 1 \N \N \N \N \N \N
\N \N
+24 02-24-10-21 1 1 1 1 1.0 1 1
\N \N
+
+-- !select_default_nullable_old_planner --
+21 04-21-11 1 1 1 2 1.0 1 1
\N 1
+21 04-21-11 1 1 1 2 1.0 1 1
1 \N
+22 04-22-10-21 0 0 1 1 0.5 1 0
\N 1
+22 04-22-10-21 0 1 1 2 0.6666666666666666
1 0 1 1
+22 04-22-10-21 1 0 0 1 0.3333333333333333
1 0 0 0
+22 04-22-10-21 1 0 1 1 0.5 1 0
0 \N
+23 04-23-10 1 1 1 2 1.0 1 1
\N 1
+23 04-23-10 1 1 1 2 1.0 1 1
1 \N
+24 02-24-10-21 1 1 1 2 1.0 1 1
\N 1
+24 02-24-10-21 1 1 1 2 1.0 1 1
1 \N
+
+-- !select_empty_window_nullable_old_planner --
+21 04-21-11 1 \N \N \N \N \N \N
\N \N
+21 04-21-11 1 1 1 1 1.0 1 1
\N \N
+22 04-22-10-21 0 \N \N \N \N \N \N
\N 0
+22 04-22-10-21 0 1 1 1 1.0 1 1
0 \N
+22 04-22-10-21 1 0 0 0 0.0 0 0
\N 1
+22 04-22-10-21 1 0 0 0 0.0 0 0
1 \N
+23 04-23-10 1 \N \N \N \N \N \N
\N \N
+23 04-23-10 1 1 1 1 1.0 1 1
\N \N
+24 02-24-10-21 1 \N \N \N \N \N \N
\N \N
+24 02-24-10-21 1 1 1 1 1.0 1 1
\N \N
+
diff --git a/regression-test/data/correctness_p0/test_first_value_window.out
b/regression-test/data/correctness_p0/test_first_value_window.out
index cabae5b19ab..9951ad95c60 100644
--- a/regression-test/data/correctness_p0/test_first_value_window.out
+++ b/regression-test/data/correctness_p0/test_first_value_window.out
@@ -20,3 +20,24 @@
23 04-23-10 ["p7", "year4"] \N \N
24 02-24-10-21 [""] \N \N
+-- !select_default2 --
+21 04-21-11 1 1 1 1
+22 04-22-10-21 0 0 0 0
+22 04-22-10-21 1 0 0 0
+23 04-23-10 1 1 1 1
+24 02-24-10-21 1 1 1 1
+
+-- !select_default3 --
+1 21 04-21-11 \N \N \N 2
+2 21 04-21-12 2 \N \N 2
+3 21 04-21-13 3 2 2 2
+4 22 04-22-10-21 \N \N \N \N
+5 22 04-22-10-22 \N \N \N 5
+6 22 04-22-10-23 5 \N \N 5
+7 22 04-22-10-24 \N 5 5 5
+8 22 04-22-10-25 9 \N \N 9
+9 23 04-23-11 \N \N \N 10
+10 23 04-23-12 10 \N \N 10
+11 23 04-23-13 \N 10 10 10
+12 24 02-24-10-21 \N \N \N \N
+
diff --git a/regression-test/data/correctness_p0/test_first_value_window.out
b/regression-test/data/correctness_p0/test_first_value_window_legacy_planner.out
similarity index 59%
copy from regression-test/data/correctness_p0/test_first_value_window.out
copy to
regression-test/data/correctness_p0/test_first_value_window_legacy_planner.out
index cabae5b19ab..9951ad95c60 100644
--- a/regression-test/data/correctness_p0/test_first_value_window.out
+++
b/regression-test/data/correctness_p0/test_first_value_window_legacy_planner.out
@@ -20,3 +20,24 @@
23 04-23-10 ["p7", "year4"] \N \N
24 02-24-10-21 [""] \N \N
+-- !select_default2 --
+21 04-21-11 1 1 1 1
+22 04-22-10-21 0 0 0 0
+22 04-22-10-21 1 0 0 0
+23 04-23-10 1 1 1 1
+24 02-24-10-21 1 1 1 1
+
+-- !select_default3 --
+1 21 04-21-11 \N \N \N 2
+2 21 04-21-12 2 \N \N 2
+3 21 04-21-13 3 2 2 2
+4 22 04-22-10-21 \N \N \N \N
+5 22 04-22-10-22 \N \N \N 5
+6 22 04-22-10-23 5 \N \N 5
+7 22 04-22-10-24 \N 5 5 5
+8 22 04-22-10-25 9 \N \N 9
+9 23 04-23-11 \N \N \N 10
+10 23 04-23-12 10 \N \N 10
+11 23 04-23-13 \N 10 10 10
+12 24 02-24-10-21 \N \N \N \N
+
diff --git a/regression-test/data/correctness_p0/test_last_value_window.out
b/regression-test/data/correctness_p0/test_last_value_window.out
index 2ca759725ee..5792ccb0aa5 100644
--- a/regression-test/data/correctness_p0/test_last_value_window.out
+++ b/regression-test/data/correctness_p0/test_last_value_window.out
@@ -13,3 +13,18 @@
23 04-23-10 ["p7", "year4"] ["p7", "year4"]
24 02-24-10-21 [""] [""]
+-- !select_null --
+1 21 04-21-11 1 \N 1
+2 21 04-21-12 \N \N 1
+3 21 04-21-13 \N \N \N
+4 22 04-22-10 0 8 8
+5 22 04-22-11 8 \N 8
+6 22 04-22-12 \N \N 8
+7 23 04-23-13 \N 2 2
+8 23 04-23-14 2 \N 2
+9 23 04-23-15 \N \N 2
+10 23 04-23-16 \N \N \N
+11 24 02-24-10-22 \N 9 9
+12 24 02-24-10-23 9 \N 9
+13 24 02-24-10-24 \N \N 9
+
diff --git a/regression-test/data/correctness_p0/test_last_value_window.out
b/regression-test/data/correctness_p0/test_last_value_window_legacy_planner.out
similarity index 60%
copy from regression-test/data/correctness_p0/test_last_value_window.out
copy to
regression-test/data/correctness_p0/test_last_value_window_legacy_planner.out
index 2ca759725ee..5792ccb0aa5 100644
--- a/regression-test/data/correctness_p0/test_last_value_window.out
+++
b/regression-test/data/correctness_p0/test_last_value_window_legacy_planner.out
@@ -13,3 +13,18 @@
23 04-23-10 ["p7", "year4"] ["p7", "year4"]
24 02-24-10-21 [""] [""]
+-- !select_null --
+1 21 04-21-11 1 \N 1
+2 21 04-21-12 \N \N 1
+3 21 04-21-13 \N \N \N
+4 22 04-22-10 0 8 8
+5 22 04-22-11 8 \N 8
+6 22 04-22-12 \N \N 8
+7 23 04-23-13 \N 2 2
+8 23 04-23-14 2 \N 2
+9 23 04-23-15 \N \N 2
+10 23 04-23-16 \N \N \N
+11 24 02-24-10-22 \N 9 9
+12 24 02-24-10-23 9 \N 9
+13 24 02-24-10-24 \N \N 9
+
diff --git
a/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
b/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
index 1bb79e5cbe4..52e2f699a2d 100644
---
a/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
+++
b/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
@@ -16,6 +16,8 @@
// under the License.
suite("test_always_nullable_window_function") {
+ sql """ set enable_nereids_planner = true; """
+ sql """ set enable_fallback_to_original_planner = false; """
def tableName = "test_always_nullable_window_function_table"
def nullableTableName = "test_always_nullable_window_function_table"
diff --git
a/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
b/regression-test/suites/correctness_p0/test_always_nullable_window_function_legacy_planner.groovy
similarity index 93%
copy from
regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
copy to
regression-test/suites/correctness_p0/test_always_nullable_window_function_legacy_planner.groovy
index 1bb79e5cbe4..3c8194d05e0 100644
---
a/regression-test/suites/correctness_p0/test_always_nullable_window_function.groovy
+++
b/regression-test/suites/correctness_p0/test_always_nullable_window_function_legacy_planner.groovy
@@ -15,11 +15,11 @@
// specific language governing permissions and limitations
// under the License.
-suite("test_always_nullable_window_function") {
- def tableName = "test_always_nullable_window_function_table"
- def nullableTableName = "test_always_nullable_window_function_table"
+suite("test_always_nullable_window_function_legacy_planner") {
+ sql """ set enable_nereids_planner = false; """
- sql "set enable_nereids_planner = 1"
+ def tableName = "test_always_nullable_window_function_table_legacy_planner"
+ def nullableTableName =
"test_always_nullable_window_function_table_legacy_planner"
sql """ DROP TABLE IF EXISTS ${tableName} """
sql """
@@ -77,8 +77,8 @@ suite("test_always_nullable_window_function") {
avg(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) avg_value,
max(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) max_value,
min(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) min_value,
- lag(state, 0, null) over (partition by myday order by time_col)
lag_value,
- lead(state, 0, null) over (partition by myday order by time_col)
lead_value
+ lag(state, 10, null) over (partition by myday order by time_col)
lag_value,
+ lead(state, 10, null) over (partition by myday order by time_col)
lead_value
from ${tableName} order by myday, time_col, state;
"""
qt_select_empty_window """
@@ -102,8 +102,8 @@ suite("test_always_nullable_window_function") {
avg(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) avg_value,
max(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) max_value,
min(state) over(partition by myday order by time_col rows BETWEEN
1 preceding AND 1 following) min_value,
- lag(state, 0, null) over (partition by myday order by time_col)
lag_value,
- lead(state, 0, null) over (partition by myday order by time_col)
lead_value
+ lag(state, 10, null) over (partition by myday order by time_col)
lag_value,
+ lead(state, 10, null) over (partition by myday order by time_col)
lead_value
from ${nullableTableName} order by myday, time_col, state;
"""
qt_select_empty_window_nullable """
@@ -121,7 +121,7 @@ suite("test_always_nullable_window_function") {
sql "set enable_nereids_planner = 0"
- qt_select_default_old_planer """
+ qt_select_default_old_planner """
select *,
first_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 following) f_value,
last_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 following) l_value,
@@ -133,7 +133,7 @@ suite("test_always_nullable_window_function") {
lead(state, 1, null) over (partition by myday order by time_col)
lead_value
from ${tableName} order by myday, time_col, state;
"""
- qt_select_empty_window_old_planer """
+ qt_select_empty_window_old_planner """
select *,
first_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 preceding) f_value,
last_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 preceding) l_value,
@@ -146,7 +146,7 @@ suite("test_always_nullable_window_function") {
from ${tableName} order by myday, time_col, state;
"""
- qt_select_default_nullable_old_planer """
+ qt_select_default_nullable_old_planner """
select *,
first_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 following) f_value,
last_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 following) l_value,
@@ -158,7 +158,7 @@ suite("test_always_nullable_window_function") {
lead(state, 1, null) over (partition by myday order by time_col)
lead_value
from ${nullableTableName} order by myday, time_col, state;
"""
- qt_select_empty_window_nullable_old_planer """
+ qt_select_empty_window_nullable_old_planner """
select *,
first_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 preceding) f_value,
last_value(state) over(partition by myday order by time_col rows
BETWEEN 1 preceding AND 1 preceding) l_value,
diff --git
a/regression-test/suites/correctness_p0/test_first_value_window.groovy
b/regression-test/suites/correctness_p0/test_first_value_window.groovy
index 25246445fbb..8d0a3097056 100644
--- a/regression-test/suites/correctness_p0/test_first_value_window.groovy
+++ b/regression-test/suites/correctness_p0/test_first_value_window.groovy
@@ -16,8 +16,10 @@
// under the License.
suite("test_first_value_window") {
- def tableName = "test_first_value_window_state"
+ sql """ set enable_nereids_planner = true; """
+ sql """ set enable_fallback_to_original_planner = false; """
+ def tableName = "test_first_value_window_state"
sql """ DROP TABLE IF EXISTS ${tableName} """
sql """
@@ -81,4 +83,80 @@ suite("test_first_value_window") {
last_value(999) over(partition by myday order by time_col rows
between 1 preceding and 1 preceding) last_value
from test_first_value_window_array order by myday, time_col;
"""
+
+ def tableName2 = "test_first_value_window_state_not_null"
+
+ sql """ DROP TABLE IF EXISTS ${tableName2} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName2} (
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`myday`,time_col,state)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`myday`) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName2} VALUES
+ (21,"04-21-11",1),
+ (22,"04-22-10-21",0),
+ (22,"04-22-10-21",1),
+ (23,"04-23-10",1),
+ (24,"02-24-10-21",1); """
+
+ qt_select_default2 """
+ select *
+ ,first_value(state) over(partition by `myday` order by `time_col`)
v1
+ ,first_value(state, 0) over(partition by `myday` order by
`time_col`) v2
+ ,first_value(state, 1) over(partition by `myday` order by
`time_col`) v3
+ from ${tableName2} order by `myday`, `time_col`, `state`;
+ """
+
+ def tableName3 = "test_first_value_window_state_ignore_null"
+
+ sql """ DROP TABLE IF EXISTS ${tableName3} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName3} (
+ `id` INT,
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`, `myday`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`, `myday`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName3} VALUES
+ (1,21,"04-21-11",null),
+ (2,21,"04-21-12",2),
+ (3,21,"04-21-13",3),
+ (4,22,"04-22-10-21",null),
+ (5,22,"04-22-10-22",null),
+ (6,22,"04-22-10-23",5),
+ (7,22,"04-22-10-24",null),
+ (8,22,"04-22-10-25",9),
+ (9,23,"04-23-11",null),
+ (10,23,"04-23-12",10),
+ (11,23,"04-23-13",null),
+ (12,24,"02-24-10-21",null); """
+
+ qt_select_default3 """
+ select *
+ ,first_value(`state`) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v1
+ ,first_value(`state`, 0) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v2
+ ,first_value(`state`, 1) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v3
+ from ${tableName3} order by `id`, `myday`, `time_col`;
+ """
}
diff --git
a/regression-test/suites/correctness_p0/test_first_value_window_legacy_planner.groovy
b/regression-test/suites/correctness_p0/test_first_value_window_legacy_planner.groovy
new file mode 100644
index 00000000000..37d6e62c2f5
--- /dev/null
+++
b/regression-test/suites/correctness_p0/test_first_value_window_legacy_planner.groovy
@@ -0,0 +1,161 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_first_value_window_legacy_planner") {
+ sql """ set enable_nereids_planner = false; """
+
+ def tableName = "test_first_value_window_state_legacy_planner"
+
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`myday`,time_col,state)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`myday`) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName} VALUES
+ (21,"04-21-11",1),
+ (22,"04-22-10-21",0),
+ (22,"04-22-10-21",1),
+ (23,"04-23-10",1),
+ (24,"02-24-10-21",1); """
+
+ qt_select_default """ select *,first_value(state) over(partition by myday
order by time_col range between current row and unbounded following) from
${tableName} order by myday, time_col, state; """
+
+
+ def tableName1 = "test_first_value_window_array_legacy_planner"
+
+ sql """ DROP TABLE IF EXISTS ${tableName1} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName1} (
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` ARRAY<STRING>
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`myday`,time_col)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`myday`) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName1} VALUES
+ (21,"04-21-11",["amory", "clever"]),
+ (22,"04-22-10-21",["is ", "cute", "tea"]),
+ (22,"04-22-10-21",["doris", "aws", "greate"]),
+ (23,"04-23-10", ["p7", "year4"]),
+ (24,"02-24-10-21",[""]); """
+
+ qt_select_default """ select *,first_value(state) over(partition by myday
order by time_col range between current row and unbounded following) from
${tableName1} order by myday, time_col; """
+
+ qt_select_always_nullable """
+ select
+ *,
+ first_value(1) over(partition by myday order by time_col rows
between 1 preceding and 1 preceding) first_value,
+ last_value(999) over(partition by myday order by time_col rows
between 1 preceding and 1 preceding) last_value
+ from test_first_value_window_array_legacy_planner order by myday,
time_col;
+ """
+
+ def tableName2 = "test_first_value_window_state_not_null_legacy_planner"
+
+ sql """ DROP TABLE IF EXISTS ${tableName2} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName2} (
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`myday`,time_col,state)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`myday`) BUCKETS 2
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName2} VALUES
+ (21,"04-21-11",1),
+ (22,"04-22-10-21",0),
+ (22,"04-22-10-21",1),
+ (23,"04-23-10",1),
+ (24,"02-24-10-21",1); """
+
+ qt_select_default2 """
+ select *
+ ,first_value(state) over(partition by `myday` order by `time_col`)
v1
+ ,first_value(state, 0) over(partition by `myday` order by
`time_col`) v2
+ ,first_value(state, 1) over(partition by `myday` order by
`time_col`) v3
+ from ${tableName2} order by `myday`, `time_col`, `state`;
+ """
+
+ def tableName3 = "test_first_value_window_state_ignore_null_legacy_planner"
+
+ sql """ DROP TABLE IF EXISTS ${tableName3} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName3} (
+ `id` INT,
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`, `myday`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`, `myday`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableName3} VALUES
+ (1,21,"04-21-11",null),
+ (2,21,"04-21-12",2),
+ (3,21,"04-21-13",3),
+ (4,22,"04-22-10-21",null),
+ (5,22,"04-22-10-22",null),
+ (6,22,"04-22-10-23",5),
+ (7,22,"04-22-10-24",null),
+ (8,22,"04-22-10-25",9),
+ (9,23,"04-23-11",null),
+ (10,23,"04-23-12",10),
+ (11,23,"04-23-13",null),
+ (12,24,"02-24-10-21",null); """
+
+ qt_select_default3 """
+ select *
+ ,first_value(`state`) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v1
+ ,first_value(`state`, 0) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v2
+ ,first_value(`state`, 1) over(partition by `myday` order by
`time_col` rows between 1 preceding and 1 following) v3
+ from ${tableName3} order by `id`, `myday`, `time_col`;
+ """
+}
diff --git
a/regression-test/suites/correctness_p0/test_last_value_window.groovy
b/regression-test/suites/correctness_p0/test_last_value_window.groovy
index d9b4c5f0e5c..3f3633d2270 100644
--- a/regression-test/suites/correctness_p0/test_last_value_window.groovy
+++ b/regression-test/suites/correctness_p0/test_last_value_window.groovy
@@ -16,8 +16,10 @@
// under the License.
suite("test_last_value_window") {
- def tableName = "test_last_value_window_state"
+ sql """ set enable_nereids_planner = true; """
+ sql """ set enable_fallback_to_original_planner = false; """
+ def tableName = "test_last_value_window_state"
sql """ DROP TABLE IF EXISTS ${tableName} """
sql """
@@ -74,5 +76,43 @@ suite("test_last_value_window") {
qt_select_default """ select *,last_value(state) over(partition by myday
order by time_col range between current row and unbounded following) from
${tableName1} order by myday, time_col; """
+ def tableNameWithNull = "test_last_value_window_state_null"
+ sql """ DROP TABLE IF EXISTS ${tableNameWithNull} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableNameWithNull} (
+ `id` INT,
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`myday`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableNameWithNull} VALUES
+ (1,21,"04-21-11",1),
+ (2,21,"04-21-12",null),
+ (3,21,"04-21-13",null),
+ (4,22,"04-22-10",0),
+ (5,22,"04-22-11",8),
+ (6,22,"04-22-12",null),
+ (7,23,"04-23-13",null),
+ (8,23,"04-23-14",2),
+ (9,23,"04-23-15",null),
+ (10,23,"04-23-16",null),
+ (11,24,"02-24-10-22",null),
+ (12,24,"02-24-10-23",9),
+ (13,24,"02-24-10-24",null); """
+ qt_select_null """ select *
+ , last_value(state, false) over(partition by myday order by
time_col rows between 1 preceding and 1 following) v1
+ , last_value(state, true) over(partition by myday order by
time_col rows between 1 preceding and 1 following) v2
+ from ${tableNameWithNull} order by id, myday, time_col;
+ """
}
diff --git
a/regression-test/suites/correctness_p0/test_last_value_window.groovy
b/regression-test/suites/correctness_p0/test_last_value_window_legacy_planner.groovy
similarity index 59%
copy from regression-test/suites/correctness_p0/test_last_value_window.groovy
copy to
regression-test/suites/correctness_p0/test_last_value_window_legacy_planner.groovy
index d9b4c5f0e5c..de20441dcc9 100644
--- a/regression-test/suites/correctness_p0/test_last_value_window.groovy
+++
b/regression-test/suites/correctness_p0/test_last_value_window_legacy_planner.groovy
@@ -15,9 +15,10 @@
// specific language governing permissions and limitations
// under the License.
-suite("test_last_value_window") {
- def tableName = "test_last_value_window_state"
+suite("test_last_value_window_legacy_planner") {
+ sql """ set enable_nereids_planner = false; """
+ def tableName = "test_last_value_window_state_legacy_planner"
sql """ DROP TABLE IF EXISTS ${tableName} """
sql """
@@ -36,7 +37,7 @@ suite("test_last_value_window") {
);
"""
- sql """ INSERT INTO ${tableName} VALUES
+ sql """ INSERT INTO ${tableName} VALUES
(21,"04-21-11",1),
(22,"04-22-10-21",0),
(22,"04-22-10-21",1),
@@ -46,7 +47,7 @@ suite("test_last_value_window") {
qt_select_default """ select *,last_value(state) over(partition by myday
order by time_col) from ${tableName} order by myday, time_col, state; """
- def tableName1 = "test_last_value_window_array"
+ def tableName1 = "test_last_value_window_array_legacy_planner"
sql """ DROP TABLE IF EXISTS ${tableName1} """
sql """
@@ -74,5 +75,43 @@ suite("test_last_value_window") {
qt_select_default """ select *,last_value(state) over(partition by myday
order by time_col range between current row and unbounded following) from
${tableName1} order by myday, time_col; """
+ def tableNameWithNull = "test_last_value_window_state_null_legacy_planner"
+ sql """ DROP TABLE IF EXISTS ${tableNameWithNull} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableNameWithNull} (
+ `id` INT,
+ `myday` INT,
+ `time_col` VARCHAR(40) NOT NULL,
+ `state` INT
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`,`myday`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 4
+ PROPERTIES (
+ "replication_num" = "1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ sql """ INSERT INTO ${tableNameWithNull} VALUES
+ (1,21,"04-21-11",1),
+ (2,21,"04-21-12",null),
+ (3,21,"04-21-13",null),
+ (4,22,"04-22-10",0),
+ (5,22,"04-22-11",8),
+ (6,22,"04-22-12",null),
+ (7,23,"04-23-13",null),
+ (8,23,"04-23-14",2),
+ (9,23,"04-23-15",null),
+ (10,23,"04-23-16",null),
+ (11,24,"02-24-10-22",null),
+ (12,24,"02-24-10-23",9),
+ (13,24,"02-24-10-24",null); """
+ qt_select_null """ select *
+ , last_value(state, false) over(partition by myday order by
time_col rows between 1 preceding and 1 following) v1
+ , last_value(state, true) over(partition by myday order by
time_col rows between 1 preceding and 1 following) v2
+ from ${tableNameWithNull} order by id, myday, time_col;
+ """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
