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
commit 89c940cc8b6e5ded4c0fa4fa044bab212a7f8599
Author: xiejiajun <jiajunbernou...@foxmail.com>
AuthorDate: Sun Aug 21 14:47:58 2022 +0800
[CALCITE-5241] Implement CHAR function for MySQL and Spark, also JDBC '{fn
CHAR(n)}'
Close apache/calcite#2878
---
core/src/main/codegen/templates/Parser.jj | 1 +
.../calcite/adapter/enumerable/RexImpTable.java | 5 ++++-
.../org/apache/calcite/runtime/SqlFunctions.java | 19 ++++++++++++++++---
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 1 +
.../calcite/sql/fun/SqlLibraryOperators.java | 15 ++++++++++++++-
.../org/apache/calcite/sql/type/ReturnTypes.java | 7 +++++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 ++
.../apache/calcite/sql/test/SqlAdvisorTest.java | 1 +
core/src/test/resources/sql/functions.iq | 15 +++++++++++++--
site/_docs/reference.md | 10 ++++------
.../org/apache/calcite/test/SqlOperatorTest.java | 22 +++++++++++++++++++---
11 files changed, 82 insertions(+), 16 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 506661ed19..9ad6582315 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -6949,6 +6949,7 @@ SqlIdentifier ReservedFunctionName() :
| <AVG>
| <CARDINALITY>
| <CEILING>
+ | <CHAR>
| <CHAR_LENGTH>
| <CHARACTER_LENGTH>
| <COALESCE>
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 9738523f2c..1864b1e459 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
@@ -115,6 +115,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_LENGTH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ARRAY_REVERSE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_AND;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.BOOL_OR;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.CHAR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CHR;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.COMPRESS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.CONCAT2;
@@ -366,7 +367,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(CHR, BuiltInMethod.CHAR_FROM_UTF8.method, NullPolicy.STRICT);
defineMethod(CHARACTER_LENGTH, BuiltInMethod.CHAR_LENGTH.method,
NullPolicy.STRICT);
defineMethod(CHAR_LENGTH, BuiltInMethod.CHAR_LENGTH.method,
@@ -380,6 +381,8 @@ public class RexImpTable {
defineMethod(OVERLAY, BuiltInMethod.OVERLAY.method, NullPolicy.STRICT);
defineMethod(POSITION, BuiltInMethod.POSITION.method, NullPolicy.STRICT);
defineMethod(ASCII, BuiltInMethod.ASCII.method, NullPolicy.STRICT);
+ defineMethod(CHAR, BuiltInMethod.CHAR_FROM_ASCII.method,
+ NullPolicy.SEMI_STRICT);
defineMethod(REPEAT, BuiltInMethod.REPEAT.method, NullPolicy.STRICT);
defineMethod(SPACE, BuiltInMethod.SPACE.method, NullPolicy.STRICT);
defineMethod(STRCMP, BuiltInMethod.STRCMP.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 636b27bfd8..61914293ef 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -462,9 +462,22 @@ 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 CHAR(integer) function, as in MySQL and Spark.
+ *
+ * <p>Returns the ASCII character of {@code n} modulo 256,
+ * or null if {@code n} < 0. */
+ public static @Nullable String charFromAscii(int n) {
+ if (n < 0) {
+ return null;
+ }
+ return String.valueOf(Character.toChars(n % 256));
+ }
+
+ /** SQL CHR(integer) function, as in Oracle and Postgres.
+ *
+ * <p>Returns the UTF-8 character whose code is {@code n}. */
+ public static String charFromUtf8(int n) {
+ return String.valueOf(Character.toChars(n));
}
/** SQL OCTET_LENGTH(binary) function. */
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
index ccb804912d..4ef5e9d4ed 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -708,6 +708,7 @@ public class SqlJdbcFunctionCall extends SqlFunction {
map.put("TRUNCATE", simple(SqlStdOperatorTable.TRUNCATE));
map.put("ASCII", simple(SqlStdOperatorTable.ASCII));
+ map.put("CHAR", simple(SqlLibraryOperators.CHAR));
map.put("CONCAT", simple(SqlStdOperatorTable.CONCAT));
map.put("DIFFERENCE", simple(SqlLibraryOperators.DIFFERENCE));
map.put("INSERT",
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 acec95ff1e..794270682c 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
@@ -650,7 +650,20 @@ public abstract class SqlLibraryOperators {
ReturnTypes.BIGINT_NULLABLE, null, OperandTypes.TIMESTAMP,
SqlFunctionCategory.TIMEDATE);
- @LibraryOperator(libraries = {ORACLE})
+ /** The "CHAR(n)" function; returns the character whose ASCII code is
+ * {@code n} % 256, or null if {@code n} < 0. */
+ @LibraryOperator(libraries = {MYSQL, SPARK})
+ public static final SqlFunction CHAR =
+ new SqlFunction("CHAR",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.CHAR_FORCE_NULLABLE,
+ null,
+ OperandTypes.INTEGER,
+ SqlFunctionCategory.STRING);
+
+ /** The "CHR(n)" function; returns the character whose UTF-8 code is
+ * {@code n}. */
+ @LibraryOperator(libraries = {ORACLE, POSTGRESQL})
public static final SqlFunction CHR =
new SqlFunction("CHR",
SqlKind.OTHER_FUNCTION,
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 3512ba3a3e..99750c535b 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
@@ -347,6 +347,13 @@ public abstract class ReturnTypes {
public static final SqlReturnTypeInference CHAR =
explicit(SqlTypeName.CHAR);
+ /**
+ * Type-inference strategy whereby the result type of a call is a nullable
+ * CHAR(1).
+ */
+ public static final SqlReturnTypeInference CHAR_FORCE_NULLABLE =
+ CHAR.andThen(SqlTypeTransforms.FORCE_NULLABLE);
+
/**
* Type-inference strategy whereby the result type of a call is an Integer.
*/
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 a3e11dba27..2c3c2d9ba1 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -339,6 +339,8 @@ public enum BuiltInMethod {
UPPER(SqlFunctions.class, "upper", String.class),
LOWER(SqlFunctions.class, "lower", String.class),
ASCII(SqlFunctions.class, "ascii", String.class),
+ CHAR_FROM_ASCII(SqlFunctions.class, "charFromAscii", int.class),
+ CHAR_FROM_UTF8(SqlFunctions.class, "charFromUtf8", int.class),
REPEAT(SqlFunctions.class, "repeat", String.class, int.class),
SPACE(SqlFunctions.class, "space", int.class),
SOUNDEX(SqlFunctions.class, "soundex", String.class),
diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
index 82075d2ae1..54032f3db1 100644
--- a/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/test/SqlAdvisorTest.java
@@ -138,6 +138,7 @@ class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(CAST)",
"KEYWORD(CEIL)",
"KEYWORD(CEILING)",
+ "KEYWORD(CHAR)",
"KEYWORD(CHARACTER_LENGTH)",
"KEYWORD(CHAR_LENGTH)",
"KEYWORD(CLASSIFIER)",
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index dbba1a895d..9b9ab67089 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -56,7 +56,18 @@ SELECT ExtractValue('<a>c</a>', '//a');
# STRING Functions
-#CONCAT
+# CHAR
+SELECT char(null), char(-1), char(65), char(233), char(256+66);
++--------+--------+--------+--------+--------+
+| EXPR$0 | EXPR$1 | EXPR$2 | EXPR$3 | EXPR$4 |
++--------+--------+--------+--------+--------+
+| | | A | é | B |
++--------+--------+--------+--------+--------+
+(1 row)
+
+!ok
+
+# CONCAT
SELECT CONCAT('c', 'h', 'a', 'r');
+--------+
| EXPR$0 |
@@ -115,7 +126,7 @@ select sinh(1);
!ok
-#CONCAT
+# CONCAT
select concat('a', 'b');
+--------+
| EXPR$0 |
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 1f4f08efeb..54befa1199 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1749,6 +1749,7 @@ period:
| Operator syntax | Description
|:--------------- |:-----------
| {fn ASCII(string)} | Returns the ASCII code of the first character of
*string*; if the first character is a non-ASCII character, returns its Unicode
code point; returns 0 if *string* is empty
+| {fn CHAR(integer)} | Returns the character whose ASCII code is *integer* %
256, or null if *integer* < 0
| {fn CONCAT(character, character)} | Returns the concatenation of character
strings
| {fn INSERT(string1, start, length, string2)} | Inserts *string2* into a slot
in *string1*
| {fn LCASE(string)} | Returns a string in which all alphabetic characters in
*string* have been converted to lower case
@@ -1758,15 +1759,11 @@ period:
| {fn LTRIM(string)} | Returns *string* with leading space characters removed
| {fn REPLACE(string, search, replacement)} | Returns a string in which all
the occurrences of *search* in *string* are replaced with *replacement*; if
*replacement* is the empty string, the occurrences of *search* are removed
| {fn REVERSE(string)} | Returns *string* with the order of the characters
reversed
-| {fn RIGHT(string, integer)} | Returns the rightmost *length* characters from
*string*
+| {fn RIGHT(string, length)} | Returns the rightmost *length* characters from
*string*
| {fn RTRIM(string)} | Returns *string* with trailing space characters removed
| {fn SUBSTRING(string, offset, length)} | Returns a character string that
consists of *length* characters from *string* starting at the *offset* position
| {fn UCASE(string)} | Returns a string in which all alphabetic characters in
*string* have been converted to upper case
-Not implemented:
-
-* {fn CHAR(string)}
-
#### Date/time
| Operator syntax | Description
@@ -2564,7 +2561,8 @@ semantics.
| b | ARRAY_CONCAT(array [, array ]*) | Concatenates one or
more arrays. If any input argument is `NULL` the function returns `NULL`
| b | ARRAY_LENGTH(array) | Synonym for
`CARDINALITY`
| b | ARRAY_REVERSE(array) | Reverses elements of
*array*
-| o | CHR(integer) | Returns the character having the binary equivalent to
*integer* as a CHAR value
+| m s | CHAR(integer) | Returns the character
whose ASCII code is *integer* % 256, or null if *integer* < 0
+| o p | CHR(integer) | Returns the character
whose UTF-8 code is *integer*
| o | COSH(numeric) | Returns the hyperbolic
cosine of *numeric*
| o | CONCAT(string, string) | Concatenates two strings
| m p | CONCAT(string [, string ]*) | Concatenates two or
more 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 5bac48f953..8c94c1ed4b 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -1492,9 +1492,8 @@ public class SqlOperatorTest {
f.checkScalar("{fn ASCII('ABC')}", "65", "INTEGER NOT NULL");
f.checkNull("{fn ASCII(cast(null as varchar(1)))}");
- if (false) {
- f.checkScalar("{fn CHAR(code)}", null, "");
- }
+ f.checkScalar("{fn CHAR(97)}", "a", "CHAR(1)");
+
f.checkScalar("{fn CONCAT('foo', 'bar')}", "foobar", "CHAR(6) NOT NULL");
f.checkScalar("{fn DIFFERENCE('Miller', 'miller')}", "4",
@@ -1630,6 +1629,23 @@ public class SqlOperatorTest {
}
+ @Test void testChar() {
+ final SqlOperatorFixture f0 = fixture()
+ .setFor(SqlLibraryOperators.CHR, VM_FENNEL, VM_JAVA);
+ f0.checkFails("^char(97)^",
+ "No match found for function signature CHAR\\(<NUMERIC>\\)", false);
+ final SqlOperatorFixture f = f0.withLibrary(SqlLibrary.MYSQL);
+ f.checkScalar("char(null)", isNullValue(), "CHAR(1)");
+ f.checkScalar("char(-1)", isNullValue(), "CHAR(1)");
+ f.checkScalar("char(97)", "a", "CHAR(1)");
+ f.checkScalar("char(48)", "0", "CHAR(1)");
+ f.checkScalar("char(0)", String.valueOf('\u0000'), "CHAR(1)");
+ f.checkFails("^char(97.1)^",
+ "Cannot apply 'CHAR' to arguments of type 'CHAR\\(<DECIMAL\\(3,
1\\)>\\)'\\. "
+ + "Supported form\\(s\\): 'CHAR\\(<INTEGER>\\)'",
+ false);
+ }
+
@Test void testChr() {
final SqlOperatorFixture f0 = fixture()
.setFor(SqlLibraryOperators.CHR, VM_FENNEL, VM_JAVA);
