This is an automated email from the ASF dual-hosted git repository.
guohongyu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 05dbcc4def [CALCITE-6446] Add CONCAT_WS function (enabled in Spark
library)
05dbcc4def is described below
commit 05dbcc4def168b318917168fa51a784c74a34eb7
Author: YiwenWu <[email protected]>
AuthorDate: Sun Jun 23 18:57:26 2024 +0800
[CALCITE-6446] Add CONCAT_WS function (enabled in Spark library)
---
babel/src/test/resources/sql/spark.iq | 115 +++++++++++++++++++++
.../calcite/adapter/enumerable/RexImpTable.java | 4 +
.../org/apache/calcite/runtime/SqlFunctions.java | 28 +++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 4 +
.../calcite/sql/fun/SqlLibraryOperators.java | 21 ++++
.../org/apache/calcite/sql/type/OperandTypes.java | 59 +++++++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
.../org/apache/calcite/test/SqlFunctionsTest.java | 35 +++++++
site/_docs/reference.md | 1 +
.../calcite/sql/test/SqlOperatorFixture.java | 3 +
.../org/apache/calcite/test/SqlOperatorTest.java | 42 ++++++++
11 files changed, 314 insertions(+)
diff --git a/babel/src/test/resources/sql/spark.iq
b/babel/src/test/resources/sql/spark.iq
index a2ac6a709e..97ba83664f 100644
--- a/babel/src/test/resources/sql/spark.iq
+++ b/babel/src/test/resources/sql/spark.iq
@@ -240,4 +240,119 @@ EXPR$0
true
!ok
+#####################################################################
+# CONCAT_WS
+#
+# CONCAT_WS(sep[, str | array(str)]+)
+# Returns the concatenation of the strings separated by sep, skipping null
values.
+# If sep is null, returns null directly
+#
+# Returns STRING
+
+SELECT CONCAT_WS('s');
+EXPR$0
+
+!ok
+
+SELECT CONCAT_WS(',', null);
+EXPR$0
+
+!ok
+
+SELECT CONCAT_WS(',', null, null);
+EXPR$0
+
+!ok
+
+SELECT CONCAT_WS(',', null, 'a');
+EXPR$0
+a
+!ok
+
+SELECT CONCAT_WS(',', 'a', 'b');
+EXPR$0
+a,b
+!ok
+
+SELECT CONCAT_WS(',', 'a', null, 'b');
+EXPR$0
+a,b
+!ok
+
+SELECT CONCAT_WS(null, 'a', 'b');
+EXPR$0
+null
+!ok
+
+SELECT CONCAT_WS(',', 'a', 100, 'b');
+EXPR$0
+a,100,b
+!ok
+
+SELECT CONCAT_WS(',', 'a', 100.0, 'b');
+EXPR$0
+a,100.0,b
+!ok
+
+SELECT CONCAT_WS('', cast('a' as varchar(2)), cast('b' as varchar(1)));
+EXPR$0
+ab
+!ok
+
+SELECT CONCAT_WS(',', array('a', 'b', 'c'));
+EXPR$0
+a,b,c
+!ok
+
+SELECT CONCAT_WS(',', array('a', null, 'c'));
+EXPR$0
+a,c
+!ok
+
+SELECT CONCAT_WS(',', array('a'));
+EXPR$0
+a
+!ok
+
+SELECT CONCAT_WS(null, array('a'));
+EXPR$0
+null
+!ok
+
+SELECT CONCAT_WS(null, array('a'), array('b'), array('c'));
+EXPR$0
+null
+!ok
+
+SELECT CONCAT_WS(',', array('a'), array('b'), array('c'));
+EXPR$0
+a,b,c
+!ok
+
+SELECT CONCAT_WS(',', 'a1', 'b1', 'c1', array('a'), array('b'), array('c'));
+EXPR$0
+a1,b1,c1,a,b,c
+!ok
+
+SELECT CONCAT_WS(',', null, 'a1', 'b1', null, 'c1', array('a'), array('b'),
array('c'));
+EXPR$0
+a1,b1,c1,a,b,c
+!ok
+
+SELECT CONCAT_WS(',', null,'a1', 'b1', null, 'c1', array('a'), array('b'),
array('c', null, 'd'));
+EXPR$0
+a1,b1,c1,a,b,c,d
+!ok
+
+SELECT CONCAT_WS(',', 100, 'b', array('c'));
+EXPR$0
+100,b,c
+!ok
+
+SELECT CONCAT_WS(',', array(null, null));
+EXPR$0
+
+!ok
+
+
# End spark.iq
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 5d43e464e0..ce70c6cebe 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -164,6 +164,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_FUNCTION;
import static
org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_FUNCTION_WITH_NULL;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_WS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_WS_MSSQL;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_WS_SPARK;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONTAINS_SUBSTR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COSH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COTH;
@@ -587,6 +588,9 @@ public class RexImpTable {
defineMethod(CONCAT_WS_MSSQL,
BuiltInMethod.MULTI_STRING_CONCAT_WITH_SEPARATOR.method,
NullPolicy.NONE);
+ defineMethod(CONCAT_WS_SPARK,
+ BuiltInMethod.MULTI_TYPE_STRING_ARRAY_CONCAT_WITH_SEPARATOR.method,
+ NullPolicy.ARG0);
defineMethod(OVERLAY, BuiltInMethod.OVERLAY.method, NullPolicy.STRICT);
defineMethod(POSITION, BuiltInMethod.POSITION.method, NullPolicy.STRICT);
defineMethod(ASCII, BuiltInMethod.ASCII.method, NullPolicy.STRICT);
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 94cb21c9d6..30fb4d54ee 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1513,6 +1513,34 @@ public class SqlFunctions {
public static String concatMultiWithSeparator(String... args) {
// the separator arg could be null
final String sep = args[0] == null ? "" : args[0];
+ return concatMultiWithSeparator(sep, args);
+ }
+
+
+ /** SQL {@code CONCAT_WS(sep[, str | array(str)]+)} function,
+ * return null for null sep. */
+ public static String concatMultiTypeWithSeparator(String sep, Object...
args) {
+ if (args.length == 0) {
+ return "";
+ }
+ Object[] argsArray = array(args);
+ List<String> arrayList = new ArrayList<>();
+ arrayList.add(sep);
+ for (Object arg : argsArray) {
+ if (arg == null) {
+ continue;
+ }
+ if (arg instanceof String) {
+ arrayList.add((String) arg);
+ }
+ if (arg instanceof List<?>) {
+ arrayList.addAll((List<String>) arg);
+ }
+ }
+ return concatMultiWithSeparator(sep, arrayList.toArray(new String[0]));
+ }
+
+ private static String concatMultiWithSeparator(String sep, String... args) {
StringBuilder sb = new StringBuilder();
for (int i = 1; i < args.length; i++) {
if (args[i] != null) {
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 73a6c3a0da..29089a6ebe 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -439,6 +439,10 @@ public enum SqlKind {
/** The {@code CONCAT_WS} function (MSSQL). */
CONCAT_WS_MSSQL,
+
+ /** The {@code CONCAT_WS} function (Spark). */
+ CONCAT_WS_SPARK,
+
/** The "IF" function (BigQuery, Hive, Spark). */
IF,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
index 773569e416..671282383f 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java
@@ -71,6 +71,7 @@ import static
org.apache.calcite.sql.fun.SqlLibrary.POSTGRESQL;
import static org.apache.calcite.sql.fun.SqlLibrary.REDSHIFT;
import static org.apache.calcite.sql.fun.SqlLibrary.SNOWFLAKE;
import static org.apache.calcite.sql.fun.SqlLibrary.SPARK;
+import static
org.apache.calcite.sql.type.OperandTypes.STRING_FIRST_STRING_ARRAY_OPTIONAL;
import static org.apache.calcite.util.Static.RESOURCE;
import static java.util.Objects.requireNonNull;
@@ -1110,6 +1111,26 @@ public abstract class SqlLibraryOperators {
.withOperandTypeInference(InferTypes.RETURN_TYPE)
.withKind(SqlKind.CONCAT_WS_MSSQL);
+ /** The "CONCAT_WS(separator[, str | array(str)]+)" function in (SPARK).
+ *
+ * <p>For example:
+ * <ul>
+ * <li>{@code CONCAT_WS(',', 'a', 'b')} returns "{@code a,b}";
+ * <li>{@code CONCAT_WS(null, 'a', 'b')} returns NULL";
+ * <li>{@code CONCAT_WS('s')} returns "";
+ * <li>{@code CONCAT_WS('/', 'a', null, 'b')} returns "{@code a/b}";
+ * <li>{@code CONCAT_WS('/', array('a', 'b'))} returns "{@code a/b}";
+ * <li>{@code CONCAT_WS('/', 'c', array('a', 'b'))} returns "{@code c/a/b}".
+ * </ul> */
+ @LibraryOperator(libraries = {SPARK})
+ public static final SqlFunction CONCAT_WS_SPARK =
+ SqlBasicFunction.create("CONCAT_WS",
+ ReturnTypes.MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_ARG0_NULLABLE,
+ STRING_FIRST_STRING_ARRAY_OPTIONAL,
+ SqlFunctionCategory.STRING)
+ .withOperandTypeInference(InferTypes.RETURN_TYPE)
+ .withKind(SqlKind.CONCAT_WS_SPARK);
+
private static RelDataType arrayReturnType(SqlOperatorBinding opBinding) {
final List<RelDataType> operandTypes = opBinding.collectOperandTypes();
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 08b6301154..f629a00e0a 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -595,6 +595,65 @@ public abstract class OperandTypes {
}
};
+ /** Operand type-checking strategy that the first argument is of string type,
+ * and the remaining arguments can be of string or array of string type. */
+ public static final SqlOperandTypeChecker STRING_FIRST_STRING_ARRAY_OPTIONAL
=
+ new SqlOperandTypeChecker() {
+ @Override public boolean checkOperandTypes(
+ SqlCallBinding binding,
+ boolean throwOnFailure) {
+ // Check first operand is String type
+ if (!STRING.checkSingleOperandType(binding, binding.operand(0), 0,
throwOnFailure)) {
+ return false;
+ }
+ // Check Array element is String type
+ if (!checkArrayString(binding, throwOnFailure)) {
+ return false;
+ }
+ // Check Operand Types with Type Coercion
+ return checkFamilyOperandTypes(binding, throwOnFailure);
+ }
+
+ @Override public SqlOperandCountRange getOperandCountRange() {
+ return SqlOperandCountRanges.any();
+ }
+
+ @Override public String getAllowedSignatures(SqlOperator op, String
opName) {
+ return opName + "(<STRING>(,[<STRING> | ARRAY<STRING>]))";
+ }
+
+ private boolean checkFamilyOperandTypes(SqlCallBinding binding,
boolean throwOnFailure) {
+ ImmutableList.Builder<SqlTypeFamily> builder =
ImmutableList.builder();
+ for (int i = 0; i < binding.getOperandCount(); i++) {
+ SqlTypeFamily family = SqlTypeFamily.STRING;
+ if (binding.getOperandType(i).getSqlTypeName() ==
SqlTypeName.ARRAY) {
+ family = SqlTypeFamily.ARRAY;
+ }
+ builder.add(family);
+ }
+ ImmutableList<SqlTypeFamily> families = builder.build();
+ return family(families).checkOperandTypes(binding, throwOnFailure);
+ }
+
+ private boolean checkArrayString(SqlCallBinding binding, boolean
throwOnFailure) {
+ for (int i = 0; i < binding.getOperandCount(); i++) {
+ RelDataType type = binding.getOperandType(i);
+ if (type.getSqlTypeName() == SqlTypeName.ARRAY
+ && !isString(((ArraySqlType) type).getComponentType())) {
+ if (throwOnFailure) {
+ throw binding.newValidationSignatureError();
+ }
+ return false;
+ }
+ }
+ return true;
+ }
+
+ private boolean isString(RelDataType type) {
+ return SqlTypeUtil.isNull(type) || SqlTypeUtil.isString(type);
+ }
+ };
+
/** Checks that returns whether a value is a multiset or an array.
* Cf Java, where list and set are collections but a map is not. */
public static final SqlSingleOperandTypeChecker COLLECTION =
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 928ebedfb5..ff4a57b909 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -478,6 +478,8 @@ public enum BuiltInMethod {
String[].class),
MULTI_STRING_CONCAT_WITH_SEPARATOR(SqlFunctions.class,
"concatMultiWithSeparator", String[].class),
+ MULTI_TYPE_STRING_ARRAY_CONCAT_WITH_SEPARATOR(SqlFunctions.class,
+ "concatMultiTypeWithSeparator", String.class, Object[].class),
FLOOR_DIV(Math.class, "floorDiv", long.class, long.class),
FLOOR_MOD(Math.class, "floorMod", long.class, long.class),
ADD_MONTHS(DateTimeUtils.class, "addMonths", long.class, int.class),
diff --git a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
index 00e057f0d5..9ede05ec95 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -44,6 +44,7 @@ import static
org.apache.calcite.runtime.SqlFunctions.arraysOverlap;
import static org.apache.calcite.runtime.SqlFunctions.charLength;
import static org.apache.calcite.runtime.SqlFunctions.concat;
import static org.apache.calcite.runtime.SqlFunctions.concatMulti;
+import static
org.apache.calcite.runtime.SqlFunctions.concatMultiTypeWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithNull;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatWithNull;
@@ -211,6 +212,40 @@ class SqlFunctionsTest {
assertThat(concatMultiWithSeparator(null, null, null), is(""));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6446";>[CALCITE-6446]
+ * Add CONCAT_WS function (enabled in Spark library)</a>. */
+ @Test void testConcatMultiTypeWithSeparator() {
+ // string type
+ assertThat(concatMultiTypeWithSeparator("a"), is(""));
+ assertThat(concatMultiTypeWithSeparator(",", "a"), is("a"));
+ assertThat(concatMultiTypeWithSeparator(",", "a b", "cd"), is("a b,cd"));
+ assertThat(concatMultiTypeWithSeparator(",", "a b", null, "cd", null,
"e"), is("a b,cd,e"));
+ assertThat(concatMultiTypeWithSeparator(",", "", ""), is(","));
+ assertThat(concatMultiTypeWithSeparator("", null, null), is(""));
+ // array type
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList()), is(""));
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a")), is("a"));
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a", "b")),
is("a,b"));
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList("a", null,
"b")), is("a,b"));
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList(null, "b")),
is("b"));
+ assertThat(concatMultiTypeWithSeparator(",", Arrays.asList(null, null)),
is(""));
+ assertThat(
+ concatMultiTypeWithSeparator(",",
+ Arrays.asList("11", "11"), Arrays.asList("12", "12")),
is("11,11,12,12"));
+ // multi type
+ assertThat(concatMultiTypeWithSeparator(",", "11", "11",
Arrays.asList("12", "12")),
+ is("11,11,12,12"));
+ assertThat(concatMultiTypeWithSeparator(",", null, "11",
Arrays.asList("12", "12")),
+ is("11,12,12"));
+ assertThat(concatMultiTypeWithSeparator(",", "11", null,
Arrays.asList("12", "12")),
+ is("11,12,12"));
+ assertThat(
+ concatMultiTypeWithSeparator(",", "11", "11", Arrays.asList("12",
"12"),
+ Arrays.asList("13", null, "13")),
+ is("11,11,12,12,13,13"));
+ }
+
@Test void testPosixRegex() {
final SqlFunctions.PosixRegexFunction f =
new SqlFunctions.PosixRegexFunction();
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 1a78470c5b..2a3090ad26 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2731,6 +2731,7 @@ In the following:
| p q | CONCAT(string [, string ]*) | Concatenates one or
more strings, null is treated as empty string
| m p | CONCAT_WS(separator, str1 [, string ]*) | Concatenates one or
more strings, returns null only when separator is null, otherwise treats null
arguments as empty strings
| q | CONCAT_WS(separator, str1, str2 [, string ]*) | Concatenates two or
more strings, requires at least 3 arguments (up to 254), treats null arguments
as empty strings
+| s | CONCAT_WS(separator [, string | array(string)]+) | Concatenates one or
more strings or arrays. Besides the separator, other arguments can include
strings or string arrays. returns null only when separator is null, treats
other null arguments as empty strings
| m | COMPRESS(string) | Compresses a string
using zlib compression and returns the result as a binary string
| b | CONTAINS_SUBSTR(expression, string [ , json_scope => json_scope_value
]) | Returns whether *string* exists as a substring in *expression*. Optional
*json_scope* argument specifies what scope to search if *expression* is in JSON
format. Returns NULL if a NULL exists in *expression* that does not result in a
match
| q | CONVERT(type, expression [ , style ]) | Equivalent to
`CAST(expression AS type)`; ignores the *style* operand
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
index 5ffb931849..20c2dc27d9 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/test/SqlOperatorFixture.java
@@ -90,6 +90,9 @@ public interface SqlOperatorFixture extends AutoCloseable {
String INVALID_ARGUMENTS_NUMBER =
"Invalid number of arguments to function .* Was expecting .* arguments";
+ String INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR =
+ "Cannot apply '.*' to arguments of type .*";
+
//~ Enums ------------------------------------------------------------------
/**
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 1554083d21..0cad83d55a 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -131,6 +131,7 @@ import static
org.apache.calcite.sql.test.ResultCheckers.isWithin;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.BAD_DATETIME_MESSAGE;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.DIVISION_BY_ZERO_MESSAGE;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.INVALID_ARGUMENTS_NUMBER;
+import static
org.apache.calcite.sql.test.SqlOperatorFixture.INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.INVALID_CHAR_MESSAGE;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.LITERAL_OUT_OF_RANGE_MESSAGE;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.OUT_OF_RANGE_MESSAGE;
@@ -2475,6 +2476,47 @@ public class SqlOperatorTest {
f.checkString("concat_ws('', '', '', '')", "", "VARCHAR(0) NOT NULL");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6446";>[CALCITE-6446]
+ * Add CONCAT_WS function (enabled in Spark library)</a>. */
+ @Test void testConcatWSFuncInSpark() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.CONCAT_WS_SPARK)
+ .withLibrary(SqlLibrary.SPARK);
+ f.checkString("concat_ws(',', 'a')", "a", "VARCHAR(1) NOT NULL");
+ f.checkString("concat_ws(',', 'a', 'b', null, 'c')", "a,b,c",
+ "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', cast('a' as varchar), cast('b' as varchar))",
+ "a,b", "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', cast('a' as varchar(2)), cast('b' as
varchar(1)))",
+ "a,b", "VARCHAR(4) NOT NULL");
+ f.checkString("concat_ws(',', '', '', '')", ",,", "VARCHAR(2) NOT NULL");
+ f.checkString("concat_ws(',', null, null, null)", "", "VARCHAR NOT NULL");
+ // returns null if the separator is null
+ f.checkNull("concat_ws(null, 'a', 'b')");
+ f.checkNull("concat_ws(null, null, null)");
+ f.checkString("concat_ws(',')", "", "VARCHAR(0) NOT NULL");
+ // if the separator is empty string, it's equivalent to CONCAT
+ f.checkString("concat_ws('', cast('a' as varchar(2)), cast('b' as
varchar(1)))",
+ "ab", "VARCHAR(3) NOT NULL");
+ f.checkString("concat_ws('', '', '', '')", "", "VARCHAR(0) NOT NULL");
+ f.checkString("concat_ws(',', array('a', 'b'))", "a,b", "VARCHAR NOT
NULL");
+ f.checkString("concat_ws(',', null)", "", "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', array('a', null, 'b'))", "a,b", "VARCHAR NOT
NULL");
+ f.checkString("concat_ws(',', 1, 'b')", "1,b", "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', 100.0, 'b')", "100.0,b", "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', 'a', 'b', array('c'))", "a,b,c", "VARCHAR
NOT NULL");
+ f.checkString("concat_ws(',', 'a', 'b', array('c'), array('d'))",
"a,b,c,d",
+ "VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', '100', 'b', array('c'))", "100,b,c",
"VARCHAR NOT NULL");
+ f.checkString("concat_ws(',', 100, 'b', array('c'))", "100,b,c", "VARCHAR
NOT NULL");
+ f.checkString("concat_ws(',', 'a', 'b', array('100'))", "a,b,100",
"VARCHAR NOT NULL");
+ f.checkFails("^concat_ws(',', 'a', 'b', array(100))^",
+ INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR, false);
+ f.checkFails("^concat_ws(array('a', 'b'))^",
+ INVALID_ARGUMENTS_TYPE_VALIDATION_ERROR, false);
+ }
+
@Test void testModOperator() {
// "%" is allowed under BIG_QUERY, MYSQL_5 SQL conformance levels
final SqlOperatorFixture f0 = fixture()
