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 a25f4a733d [CALCITE-5664] Add CONVERT(string USING transcodingName)
function, also known as TRANSLATE
a25f4a733d is described below
commit a25f4a733dac1526dbacf8a96dca5e21c5f14e84
Author: ILuffZhe <[email protected]>
AuthorDate: Sun Apr 23 22:37:29 2023 +0800
[CALCITE-5664] Add CONVERT(string USING transcodingName) function, also
known as TRANSLATE
CONVERT(value USING transcodingName) converts a string from
one character set to another using a given transcoding.
TRANSLATE(string USING transcodingName) is equivalent.
Note that there are several other CONVERT and TRANSLATE
functions, some of which have nothing to do with character
sets, and some of with are enabled only in specific
libraries (e.g. MSSql and Oracle).
Close apache/calcite#3173
---
core/src/main/codegen/templates/Parser.jj | 9 +-
.../calcite/adapter/enumerable/RexImpTable.java | 22 +++++
.../org/apache/calcite/runtime/SqlFunctions.java | 14 +++
.../main/java/org/apache/calcite/sql/SqlKind.java | 5 +-
.../apache/calcite/sql/fun/SqlConvertFunction.java | 24 ++++-
.../calcite/sql/fun/SqlStdOperatorTable.java | 8 +-
...vertFunction.java => SqlTranslateFunction.java} | 73 ++++-----------
.../calcite/sql/validate/SqlValidatorImpl.java | 5 +-
.../calcite/sql2rel/StandardConvertletTable.java | 12 +++
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../apache/calcite/test/SqlToRelConverterTest.java | 12 +++
.../org/apache/calcite/test/SqlValidatorTest.java | 20 +++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 24 +++++
core/src/test/resources/sql/functions.iq | 103 +++++++++++++++++++++
site/_docs/reference.md | 10 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 18 ++--
.../org/apache/calcite/test/SqlOperatorTest.java | 31 +++++++
17 files changed, 310 insertions(+), 81 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 870de9dde2..3c09e77887 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6053,13 +6053,16 @@ SqlNode BuiltinFunctionCall() :
AddExpression(args, ExprContext.ACCEPT_SUB_QUERY)
(
<USING> name = SimpleIdentifier() { args.add(name); }
+ <RPAREN> {
+ return
SqlStdOperatorTable.TRANSLATE.createCall(s.end(this), args);
+ }
|
<COMMA> e = SimpleIdentifier() { args.add(e); }
<COMMA> e = SimpleIdentifier() { args.add(e); }
+ <RPAREN> {
+ return SqlStdOperatorTable.CONVERT.createCall(s.end(this),
args);
+ }
)
- <RPAREN> {
- return SqlStdOperatorTable.CONVERT.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 9978ea2647..3931ddf5b7 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
@@ -366,6 +366,7 @@ import static
org.apache.calcite.sql.fun.SqlStdOperatorTable.SYSTEM_USER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TAN;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TIMESTAMP_ADD;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TIMESTAMP_DIFF;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRANSLATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRIM;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TRUNCATE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.TUMBLE;
@@ -690,6 +691,7 @@ public class RexImpTable {
map.put(REINTERPRET, new ReinterpretImplementor());
map.put(CONVERT, new ConvertImplementor());
+ map.put(TRANSLATE, new TranslateImplementor());
final RexCallImplementor value = new ValueConstructorImplementor();
map.put(MAP_VALUE_CONSTRUCTOR, value);
@@ -3762,6 +3764,26 @@ public class RexImpTable {
}
}
+ /**
+ * Implementor for the {@code TRANSLATE} function.
+ *
+ * <p>If argument[0] is null, result is null.
+ */
+ private static class TranslateImplementor extends AbstractRexCallImplementor
{
+ TranslateImplementor() {
+ super("translate", NullPolicy.STRICT, false);
+ }
+
+ @Override Expression implementSafe(RexToLixTranslator translator,
+ RexCall call, List<Expression> argValueList) {
+ final RexNode arg0 = call.getOperands().get(0);
+ if (SqlTypeUtil.isNull(arg0.getType())) {
+ return argValueList.get(0);
+ }
+ return Expressions.call(BuiltInMethod.TRANSLATE_WITH_CHARSET.method,
argValueList);
+ }
+ }
+
/**
* Implementation that calls a given {@link java.lang.reflect.Method}.
*
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 42dccc92e0..f44195b491 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -792,6 +792,20 @@ public class SqlFunctions {
}
}
+ /** SQL {@code TRANSLATE(s USING transcodingName)} function,
+ * also known as {@code CONVERT(s USING transcodingName)}. */
+ public static String translateWithCharset(String s, String transcodingName) {
+ final Charset charset = SqlUtil.getCharset(transcodingName);
+ byte[] bytes = s.getBytes(Charset.defaultCharset());
+ final CharsetDecoder decoder = charset.newDecoder();
+ final ByteBuffer buffer = ByteBuffer.wrap(bytes);
+ try {
+ return decoder.decode(buffer).toString();
+ } catch (CharacterCodingException ex) {
+ throw RESOURCE.charsetEncoding(s, charset.name()).ex();
+ }
+ }
+
/** SQL {@code RTRIM} function applied to string. */
public static String rtrim(String s) {
return trim(false, true, " ", s);
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 e6f551fb3a..287b7d1a47 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,
+ /** {@code TRANSLATE} function. */
+ TRANSLATE,
+
/** POSITION function. */
POSITION,
@@ -1221,7 +1224,7 @@ public enum SqlKind {
public static final Set<SqlKind> EXPRESSION =
EnumSet.complementOf(
concat(
- EnumSet.of(AS, ARGUMENT_ASSIGNMENT, CONVERT, DEFAULT,
+ EnumSet.of(AS, ARGUMENT_ASSIGNMENT, CONVERT, TRANSLATE, DEFAULT,
RUNNING, FINAL, LAST, FIRST, PREV, NEXT,
FILTER, WITHIN_GROUP, IGNORE_NULLS, RESPECT_NULLS, SEPARATOR,
DESCENDING, CUBE, ROLLUP, GROUPING_SETS, EXTEND, LATERAL,
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
index cd79137a38..c21d492860 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
@@ -29,12 +29,17 @@ import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWriter;
import org.apache.calcite.sql.type.ReturnTypes;
import org.apache.calcite.sql.type.SqlOperandCountRanges;
+import org.apache.calcite.sql.type.SqlOperandTypeChecker;
+import org.apache.calcite.sql.type.SqlOperandTypeInference;
+import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.util.SqlBasicVisitor;
import org.apache.calcite.sql.util.SqlVisitor;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
+import org.checkerframework.checker.nullness.qual.Nullable;
+
import java.util.List;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -42,7 +47,13 @@ import static org.apache.calcite.util.Static.RESOURCE;
import static java.util.Objects.requireNonNull;
/**
- * Common base for the <code>CONVERT</code> function.
+ * The <code>CONVERT</code> function, which converts a string from one
character
+ * set to another.
+ *
+ * <p>Also the base class for the {@code CONVERT(string USING transcoding)}
+ * and {@code TRANSLATE(string USING transcoding)} functions (see
+ * {@link SqlTranslateFunction}).
+ *
* <p>The SQL syntax is
*
* <blockquote><pre>
@@ -53,10 +64,19 @@ public class SqlConvertFunction extends SqlFunction {
//~ Constructors -----------------------------------------------------------
protected SqlConvertFunction(String name) {
- super(name, SqlKind.CONVERT, ReturnTypes.ARG0, null, null,
+ this(name, SqlKind.CONVERT, ReturnTypes.ARG0, null, null,
SqlFunctionCategory.STRING);
}
+ protected SqlConvertFunction(String name, SqlKind kind,
+ @Nullable SqlReturnTypeInference returnTypeInference,
+ @Nullable SqlOperandTypeInference operandTypeInference,
+ @Nullable SqlOperandTypeChecker operandTypeChecker,
+ SqlFunctionCategory category) {
+ super(name, kind, returnTypeInference, operandTypeInference,
+ operandTypeChecker, category);
+ }
+
//~ Methods ----------------------------------------------------------------
@Override public void validateCall(SqlCall call, SqlValidator validator,
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 44bdf44a3e..c37d88efc5 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
@@ -1581,7 +1581,7 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
*
* <p>The SQL standard defines
* {@code CONVERT(charValue USING transcodingName)}, and MySQL implements it;
- * Calcite does not currently support this.
+ * Calcite supports this in the following TRANSLATE function.
*
* <p>MySQL and Microsoft SQL Server have a {@code CONVERT(type, value)}
* function; Calcite does not currently support this, either. */
@@ -1589,14 +1589,14 @@ public class SqlStdOperatorTable extends
ReflectiveSqlOperatorTable {
new SqlConvertFunction("CONVERT");
/**
- * The <code>TRANSLATE(<i>char_value</i> USING
<i>translation_name</i>)</code> function
- * alters the character set of a string value from one base character set to
another.
+ * The <code>TRANSLATE/CONVERT(<i>char_value</i> USING
<i>transcodingName</i>)</code> function
+ * alters the character set of a string value from one base character set to
transcodingName.
*
* <p>It is defined in the SQL standard. See also the non-standard
* {@link SqlLibraryOperators#TRANSLATE3}, which has a different purpose.
*/
public static final SqlFunction TRANSLATE =
- new SqlConvertFunction("TRANSLATE");
+ new SqlTranslateFunction("TRANSLATE");
public static final SqlFunction OVERLAY = new SqlOverlayFunction();
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlTranslateFunction.java
similarity index 59%
copy from core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
copy to core/src/main/java/org/apache/calcite/sql/fun/SqlTranslateFunction.java
index cd79137a38..82c1d2eb35 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlConvertFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlTranslateFunction.java
@@ -19,19 +19,15 @@ package org.apache.calcite.sql.fun;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
-import org.apache.calcite.sql.SqlFunction;
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.SqlUtil;
-import org.apache.calcite.sql.SqlWriter;
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.util.SqlBasicVisitor;
-import org.apache.calcite.sql.util.SqlVisitor;
import org.apache.calcite.sql.validate.SqlValidator;
import org.apache.calcite.sql.validate.SqlValidatorScope;
@@ -39,21 +35,26 @@ import java.util.List;
import static org.apache.calcite.util.Static.RESOURCE;
-import static java.util.Objects.requireNonNull;
-
/**
- * Common base for the <code>CONVERT</code> function.
+ * Common base for the <code>TRANSLATE(USING) and CONVERT(USING)</code>
function,
+ * which is different from {@link SqlLibraryOperators#TRANSLATE3} and {@link
SqlLibraryOperators#MSSQL_CONVERT}.
* <p>The SQL syntax is
*
* <blockquote><pre>
- * {@code CONVERT(characterString, sourceCharset, destCharset)}
+ * {@code TRANSLATE(characterString USING transcodingName)}
+ * </pre></blockquote>
+ *
+ * or
+ *
+ * <blockquote><pre>
+ * {@code CONVERT(characterString USING transcodingName)}
* </pre></blockquote>
*/
-public class SqlConvertFunction extends SqlFunction {
+public class SqlTranslateFunction extends SqlConvertFunction {
//~ Constructors -----------------------------------------------------------
- protected SqlConvertFunction(String name) {
- super(name, SqlKind.CONVERT, ReturnTypes.ARG0, null, null,
+ protected SqlTranslateFunction(String name) {
+ super(name, SqlKind.TRANSLATE, ReturnTypes.ARG0, null, null,
SqlFunctionCategory.STRING);
}
@@ -64,79 +65,45 @@ public class SqlConvertFunction extends SqlFunction {
// The base method validates all operands. We override because
// we don't want to validate the Charset as identifier.
final List<SqlNode> operands = call.getOperandList();
- assert operands.size() == 3;
+ assert operands.size() == 2;
operands.get(0).validateExpr(validator, scope);
- // validate if the Charsets are legal.
+ // validate if the Charset is legal.
assert operands.get(1) instanceof SqlIdentifier;
final String src_charset = operands.get(1).toString();
SqlUtil.getCharset(src_charset);
- assert operands.get(2) instanceof SqlIdentifier;
- final String dest_charset = operands.get(2).toString();
- SqlUtil.getCharset(dest_charset);
super.validateQuantifier(validator, call);
}
- @Override public <R> void acceptCall(SqlVisitor<R> visitor, SqlCall call,
- boolean onlyExpressions, SqlBasicVisitor.ArgHandler<R> argHandler) {
- if (onlyExpressions) {
- // Both operand[1] and operand[2] are not an expression, but Charset
- // identifier
- argHandler.visitChild(visitor, call, 0, call.operand(0));
- } else {
- super.acceptCall(visitor, call, onlyExpressions, argHandler);
- }
- }
-
- @Override public RelDataType deriveType(SqlValidator validator,
- SqlValidatorScope scope, SqlCall call) {
- // special case for CONVERT: don't need to derive type for Charsets
- RelDataType nodeType =
- validator.deriveType(scope, call.operand(0));
- requireNonNull(nodeType, "nodeType");
- return validateOperands(validator, scope, call);
- }
-
@Override public boolean checkOperandTypes(SqlCallBinding callBinding,
boolean throwOnFailure) {
// type of operand[0] should be Character or NULL
final RelDataType t = callBinding.getOperandType(0);
if (SqlTypeUtil.isNull(t)) {
- // convert(null, src_charset, dest_charset) is supported
+ // convert(null using transcodingName) is supported
return true;
}
if (!SqlTypeUtil.inCharFamily(t)) {
if (throwOnFailure) {
throw callBinding.newValidationError(
RESOURCE.unsupportedTypeInConvertFunc(t.getFullTypeString(),
- "CONVERT", "CHARACTER"));
+ "TRANSLATE", "CHARACTER"));
}
return false;
}
return true;
}
- @Override public void unparse(SqlWriter writer, SqlCall call, int leftPrec,
- int rightPrec) {
- final SqlWriter.Frame frame = writer.startFunCall(getName());
- for (SqlNode node : call.getOperandList()) {
- writer.sep(",");
- node.unparse(writer, leftPrec, rightPrec);
- }
- writer.endFunCall(frame);
- }
-
@Override public String getSignatureTemplate(final int operandsCount) {
- //noinspection SwitchStatementWithTooFewBranches
switch (operandsCount) {
- case 3:
- return "{0}({1}, {2}, {3})";
+ case 2:
+ return "{0}({1} USING {2})";
default:
- throw new IllegalStateException("operandsCount should be 3, got "
+ throw new IllegalStateException("operandsCount should be 2, got "
+ operandsCount);
}
}
@Override public SqlOperandCountRange getOperandCountRange() {
- return SqlOperandCountRanges.of(3);
+ return SqlOperandCountRanges.of(2);
}
}
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 3a28b178ba..b53b8c8023 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
@@ -3868,8 +3868,9 @@ public class SqlValidatorImpl implements
SqlValidatorWithHints {
// we stripped the field access. Recurse to this method, the DOT's
operand
// can be another SqlCall, or an SqlIdentifier.
checkRollUp(grandParent, parent, stripDot, scope, contextClause);
- } else if (stripDot.getKind() == SqlKind.CONVERT) {
- // operand[1] doesn't need to be checked for CONVERT
+ } else if (stripDot.getKind() == SqlKind.CONVERT
+ || stripDot.getKind() == SqlKind.TRANSLATE) {
+ // only need to check operand[0] for CONVERT or TRANSLATE
SqlNode child = ((SqlCall) stripDot).getOperandList().get(0);
checkRollUp(parent, current, child, scope, contextClause);
} else {
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 fae2a1ea8b..a8288700cc 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java
@@ -265,6 +265,7 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
registerOp(SqlStdOperatorTable.AS,
(cx, call) -> cx.convertExpression(call.operand(0)));
registerOp(SqlStdOperatorTable.CONVERT, this::convertCharset);
+ registerOp(SqlStdOperatorTable.TRANSLATE, this::translateCharset);
// "SQRT(x)" is equivalent to "POWER(x, .5)"
registerOp(SqlStdOperatorTable.SQRT,
(cx, call) -> cx.convertExpression(
@@ -755,6 +756,17 @@ public class StandardConvertletTable extends
ReflectiveConvertletTable {
rexBuilder.makeLiteral(destCharset));
}
+ protected RexNode translateCharset(
+ @UnknownInitialization StandardConvertletTable this,
+ SqlRexContext cx, SqlCall call) {
+ final SqlNode expr = call.operand(0);
+ final String transcodingName = call.operand(1).toString();
+ final RexBuilder rexBuilder = cx.getRexBuilder();
+ return rexBuilder.makeCall(SqlStdOperatorTable.TRANSLATE,
+ cx.convertExpression(expr),
+ rexBuilder.makeLiteral(transcodingName));
+ }
+
/**
* Converts a call to the {@code EXTRACT} function.
*
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 afc56eb0d0..4cc5dd3227 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -445,6 +445,7 @@ public enum BuiltInMethod {
String.class, boolean.class),
REPLACE(SqlFunctions.class, "replace", String.class, String.class,
String.class),
+ TRANSLATE_WITH_CHARSET(SqlFunctions.class, "translateWithCharset",
String.class, String.class),
TRANSLATE3(SqlFunctions.class, "translate3", String.class, String.class,
String.class),
LTRIM(SqlFunctions.class, "ltrim", String.class),
RTRIM(SqlFunctions.class, "rtrim", String.class),
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 3c863ba9b8..9326ae9fc5 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3162,6 +3162,18 @@ class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).withTrim(true).ok();
}
+ @Test void testTranslateFunc1() {
+ final String sql = "select translate(ename using utf8) as new_ename\n"
+ + "from emp";
+ sql(sql).withTrim(true).ok();
+ }
+
+ @Test void testTranslateFunc2() {
+ final String sql = "select convert(ename using utf8) as new_ename\n"
+ + "from emp";
+ sql(sql).withTrim(true).ok();
+ }
+
/**
* Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-695";>[CALCITE-695]
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 b49a7632fd..f84a1b1b79 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -942,10 +942,22 @@ public class SqlValidatorTest extends
SqlValidatorTestCase {
.fails("(?s).*not comparable to each other.*");
}
- @Disabled
- void testConvertAndTranslate() {
- expr("convert('abc' using conversion)").ok();
- expr("translate('abc' using translation)").ok();
+ @Test void testConvertAndTranslate() {
+ sql("select convert('abc' using utf16) from emp").ok();
+ sql("select convert(cast(deptno as varchar) using utf8) from emp");
+ sql("select convert(null using utf16) from emp").ok();
+ sql("select ^convert(deptno using latin1)^ from emp")
+ .fails("Invalid type 'INTEGER NOT NULL' in 'TRANSLATE' function\\. "
+ + "Only 'CHARACTER' type is supported");
+ sql("select convert(ename using utf9) from emp").fails("UTF9");
+
+ sql("select translate('abc' using utf8) from emp").ok();
+ sql("select translate(cast(deptno as varchar) using utf8) from emp");
+ sql("select translate(null using utf16) from emp").ok();
+ sql("select ^translate(deptno using latin1)^ from emp")
+ .fails("Invalid type 'INTEGER NOT NULL' in 'TRANSLATE' function\\. "
+ + "Only 'CHARACTER' type is supported");
+ sql("select translate(ename using utf9) from emp").fails("UTF9");
}
@Test void testTranslate3() {
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 44405c16d8..450421a3c3 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -7587,6 +7587,30 @@ LogicalProject(ROWTIME=[$0], PRODUCTID=[$1],
ORDERID=[$2], RANK_NUMBER=[$3])
<![CDATA[
LogicalProject(DEPTNO=[$0], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testTranslateFunc1">
+ <Resource name="sql">
+ <![CDATA[select translate(ename using utf8) as new_ename
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NEW_ENAME=[TRANSLATE($1, 'UTF8')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testTranslateFunc2">
+ <Resource name="sql">
+ <![CDATA[select convert(ename using utf8) as new_ename
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(NEW_ENAME=[TRANSLATE($1, 'UTF8')])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index 309ebf9f5f..a39f4c0bb5 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -361,4 +361,107 @@ select CONVERT(DATE, '05/01/2000', 103);
!ok
!}
+# -----------------------------------------------------------------------------
+# The standard TRANSLATE function, for changing character sets.
+
+select translate('abcd' using utf8);
++--------+
+| EXPR$0 |
++--------+
+| abcd |
++--------+
+(1 row)
+
+!ok
+
+select translate(null using utf8);
++--------+
+| EXPR$0 |
++--------+
+| |
++--------+
+(1 row)
+
+!ok
+
+!use foodmart
+select translate(cast("employee_id" as varchar) using latin1) as alia
+from "employee"
+limit 3;
++------+
+| ALIA |
++------+
+| 1 |
+| 2 |
+| 4 |
++------+
+(3 rows)
+
+!ok
+
+select "employee_id"
+from "employee"
+where translate(cast("employee_id" as varchar) using latin1) <> 1
+limit 3;
++-------------+
+| employee_id |
++-------------+
+| 2 |
+| 4 |
+| 5 |
++-------------+
+(3 rows)
+
+!ok
+
+select convert('abcd' using utf8);
++--------+
+| EXPR$0 |
++--------+
+| abcd |
++--------+
+(1 row)
+
+!ok
+
+select convert(null using utf8);
++--------+
+| EXPR$0 |
++--------+
+| |
++--------+
+(1 row)
+
+!ok
+
+!use foodmart
+select convert(cast("employee_id" as varchar) using latin1) as alia
+from "employee"
+limit 3;
++------+
+| ALIA |
++------+
+| 1 |
+| 2 |
+| 4 |
++------+
+(3 rows)
+
+!ok
+
+select "employee_id"
+from "employee"
+where convert(cast("employee_id" as varchar) using latin1) <> 1
+limit 3;
++-------------+
+| employee_id |
++-------------+
+| 2 |
+| 4 |
+| 5 |
++-------------+
+(3 rows)
+
+!ok
+
# End functions.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 2dbb03e1f1..6c3cfcf807 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1462,10 +1462,12 @@ Algorithms for implicit conversion are subject to
change across Calcite releases
#### Explicit Type Conversion
-| Operator syntax | Description
-|:------------------------------------| :----------
-| CAST(value AS type) | Converts a value to a given type
-| CONVERT(string, charSet1, charSet2) | Converts *string* from *charSet1* to
*charSet2*
+| Operator syntax | Description
+|:----------------------------------------| :----------
+| CAST(value AS type) | Converts a value to a given type
+| CONVERT(string, charSet1, charSet2) | Converts *string* from *charSet1*
to *charSet2*
+| CONVERT(value USING transcodingName) | Alter *value* from one base
character set to *transcodingName*
+| TRANSLATE(value USING transcodingName) | Alter *value* from one base
character set to *transcodingName*
Supported data types syntax:
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 f854df66eb..7e25f6aae6 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
@@ -5351,18 +5351,20 @@ public class SqlParserTest {
expr("convert('abc', utf8, utf16)")
.ok("CONVERT('abc', `UTF8`, `UTF16`)");
sql("select convert(name, latin1, gbk) as newName from t")
- .ok("SELECT CONVERT(`NAME`, `LATIN1`, `GBK`) AS `NEWNAME`\n"
- + "FROM `T`");
+ .ok("SELECT CONVERT(`NAME`, `LATIN1`, `GBK`) AS `NEWNAME`\n"
+ + "FROM `T`");
- // CONVERT function in Mysql
- if (false) {
- expr("convert('abc' using conversion)")
- .ok("CONVERT('abc' USING `CONVERSION`)");
- }
+ expr("convert('abc' using utf8)")
+ .ok("TRANSLATE('abc', `UTF8`)");
+ sql("select convert(name using gbk) as newName from t")
+ .ok("SELECT TRANSLATE(`NAME`, `GBK`) AS `NEWNAME`\n"
+ + "FROM `T`");
- // TRANSLATE need to be implemented (syntax may be different)
expr("translate('abc' using lazy_translation)")
.ok("TRANSLATE('abc', `LAZY_TRANSLATION`)");
+ sql("select translate(name using utf8) as newName from t")
+ .ok("SELECT TRANSLATE(`NAME`, `UTF8`) AS `NEWNAME`\n"
+ + "FROM `T`");
}
@Test void testTranslate3() {
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 5f8d129ef0..45e9d98ab7 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -3650,6 +3650,37 @@ public class SqlOperatorTest {
@Test void testTranslateFunc() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.TRANSLATE, VM_FENNEL, VM_JAVA);
+ f.checkFails("translate('a' using utf10)", "UTF10", false);
+ f.checkFails("convert('a' using utf10)", "UTF10", false);
+
+ f.checkFails("select ^translate(col using utf8)^\n"
+ + "from (select 1 as col\n"
+ + " from (values(true)))",
+ "Invalid type 'INTEGER NOT NULL' in 'TRANSLATE' function\\. "
+ + "Only 'CHARACTER' type is supported",
+ false);
+ f.checkFails("select ^convert(col using utf8)^\n"
+ + "from (select 1 as col\n"
+ + " from (values(true)))",
+ "Invalid type 'INTEGER NOT NULL' in 'TRANSLATE' function\\. "
+ + "Only 'CHARACTER' type is supported",
+ false);
+
+ f.check("select translate(col using utf8)\n"
+ + "from (select 'a' as col\n"
+ + " from (values(true)))",
+ SqlTests.ANY_TYPE_CHECKER, 'a');
+ f.check("select convert(col using utf8)\n"
+ + "from (select 'a' as col\n"
+ + " from (values(true)))",
+ SqlTests.ANY_TYPE_CHECKER, 'a');
+
+ f.checkType("translate('a' using gbk)", "CHAR(1) NOT NULL");
+ f.checkType("convert('a' using gbk)", "CHAR(1) NOT NULL");
+ f.checkType("translate(null using utf16)", "NULL");
+ f.checkType("convert(null using utf16)", "NULL");
+ f.checkType("translate(cast(1 as varchar(2)) using latin1)", "VARCHAR(2)
NOT NULL");
+ f.checkType("convert(cast(1 as varchar(2)) using latin1)", "VARCHAR(2) NOT
NULL");
}
@Test void testTranslate3Func() {
