This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 1bceac2fdd2 [branch-4.0](function) add a lambda functor version for
array_sort (#57828) (#62829)
1bceac2fdd2 is described below
commit 1bceac2fdd26d291703d289c4ef97659348360bc
Author: Chenyang Sun <[email protected]>
AuthorDate: Thu May 7 16:57:46 2026 +0800
[branch-4.0](function) add a lambda functor version for array_sort (#57828)
(#62829)
pick from master #57828
Co-authored-by: admiring_xm <[email protected]>
---
.../lambda_function/lambda_function_factory.h | 2 +
.../glue/translator/ExpressionTranslator.java | 56 +++
.../nereids/rules/analysis/ExpressionAnalyzer.java | 2 +-
.../rewrite/AccessPathExpressionCollector.java | 12 +
.../expressions/functions/scalar/ArraySort.java | 35 +-
.../trees/expressions/functions/scalar/Lambda.java | 13 +-
.../nereids_function_p0/scalar_function/Array2.out | 241 ++++++++++
.../scalar_function/Array2.groovy | 499 +++++++++++++++++++++
8 files changed, 856 insertions(+), 4 deletions(-)
diff --git a/be/src/vec/exprs/lambda_function/lambda_function_factory.h
b/be/src/vec/exprs/lambda_function/lambda_function_factory.h
index ce607748732..d9db354fcb4 100644
--- a/be/src/vec/exprs/lambda_function/lambda_function_factory.h
+++ b/be/src/vec/exprs/lambda_function/lambda_function_factory.h
@@ -28,6 +28,7 @@ class LambdaFunctionFactory;
void register_function_array_map(LambdaFunctionFactory& factory);
void register_function_array_filter(LambdaFunctionFactory& factory);
+void register_function_array_sort(LambdaFunctionFactory& factory);
class LambdaFunctionFactory {
using Creator = std::function<LambdaFunctionPtr()>;
@@ -62,6 +63,7 @@ public:
std::call_once(oc, []() {
register_function_array_map(instance);
register_function_array_filter(instance);
+ register_function_array_sort(instance);
});
return instance;
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
index 4d127077d4f..58c0ca9217d 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/glue/translator/ExpressionTranslator.java
@@ -95,6 +95,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.combinator.StateComb
import
org.apache.doris.nereids.trees.expressions.functions.combinator.UnionCombinator;
import
org.apache.doris.nereids.trees.expressions.functions.generator.TableGeneratingFunction;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMap;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.ArraySort;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DictGet;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DictGetMany;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ElementAt;
@@ -584,6 +585,61 @@ public class ExpressionTranslator extends
DefaultExpressionVisitor<Expr, PlanTra
return functionCallExpr;
}
+ @Override
+ public Expr visitArraySort(ArraySort arraySort, PlanTranslatorContext
context) {
+ if (!(arraySort.child(0) instanceof Lambda)) {
+ return visitScalarFunction(arraySort, context);
+ }
+ Lambda lambda = (Lambda) arraySort.child(0);
+ List<Expr> arguments = new ArrayList<>(arraySort.children().size());
+ arguments.add(null);
+
+ // Construct the first column
+ ArrayItemReference arrayItemReference = lambda.getLambdaArgument(0);
+ String argName = arrayItemReference.getName();
+ Expr expr = arrayItemReference.getArrayExpression().accept(this,
context);
+ arguments.add(expr);
+ ColumnRefExpr column = new ColumnRefExpr();
+ column.setName(argName);
+ column.setColumnId(0);
+ column.setNullable(true);
+ column.setType(((ArrayType) expr.getType()).getItemType());
+ context.addExprIdColumnRefPair(arrayItemReference.getExprId(), column);
+
+ // the second column here will not be used; it's just a placeholder.
+ arrayItemReference = lambda.getLambdaArgument(1);
+ ColumnRefExpr column2 = new ColumnRefExpr(column);
+ column2.setColumnId(1);
+ context.addExprIdColumnRefPair(arrayItemReference.getExprId(),
column2);
+
+ List<Type> argTypes = arraySort.getArguments().stream()
+ .map(Expression::getDataType)
+ .map(DataType::toCatalogDataType)
+ .collect(Collectors.toList());
+ // two slots are same, we only need one
+ lambda.getLambdaArguments().stream().skip(1)
+ .map(ArrayItemReference::getArrayExpression)
+ .map(Expression::getDataType)
+ .map(DataType::toCatalogDataType)
+ .forEach(argTypes::add);
+ NullableMode nullableMode = arraySort.nullable()
+ ? NullableMode.ALWAYS_NULLABLE
+ : NullableMode.ALWAYS_NOT_NULLABLE;
+ Type itemType = ((ArrayType) arguments.get(1).getType()).getItemType();
+ org.apache.doris.catalog.Function catalogFunction = new Function(
+ new FunctionName(arraySort.getName()), argTypes,
+ ArrayType.create(itemType, true),
+ true, true, nullableMode);
+
+ // create catalog FunctionCallExpr without analyze again
+ Expr lambdaBody = visitLambda(lambda, context);
+ arguments.set(0, lambdaBody);
+ LambdaFunctionCallExpr functionCallExpr = new
LambdaFunctionCallExpr(catalogFunction,
+ new FunctionParams(false, arguments));
+ functionCallExpr.setNullableFromNereids(arraySort.nullable());
+ return functionCallExpr;
+ }
+
@Override
public Expr visitDictGet(DictGet dictGet, PlanTranslatorContext context) {
List<Expr> arguments = dictGet.getArguments().stream()
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
index 414affb39ac..acd2c82565e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/analysis/ExpressionAnalyzer.java
@@ -393,7 +393,7 @@ public class ExpressionAnalyzer extends
SubExprAnalyzer<ExpressionRewriteContext
// bindLambdaFunction
Lambda lambda = (Lambda) unboundFunction.children().get(0);
Expression lambdaFunction = lambda.getLambdaFunction();
- List<ArrayItemReference> arrayItemReferences =
lambda.makeArguments(subChildren);
+ List<ArrayItemReference> arrayItemReferences =
lambda.makeArguments(unboundFunction.getName(), subChildren);
List<Slot> boundedSlots = arrayItemReferences.stream()
.map(ArrayItemReference::toSlot)
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/AccessPathExpressionCollector.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/AccessPathExpressionCollector.java
index 9ff9bfe998e..dcfcce4cf4e 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/AccessPathExpressionCollector.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/rewrite/AccessPathExpressionCollector.java
@@ -38,6 +38,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMap;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAll;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayMatchAny;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ArrayReverseSplit;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.ArraySort;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArraySortBy;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ArraySplit;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ElementAt;
@@ -252,6 +253,17 @@ public class AccessPathExpressionCollector extends
DefaultExpressionVisitor<Void
return visit(arrayMap, context);
}
+ @Override
+ public Void visitArraySort(ArraySort arraySort, CollectorContext context) {
+ // ARRAY_SORT(lambda, <arr>)
+
+ Expression argument = arraySort.getArgument(0);
+ if ((argument instanceof Lambda)) {
+ return collectArrayPathInLambda((Lambda) argument, context);
+ }
+ return visit(arraySort, context);
+ }
+
@Override
public Void visitArrayCount(ArrayCount arrayCount, CollectorContext
context) {
// ARRAY_COUNT(<lambda>, <arr>[, ... ])
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArraySort.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArraySort.java
index daf1330032c..2e8b738237a 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArraySort.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ArraySort.java
@@ -19,6 +19,7 @@ package
org.apache.doris.nereids.trees.expressions.functions.scalar;
import org.apache.doris.catalog.FunctionSignature;
import org.apache.doris.nereids.exceptions.AnalysisException;
+import org.apache.doris.nereids.trees.expressions.ArrayItemReference;
import org.apache.doris.nereids.trees.expressions.Expression;
import
org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature;
import org.apache.doris.nereids.trees.expressions.functions.PropagateNullable;
@@ -26,6 +27,7 @@ import
org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
import org.apache.doris.nereids.types.ArrayType;
import org.apache.doris.nereids.types.DataType;
+import org.apache.doris.nereids.types.LambdaType;
import org.apache.doris.nereids.types.coercion.AnyDataType;
import com.google.common.base.Preconditions;
@@ -40,7 +42,8 @@ public class ArraySort extends ScalarFunction
implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable {
public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
-
FunctionSignature.retArgType(0).args(ArrayType.of(AnyDataType.INSTANCE_WITHOUT_INDEX))
+
FunctionSignature.retArgType(0).args(ArrayType.of(AnyDataType.INSTANCE_WITHOUT_INDEX)),
+
FunctionSignature.ret(ArrayType.of(AnyDataType.INSTANCE_WITHOUT_INDEX)).args(LambdaType.INSTANCE)
);
/**
@@ -77,7 +80,35 @@ public class ArraySort extends ScalarFunction
@Override
public ArraySort withChildren(List<Expression> children) {
Preconditions.checkArgument(children.size() == 1);
- return new ArraySort(getFunctionParams(children));
+ return new ArraySort(children.get(0));
+ }
+
+ @Override
+ public DataType getDataType() {
+ if (children.get(0) instanceof Lambda) {
+ Lambda lambda = (Lambda) children.get(0);
+ ArrayItemReference argRef = lambda.getLambdaArguments().get(0);
+ Expression arrayExpr = argRef.getArrayExpression();
+ ArrayType arrayType = (ArrayType) arrayExpr.getDataType();
+ return ArrayType.of(arrayType.getItemType(), true);
+ } else if (children.get(0).getDataType() instanceof ArrayType) {
+ Expression arrayExpr = children.get(0);
+ ArrayType arrayType = (ArrayType) arrayExpr.getDataType();
+ return ArrayType.of(arrayType.getItemType(), true);
+ } else {
+ throw new AnalysisException("The first arg of array_sort must be
lambda or array");
+ }
+ }
+
+ @Override
+ public boolean nullable() {
+ if (children.get(0) instanceof Lambda) {
+ return ((Lambda) children.get(0)).getLambdaArguments().stream()
+ .map(ArrayItemReference::getArrayExpression)
+ .anyMatch(Expression::nullable);
+ } else {
+ return child(0).nullable();
+ }
}
@Override
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Lambda.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Lambda.java
index 35463aa829b..192f04e5632 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Lambda.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Lambda.java
@@ -60,12 +60,23 @@ public class Lambda extends Expression {
/**
* make slot according array expression
+ * @param functionName function name
* @param arrays array expression
* @return item slots of array expression
*/
- public ImmutableList<ArrayItemReference> makeArguments(List<Expression>
arrays) {
+ public ImmutableList<ArrayItemReference> makeArguments(String
functionName, List<Expression> arrays) {
Builder<ArrayItemReference> builder = new ImmutableList.Builder<>();
if (arrays.size() != argumentNames.size()) {
+ // In the lambda expression of array_sort, x and y point to the
same slot.
+ if (functionName.equalsIgnoreCase("array_sort") && arrays.size()
== 1 && argumentNames.size() == 2) {
+ Expression array = arrays.get(0);
+ if (!(array.getDataType() instanceof ArrayType)) {
+ throw new AnalysisException(String.format("lambda argument
must be array but is %s", array));
+ }
+ builder.add(new ArrayItemReference(argumentNames.get(0),
array));
+ builder.add(new ArrayItemReference(argumentNames.get(1),
array));
+ return builder.build();
+ }
throw new AnalysisException(String.format("lambda %s arguments'
size is not equal parameters' size",
toSql()));
}
diff --git
a/regression-test/data/nereids_function_p0/scalar_function/Array2.out
b/regression-test/data/nereids_function_p0/scalar_function/Array2.out
new file mode 100644
index 00000000000..7767feb9c14
--- /dev/null
+++ b/regression-test/data/nereids_function_p0/scalar_function/Array2.out
@@ -0,0 +1,241 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql_array_sort_1 --
+[null, null, 5, 3, 2, 2, 1]
+
+-- !sql_array_sort_2 --
+[5, 3, 2, 2, 1, null, null]
+
+-- !sql_array_sort_3 --
+[5, 3, 2, 2, 1]
+
+-- !sql_array_sort_4 --
+["dc", "bc", "ab"]
+
+-- !sql_array_sort_5 --
+["a", "abc", "abcd"]
+
+-- !sql_array_sort_6 --
+[[1, 2], [2, 3, 1], [4, 2, 1, 4]]
+
+-- !sql_array_sort_7 --
+["192.168.0.1", "192.168.0.2", "192.168.0.3"]
+
+-- !sql_array_sort_8 --
+[5.34, 3.00, 2.20, 1.20, -2.10]
+
+-- !sql_array_sort_Tinyint --
+[null, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Tinyint_notnull --
+[12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Smallint --
+[null, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Smallint_notnull --
+[12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Int --
+[null, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Int_notnull --
+[12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Bigint --
+[null, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Bigint_notnull --
+[12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Largeint --
+[null, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_LargeInt_notnull --
+[12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1]
+
+-- !sql_array_sort_Float --
+[null, 1.2, 1.1, 1, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1]
+
+-- !sql_array_sort_Float_notnull --
+[1.2, 1.1, 1, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1]
+
+-- !sql_array_sort_Double --
+[null, 1.2, 1.1, 1, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1]
+
+-- !sql_array_sort_Double_notnull --
+[1.2, 1.1, 1, 0.9, 0.8, 0.7, 0.6, 0.5, 0.4, 0.3, 0.2, 0.1]
+
+-- !sql_array_sort_Demical1 --
+[null, 1.200, 1.100, 1.000, 0.900, 0.800, 0.700, 0.600, 0.500, 0.400, 0.300,
0.200, 0.100]
+
+-- !sql_array_sort_Demical1_notnull --
+[1.200, 1.100, 1.000, 0.900, 0.800, 0.700, 0.600, 0.500, 0.400, 0.300, 0.200,
0.100]
+
+-- !sql_array_sort_Demical2 --
+[null, 1.20000, 1.10000, 1.00000, 0.90000, 0.80000, 0.70000, 0.60000, 0.50000,
0.40000, 0.30000, 0.20000, 0.10000]
+
+-- !sql_array_sort_Demical2_notnull --
+[1.20000, 1.10000, 1.00000, 0.90000, 0.80000, 0.70000, 0.60000, 0.50000,
0.40000, 0.30000, 0.20000, 0.10000]
+
+-- !sql_array_sort_Demical3 --
+[null, 1.200000000, 1.100000000, 1.000000000, 0.900000000, 0.800000000,
0.700000000, 0.600000000, 0.500000000, 0.400000000, 0.300000000, 0.200000000,
0.100000000]
+
+-- !sql_array_sort_Demical3_notnull --
+[1.200000000, 1.100000000, 1.000000000, 0.900000000, 0.800000000, 0.700000000,
0.600000000, 0.500000000, 0.400000000, 0.300000000, 0.200000000, 0.100000000]
+
+-- !sql_array_sort_Demical4 --
+[null, 1.200, 1.100, 1.000, 0.900, 0.800, 0.700, 0.600, 0.500, 0.400, 0.300,
0.200, 0.100]
+
+-- !sql_array_sort_Demical4_notnull --
+[1.200, 1.100, 1.000, 0.900, 0.800, 0.700, 0.600, 0.500, 0.400, 0.300, 0.200,
0.100]
+
+-- !sql_array_sort_Demical5 --
+[null, 1.20000, 1.10000, 1.00000, 0.90000, 0.80000, 0.70000, 0.60000, 0.50000,
0.40000, 0.30000, 0.20000, 0.10000]
+
+-- !sql_array_sort_Demical5_notnull --
+[1.20000, 1.10000, 1.00000, 0.90000, 0.80000, 0.70000, 0.60000, 0.50000,
0.40000, 0.30000, 0.20000, 0.10000]
+
+-- !sql_array_sort_Demical6 --
+[null, 1.200000000, 1.100000000, 1.000000000, 0.900000000, 0.800000000,
0.700000000, 0.600000000, 0.500000000, 0.400000000, 0.300000000, 0.200000000,
0.100000000]
+
+-- !sql_array_sort_Demical6_notnull --
+[1.200000000, 1.100000000, 1.000000000, 0.900000000, 0.800000000, 0.700000000,
0.600000000, 0.500000000, 0.400000000, 0.300000000, 0.200000000, 0.100000000]
+
+-- !sql_array_sort_Char --
+["null", "char33", "char33", "char33", "char33", "char32", "char32", "char32",
"char32", "char31", "char31", "char31", "char31"]
+
+-- !sql_array_sort_Char_notnull --
+["char33", "char33", "char33", "char33", "char32", "char32", "char32",
"char32", "char31", "char31", "char31", "char31"]
+
+-- !sql_array_sort_Varchar --
+["varchar33", "varchar33", "varchar33", "varchar33", "varchar32", "varchar32",
"varchar32", "varchar32", "varchar31", "varchar31", "varchar31", "varchar31",
"null"]
+
+-- !sql_array_sort_Varchar_notnull --
+["varchar33", "varchar33", "varchar33", "varchar33", "varchar32", "varchar32",
"varchar32", "varchar32", "varchar31", "varchar31", "varchar31", "varchar31"]
+
+-- !sql_array_sort_String --
+["string3", "string3", "string3", "string3", "string2", "string2", "string2",
"string2", "string1", "string1", "string1", "string1", "null"]
+
+-- !sql_array_sort_String_notnull --
+["string3", "string3", "string3", "string3", "string2", "string2", "string2",
"string2", "string1", "string1", "string1", "string1"]
+
+-- !sql_array_sort_Date --
+[null, "2012-03-12", "2012-03-11", "2012-03-10", "2012-03-09", "2012-03-08",
"2012-03-07", "2012-03-06", "2012-03-05", "2012-03-04", "2012-03-03",
"2012-03-02", "2012-03-01"]
+
+-- !sql_array_sort_Date_notnull --
+["2012-03-12", "2012-03-11", "2012-03-10", "2012-03-09", "2012-03-08",
"2012-03-07", "2012-03-06", "2012-03-05", "2012-03-04", "2012-03-03",
"2012-03-02", "2012-03-01"]
+
+-- !sql_array_sort_DateV2 --
+[null, "2012-03-12", "2012-03-11", "2012-03-10", "2012-03-09", "2012-03-08",
"2012-03-07", "2012-03-06", "2012-03-05", "2012-03-04", "2012-03-03",
"2012-03-02", "2012-03-01"]
+
+-- !sql_array_sort_DateV2_notnull --
+["2012-03-12", "2012-03-11", "2012-03-10", "2012-03-09", "2012-03-08",
"2012-03-07", "2012-03-06", "2012-03-05", "2012-03-04", "2012-03-03",
"2012-03-02", "2012-03-01"]
+
+-- !sql_array_sort_Datetime --
+[null, "2012-03-12 12:11:12", "2012-03-11 11:10:11", "2012-03-10 10:09:10",
"2012-03-09 09:08:09", "2012-03-08 08:07:08", "2012-03-07 07:06:07",
"2012-03-06 06:05:06", "2012-03-05 05:04:05", "2012-03-04 04:03:04",
"2012-03-03 03:02:03", "2012-03-02 02:01:02", "2012-03-01 01:00:01"]
+
+-- !sql_array_sort_Datetime_notnull --
+["2012-03-12 12:11:12", "2012-03-11 11:10:11", "2012-03-10 10:09:10",
"2012-03-09 09:08:09", "2012-03-08 08:07:08", "2012-03-07 07:06:07",
"2012-03-06 06:05:06", "2012-03-05 05:04:05", "2012-03-04 04:03:04",
"2012-03-03 03:02:03", "2012-03-02 02:01:02", "2012-03-01 01:00:01"]
+
+-- !sql_array_sort_DatetimeV2 --
+[null, "2012-03-12 12:11:12.000000", "2012-03-11 11:10:11.000000", "2012-03-10
10:09:10.000000", "2012-03-09 09:08:09.000000", "2012-03-08 08:07:08.000000",
"2012-03-07 07:06:07.000000", "2012-03-06 06:05:06.000000", "2012-03-05
05:04:05.000000", "2012-03-04 04:03:04.000000", "2012-03-03 03:02:03.000000",
"2012-03-02 02:01:02.000000", "2012-03-01 01:00:01.000000"]
+
+-- !sql_array_sort_DatetimeV2_notnull --
+["2012-03-12 12:11:12.000000", "2012-03-11 11:10:11.000000", "2012-03-10
10:09:10.000000", "2012-03-09 09:08:09.000000", "2012-03-08 08:07:08.000000",
"2012-03-07 07:06:07.000000", "2012-03-06 06:05:06.000000", "2012-03-05
05:04:05.000000", "2012-03-04 04:03:04.000000", "2012-03-03 03:02:03.000000",
"2012-03-02 02:01:02.000000", "2012-03-01 01:00:01.000000"]
+
+-- !sql_array_sort_Boolean --
+[null, 1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0]
+
+-- !sql_array_sort_Boolean_notnull --
+[1, 1, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0]
+
+-- !sql_array_sort_lambda_multir_tinyint --
+1 [null, 5, 3, 2, 1]
+2 [null, 9, 8, 7, 6]
+3 [null, 4, 3, 2, 1]
+
+-- !sql_array_sort_lambda_multir_smallint --
+1 [null, 5, 3, 2, 1]
+2 [null, 9, 8, 7, 6]
+3 [null, 4, 3, 2, 1]
+
+-- !sql_array_sort_lambda_multir_int --
+1 [null, 5, 3, 2, 1]
+2 [null, 9, 8, 7, 6]
+3 [null, 4, 3, 2, 1]
+
+-- !sql_array_sort_lambda_multir_bigint --
+1 [null, 5, 3, 2, 1]
+2 [null, 9, 8, 7, 6]
+3 [null, 4, 3, 2, 1]
+
+-- !sql_array_sort_lambda_multir_largeint --
+1 [null, 5, 3, 2, 1]
+2 [null, 9, 8, 7, 6]
+3 [null, 4, 3, 2, 1]
+
+-- !sql_array_sort_lambda_multir_float --
+1 [null, 5.1, 3.1, 2.1, 1.1]
+2 [null, 9.1, 8.1, 7.1, 6.1]
+3 [null, 4.1, 3.1, 2.1, 1.1]
+
+-- !sql_array_sort_lambda_multir_double --
+1 [null, 5.1, 3.1, 2.1, 1.1]
+2 [null, 9.1, 8.1, 7.1, 6.1]
+3 [null, 4.1, 3.1, 2.1, 1.1]
+
+-- !sql_array_sort_lambda_multir_date --
+1 [null, "2024-01-05", "2024-01-03", "2024-01-02", "2024-01-01"]
+2 [null, "2024-02-09", "2024-02-08", "2024-02-07", "2024-02-06"]
+3 [null, "2024-03-04", "2024-03-03", "2024-03-02", "2024-03-01"]
+
+-- !sql_array_sort_lambda_multir_datetime --
+1 [null, "2024-01-05 10:00:00", "2024-01-03 10:00:00", "2024-01-02
10:00:00", "2024-01-01 10:00:00"]
+2 [null, "2024-02-09 10:00:00", "2024-02-08 10:00:00", "2024-02-07
10:00:00", "2024-02-06 10:00:00"]
+3 [null, "2024-03-04 10:00:00", "2024-03-03 10:00:00", "2024-03-02
10:00:00", "2024-03-01 10:00:00"]
+
+-- !sql_array_sort_lambda_multir_datev2 --
+1 [null, "2024-01-05", "2024-01-03", "2024-01-02", "2024-01-01"]
+2 [null, "2024-02-09", "2024-02-08", "2024-02-07", "2024-02-06"]
+3 [null, "2024-03-04", "2024-03-03", "2024-03-02", "2024-03-01"]
+
+-- !sql_array_sort_lambda_multir_datetimev2_0 --
+1 [null, "2024-01-05 10:00:00", "2024-01-03 10:00:00", "2024-01-02
10:00:00", "2024-01-01 10:00:00"]
+2 [null, "2024-02-09 10:00:00", "2024-02-08 10:00:00", "2024-02-07
10:00:00", "2024-02-06 10:00:00"]
+3 [null, "2024-03-04 10:00:00", "2024-03-03 10:00:00", "2024-03-02
10:00:00", "2024-03-01 10:00:00"]
+
+-- !sql_array_sort_lambda_multir_datetimev2_6 --
+1 [null, "2024-01-05 10:00:00.000000", "2024-01-03 10:00:00.000000",
"2024-01-02 10:00:00.000000", "2024-01-01 10:00:00.000000"]
+2 [null, "2024-02-09 10:00:00.000000", "2024-02-08 10:00:00.000000",
"2024-02-07 10:00:00.000000", "2024-02-06 10:00:00.000000"]
+3 [null, "2024-03-04 10:00:00.000000", "2024-03-03 10:00:00.000000",
"2024-03-02 10:00:00.000000", "2024-03-01 10:00:00.000000"]
+
+-- !sql_array_sort_lambda_multir_char --
+1 [null, "e", "c", "b", "a"]
+2 [null, "i", "h", "g", "f"]
+3 [null, "d", "c", "b", "a"]
+
+-- !sql_array_sort_lambda_multir_varchar --
+1 [null, "eee", "ccc", "bbb", "aaa"]
+2 [null, "iii", "hhh", "ggg", "fff"]
+3 [null, "ddd", "ccc", "bbb", "aaa"]
+
+-- !sql_array_sort_lambda_multir_string --
+1 [null, "eee", "ccc", "bbb", "aaa"]
+2 [null, "iii", "hhh", "ggg", "fff"]
+3 [null, "ddd", "ccc", "bbb", "aaa"]
+
+-- !sql_array_sort_lambda_multir_decimal38_38 --
+1 [null, 0.00000000000000000000000000000000000005,
0.00000000000000000000000000000000000003,
0.00000000000000000000000000000000000002,
0.00000000000000000000000000000000000001]
+2 [null, 0.00000000000000000000000000000000000009,
0.00000000000000000000000000000000000008,
0.00000000000000000000000000000000000007,
0.00000000000000000000000000000000000006]
+3 [null, 0.00000000000000000000000000000000000004,
0.00000000000000000000000000000000000003,
0.00000000000000000000000000000000000002,
0.00000000000000000000000000000000000001]
+
+-- !sql_array_sort_lambda_multir_ipv4 --
+1 [null, "127.0.0.5", "127.0.0.3", "127.0.0.2", "127.0.0.1"]
+2 [null, "127.0.0.9", "127.0.0.8", "127.0.0.7", "127.0.0.6"]
+3 [null, "127.0.0.4", "127.0.0.3", "127.0.0.2", "127.0.0.1"]
+
+-- !sql_array_sort_lambda_multir_ipv6 --
+1 [null, "::5", "::3", "::2", "::1"]
+2 [null, "::9", "::8", "::7", "::6"]
+3 [null, "::4", "::3", "::2", "::1"]
+
diff --git
a/regression-test/suites/nereids_function_p0/scalar_function/Array2.groovy
b/regression-test/suites/nereids_function_p0/scalar_function/Array2.groovy
new file mode 100644
index 00000000000..eaa648e6c45
--- /dev/null
+++ b/regression-test/suites/nereids_function_p0/scalar_function/Array2.groovy
@@ -0,0 +1,499 @@
+// 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("nereids_scalar_fn_Array2") {
+ sql 'use regression_test_nereids_function_p0'
+ sql 'set enable_nereids_planner=true'
+ sql 'set enable_fallback_to_original_planner=false'
+ sql 'set enable_decimal256=true;'
+
+ order_qt_sql_array_sort_1 """SELECT array_sort((x, y) -> CASE WHEN x IS
NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ [3, 2, null, 5, null, 1, 2])"""
+ order_qt_sql_array_sort_2 """SELECT array_sort((x, y) -> CASE WHEN x IS
NULL THEN 1
+ WHEN y IS NULL THEN -1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ [3, 2, null, 5, null, 1, 2])"""
+ order_qt_sql_array_sort_3 """SELECT array_sort((x, y) -> IF(x < y, 1, IF(x
= y, 0, -1)), [3, 2, 5, 1, 2])"""
+ order_qt_sql_array_sort_4 """SELECT array_sort((x, y) -> IF(x < y, 1, IF(x
= y, 0, -1)), ['bc', 'ab', 'dc'])"""
+ order_qt_sql_array_sort_5 """SELECT array_sort((x, y) -> IF(length(x) <
length(y), -1,
+ IF(length(x) = length(y), 0, 1)),
+ ['a', 'abcd', 'abc'])"""
+ order_qt_sql_array_sort_6 """SELECT array_sort((x, y) -> IF(cardinality(x)
< cardinality(y), -1,
+ IF(cardinality(x) = cardinality(y), 0, 1)),
+ [[2, 3, 1], [4, 2, 1, 4], [1, 2]])"""
+ order_qt_sql_array_sort_7 """SELECT array_sort((x, y) ->
IF(IPV4_STRING_TO_NUM_OR_NULL(x) < IPV4_STRING_TO_NUM_OR_NULL(y), -1,
+ IF(IPV4_STRING_TO_NUM_OR_NULL(x) =
IPV4_STRING_TO_NUM_OR_NULL(y), 0, 1)),
+ ['192.168.0.3', '192.168.0.1',
'192.168.0.2'])"""
+ order_qt_sql_array_sort_8 """SELECT array_sort((x, y) -> IF(x < y, 1, IF(x
= y, 0, -1)), [3, -2.1, 5.34, 1.2, 2.2])"""
+
+ order_qt_sql_array_sort_Tinyint """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(ktint) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Tinyint_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(ktint) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Smallint """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(ksint) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Smallint_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(ksint) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Int """SELECT array_sort((x, y) -> CASE WHEN x IS
NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kint) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Int_notnull """SELECT array_sort((x, y) -> IF(x <
y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kint) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Bigint """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kbint) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Bigint_notnull """SELECT array_sort((x, y) -> IF(x
< y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kbint) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Largeint """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(klint) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_LargeInt_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(klint) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Float """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kfloat) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Float_notnull """SELECT array_sort((x, y) -> IF(x
< y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kfloat) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Double """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdbl) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Double_notnull """SELECT array_sort((x, y) -> IF(x
< y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdbl) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical1 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmls1) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical1_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmls1) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical2 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmls2) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical2_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmls2) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical3 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmls3) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical3_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmls3) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical4 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmlv3s1) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical4_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmlv3s1) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical5 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmlv3s2) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical5_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmlv3s2) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Demical6 """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdcmlv3s3) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Demical6_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdcmlv3s3) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Char """SELECT array_sort((x, y) -> CASE WHEN x IS
NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kchrs3) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Char_notnull """SELECT array_sort((x, y) -> IF(x <
y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kchrs3) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Varchar """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kvchrs3) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Varchar_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kvchrs3) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_String """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kstr) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_String_notnull """SELECT array_sort((x, y) -> IF(x
< y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kstr) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Date """SELECT array_sort((x, y) -> CASE WHEN x IS
NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT array_agg(kdt)
AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Date_notnull """SELECT array_sort((x, y) -> IF(x <
y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdt) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_DateV2 """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdtv2) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_DateV2_notnull """SELECT array_sort((x, y) -> IF(x
< y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdtv2) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Datetime """SELECT array_sort((x, y) -> CASE WHEN
x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdtm) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Datetime_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdtm) AS arr FROM
fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_DatetimeV2 """SELECT array_sort((x, y) -> CASE
WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kdtmv2s3) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_DatetimeV2_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kdtmv2s3) AS arr
FROM fn_test_not_nullable)t"""
+ order_qt_sql_array_sort_Boolean """SELECT array_sort((x, y) -> CASE WHEN x
IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END, arr) FROM (SELECT
array_agg(kbool) AS arr FROM fn_test)t"""
+ order_qt_sql_array_sort_Boolean_notnull """SELECT array_sort((x, y) ->
IF(x < y, 1, IF(x = y, 0, -1)), arr) FROM (SELECT array_agg(kbool) AS arr FROM
fn_test_not_nullable)t"""
+
+ // Test multiple rows of data
+ sql """ DROP TABLE IF EXISTS fn_test_array_sort"""
+ sql """
+ CREATE TABLE IF NOT EXISTS `fn_test_array_sort` (
+ `id` int null,
+ `katint` array<tinyint(4)> null,
+ `kasint` array<smallint(6)> null,
+ `kaint` array<int> null,
+ `kabint` array<bigint(20)> null,
+ `kalint` array<largeint(40)> null,
+ `kafloat` array<float> null,
+ `kadbl` array<double> null,
+ `kadt` array<date> null,
+ `kadtm` array<datetime> null,
+ `kadtv2` array<datev2> null,
+ `kadtmv2_` array<datetimev2(0)> null,
+ `kadtmv2` array<datetimev2(6)> null,
+ `kachr` array<char(255)> null,
+ `kavchr` array<varchar(65533)> null,
+ `kastr` array<string> null,
+ `kadcml2` array<decimal(38, 38)> null,
+ `kaipv4` array<ipv4> null,
+ `kaipv6` array<ipv6> null
+ ) engine=olap
+ DISTRIBUTED BY HASH(`id`) BUCKETS 4
+ properties("replication_num" = "1","store_row_column" = "true")
+ """
+
+ sql """INSERT INTO fn_test_array_sort (
+ id, katint, kasint, kaint, kabint, kalint, kafloat, kadbl,
+ kadt, kadtm, kadtv2, kadtmv2_, kadtmv2,
+ kachr, kavchr, kastr,
+ kadcml2, kaipv4, kaipv6
+ ) VALUES
+ (
+ 1,
+ [3, 1, NULL, 5, 2],
+ [3, 1, NULL, 5, 2],
+ [3, 1, NULL, 5, 2],
+ [3, 1, NULL, 5, 2],
+ [3, 1, NULL, 5, 2],
+ [3.1, 1.1, NULL, 5.1, 2.1],
+ [3.1, 1.1, NULL, 5.1, 2.1],
+ ['2024-01-03', '2024-01-01', NULL, '2024-01-05', '2024-01-02'],
+ ['2024-01-03 10:00:00','2024-01-01 10:00:00',NULL,'2024-01-05
10:00:00','2024-01-02 10:00:00'],
+ ['2024-01-03','2024-01-01',NULL,'2024-01-05','2024-01-02'],
+ ['2024-01-03 10:00:00', '2024-01-01 10:00:00', NULL, '2024-01-05
10:00:00', '2024-01-02 10:00:00'],
+ ['2024-01-03 10:00:00.000000','2024-01-01
10:00:00.000000',NULL,'2024-01-05 10:00:00.000000','2024-01-02
10:00:00.000000'],
+ ['c','a',NULL,'e','b'],
+ ['ccc','aaa',NULL,'eee','bbb'],
+ ['ccc','aaa',NULL,'eee','bbb'],
+ [0.00000000000000000000000000000000000003, NULL,
+ 0.00000000000000000000000000000000000001,
+ 0.00000000000000000000000000000000000005,
+ 0.00000000000000000000000000000000000002],
+ ['127.0.0.3','127.0.0.1',NULL,'127.0.0.5','127.0.0.2'],
+ ['::3','::1',NULL,'::5','::2']
+ ),
+ (
+ 2,
+ [9, 7, NULL, 8, 6],
+ [9, 7, NULL, 8, 6],
+ [9, 7, NULL, 8, 6],
+ [9, 7, NULL, 8, 6],
+ [9, 7, NULL, 8, 6],
+ [9.1, 7.1, NULL, 8.1, 6.1],
+ [9.1, 7.1, NULL, 8.1, 6.1],
+ ['2024-02-09','2024-02-07',NULL,'2024-02-08','2024-02-06'],
+ ['2024-02-09 10:00:00','2024-02-07 10:00:00',NULL,'2024-02-08
10:00:00','2024-02-06 10:00:00'],
+ ['2024-02-09','2024-02-07',NULL,'2024-02-08','2024-02-06'],
+ ['2024-02-09 10:00:00','2024-02-07 10:00:00',NULL,'2024-02-08
10:00:00','2024-02-06 10:00:00'],
+ ['2024-02-09 10:00:00.000000','2024-02-07
10:00:00.000000',NULL,'2024-02-08 10:00:00.000000','2024-02-06
10:00:00.000000'],
+ ['i','g',NULL,'h','f'],
+ ['iii','ggg',NULL,'hhh','fff'],
+ ['iii','ggg',NULL,'hhh','fff'],
+ [0.00000000000000000000000000000000000009, NULL,
+ 0.00000000000000000000000000000000000007,
+ 0.00000000000000000000000000000000000008,
+ 0.00000000000000000000000000000000000006],
+ ['127.0.0.9','127.0.0.7',NULL,'127.0.0.8','127.0.0.6'],
+ ['::9','::7',NULL,'::8','::6']
+ ),
+ (
+ 3,
+ [4, 3, NULL, 2, 1],
+ [4, 3, NULL, 2, 1],
+ [4, 3, NULL, 2, 1],
+ [4, 3, NULL, 2, 1],
+ [4, 3, NULL, 2, 1],
+ [4.1, 3.1, NULL, 2.1, 1.1],
+ [4.1, 3.1, NULL, 2.1, 1.1],
+ ['2024-03-04','2024-03-03',NULL,'2024-03-02','2024-03-01'],
+ ['2024-03-04 10:00:00','2024-03-03 10:00:00',NULL,'2024-03-02
10:00:00','2024-03-01 10:00:00'],
+ ['2024-03-04','2024-03-03',NULL,'2024-03-02','2024-03-01'],
+ ['2024-03-04 10:00:00','2024-03-03 10:00:00',NULL,'2024-03-02
10:00:00','2024-03-01 10:00:00'],
+ ['2024-03-04 10:00:00.000000','2024-03-03
10:00:00.000000',NULL,'2024-03-02 10:00:00.000000','2024-03-01
10:00:00.000000'],
+ ['d','c',NULL,'b','a'],
+ ['ddd','ccc',NULL,'bbb','aaa'],
+ ['ddd','ccc',NULL,'bbb','aaa'],
+ [0.00000000000000000000000000000000000004, NULL,
+ 0.00000000000000000000000000000000000003,
+ 0.00000000000000000000000000000000000002,
+ 0.00000000000000000000000000000000000001],
+ ['127.0.0.4','127.0.0.3',NULL,'127.0.0.2','127.0.0.1'],
+ ['::4','::3',NULL,'::2','::1']
+ )
+ """
+
+ order_qt_sql_array_sort_lambda_multir_tinyint """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ katint)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_smallint """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kasint)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_int """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kaint)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_bigint """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kabint)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_largeint """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kalint)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_float """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kafloat)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_double """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadbl)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_date """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadt)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_datetime """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadtm)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_datev2 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadtv2)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_datetimev2_0 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadtmv2_)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_datetimev2_6 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadtmv2)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_char """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kachr)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_varchar """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kavchr)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_string """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kastr)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_decimal38_38 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kadcml2)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_ipv4 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kaipv4)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+
+ order_qt_sql_array_sort_lambda_multir_ipv6 """
+ SELECT id, array_sort(
+ (x, y) -> CASE WHEN x IS NULL THEN -1
+ WHEN y IS NULL THEN 1
+ WHEN x < y THEN 1
+ WHEN x = y THEN 0
+ ELSE -1 END,
+ kaipv6)
+ FROM fn_test_array_sort
+ ORDER BY id
+ """
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
