This is an automated email from the ASF dual-hosted git repository.
jhyde 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 9df9b536b7 [CALCITE-5741] Add CONCAT_WS function (enabled in MSSQL,
MySQL, Postgres libraries)
9df9b536b7 is described below
commit 9df9b536b77463229bc46322167667c36414a828
Author: ILuffZhe <[email protected]>
AuthorDate: Sun Jun 18 00:35:02 2023 +0800
[CALCITE-5741] Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres
libraries)
Close apache/calcite#3271
---
.../calcite/adapter/enumerable/RexImpTable.java | 11 +-
.../org/apache/calcite/runtime/SqlFunctions.java | 19 +++
.../main/java/org/apache/calcite/sql/SqlKind.java | 3 +
.../calcite/sql/fun/SqlLibraryOperators.java | 46 ++++++
.../org/apache/calcite/sql/type/ReturnTypes.java | 88 ++++++++++-
.../org/apache/calcite/util/BuiltInMethod.java | 8 +-
.../org/apache/calcite/test/SqlFunctionsTest.java | 14 ++
core/src/test/resources/sql/functions.iq | 167 +++++++++++++++++++++
site/_docs/reference.md | 12 +-
.../org/apache/calcite/test/SqlOperatorTest.java | 54 ++++++-
10 files changed, 410 insertions(+), 12 deletions(-)
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 ec7c628262..f5a27adb3f 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
@@ -148,6 +148,8 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.COMPRESS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT2;
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.COSH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COTH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CSC;
@@ -501,7 +503,14 @@ public class RexImpTable {
NullPolicy.STRICT);
defineMethod(CONCAT_FUNCTION_WITH_NULL,
BuiltInMethod.MULTI_STRING_CONCAT_WITH_NULL.method, NullPolicy.NONE);
- defineMethod(CONCAT2, BuiltInMethod.STRING_CONCAT_WITH_NULL.method,
NullPolicy.ALL);
+ defineMethod(CONCAT2, BuiltInMethod.STRING_CONCAT_WITH_NULL.method,
+ NullPolicy.ALL);
+ defineMethod(CONCAT_WS,
+ BuiltInMethod.MULTI_STRING_CONCAT_WITH_SEPARATOR.method,
+ NullPolicy.ARG0);
+ defineMethod(CONCAT_WS_MSSQL,
+ BuiltInMethod.MULTI_STRING_CONCAT_WITH_SEPARATOR.method,
+ NullPolicy.NONE);
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 e7473b4790..26990d03f6 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -802,6 +802,25 @@ public class SqlFunctions {
return sb.toString();
}
+ /** SQL {@code CONCAT_WS(sep, arg1, arg2, ...)} function;
+ * treats null arguments as empty strings. */
+ public static String concatMultiWithSeparator(String... args) {
+ // the separator arg could be null
+ final String sep = args[0] == null ? "" : args[0];
+ StringBuilder sb = new StringBuilder();
+ for (int i = 1; i < args.length; i++) {
+ if (args[i] != null) {
+ if (i < args.length - 1) {
+ sb.append(args[i]).append(sep);
+ } else {
+ // no separator after the last arg
+ sb.append(args[i]);
+ }
+ }
+ }
+ return sb.toString();
+ }
+
/** SQL {@code CONVERT(s, src_charset, dest_charset)} function. */
public static String convertWithCharset(String s, String srcCharset,
String destCharset) {
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 ed48b413bf..a49423d684 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -427,6 +427,9 @@ public enum SqlKind {
/** The {@code CONCAT} function (Postgresql and MSSQL) that ignores NULL. */
CONCAT_WITH_NULL,
+ /** The {@code CONCAT_WS} function (MSSQL). */
+ CONCAT_WS_MSSQL,
+
/** 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 fa9c4e4f85..4ceffd43e7 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
@@ -906,6 +906,52 @@ public abstract class SqlLibraryOperators {
.withOperandTypeInference(InferTypes.RETURN_TYPE)
.withKind(SqlKind.CONCAT2);
+ /** The "CONCAT_WS(separator, arg1, ...)" function (MySQL, Postgres);
+ * concatenates strings with separator, and treats null arguments as empty
+ * strings. For example:
+ *
+ * <ul>
+ * <li>{@code CONCAT_WS(',', 'a')} returns "{@code a}";
+ * <li>{@code CONCAT_WS(',', 'a', 'b')} returns "{@code a,b}".
+ * </ul>
+ *
+ * <p>Returns null if the separator arg is null.
+ * For example, {@code CONCAT_WS(null, 'a', 'b')} returns null.
+ *
+ * <p>If all the arguments except the separator are null,
+ * it also returns the empty string.
+ * For example, {@code CONCAT_WS(',', null, null)} returns "". */
+ @LibraryOperator(libraries = {MYSQL, POSTGRESQL})
+ public static final SqlFunction CONCAT_WS =
+ SqlBasicFunction.create("CONCAT_WS",
+ ReturnTypes.MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_ARG0_NULLABLE,
+ OperandTypes.repeat(SqlOperandCountRanges.from(2),
+ OperandTypes.STRING),
+ SqlFunctionCategory.STRING)
+ .withOperandTypeInference(InferTypes.RETURN_TYPE);
+
+ /** The "CONCAT_WS(separator, arg1, arg2, ...)" function in (MSSQL).
+ *
+ * <p>Differs from {@link #CONCAT_WS} (MySQL, Postgres) in that it accepts
+ * between 3 and 254 arguments, and never returns null (even if the separator
+ * is null). For example:
+ *
+ * <ul>
+ * <li>{@code CONCAT_WS(',', 'a', 'b')} returns "{@code a,b}";
+ * <li>{@code CONCAT_WS(null, 'a', 'b')} returns "{@code ab}";
+ * <li>{@code CONCAT_WS(',', null, null)} returns "";
+ * <li>{@code CONCAT_WS(null, null, null)} returns "".
+ * </ul> */
+ @LibraryOperator(libraries = {MSSQL})
+ public static final SqlFunction CONCAT_WS_MSSQL =
+ SqlBasicFunction.create("CONCAT_WS",
+ ReturnTypes.MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_NOT_NULLABLE,
+ OperandTypes.repeat(SqlOperandCountRanges.between(3, 254),
+ OperandTypes.STRING),
+ SqlFunctionCategory.STRING)
+ .withOperandTypeInference(InferTypes.RETURN_TYPE)
+ .withKind(SqlKind.CONCAT_WS_MSSQL);
+
private static RelDataType arrayReturnType(SqlOperatorBinding opBinding) {
RelDataType type =
opBinding.getOperandCount() > 0
diff --git a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
index e2dffc5e89..901d3dc395 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/ReturnTypes.java
@@ -947,7 +947,7 @@ public abstract class ReturnTypes {
* <p>concat(cast('a' as varchar(2)), cast('b' as varchar(3)),cast('c' as
varchar(2)))
* returns varchar(7).
*
- * <p>concat(cast('a' as varchar), cast('b' as varchar(2), cast('c' as
varchar(2))))
+ * <p>concat(cast('a' as varchar), cast('b' as varchar(2)), cast('c' as
varchar(2)))
* returns varchar.
*
* <p>concat(cast('a' as varchar(65535)), cast('b' as varchar(2)), cast('c'
as varchar(2)))
@@ -984,6 +984,68 @@ public abstract class ReturnTypes {
.createSqlType(SqlTypeName.VARCHAR, typePrecision);
};
+ /**
+ * Type-inference strategy for String concatenation with separator.
+ * The precision of separator should be calculated during combining.
+ * Result is varying if either input is; otherwise fixed.
+ *
+ * <p>For example:
+ *
+ * <ul>
+ * <li>{@code concat_ws(',', cast('a' as varchar(2), cast('b' as
+ * varchar(3)), cast('c' as varchar(2)))}
+ * returns {@code varchar(9)};
+ *
+ * <li>{@code concat_ws(',', cast('a' as varchar), cast('b' as
+ * varchar(2)), cast('c' as varchar(2)))}
+ * returns {@code varchar};
+ *
+ * <li>{@code concat_ws(',', cast('a' as varchar(65535)), cast('b'
+ * as varchar(2)), cast('c' as varchar(2)))}
+ * returns {@code varchar}.
+ * </ul>
+ */
+ public static final SqlReturnTypeInference
MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION =
+ ReturnTypes::multivalentStringWithSepSumPrecision;
+
+ private static RelDataType multivalentStringWithSepSumPrecision(
+ SqlOperatorBinding opBinding) {
+ boolean hasPrecisionNotSpecifiedOperand = false;
+ boolean precisionOverflow = false;
+ int typePrecision = RelDataType.PRECISION_NOT_SPECIFIED;
+ long amount = 0;
+ List<RelDataType> operandTypes = opBinding.collectOperandTypes();
+ final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
+ final RelDataTypeSystem typeSystem = typeFactory.getTypeSystem();
+ int separatorPrecision = operandTypes.get(0).getPrecision();
+ // when separator's precision is not specified
+ if (separatorPrecision == typePrecision) {
+ return typeFactory.createSqlType(SqlTypeName.VARCHAR, typePrecision);
+ }
+ for (int i = 1; i < operandTypes.size(); i++) {
+ int operandPrecision = operandTypes.get(i).getPrecision();
+ amount = (long) operandPrecision + amount;
+ // separator's Precision shouldn't be added when encountering null value
+ // or the last string arg
+ if (operandPrecision >= 0 && i < operandTypes.size() - 1) {
+ amount = amount + separatorPrecision;
+ }
+ if (operandPrecision == RelDataType.PRECISION_NOT_SPECIFIED) {
+ hasPrecisionNotSpecifiedOperand = true;
+ break;
+ }
+ if (amount > typeSystem.getMaxPrecision(SqlTypeName.VARCHAR)) {
+ precisionOverflow = true;
+ break;
+ }
+ }
+ if (!(hasPrecisionNotSpecifiedOperand || precisionOverflow)) {
+ typePrecision = (int) amount;
+ }
+
+ return typeFactory.createSqlType(SqlTypeName.VARCHAR, typePrecision);
+ }
+
/**
* Same as {@link #MULTIVALENT_STRING_SUM_PRECISION} and using
* {@link org.apache.calcite.sql.type.SqlTypeTransforms#TO_NULLABLE}.
@@ -996,14 +1058,34 @@ public abstract class ReturnTypes {
* {@link org.apache.calcite.sql.type.SqlTypeTransforms#TO_NOT_NULLABLE}.
*/
public static final SqlReturnTypeInference
MULTIVALENT_STRING_SUM_PRECISION_NOT_NULLABLE =
-
MULTIVALENT_STRING_SUM_PRECISION.andThen(SqlTypeTransforms.TO_NOT_NULLABLE);
+ MULTIVALENT_STRING_SUM_PRECISION
+ .andThen(SqlTypeTransforms.TO_NOT_NULLABLE);
+
+ /**
+ * Same as {@link #MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION} and using
+ * {@link org.apache.calcite.sql.type.SqlTypeTransforms#TO_NOT_NULLABLE}.
+ */
+ public static final SqlReturnTypeInference
+ MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_NOT_NULLABLE =
+ MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION
+ .andThen(SqlTypeTransforms.TO_NOT_NULLABLE);
+
+ /**
+ * Same as {@link #MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION} and using
+ * {@link org.apache.calcite.sql.type.SqlTypeTransforms#TO_NULLABLE_ALL}.
+ */
+ public static final SqlReturnTypeInference
+ MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION_ARG0_NULLABLE =
+ MULTIVALENT_STRING_WITH_SEP_SUM_PRECISION
+ .andThen(SqlTypeTransforms.ARG0_NULLABLE);
/**
* Same as {@link #MULTIVALENT_STRING_SUM_PRECISION} and using
* {@link org.apache.calcite.sql.type.SqlTypeTransforms#TO_NULLABLE_ALL}.
*/
public static final SqlReturnTypeInference
MULTIVALENT_STRING_SUM_PRECISION_NULLABLE_ALL =
-
MULTIVALENT_STRING_SUM_PRECISION.andThen(SqlTypeTransforms.TO_NULLABLE_ALL);
+ MULTIVALENT_STRING_SUM_PRECISION
+ .andThen(SqlTypeTransforms.TO_NULLABLE_ALL);
/**
* Same as {@link #DYADIC_STRING_SUM_PRECISION} and using
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 a0826d1a5d..6dda17513f 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -422,9 +422,13 @@ public enum BuiltInMethod {
OCTET_LENGTH(SqlFunctions.class, "octetLength", ByteString.class),
CHAR_LENGTH(SqlFunctions.class, "charLength", String.class),
STRING_CONCAT(SqlFunctions.class, "concat", String.class, String.class),
- STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatWithNull", String.class,
String.class),
+ STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatWithNull", String.class,
+ String.class),
MULTI_STRING_CONCAT(SqlFunctions.class, "concatMulti", String[].class),
- MULTI_STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatMultiWithNull",
String[].class),
+ MULTI_STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatMultiWithNull",
+ String[].class),
+ MULTI_STRING_CONCAT_WITH_SEPARATOR(SqlFunctions.class,
+ "concatMultiWithSeparator", String[].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 1b47ba7ec7..2dbce65720 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.charLength;
import static org.apache.calcite.runtime.SqlFunctions.concat;
import static org.apache.calcite.runtime.SqlFunctions.concatMulti;
import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithNull;
+import static org.apache.calcite.runtime.SqlFunctions.concatMultiWithSeparator;
import static org.apache.calcite.runtime.SqlFunctions.concatWithNull;
import static org.apache.calcite.runtime.SqlFunctions.fromBase64;
import static org.apache.calcite.runtime.SqlFunctions.greater;
@@ -170,6 +171,19 @@ class SqlFunctionsTest {
assertThat(concatMultiWithNull("a", null, "b"), is("ab"));
}
+ @Test void testConcatMultiWithSeparator() {
+ assertThat(concatMultiWithSeparator(",", "a"), is("a"));
+ assertThat(concatMultiWithSeparator(",", "a b", "cd"), is("a b,cd"));
+ assertThat(concatMultiWithSeparator(",", "a b", null, "cd", null, "e"),
is("a b,cd,e"));
+ assertThat(concatMultiWithSeparator(",", null, null), is(""));
+ assertThat(concatMultiWithSeparator(",", "", ""), is(","));
+ assertThat(concatMultiWithSeparator("", "a", "b", null, "c"), is("abc"));
+ assertThat(concatMultiWithSeparator("", null, null), is(""));
+ // The separator could be null, and it is treated as empty string
+ assertThat(concatMultiWithSeparator(null, "a", "b", null, "c"), is("abc"));
+ assertThat(concatMultiWithSeparator(null, null, null), is(""));
+ }
+
@Test void testPosixRegex() {
assertThat(posixRegex("abc", "abc", true), is(true));
assertThat(posixRegex("abc", "^a", true), is(true));
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index 2c0bf3cb0a..92dc4f15b9 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -103,6 +103,96 @@ from t;
!ok
+# [CALCITE-5741] Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres
libraries)
+# CONCAT_WS in Postgres and MySQL returns null only when the separator arg is
null.
+select concat_ws(',', 'a');
++--------+
+| EXPR$0 |
++--------+
+| a |
++--------+
+(1 row)
+
+!ok
+
+select concat_ws(',', 'a', 'b', 'c');
++--------+
+| EXPR$0 |
++--------+
+| a,b,c |
++--------+
+(1 row)
+
+!ok
+
+select concat_ws(',', 'a', cast(null as varchar), 'b');
++--------+
+| EXPR$0 |
++--------+
+| a,b |
++--------+
+(1 row)
+
+!ok
+
+select concat_ws(',', '', '', '');
++--------+
+| EXPR$0 |
++--------+
+| ,, |
++--------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws(',', '') as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws('', '', '') as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws(',', cast(null as varchar), cast(null as varchar))
as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws(cast(null as varchar), 'a', 'b') as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | true |
++---+-----------+
+(1 row)
+
+!ok
+
# Compression Functions
SELECT COMPRESS('sample');
@@ -514,6 +604,83 @@ select CONVERT(DATE, '05/01/2000', 103);
!ok
!}
+# CONCAT_WS in MSSQL
+select concat_ws(',', 'a', 'b');
++--------+
+| EXPR$0 |
++--------+
+| a,b |
++--------+
+(1 row)
+
+!ok
+
+select concat_ws(',', 'a', cast(null as varchar), 'b');
++--------+
+| EXPR$0 |
++--------+
+| a,b |
++--------+
+(1 row)
+
+!ok
+
+select concat_ws(',', '', '', '');
++--------+
+| EXPR$0 |
++--------+
+| ,, |
++--------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws('', '', '') as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws(',', cast(null as varchar), cast(null as varchar))
as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+with t as (select concat_ws(cast(null as varchar), '', '', '') as c)
+select c, c is null as c_is_null
+from t;
++---+-----------+
+| C | C_IS_NULL |
++---+-----------+
+| | false |
++---+-----------+
+(1 row)
+
+!ok
+
+select concat_ws(cast(null as varchar), 'a', cast(null as varchar), 'b');
++--------+
+| EXPR$0 |
++--------+
+| ab |
++--------+
+(1 row)
+
+!ok
+
# [CALCITE-5771] Apply two different NULL semantics for CONCAT
function(enabled in MySQL, Postgresql, BigQuery and MSSQL)
with t as (select concat(null) as c)
select c, c is null as c_is_null
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index f6aabaef0b..a701fe9eda 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1280,8 +1280,8 @@ completeness.
| string1 NOT LIKE string2 [ ESCAPE string3 ] | Whether *string1* does
not match pattern *string2*
| string1 SIMILAR TO string2 [ ESCAPE string3 ] | Whether *string1*
matches regular expression *string2*
| string1 NOT SIMILAR TO string2 [ ESCAPE string3 ] | Whether *string1* does
not match regular expression *string2*
-| value IN (value [, value]*) | Whether *value* is equal
to a value in a list
-| value NOT IN (value [, value]*) | Whether *value* is not
equal to every value in a list
+| value IN (value [, value ]*) | Whether *value* is equal
to a value in a list
+| value NOT IN (value [, value ]*) | Whether *value* is not
equal to every value in a list
| value IN (sub-query) | Whether *value* is equal
to a row returned by *sub-query*
| value NOT IN (sub-query) | Whether *value* is not
equal to every row returned by *sub-query*
| value comparison SOME (sub-query or collection) | Whether *value*
*comparison* at least one row returned by *sub-query* or *collection*
@@ -2497,7 +2497,7 @@ Not implemented:
Not implemented:
* ST_TriangleAspect(geom) Returns the aspect of a triangle
-* ST_TriangleContouring(query \[, z1, z2, z3 ]\[, varArgs]*) Splits triangles
into smaller triangles according to classes
+* ST_TriangleContouring(query \[, z1, z2, z3 ]\[, varArgs ]*) Splits triangles
into smaller triangles according to classes
* ST_TriangleDirection(geom) Computes the direction of steepest ascent of a
triangle and returns it as a line-string
* ST_TriangleSlope(geom) Computes the slope of a triangle as a percentage
* ST_Voronoi(geom [, outDimension [, envelopePolygon ]]) Creates a Voronoi
diagram
@@ -2679,6 +2679,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| o | CONCAT(string, string) | Concatenates two
strings, returns null only when both string arguments are null, otherwise
treats null as empty string
| b m | CONCAT(string [, string ]*) | Concatenates one or
more strings, returns null if any of the arguments is null
| 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
| m | COMPRESS(string) | Compresses a string
using zlib compression and returns the result as a binary string
| q | CONVERT(type, expression [ , style ]) | Equivalent to
`CAST(expression AS type)`; ignores the *style* operand
| p | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
@@ -2735,8 +2737,8 @@ BigQuery's type system uses confusingly different names
for types and functions:
| m | JSON_INSERT(jsonValue, path, val [, path, val ]*) | Returns a JSON
document insert a data of *jsonValue*, *path*, *val*
| m | JSON_KEYS(jsonValue [, path ]) | Returns a string
indicating the keys of a JSON *jsonValue*
| m | JSON_REMOVE(jsonValue, path [, path ]) | Removes data from
*jsonValue* using a series of *path* expressions and returns the result
-| m | JSON_REPLACE(jsonValue, path, val[, path, val]*) | Returns a JSON
document replace a data of *jsonValue*, *path*, *val*
-| m | JSON_SET(jsonValue, path, val[, path, val]*) | Returns a JSON document
set a data of *jsonValue*, *path*, *val*
+| m | JSON_REPLACE(jsonValue, path, val [, path, val ]*) | Returns a JSON
document replace a data of *jsonValue*, *path*, *val*
+| m | JSON_SET(jsonValue, path, val [, path, val ]*) | Returns a JSON document
set a data of *jsonValue*, *path*, *val*
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of
bytes used to store the binary representation of *jsonValue*
| b o | LEAST(expr [, expr ]* ) | Returns the least of
the expressions
| b m p | LEFT(string, length) | Returns the leftmost
*length* characters from the *string*
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 4553073d89..2add67eb87 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -1963,7 +1963,7 @@ public class SqlOperatorTest {
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5771";>[CALCITE-5771]
* Apply two different NULL semantics for CONCAT function(enabled in MySQL,
- * Postgresql, BigQuery and MSSQL)</a>. */
+ * Postgres, BigQuery and MSSQL)</a>. */
private static void checkConcatFuncWithNull(SqlOperatorFixture f) {
f.setFor(SqlLibraryOperators.CONCAT_FUNCTION_WITH_NULL);
f.checkString("concat('a', 'b', 'c')", "abc", "VARCHAR(3) NOT NULL");
@@ -1999,6 +1999,58 @@ public class SqlOperatorTest {
f.checkFails("^concat('a')^", INVALID_ARGUMENTS_NUMBER, false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-5741";>[CALCITE-5741]
+ * Add CONCAT_WS function (enabled in MSSQL, MySQL, Postgres
+ * libraries)</a>. */
+ @Test void testConcatWSFunc() {
+ final SqlOperatorFixture f = fixture();
+ checkConcatWithSeparator(f.withLibrary(SqlLibrary.MYSQL));
+ checkConcatWithSeparator(f.withLibrary(SqlLibrary.POSTGRESQL));
+ checkConcatWithSeparatorInMSSQL(f.withLibrary(SqlLibrary.MSSQL));
+ }
+
+ private static void checkConcatWithSeparator(SqlOperatorFixture f) {
+ f.setFor(SqlLibraryOperators.CONCAT_WS);
+ 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.checkFails("^concat_ws(',')^", INVALID_ARGUMENTS_NUMBER, false);
+ // 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");
+ }
+
+ private static void checkConcatWithSeparatorInMSSQL(SqlOperatorFixture f) {
+ f.setFor(SqlLibraryOperators.CONCAT_WS_MSSQL);
+ 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");
+ f.checkString("concat_ws(null, 'a', 'b')", "ab", "VARCHAR NOT NULL");
+ f.checkString("concat_ws(null, null, null)", "", "VARCHAR NOT NULL");
+ f.checkFails("^concat_ws(',')^", INVALID_ARGUMENTS_NUMBER, false);
+ f.checkFails("^concat_ws(',', 'a')^", INVALID_ARGUMENTS_NUMBER, false);
+ // 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");
+ }
+
@Test void testModOperator() {
// "%" is allowed under BIG_QUERY, MYSQL_5 SQL conformance levels
final SqlOperatorFixture f0 = fixture()
