MartijnVisser commented on code in PR #22951: URL: https://github.com/apache/flink/pull/22951#discussion_r1489314997
########## flink-table/flink-table-common/src/main/java/org/apache/flink/table/functions/BuiltInFunctionDefinitions.java: ########## @@ -231,6 +232,21 @@ ANY, and(logical(LogicalTypeRoot.BOOLEAN), LITERAL) "org.apache.flink.table.runtime.functions.scalar.ArrayContainsFunction") .build(); + public static final BuiltInFunctionDefinition ARRAY_SORT = + BuiltInFunctionDefinition.newBuilder() + .name("ARRAY_SORT") + .kind(SCALAR) + .inputTypeStrategy( + or( + sequence(new ArrayComparableElementArgumentTypeStrategy()), + sequence( + new ArrayComparableElementArgumentTypeStrategy(), + logical(LogicalTypeRoot.BOOLEAN)))) Review Comment: This is quite a rabbit hole 😆 1. Databricks has two functions: * sort_array https://docs.databricks.com/en/sql/language-manual/functions/sort_array.html - Sorts ascending, puts NULL at beginning. * array_sort https://docs.databricks.com/en/sql/language-manual/functions/array_sort.html - Sorts ascending, puts NULL at the end. 2. Spark has https://spark.apache.org/docs/latest/api/sql/index.html#sort_array which is indeed not different from the Databricks one. Sorts ascending, puts NULL at beginning 3. Snowflake indeed has https://docs.snowflake.com/en/sql-reference/functions/array_sort - Sorts ascending, puts NULL at the end. 4. Trino, that has `array_sort(x)` - Sorts ascending, puts NULL at the end. 5. StarRocks also has this function https://docs.starrocks.io/docs/2.2/sql-reference/sql-functions/array-functions/array_sort/ - Sorts ascending, puts NULL at beginning 6. Doris has it https://doris.apache.org/docs/1.2/sql-manual/sql-functions/array-functions/array_sort/ - Sorts ascending, puts NULL at beginning 7. Hive uses NULLS FIRST as the default behavior in ascending ordering mode, per https://issues.apache.org/jira/browse/HIVE-12994 - Sorts ascending, puts NULL at beginning. I do think that the Snowflake has the cleanest function signature: there's just one function, and it has two optional arguments to give all the flexibility. I think the default sorting mode should be ascending, and then it boils down to decide on the default location on where to put NULL. Out of the 7 implementations, we have 5 who put NULL at the beginning, and 2 who put them at the end. Naturally, I think we should put NULL at the beginning but I'm +0 on the default location of NULL. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: issues-unsubscr...@flink.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org