This is an automated email from the ASF dual-hosted git repository. chunwei pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/master by this push: new 5754fa9 [CALCITE-2510] Implement CHR function (Sergey Tsvetkov, Chunwei Lei) 5754fa9 is described below commit 5754fa95e20fb6a961b01c1c951fc47f21132923 Author: Sergei_Tsvetkov <9326...@gmail.com> AuthorDate: Mon Jun 10 17:26:26 2019 +0800 [CALCITE-2510] Implement CHR function (Sergey Tsvetkov, Chunwei Lei) It follows oracle standard. Sergey started the work and Chunwei rebased and made some fixup. Close apache/calcite#810 --- .../org/apache/calcite/adapter/enumerable/RexImpTable.java | 2 ++ .../main/java/org/apache/calcite/runtime/SqlFunctions.java | 5 +++++ .../org/apache/calcite/sql/fun/SqlLibraryOperators.java | 10 ++++++++++ .../main/java/org/apache/calcite/sql/type/ReturnTypes.java | 6 ++++++ .../org/apache/calcite/sql/test/SqlOperatorBaseTest.java | 13 +++++++++++++ site/_docs/reference.md | 1 + 6 files changed, 37 insertions(+) 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 db1da06..24fd420 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 @@ -92,6 +92,7 @@ import static org.apache.calcite.linq4j.tree.ExpressionType.NotEqual; import static org.apache.calcite.linq4j.tree.ExpressionType.OrElse; import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract; import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus; +import static org.apache.calcite.sql.fun.SqlLibraryOperators.CHR; import static org.apache.calcite.sql.fun.SqlLibraryOperators.DAYNAME; import static org.apache.calcite.sql.fun.SqlLibraryOperators.DIFFERENCE; import static org.apache.calcite.sql.fun.SqlLibraryOperators.FROM_BASE64; @@ -294,6 +295,7 @@ public class RexImpTable { defineMethod(RIGHT, BuiltInMethod.RIGHT.method, NullPolicy.ANY); defineMethod(REPLACE, BuiltInMethod.REPLACE.method, NullPolicy.STRICT); defineMethod(TRANSLATE3, BuiltInMethod.TRANSLATE3.method, NullPolicy.STRICT); + defineMethod(CHR, "chr", NullPolicy.STRICT); defineMethod(CHARACTER_LENGTH, BuiltInMethod.CHAR_LENGTH.method, NullPolicy.STRICT); defineMethod(CHAR_LENGTH, BuiltInMethod.CHAR_LENGTH.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 a4fa957..68f6d94 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -355,6 +355,11 @@ public class SqlFunctions { return s.substring(len - n); } + /** SQL CHR(long) function. */ + public static String chr(long n) { + return String.valueOf(Character.toChars((int) n)); + } + /** SQL CHARACTER_LENGTH(string) function. */ public static int charLength(String s) { return s.length(); 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 15a7afc..93dc462 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 @@ -253,6 +253,16 @@ public abstract class SqlLibraryOperators { null, OperandTypes.or(OperandTypes.STRING, OperandTypes.BINARY), SqlFunctionCategory.STRING); + + @LibraryOperator(libraries = {ORACLE}) + public static final SqlFunction CHR = + new SqlFunction( + "CHR", + SqlKind.OTHER_FUNCTION, + ReturnTypes.CHAR, + null, + OperandTypes.INTEGER, + SqlFunctionCategory.STRING); } // End SqlLibraryOperators.java 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 aba475f..06a1648 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 @@ -252,6 +252,12 @@ public abstract class ReturnTypes { cascade(DOUBLE, SqlTypeTransforms.TO_NULLABLE); /** + * Type-inference strategy whereby the result type of a call is a Char. + */ + public static final SqlReturnTypeInference CHAR = + explicit(SqlTypeName.CHAR); + + /** * Type-inference strategy whereby the result type of a call is an Integer. */ public static final SqlReturnTypeInference INTEGER = diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 544e8ec..ee0ed1f 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -1985,6 +1985,19 @@ public abstract class SqlOperatorBaseTest { } + @Test public void testChr() { + tester.setFor(SqlLibraryOperators.CHR, VM_FENNEL, VM_JAVA); + final SqlTester tester1 = oracleTester(); + tester1.checkScalar("chr(97)", + "a", "CHAR(1) NOT NULL"); + tester1.checkScalar("chr(48)", + "0", "CHAR(1) NOT NULL"); + tester1.checkScalar("chr(0)", + String.valueOf('\u0000'), "CHAR(1) NOT NULL"); + tester.checkFails("^chr(97.1)^", + "No match found for function signature CHR\\(<NUMERIC>\\)", false); + } + @Test public void testSelect() { tester.check( "select * from (values(1))", diff --git a/site/_docs/reference.md b/site/_docs/reference.md index d0a6bc2..d6f8a31 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -2088,6 +2088,7 @@ semantics. | C | Operator syntax | Description |:- |:-----------------------------------------------|:----------- +| o | CHR(integer) | Returns the character having the binary equivalent to *integer* as a CHAR value | o | DECODE(value, value1, result1 [, valueN, resultN ]* [, default ]) | Compares *value* to each *valueN* value one by one; if *value* is equal to a *valueN*, returns the corresponding *resultN*, else returns *default*, or NULL if *default* is not specified | p | DIFFERENCE(string, string) | Returns a measure of the similarity of two strings, namely the number of character positions that their `SOUNDEX` values have in common: 4 if the `SOUNDEX` values are same and 0 if the `SOUNDEX` values are totally different | o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions