This is an automated email from the ASF dual-hosted git repository.
zhehu 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 901aadbe8b [CALCITE-6730] Add CONVERT function(enabled in Oracle
library)
901aadbe8b is described below
commit 901aadbe8bd55e2da791d9d827185a8c803b1b76
Author: Zhe Hu <[email protected]>
AuthorDate: Sun Dec 22 11:28:17 2024 +0800
[CALCITE-6730] Add CONVERT function(enabled in Oracle library)
---
core/src/main/codegen/templates/Parser.jj | 15 ++-
.../calcite/adapter/enumerable/RexImpTable.java | 2 +
.../org/apache/calcite/runtime/SqlFunctions.java | 23 ++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 7 +-
.../calcite/sql/fun/SqlLibraryOperators.java | 4 +
.../calcite/sql/fun/SqlOracleConvertFunction.java | 123 +++++++++++++++++++++
.../calcite/sql/fun/SqlStdOperatorTable.java | 11 ++
.../calcite/sql/validate/SqlValidatorImpl.java | 6 +-
.../calcite/sql2rel/StandardConvertletTable.java | 38 +++++--
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../java/org/apache/calcite/test/JdbcTest.java | 42 +++++++
.../org/apache/calcite/test/SqlFunctionsTest.java | 6 +
core/src/test/resources/sql/functions.iq | 21 ++++
site/_docs/reference.md | 1 +
.../apache/calcite/sql/parser/SqlParserTest.java | 13 +++
.../org/apache/calcite/test/SqlOperatorTest.java | 47 ++++++++
16 files changed, 345 insertions(+), 15 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index f826c4241e..279c6caf6b 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6319,10 +6319,17 @@ SqlNode BuiltinFunctionCall() :
}
|
<COMMA> e = SimpleIdentifier() { args.add(e); }
- <COMMA> e = SimpleIdentifier() { args.add(e); }
- <RPAREN> {
- return SqlStdOperatorTable.CONVERT.createCall(s.end(this),
args);
- }
+ (
+ <COMMA> e = SimpleIdentifier() { args.add(e); }
+ <RPAREN> {
+ SqlOperator op =
SqlStdOperatorTable.getConvertFuncByConformance(this.conformance);
+ return op.createCall(s.end(this), args);
+ }
+ |
+ <RPAREN> {
+ return
SqlLibraryOperators.CONVERT_ORACLE.createCall(s.end(this), args);
+ }
+ )
)
|
// MSSql CONVERT(type, val [,style])
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 fd7aa0cf76..72b5c44cb1 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
@@ -181,6 +181,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_WS_MSSQL;
import static
org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT_WS_POSTGRESQL;
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.CONVERT_ORACLE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COSD;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COSH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COTH;
@@ -740,6 +741,7 @@ public class RexImpTable {
defineMethod(CONCAT_WS_SPARK,
BuiltInMethod.MULTI_TYPE_STRING_ARRAY_CONCAT_WITH_SEPARATOR.method,
NullPolicy.ARG0);
+ defineMethod(CONVERT_ORACLE, BuiltInMethod.CONVERT_ORACLE.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 ba6cf04f73..2ed54e868e 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1681,6 +1681,29 @@ public class SqlFunctions {
}
}
+ /** Oracle's {@code CONVERT(charValue, destCharsetName[, srcCharsetName])}
function,
+ * return null if s is null or empty. */
+ public static String convertOracle(String s, String... args) {
+ final Charset src;
+ final Charset dest;
+ if (args.length == 1) {
+ // srcCharsetName is not specified
+ src = Charset.defaultCharset();
+ dest = SqlUtil.getCharset(args[0]);
+ } else {
+ dest = SqlUtil.getCharset(args[0]);
+ src = SqlUtil.getCharset(args[1]);
+ }
+ byte[] bytes = s.getBytes(src);
+ final CharsetDecoder decoder = dest.newDecoder();
+ final ByteBuffer buffer = ByteBuffer.wrap(bytes);
+ try {
+ return decoder.decode(buffer).toString();
+ } catch (CharacterCodingException ex) {
+ throw RESOURCE.charsetEncoding(s, dest.name()).ex();
+ }
+ }
+
/** State for {@code PARSE_URL}. */
@Deterministic
public static class ParseUrlFunction {
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 5457f205b1..84744b4c62 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -147,6 +147,9 @@ public enum SqlKind {
/** {@code CONVERT} function. */
CONVERT,
+ /** Oracle's {@code CONVERT} function. */
+ CONVERT_ORACLE,
+
/** {@code TRANSLATE} function. */
TRANSLATE,
@@ -1455,8 +1458,8 @@ public enum SqlKind {
public static final Set<SqlKind> EXPRESSION =
EnumSet.complementOf(
concat(
- EnumSet.of(AS, ARGUMENT_ASSIGNMENT, CONVERT, TRANSLATE, DEFAULT,
- RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
+ EnumSet.of(AS, ARGUMENT_ASSIGNMENT, CONVERT, CONVERT_ORACLE,
TRANSLATE,
+ DEFAULT, RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS, SEPARATOR,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
SELECT, JOIN, OTHER_FUNCTION, POSITION, CAST, TRIM, FLOOR,
CEIL,
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 b0173db832..9dbb0d8334 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
@@ -458,6 +458,10 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction SUBSTR_ORACLE =
SUBSTR.withKind(SqlKind.SUBSTR_ORACLE);
+ @LibraryOperator(libraries = {ORACLE})
+ public static final SqlFunction CONVERT_ORACLE =
+ new SqlOracleConvertFunction("CONVERT");
+
/** PostgreSQL's "SUBSTR(string, position [, substringLength ])" function. */
@LibraryOperator(libraries = {POSTGRESQL})
public static final SqlFunction SUBSTR_POSTGRESQL =
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlOracleConvertFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlOracleConvertFunction.java
new file mode 100644
index 0000000000..218b3ba5f1
--- /dev/null
+++
b/core/src/main/java/org/apache/calcite/sql/fun/SqlOracleConvertFunction.java
@@ -0,0 +1,123 @@
+/*
+ * 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.
+ */
+package org.apache.calcite.sql.fun;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexCallBinding;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.SqlFunctionCategory;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlOperandCountRange;
+import org.apache.calcite.sql.SqlOperatorBinding;
+import org.apache.calcite.sql.SqlUtil;
+import org.apache.calcite.sql.type.ReturnTypes;
+import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.validate.SqlValidator;
+import org.apache.calcite.sql.validate.SqlValidatorScope;
+
+import java.nio.charset.Charset;
+import java.util.List;
+
+import static org.apache.calcite.sql.type.NonNullableAccessors.getCollation;
+
+import static java.util.Objects.requireNonNull;
+
+/** Oracle's "CONVERT(charValue, destCharsetName[, srcCharsetName])" function.
+ *
+ * <p>It has a slight different semantics to standard SQL's
+ * {@link SqlStdOperatorTable#CONVERT} function on operands' order, and default
+ * charset will be used if the {@code srcCharsetName} is not specified.
+ *
+ * <p>Returns null if {@code charValue} is null or empty. */
+public class SqlOracleConvertFunction extends SqlConvertFunction {
+ //~ Constructors
-----------------------------------------------------------
+
+ public SqlOracleConvertFunction(String name) {
+ super(name, SqlKind.CONVERT_ORACLE, ReturnTypes.ARG0, null, null,
+ SqlFunctionCategory.STRING);
+ }
+
+ //~ Methods
----------------------------------------------------------------
+
+ @Override public void validateCall(SqlCall call, SqlValidator validator,
+ SqlValidatorScope scope, SqlValidatorScope operandScope) {
+ final List<SqlNode> operands = call.getOperandList();
+ operands.get(0).validateExpr(validator, scope);
+ // validate if the Charsets are legal.
+ assert operands.get(1) instanceof SqlIdentifier;
+ final String src_charset = operands.get(1).toString();
+ SqlUtil.getCharset(src_charset);
+ if (operands.size() == 3) {
+ assert operands.get(2) instanceof SqlIdentifier;
+ final String dest_charset = operands.get(2).toString();
+ SqlUtil.getCharset(dest_charset);
+ }
+ super.validateQuantifier(validator, call);
+ }
+
+ @Override public RelDataType inferReturnType(
+ SqlOperatorBinding opBinding) {
+ final RelDataType ret = opBinding.getOperandType(0);
+ if (SqlTypeUtil.isNull(ret)) {
+ return ret;
+ }
+ final String descCharsetName;
+ if (opBinding instanceof SqlCallBinding) {
+ descCharsetName = ((SqlCallBinding)
opBinding).getCall().operand(1).toString();
+ } else {
+ descCharsetName = ((RexCallBinding)
opBinding).getStringLiteralOperand(1);
+ }
+ assert descCharsetName != null;
+ Charset descCharset = SqlUtil.getCharset(descCharsetName);
+ return opBinding
+ .getTypeFactory().createTypeWithCharsetAndCollation(ret, descCharset,
getCollation(ret));
+ }
+
+ @Override public RelDataType deriveType(SqlValidator validator,
+ SqlValidatorScope scope, SqlCall call) {
+ RelDataType nodeType =
+ validator.deriveType(scope, call.operand(0));
+ requireNonNull(nodeType, "nodeType");
+ RelDataType ret = validateOperands(validator, scope, call);
+ if (SqlTypeUtil.isNull(ret)) {
+ return ret;
+ }
+ Charset descCharset = SqlUtil.getCharset(call.operand(1).toString());
+ return validator.getTypeFactory()
+ .createTypeWithCharsetAndCollation(ret, descCharset,
getCollation(ret));
+ }
+
+ @Override public String getSignatureTemplate(final int operandsCount) {
+ switch (operandsCount) {
+ case 2:
+ return "{0}({1}, {2})";
+ case 3:
+ return "{0}({1}, {2}, {3})";
+ default:
+ throw new IllegalStateException("operandsCount should be 2 or 3, got "
+ + operandsCount);
+ }
+ }
+
+ @Override public SqlOperandCountRange getOperandCountRange() {
+ return SqlOperandCountRanges.between(2, 3);
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
index 9e0ca8bb0a..b7bf8ca25b 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
@@ -2832,4 +2832,15 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
return floor ? SqlStdOperatorTable.FLOOR : SqlStdOperatorTable.CEIL;
}
}
+
+ /** Returns the operator for standard {@code CONVERT} and Oracle's {@code
CONVERT}
+ * with the given library. */
+ public static SqlOperator getConvertFuncByConformance(SqlConformance
conformance) {
+ if (SqlConformanceEnum.ORACLE_10 == conformance
+ || SqlConformanceEnum.ORACLE_12 == conformance) {
+ return SqlLibraryOperators.CONVERT_ORACLE;
+ } else {
+ return SqlStdOperatorTable.CONVERT;
+ }
+ }
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index f216081fce..f992321140 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -4287,8 +4287,10 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
// can be another SqlCall, or an SqlIdentifier.
checkRollUp(grandParent, parent, stripDot, scope, contextClause);
} else if (stripDot.getKind() == SqlKind.CONVERT
- || stripDot.getKind() == SqlKind.TRANSLATE) {
- // only need to check operand[0] for CONVERT or TRANSLATE
+ || stripDot.getKind() == SqlKind.TRANSLATE
+ || stripDot.getKind() == SqlKind.CONVERT_ORACLE) {
+ // only need to check operand[0] for
+ // CONVERT, TRANSLATE or CONVERT_ORACLE
SqlNode child = ((SqlCall) stripDot).getOperandList().get(0);
checkRollUp(parent, current, child, scope, contextClause);
} else if (stripDot.getKind() == SqlKind.LAMBDA) {
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
index ff0afa6037..78f8865915 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -296,6 +296,7 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
(cx, call) -> cx.convertExpression(call.operand(0)));
registerOp(SqlStdOperatorTable.CONVERT, this::convertCharset);
+ registerOp(SqlLibraryOperators.CONVERT_ORACLE, this::convertCharset);
registerOp(SqlStdOperatorTable.TRANSLATE, this::translateCharset);
// "SQRT(x)" is equivalent to "POWER(x, .5)"
registerOp(SqlStdOperatorTable.SQRT,
@@ -855,15 +856,38 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
protected RexNode convertCharset(
@UnknownInitialization StandardConvertletTable this,
SqlRexContext cx, SqlCall call) {
+ final RexBuilder rexBuilder = cx.getRexBuilder();
final SqlParserPos pos = call.getParserPosition();
final SqlNode expr = call.operand(0);
- final String srcCharset = call.operand(1).toString();
- final String destCharset = call.operand(2).toString();
- final RexBuilder rexBuilder = cx.getRexBuilder();
- return rexBuilder.makeCall(pos, SqlStdOperatorTable.CONVERT,
- cx.convertExpression(expr),
- rexBuilder.makeLiteral(srcCharset),
- rexBuilder.makeLiteral(destCharset));
+ final SqlOperator op = call.getOperator();
+ final String srcCharset;
+ final String destCharset;
+ switch (op.getKind()) {
+ case CONVERT:
+ srcCharset = call.operand(1).toString();
+ destCharset = call.operand(2).toString();
+ return rexBuilder.makeCall(pos, op,
+ cx.convertExpression(expr),
+ rexBuilder.makeLiteral(srcCharset),
+ rexBuilder.makeLiteral(destCharset));
+ case CONVERT_ORACLE:
+ destCharset = call.operand(1).toString();
+ switch (call.operandCount()) {
+ case 2:
+ // when srcCharsetName is not specified
+ return rexBuilder.makeCall(pos, op,
+ cx.convertExpression(expr),
+ rexBuilder.makeLiteral(destCharset));
+ default:
+ srcCharset = call.operand(2).toString();
+ return rexBuilder.makeCall(pos, op,
+ cx.convertExpression(expr),
+ rexBuilder.makeLiteral(destCharset),
+ rexBuilder.makeLiteral(srcCharset));
+ }
+ default:
+ throw Util.unexpected(op.getKind());
+ }
}
protected RexNode translateCharset(
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 6ea6701a65..fd96d99a28 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -411,6 +411,7 @@ public enum BuiltInMethod {
TO_CODE_POINTS(SqlFunctions.class, "toCodePoints", String.class),
CONVERT(SqlFunctions.class, "convertWithCharset", String.class, String.class,
String.class),
+ CONVERT_ORACLE(SqlFunctions.class, "convertOracle", String.class,
String[].class),
EXP(SqlFunctions.class, "exp", double.class),
MOD(SqlFunctions.class, "mod", long.class, long.class),
POWER(SqlFunctions.class, "power", double.class, double.class),
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 0dcc797d4e..f378fcca44 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -85,6 +85,7 @@ import org.apache.calcite.sql.parser.SqlParser;
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.parser.impl.SqlParserImpl;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.validate.SqlConformanceEnum;
import org.apache.calcite.sql2rel.SqlToRelConverter.Config;
import org.apache.calcite.test.schemata.catchall.CatchallSchema;
import org.apache.calcite.test.schemata.foodmart.FoodmartSchema;
@@ -7352,6 +7353,47 @@ public class JdbcTest {
.throws_("No match found for function signature NVL(<NUMERIC>,
<NUMERIC>)");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6730";>[CALCITE-6730]
+ * Add CONVERT function(enabled in Oracle library)</a>. */
+ @Test void testConvertOracle() {
+ CalciteAssert.AssertThat withOracle10 =
+ CalciteAssert.hr()
+ .with(SqlConformanceEnum.ORACLE_10);
+ testConvertOracleInternal(withOracle10);
+
+ CalciteAssert.AssertThat withOracle12
+ = withOracle10.with(SqlConformanceEnum.ORACLE_12);
+ testConvertOracleInternal(withOracle12);
+ }
+
+ private void testConvertOracleInternal(CalciteAssert.AssertThat with) {
+ with.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ + "where convert(\"name\", GBK)=_GBK'Eric'")
+ .returns("name=Eric; empid=200\n");
+ with.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ + "where _BIG5'Eric'=convert(\"name\", LATIN1)")
+ .throws_("Cannot apply operation '=' to strings with "
+ + "different charsets 'Big5' and 'ISO-8859-1'");
+ // use LATIN1 as dest charset, not BIG5
+ with.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ + "where _BIG5'Eric'=convert(\"name\", LATIN1, BIG5)")
+ .throws_("Cannot apply operation '=' to strings with "
+ + "different charsets 'Big5' and 'ISO-8859-1'");
+
+ // check cast
+ with.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ + "where cast(convert(\"name\", LATIN1, UTF8) as varchar)='Eric'")
+ .returns("name=Eric; empid=200\n");
+ // the result of convert(\"name\", GBK) has GBK charset
+ // while CHAR(5) has ISO-8859-1 charset, which is not allowed to cast
+ with.query("select \"name\", \"empid\" from \"hr\".\"emps\"\n"
+ + "where cast(convert(\"name\", GBK) as varchar)='Eric'")
+ .throws_(
+ "cannot convert value of type "
+ + "JavaType(class java.lang.String CHARACTER SET \"GBK\") to
type VARCHAR NOT NULL");
+ }
+
@Test void testIf() {
CalciteAssert.that(CalciteAssert.Config.REGULAR)
.with(CalciteConnectionProperty.FUN, "bigquery")
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 34f833eeb6..907390828d 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlFunctionsTest.java
@@ -50,6 +50,7 @@ import static
org.apache.calcite.runtime.SqlFunctions.concatMultiTypeWithSeparat
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.convertOracle;
import static org.apache.calcite.runtime.SqlFunctions.fromBase64;
import static org.apache.calcite.runtime.SqlFunctions.greater;
import static org.apache.calcite.runtime.SqlFunctions.initcap;
@@ -319,6 +320,11 @@ class SqlFunctionsTest {
assertThat(concatMultiObjectWithSeparator("abc", null, null), is(""));
}
+ @Test void testConvertOracle() {
+ assertThat(convertOracle("a", "UTF8", "LATIN1"), is("a"));
+ assertThat(convertOracle("a", "UTF8"), is("a"));
+ }
+
@Test void testPosixRegex() {
final SqlFunctions.PosixRegexFunction f =
new SqlFunctions.PosixRegexFunction();
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index 2ffc73eb45..1322a332a7 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -540,6 +540,27 @@ from t;
!ok
+# [CALCITE-6730] Add CONVERT function(enabled in Oracle library)
+select convert('abcd', utf8);
++--------+
+| EXPR$0 |
++--------+
+| abcd |
++--------+
+(1 row)
+
+!ok
+
+select convert('abcd', utf8, latin1);
++--------+
+| EXPR$0 |
++--------+
+| abcd |
++--------+
+(1 row)
+
+!ok
+
SELECT XMLTRANSFORM(
'<?xml version="1.0"?>
<Article>
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index e48d6afc6b..6f8afb154a 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2831,6 +2831,7 @@ In the following:
| 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
+| o | CONVERT(string, destCharSet[, srcCharSet]) | Converts *string* from
*srcCharSet* to *destCharSet*. If the *srcCharSet* parameter is not specified,
then it uses the default CharSet
| p r | CONVERT_TIMEZONE(tz1, tz2, datetime) | Converts the timezone
of *datetime* from *tz1* to *tz2*
| p | COSD(numeric) | Returns the cosine of
*numeric* in degrees as a double. Returns NaN if *numeric* is NaN. Fails if
*numeric* is greater than the maximum double value.
| * | COSH(numeric) | Returns the hyperbolic
cosine of *numeric*
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 39faceb01b..705cfc3e7f 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -5741,6 +5741,19 @@ public class SqlParserTest {
+ "FROM (VALUES (ROW(TRUE))))");
}
+ @Test void testConvertOracle() {
+ // If there are 3 params in CONVERT_ORACLE operator, it's valid when
+ // the ORACLE function library is enabled ('fun=oracle').
+ // But the parser can always parse it.
+ expr("convert('abc', utf8, gbk)")
+ .ok("CONVERT('abc', `UTF8`, `GBK`)");
+ expr("convert('abc', utf8)")
+ .ok("CONVERT('abc', `UTF8`)");
+ sql("select convert(name, latin1) as newName from t")
+ .ok("SELECT CONVERT(`NAME`, `LATIN1`) AS `NEWNAME`\n"
+ + "FROM `T`");
+ }
+
@Test void testTranslate3() {
expr("translate('aaabbbccc', 'ab', '+-')")
.ok("TRANSLATE('aaabbbccc', 'ab', '+-')");
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 9e10c67b67..733df20be2 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -4646,6 +4646,53 @@ public class SqlOperatorTest {
false);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6730";>[CALCITE-6730]
+ * Add CONVERT function(enabled in Oracle library)</a>. */
+ @Test void testConvertOracleFunc() {
+ final SqlOperatorFixture f = fixture()
+ .setFor(SqlLibraryOperators.CONVERT_ORACLE, VM_JAVA)
+ .withLibrary(SqlLibrary.ORACLE);
+
+ final Consumer<SqlOperatorFixture> consumer = f0 -> {
+ f0.checkFails("convert('a', utf8, utf10)", "UTF10", false);
+ f0.checkFails("convert('a', utf8, ^null^)",
+ "(?s).*Encountered \\\"null\\\" at.*", false);
+ f0.checkFails("convert('a', ^null^, utf8)",
+ "(?s).*Encountered \\\"null\\\" at.*", false);
+ f0.checkFails("^convert(1, utf8, gbk)^",
+ "Invalid type 'INTEGER NOT NULL' in 'CONVERT' function\\. "
+ + "Only 'CHARACTER' type is supported",
+ false);
+ f0.checkType("convert('a', utf16, gbk)", "CHAR(1) NOT NULL");
+ f0.checkType("convert('a', utf16)", "CHAR(1) NOT NULL");
+ f0.checkType("convert(null, utf16, gbk)", "NULL");
+ f0.checkType("convert('', utf16, gbk)", "CHAR(0) NOT NULL");
+ f0.checkType("convert(cast(1 as varchar(2)), utf8, latin1)", "VARCHAR(2)
NOT NULL");
+
+ // cast check
+ f.check("select 'a' as alia\n"
+ + " from (values(true)) where cast(convert('col', latin1) as
char(3))='col'",
+ SqlTests.ANY_TYPE_CHECKER, 'a');
+ f.checkFails("select 'a' as alia\n"
+ + " from (values(true)) where ^cast(convert('col', latin1) as
char(3))=_GBK'col'^",
+ "Cannot apply operation '=' to strings with "
+ + "different charsets 'ISO-8859-1' and 'GBK'",
+ false);
+ // the result of convert('col', gbk) has GBK charset
+ // while CHAR(3) has ISO-8859-1 charset, which is not allowed to cast
+ f.checkFails("select 'a' as alia\n"
+ + " from (values(true)) where ^cast(convert('col', gbk) as
char(3))^=_GBK'col'",
+ "Cast function cannot convert value of type "
+ + "CHAR\\(3\\) CHARACTER SET \"GBK\" NOT NULL to type
CHAR\\(3\\) NOT NULL",
+ false);
+ };
+
+ final List<SqlConformanceEnum> conformances =
+ list(SqlConformanceEnum.ORACLE_10, SqlConformanceEnum.ORACLE_12);
+ f.forEachConformance(conformances, consumer);
+ }
+
@Test void testTranslateFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TRANSLATE, VM_JAVA);
