This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 434357ddbb [CALCITE-7063] Result type inferred for CONCAT_FUNCTION is
incorrect for BINARY arguments
434357ddbb is described below
commit 434357ddbb73aea5a45534deff20370a552cc3f3
Author: Mihai Budiu <[email protected]>
AuthorDate: Sat Aug 8 18:22:42 2026 -0700
[CALCITE-7063] Result type inferred for CONCAT_FUNCTION is incorrect for
BINARY arguments
Signed-off-by: Mihai Budiu <[email protected]>
---
.../calcite/adapter/enumerable/RexImpTable.java | 39 +++++++++++++++++++---
.../org/apache/calcite/runtime/SqlFunctions.java | 37 ++++++++++++++++++++
.../calcite/sql/fun/SqlLibraryOperators.java | 7 ++--
.../org/apache/calcite/sql/type/ReturnTypes.java | 25 ++++++++++++--
.../org/apache/calcite/util/BuiltInMethod.java | 4 +++
.../org/apache/calcite/test/SqlFunctionsTest.java | 18 +++++++++-
.../org/apache/calcite/test/SqlValidatorTest.java | 11 ++++++
site/_docs/reference.md | 6 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 27 +++++++++++++++
9 files changed, 162 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 1ea24311e3..12663a2bba 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
@@ -762,10 +762,14 @@ void populate1() {
defineMethod(BITNOT, BuiltInMethod.BIT_NOT.method,
NullPolicy.STRICT);
define(CONCAT, new ConcatImplementor());
- defineMethod(CONCAT_FUNCTION, BuiltInMethod.MULTI_STRING_CONCAT.method,
- NullPolicy.STRICT);
- defineMethod(CONCAT_FUNCTION_WITH_NULL,
- BuiltInMethod.MULTI_STRING_CONCAT_WITH_NULL.method, NullPolicy.NONE);
+ define(CONCAT_FUNCTION,
+ new
ConcatFunctionImplementor(BuiltInMethod.MULTI_STRING_CONCAT.method,
+ BuiltInMethod.MULTI_BYTE_STRING_CONCAT.method,
NullPolicy.STRICT));
+ define(CONCAT_FUNCTION_WITH_NULL,
+ new ConcatFunctionImplementor(
+ BuiltInMethod.MULTI_STRING_CONCAT_WITH_NULL.method,
+ BuiltInMethod.MULTI_BYTE_STRING_CONCAT_WITH_NULL.method,
+ NullPolicy.NONE));
defineMethod(CONCAT2, BuiltInMethod.STRING_CONCAT_WITH_NULL.method,
NullPolicy.ALL);
defineMethod(CONCAT_WS,
@@ -3910,6 +3914,33 @@ private static class ConcatImplementor extends
AbstractRexCallImplementor {
}
}
+ /** Implementor for the multivalent CONCAT functions.
+ * Dispatches to the binary-string runtime method when the result type is
+ * binary; the character and binary methods cannot share a signature because
+ * varargs calls require the exact array component type. */
+ private static class ConcatFunctionImplementor
+ extends AbstractRexCallImplementor {
+ private final MethodImplementor stringImplementor;
+ private final MethodImplementor byteStringImplementor;
+
+ ConcatFunctionImplementor(Method stringMethod, Method byteStringMethod,
+ NullPolicy nullPolicy) {
+ super("concat", nullPolicy, false);
+ stringImplementor = new MethodImplementor(stringMethod, nullPolicy,
false);
+ byteStringImplementor =
+ new MethodImplementor(byteStringMethod, nullPolicy, false);
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator,
+ RexCall call, List<Expression> argValueList) {
+ final MethodImplementor implementor =
+ SqlTypeName.BINARY_TYPES.contains(call.type.getSqlTypeName())
+ ? byteStringImplementor
+ : stringImplementor;
+ return implementor.implementSafe(translator, call, argValueList);
+ }
+ }
+
/** Implementor for a value-constructor. */
private static class ValueConstructorImplementor
extends AbstractRexCallImplementor {
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 32fa29fbd0..d2bd353052 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1763,11 +1763,35 @@ public static ByteString concat(ByteString s0,
ByteString s1) {
return s0.concat(s1);
}
+ /** Concatenates two binary strings.
+ * Returns null only when both b0 and b1 are null,
+ * otherwise null is treated as empty binary string. */
+ public static @Nullable ByteString concatWithNull(@Nullable ByteString b0,
+ @Nullable ByteString b1) {
+ if (b0 == null) {
+ return b1;
+ } else if (b1 == null) {
+ return b0;
+ } else {
+ return b0.concat(b1);
+ }
+ }
+
/** SQL {@code CONCAT(arg0, arg1, arg2, ...)} function. */
public static String concatMulti(String... args) {
return String.join("", args);
}
+ /** SQL {@code CONCAT(arg0, arg1, arg2, ...)} function,
+ * applied to binary strings. */
+ public static ByteString concatMulti(ByteString... args) {
+ ByteString result = ByteString.EMPTY;
+ for (ByteString arg : args) {
+ result = result.concat(arg);
+ }
+ return result;
+ }
+
/** SQL {@code CONCAT(arg0, ...)} function which can accept null
* but never return null. Always treats null as empty string. */
public static String concatMultiWithNull(String... args) {
@@ -1778,6 +1802,19 @@ public static String concatMultiWithNull(String... args)
{
return sb.toString();
}
+ /** SQL {@code CONCAT(arg0, ...)} function applied to binary strings.
+ * Accepts null arguments, treating each as an empty binary string,
+ * and never returns null. */
+ public static ByteString concatMultiWithNull(ByteString... args) {
+ ByteString result = ByteString.EMPTY;
+ for (ByteString arg : args) {
+ if (arg != null) {
+ result = result.concat(arg);
+ }
+ }
+ return result;
+ }
+
/** SQL {@code CONCAT_WS(sep, arg1, arg2, ...)} function;
* treats null arguments as empty strings. */
public static String concatMultiWithSeparator(String... args) {
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 089f20eec9..6b20e97997 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
@@ -1215,13 +1215,16 @@ static RelDataType deriveTypeSplit(SqlOperatorBinding
operatorBinding,
* "CONCAT(null, null, null)" returns "".
*
* <p>It differs from {@link #CONCAT_FUNCTION} when processing
- * null values. */
+ * null values.
+ *
+ * <p>Requires character operands because MSSQL and PostgreSQL convert
+ * every argument to a character string. */
@LibraryOperator(libraries = {MSSQL, POSTGRESQL}, exceptLibraries =
{REDSHIFT})
public static final SqlFunction CONCAT_FUNCTION_WITH_NULL =
SqlBasicFunction.create("CONCAT",
ReturnTypes.MULTIVALENT_STRING_SUM_PRECISION_NOT_NULLABLE,
OperandTypes.repeat(SqlOperandCountRanges.from(1),
- OperandTypes.STRING),
+ OperandTypes.CHARACTER),
SqlFunctionCategory.STRING)
.withOperandTypeInference(InferTypes.RETURN_TYPE)
.withKind(SqlKind.CONCAT_WITH_NULL);
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 f64881cf43..2ad00299f8 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
@@ -1300,16 +1300,37 @@ private static RelDataType deriveNullable(
*
* <p>concat(cast('a' as varchar(65535)), cast('b' as varchar(2)), cast('c'
as varchar(2)))
* returns varchar.
+ *
+ * <p>The result is VARBINARY if all operands are of the BINARY family,
+ * and VARCHAR otherwise; operands of NULL or ANY type do not influence
+ * this choice. Mixing operands of the CHARACTER and BINARY families is
+ * an error.
+ *
+ * <p>concat(x'0a', x'0b') returns varbinary(2).
*/
public static final SqlReturnTypeInference MULTIVALENT_STRING_SUM_PRECISION =
opBinding -> {
boolean hasPrecisionNotSpecifiedOperand = false;
boolean precisionOverflow = false;
+ boolean hasBinaryOperand = false;
+ boolean hasCharacterOperand = false;
int typePrecision;
long amount = 0;
List<RelDataType> operandTypes = opBinding.collectOperandTypes();
final RelDataTypeFactory typeFactory = opBinding.getTypeFactory();
final RelDataTypeSystem typeSystem = typeFactory.getTypeSystem();
+ for (RelDataType operandType : operandTypes) {
+ if (operandType.getFamily() == SqlTypeFamily.BINARY) {
+ hasBinaryOperand = true;
+ } else if (SqlTypeUtil.inCharFamily(operandType)) {
+ hasCharacterOperand = true;
+ }
+ }
+ if (hasBinaryOperand && hasCharacterOperand) {
+ throw opBinding.newError(RESOURCE.needSameTypeParameter());
+ }
+ final SqlTypeName typeName =
+ hasBinaryOperand ? SqlTypeName.VARBINARY : SqlTypeName.VARCHAR;
for (RelDataType operandType : operandTypes) {
int operandPrecision = operandType.getPrecision();
amount = (long) operandPrecision + amount;
@@ -1317,7 +1338,7 @@ private static RelDataType deriveNullable(
hasPrecisionNotSpecifiedOperand = true;
break;
}
- if (amount > typeSystem.getMaxPrecision(SqlTypeName.VARCHAR)) {
+ if (amount > typeSystem.getMaxPrecision(typeName)) {
precisionOverflow = true;
break;
}
@@ -1329,7 +1350,7 @@ private static RelDataType deriveNullable(
}
return opBinding.getTypeFactory()
- .createSqlType(SqlTypeName.VARCHAR, typePrecision);
+ .createSqlType(typeName, typePrecision);
};
/**
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 6b4e52fdf9..8e1e190548 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -572,6 +572,10 @@ public enum BuiltInMethod {
MULTI_STRING_CONCAT(SqlFunctions.class, "concatMulti", String[].class),
MULTI_STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatMultiWithNull",
String[].class),
+ MULTI_BYTE_STRING_CONCAT(SqlFunctions.class, "concatMulti",
+ ByteString[].class),
+ MULTI_BYTE_STRING_CONCAT_WITH_NULL(SqlFunctions.class, "concatMultiWithNull",
+ ByteString[].class),
MULTI_STRING_CONCAT_WITH_SEPARATOR(SqlFunctions.class,
"concatMultiWithSeparator", String[].class),
MULTI_TYPE_STRING_ARRAY_CONCAT_WITH_SEPARATOR(SqlFunctions.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 ac040bd48c..508008deb0 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -258,8 +258,13 @@ static <E> List<E> list() {
// it is treated like empty string, if both values are null, returns null.
// As the following tests show.
assertThat(concatWithNull("a", null), is("a"));
- assertThat(concatWithNull(null, null), is(nullValue()));
+ assertThat(concatWithNull((String) null, null), is(nullValue()));
assertThat(concatWithNull(null, "b"), is("b"));
+ // Binary strings
+ assertThat(concatWithNull(b("0a"), b("0b")), is(b("0a0b")));
+ assertThat(concatWithNull(b("0a"), null), is(b("0a")));
+ assertThat(concatWithNull(null, b("0b")), is(b("0b")));
+ assertThat(concatWithNull((ByteString) null, null), is(nullValue()));
}
@Test void testConcatMulti() {
@@ -270,6 +275,9 @@ static <E> List<E> list() {
assertThat(concatMulti((String) null), is("null"));
assertThat(concatMulti((String) null, null), is("nullnull"));
assertThat(concatMulti("a", null, "b"), is("anullb"));
+ // Binary strings
+ assertThat(concatMulti(b("0a"), b("0b"), b("0c")), is(b("0a0b0c")));
+ assertThat(concatMulti(new ByteString[0]), is(ByteString.EMPTY));
}
@Test void testConcatMultiWithNull() {
@@ -279,6 +287,14 @@ static <E> List<E> list() {
assertThat(concatMultiWithNull((String) null, ""), is(""));
assertThat(concatMultiWithNull((String) null, null, null), is(""));
assertThat(concatMultiWithNull("a", null, "b"), is("ab"));
+ // Binary strings; null is treated as an empty binary string
+ assertThat(concatMultiWithNull(b("0a"), null, b("0c")), is(b("0a0c")));
+ assertThat(concatMultiWithNull((ByteString) null, null),
is(ByteString.EMPTY));
+ }
+
+ /** Creates a {@link ByteString} from a hex string. */
+ private static ByteString b(String hex) {
+ return ByteString.of(hex, 16);
}
@Test void testConcatMultiWithSeparator() {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index eaa4b17a4b..6c10478dbd 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -883,6 +883,17 @@ static SqlOperatorTable operatorTableFor(SqlLibrary
library) {
s.withExpr("concat('a', 'b')").ok();
s.withExpr("concat(x'12', x'34')").ok();
s.withExpr("concat(_UTF16'a', _UTF16'b', _UTF16'c')").ok();
+ // Test case for [CALCITE-7063]
+ // Result type inferred for CONCAT_FUNCTION is incorrect for BINARY
arguments
+ // The PostgreSQL variant produces a character result; binary arguments
+ // are implicitly cast to character strings
+ s.withExpr("concat(x'12', x'34')").columnType("VARCHAR NOT NULL");
+ s.withExpr("concat('a', x'12')").columnType("VARCHAR NOT NULL");
+ s.withExpr("concat(x'12', x'34')")
+ .withWhole(true)
+ .withTypeCoercion(false)
+ .fails("(?s)Cannot apply 'CONCAT' to arguments of type "
+ + "'CONCAT\\(<BINARY\\(1\\)>, <BINARY\\(1\\)>\\)'\\. .*");
s.withExpr("concat('aabbcc', 'ab', '+-')")
.columnType("VARCHAR(10) NOT NULL");
s.withExpr("concat('aabbcc', CAST(NULL AS VARCHAR(20)), '+-')")
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index befae3b624..08938ec364 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -3042,9 +3042,9 @@ ### Dialect-specific Operators
| b o p r | CHR(integer) | Returns the character
whose UTF-8 code is *integer*
| b | CODE_POINTS_TO_BYTES(integers) | Converts *integers*, an
array of integers between 0 and 255 inclusive, into bytes; throws error if any
element is out of range
| b | CODE_POINTS_TO_STRING(integers) | Converts *integers*, an
array of integers between 0 and 0xD7FF or between 0xE000 and 0x10FFFF
inclusive, into string; throws error if any element is out of range
-| o r | 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
+| o r | CONCAT(string, string) | Concatenates two
character or binary 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 character or binary strings, returns null if any of the arguments is null;
mixing character and binary arguments is not allowed
+| p q | CONCAT(string [, string ]*) | Concatenates one or
more character strings, null is treated as empty string; binary arguments are
implicitly cast to character strings
| m | CONCAT_WS(separator, str1 [, string ]*) | Concatenates one or
more strings, returns null only when separator is null, otherwise treats null
arguments as empty strings
| p | CONCAT_WS(separator, any [, any ]*) | Concatenates all but
the first argument, 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
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 1400f90a06..cdae823649 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -2685,6 +2685,14 @@ private static void checkConcatFunc(SqlOperatorFixture
f) {
f.checkString("concat('', '', 'a')", "a", "VARCHAR(1) NOT NULL");
f.checkString("concat('', '', '')", "", "VARCHAR(0) NOT NULL");
f.checkFails("^concat()^", INVALID_ARGUMENTS_NUMBER, false);
+ // Test case for [CALCITE-7063]
+ // Result type inferred for CONCAT_FUNCTION is incorrect for BINARY
arguments
+ f.checkString("concat(x'0a', x'0b', x'0c')", "0a0b0c", "VARBINARY(3) NOT
NULL");
+ f.checkString("concat(cast(x'0a' as varbinary), x'0b')", "0a0b",
+ "VARBINARY NOT NULL");
+ f.checkNull("concat(x'0a', cast(null as varbinary))");
+ f.checkFails("^concat('a', x'0a')^", "Parameters must be of the same type",
+ false);
}
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-6518";>
@@ -2711,6 +2719,14 @@ private static void
checkConcatFuncWithNull(SqlOperatorFixture f) {
f.checkString("concat(null, null, null)", "", "VARCHAR NOT NULL");
f.checkString("concat('', null, '')", "", "VARCHAR NOT NULL");
f.checkFails("^concat()^", INVALID_ARGUMENTS_NUMBER, false);
+ // Test case for [CALCITE-7063]
+ // Result type inferred for CONCAT_FUNCTION is incorrect for BINARY
arguments
+ // MSSQL and PostgreSQL CONCAT convert every argument to a character
+ // string, so binary arguments are implicitly cast to character
+ f.checkString("concat(x'0a', x'0b', x'0c')", "0a0b0c", "VARCHAR NOT NULL");
+ f.checkString("concat(x'0a', cast(null as varbinary), x'0c')", "0a0c",
+ "VARCHAR NOT NULL");
+ f.checkString("concat('a', x'0a')", "a0a", "VARCHAR NOT NULL");
}
private static void checkConcat2Func(SqlOperatorFixture f) {
@@ -2732,6 +2748,17 @@ private static void checkConcat2Func(SqlOperatorFixture
f) {
f.checkNull("concat(null, null)");
f.checkFails("^concat('a', 'b', 'c')^", INVALID_ARGUMENTS_NUMBER, false);
f.checkFails("^concat('a')^", INVALID_ARGUMENTS_NUMBER, false);
+ // Test case for [CALCITE-7063]
+ // Result type inferred for CONCAT_FUNCTION is incorrect for BINARY
arguments
+ f.checkString("concat(x'0a', x'0b')", "0a0b", "VARBINARY(2) NOT NULL");
+ f.checkString("concat(x'0a', cast(null as varbinary))", "0a",
+ "VARBINARY NOT NULL");
+ f.checkNull("concat(cast(null as varbinary), cast(null as varbinary))");
+ f.checkFails("^concat('a', x'0a')^",
+ "Cannot apply 'CONCAT' to arguments of type "
+ + "'CONCAT\\(<CHAR\\(1\\)>, <BINARY\\(1\\)>\\)'\\. Supported "
+ + "form\\(s\\): 'CONCAT\\(<STRING>, <STRING>\\)'",
+ false);
}
/** Test case for
