You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/06/03 15:53:10 UTC
[calcite] branch master updated: [CALCITE-3005] Implement string
functions: LEFT, RIGHT (xuqianjin)
This is an automated email from the ASF dual-hosted git repository.
jhyde 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 7f33215 [CALCITE-3005] Implement string functions: LEFT, RIGHT (xuqianjin)
7f33215 is described below
commit 7f33215ffaf9c0b8f4bef082913c910c77bf4427
Author: XuQianJin-Stars <x1...@163.com>
AuthorDate: Wed Apr 17 16:41:10 2019 +0800
[CALCITE-3005] Implement string functions: LEFT, RIGHT (xuqianjin)
Close apache/calcite#1168
---
core/src/main/codegen/templates/Parser.jj | 9 ++--
.../calcite/adapter/enumerable/RexImpTable.java | 4 ++
.../org/apache/calcite/runtime/SqlFunctions.java | 61 ++++++++++++++++-----
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 51 ++++++++----------
.../calcite/sql/fun/SqlLibraryOperators.java | 12 +++++
.../org/apache/calcite/sql/type/OperandTypes.java | 7 +++
.../org/apache/calcite/util/BuiltInMethod.java | 2 +
.../apache/calcite/sql/test/SqlAdvisorTest.java | 2 +
.../calcite/sql/test/SqlOperatorBaseTest.java | 62 +++++++++++++++++++---
site/_docs/reference.md | 32 +++++------
10 files changed, 176 insertions(+), 66 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 06cf491..5161984 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -5825,6 +5825,7 @@ SqlIdentifier ReservedFunctionName() :
| <HOUR>
| <LAG>
| <LEAD>
+ | <LEFT>
| <LAST_VALUE>
| <LN>
| <LOCALTIME>
@@ -5845,6 +5846,7 @@ SqlIdentifier ReservedFunctionName() :
| <REGR_COUNT>
| <REGR_SXX>
| <REGR_SYY>
+ | <RIGHT>
| <ROW_NUMBER>
| <SECOND>
| <SQRT>
@@ -5930,11 +5932,8 @@ SqlNode JdbcFunctionCall() :
|
(
// INSERT is a reserved word, but we need to handle {fn insert}
- <INSERT> { name = unquotedIdentifier(); }
- |
- // TRUNCATE is a reserved word, but we need to handle {fn truncate}
- LOOKAHEAD(1)
- <TRUNCATE> { name = unquotedIdentifier(); }
+ // Similarly LEFT, RIGHT, TRUNCATE
+ ( <INSERT> | <LEFT> | <RIGHT> | <TRUNCATE> ) { name = unquotedIdentifier(); }
|
// For cases like {fn power(1,2)} and {fn lower('a')}
id = ReservedFunctionName() { name = id.getSimple(); }
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 296215f..21baf7a 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
@@ -99,8 +99,10 @@ import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_PRETTY;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_REMOVE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_STORAGE_SIZE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.JSON_TYPE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.LEFT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REPEAT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.REVERSE;
+import static org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SOUNDEX;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SPACE;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
@@ -281,6 +283,8 @@ public class RexImpTable {
defineMethod(LOWER, BuiltInMethod.LOWER.method, NullPolicy.STRICT);
defineMethod(INITCAP, BuiltInMethod.INITCAP.method, NullPolicy.STRICT);
defineMethod(SUBSTRING, BuiltInMethod.SUBSTRING.method, NullPolicy.STRICT);
+ defineMethod(LEFT, BuiltInMethod.LEFT.method, NullPolicy.ANY);
+ defineMethod(RIGHT, BuiltInMethod.RIGHT.method, NullPolicy.ANY);
defineMethod(REPLACE, BuiltInMethod.REPLACE.method, NullPolicy.STRICT);
defineMethod(TRANSLATE3, BuiltInMethod.TRANSLATE3.method, NullPolicy.STRICT);
defineMethod(CHARACTER_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 8804d00..a0c6587 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -67,6 +67,7 @@ import java.util.Set;
import java.util.TimeZone;
import java.util.concurrent.atomic.AtomicLong;
import java.util.regex.Pattern;
+import javax.annotation.Nonnull;
import static org.apache.calcite.util.Static.RESOURCE;
@@ -257,6 +258,54 @@ public class SqlFunctions {
return SOUNDEX_LENGTH;
}
+ /** SQL LEFT(string, integer) function. */
+ public static @Nonnull String left(@Nonnull String s, int n) {
+ if (n <= 0) {
+ return "";
+ }
+ int len = s.length();
+ if (n >= len) {
+ return s;
+ }
+ return s.substring(0, n);
+ }
+
+ /** SQL LEFT(ByteString, integer) function. */
+ public static @Nonnull ByteString left(@Nonnull ByteString s, int n) {
+ if (n <= 0) {
+ return ByteString.EMPTY;
+ }
+ int len = s.length();
+ if (n >= len) {
+ return s;
+ }
+ return s.substring(0, n);
+ }
+
+ /** SQL RIGHT(string, integer) function. */
+ public static @Nonnull String right(@Nonnull String s, int n) {
+ if (n <= 0) {
+ return "";
+ }
+ int len = s.length();
+ if (n >= len) {
+ return s;
+ }
+ return s.substring(len - n);
+ }
+
+ /** SQL RIGHT(ByteString, integer) function. */
+ public static @Nonnull ByteString right(@Nonnull ByteString s, int n) {
+ if (n <= 0) {
+ return ByteString.EMPTY;
+ }
+ final int len = s.length();
+ if (n >= len) {
+ return s;
+ }
+ return s.substring(len - n);
+ }
+
/** SQL CHARACTER_LENGTH(string) function. */
public static int charLength(String s) {
return s.length();
@@ -361,9 +410,6 @@ public class SqlFunctions {
/** SQL {@code OVERLAY} function. */
public static String overlay(String s, String r, int start) {
- if (s == null || r == null) {
- return null;
- }
return s.substring(0, start - 1)
+ r
+ s.substring(start - 1 + r.length());
@@ -371,9 +417,6 @@ public class SqlFunctions {
/** SQL {@code OVERLAY} function. */
public static String overlay(String s, String r, int start, int length) {
- if (s == null || r == null) {
- return null;
- }
return s.substring(0, start - 1)
+ r
+ s.substring(start - 1 + length);
@@ -381,9 +424,6 @@ public class SqlFunctions {
/** SQL {@code OVERLAY} function applied to binary strings. */
public static ByteString overlay(ByteString s, ByteString r, int start) {
- if (s == null || r == null) {
- return null;
- }
return s.substring(0, start - 1)
.concat(r)
.concat(s.substring(start - 1 + r.length()));
@@ -392,9 +432,6 @@ public class SqlFunctions {
/** SQL {@code OVERLAY} function applied to binary strings. */
public static ByteString overlay(ByteString s, ByteString r, int start,
int length) {
- if (s == null || r == null) {
- return null;
- }
return s.substring(0, start - 1)
.concat(r)
.concat(s.substring(start - 1 + length));
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 4e17613..7a8f3b3 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -696,29 +696,26 @@ public class SqlJdbcFunctionCall extends SqlFunction {
map.put("TAN", simple(SqlStdOperatorTable.TAN));
map.put("TRUNCATE", simple(SqlStdOperatorTable.TRUNCATE));
+ map.put("ASCII", simple(SqlStdOperatorTable.ASCII));
map.put("CONCAT", simple(SqlStdOperatorTable.CONCAT));
+ map.put("DIFFERENCE", simple(SqlLibraryOperators.DIFFERENCE));
map.put("INSERT",
new PermutingMakeCall(SqlStdOperatorTable.OVERLAY, new int[]{0, 2, 3, 1}));
map.put("LCASE", simple(SqlStdOperatorTable.LOWER));
map.put("LENGTH", simple(SqlStdOperatorTable.CHARACTER_LENGTH));
map.put("LOCATE", simple(SqlStdOperatorTable.POSITION));
- map.put("ASCII", simple(SqlStdOperatorTable.ASCII));
- map.put("DIFFERENCE", simple(SqlLibraryOperators.DIFFERENCE));
+ map.put("LEFT", simple(SqlLibraryOperators.LEFT));
+ map.put("LTRIM", trim(SqlTrimFunction.Flag.LEADING));
map.put("REPEAT", simple(SqlLibraryOperators.REPEAT));
+ map.put("REPLACE", simple(SqlStdOperatorTable.REPLACE));
+ map.put("REVERSE", simple(SqlLibraryOperators.REVERSE));
+ map.put("RIGHT", simple(SqlLibraryOperators.RIGHT));
+ map.put("RTRIM", trim(SqlTrimFunction.Flag.TRAILING));
map.put("SOUNDEX", simple(SqlLibraryOperators.SOUNDEX));
map.put("SPACE", simple(SqlLibraryOperators.SPACE));
- map.put("REVERSE", simple(SqlLibraryOperators.REVERSE));
- map.put("LTRIM",
- new SimpleMakeCall(SqlStdOperatorTable.TRIM) {
- @Override public SqlCall createCall(SqlParserPos pos,
- SqlNode... operands) {
- assert 1 == operands.length;
- return super.createCall(pos,
- SqlTrimFunction.Flag.LEADING.symbol(SqlParserPos.ZERO),
- SqlLiteral.createCharString(" ", SqlParserPos.ZERO),
- operands[0]);
- }
- });
+ map.put("SUBSTRING", simple(SqlStdOperatorTable.SUBSTRING));
+ map.put("UCASE", simple(SqlStdOperatorTable.UPPER));
+
map.put("YEAR", simple(SqlStdOperatorTable.YEAR));
map.put("QUARTER", simple(SqlStdOperatorTable.QUARTER));
map.put("MONTH", simple(SqlStdOperatorTable.MONTH));
@@ -730,20 +727,6 @@ public class SqlJdbcFunctionCall extends SqlFunction {
map.put("MINUTE", simple(SqlStdOperatorTable.MINUTE));
map.put("SECOND", simple(SqlStdOperatorTable.SECOND));
- map.put("RTRIM",
- new SimpleMakeCall(SqlStdOperatorTable.TRIM) {
- @Override public SqlCall createCall(SqlParserPos pos,
- SqlNode... operands) {
- assert 1 == operands.length;
- return super.createCall(pos,
- SqlTrimFunction.Flag.TRAILING.symbol(SqlParserPos.ZERO),
- SqlLiteral.createCharString(" ", SqlParserPos.ZERO),
- operands[0]);
- }
- });
- map.put("SUBSTRING", simple(SqlStdOperatorTable.SUBSTRING));
- map.put("REPLACE", simple(SqlStdOperatorTable.REPLACE));
- map.put("UCASE", simple(SqlStdOperatorTable.UPPER));
map.put("CURDATE", simple(SqlStdOperatorTable.CURRENT_DATE));
map.put("CURTIME", simple(SqlStdOperatorTable.LOCALTIME));
map.put("NOW", simple(SqlStdOperatorTable.CURRENT_TIMESTAMP));
@@ -777,6 +760,18 @@ public class SqlJdbcFunctionCall extends SqlFunction {
this.map = map.build();
}
+ private MakeCall trim(SqlTrimFunction.Flag flag) {
+ return new SimpleMakeCall(SqlStdOperatorTable.TRIM) {
+ @Override public SqlCall createCall(SqlParserPos pos,
+ SqlNode... operands) {
+ assert 1 == operands.length;
+ return super.createCall(pos, flag.symbol(pos),
+ SqlLiteral.createCharString(" ", SqlParserPos.ZERO),
+ operands[0]);
+ }
+ };
+ }
+
private MakeCall simple(SqlOperator operator) {
return new SimpleMakeCall(operator);
}
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 28c7f9f..ff27685 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
@@ -154,6 +154,12 @@ public abstract class SqlLibraryOperators {
public static final SqlFunction JSON_STORAGE_SIZE = new SqlJsonStorageSizeFunction();
@LibraryOperator(libraries = {MYSQL, POSTGRESQL})
+ public static final SqlFunction LEFT =
+ new SqlFunction("LEFT", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0_NULLABLE_VARYING, null,
+ OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
+
+ @LibraryOperator(libraries = {MYSQL, POSTGRESQL})
public static final SqlFunction REPEAT =
new SqlFunction(
"REPEAT",
@@ -163,6 +169,12 @@ public abstract class SqlLibraryOperators {
OperandTypes.STRING_INTEGER,
SqlFunctionCategory.STRING);
+ @LibraryOperator(libraries = {MYSQL, POSTGRESQL})
+ public static final SqlFunction RIGHT =
+ new SqlFunction("RIGHT", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0_NULLABLE_VARYING, null,
+ OperandTypes.CBSTRING_INTEGER, SqlFunctionCategory.STRING);
+
@LibraryOperator(libraries = {MYSQL})
public static final SqlFunction SPACE =
new SqlFunction(
diff --git a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
index 5d12e12..ba1cd10 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/OperandTypes.java
@@ -408,6 +408,13 @@ public abstract class OperandTypes {
public static final SqlSingleOperandTypeChecker STRING_INTEGER =
family(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER);
+ /** Operand type-checking strategy where the first operand is a character or
+ * binary string (CHAR, VARCHAR, BINARY or VARBINARY), and the second operand
+ * is INTEGER. */
+ public static final SqlSingleOperandTypeChecker CBSTRING_INTEGER =
+ or(family(SqlTypeFamily.STRING, SqlTypeFamily.INTEGER),
+ family(SqlTypeFamily.BINARY, SqlTypeFamily.INTEGER));
+
/**
* Operand type-checking strategy where two operands must both be in the
* same string type family and last type is 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 18b21d1..246479a 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -277,6 +277,8 @@ public enum BuiltInMethod {
SOUNDEX(SqlFunctions.class, "soundex", String.class),
DIFFERENCE(SqlFunctions.class, "difference", String.class, String.class),
REVERSE(SqlFunctions.class, "reverse", String.class),
+ LEFT(SqlFunctions.class, "left", String.class, int.class),
+ RIGHT(SqlFunctions.class, "right", String.class, int.class),
JSONIZE(JsonFunctions.class, "jsonize", Object.class),
DEJSONIZE(JsonFunctions.class, "dejsonize", String.class),
JSON_VALUE_EXPRESSION(JsonFunctions.class, "jsonValueExpression",
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 a31e5b6..74ccadb 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
@@ -176,6 +176,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(LAG)",
"KEYWORD(LAST_VALUE)",
"KEYWORD(LEAD)",
+ "KEYWORD(LEFT)",
"KEYWORD(LN)",
"KEYWORD(LOCALTIME)",
"KEYWORD(LOCALTIMESTAMP)",
@@ -205,6 +206,7 @@ public class SqlAdvisorTest extends SqlValidatorTestCase {
"KEYWORD(REGR_COUNT)",
"KEYWORD(REGR_SXX)",
"KEYWORD(REGR_SYY)",
+ "KEYWORD(RIGHT)",
"KEYWORD(ROW)",
"KEYWORD(ROW_NUMBER)",
"KEYWORD(RUNNING)",
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 796adfb..9a112bf 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
@@ -82,6 +82,7 @@ import java.util.Locale;
import java.util.TimeZone;
import java.util.function.Consumer;
import java.util.regex.Pattern;
+import java.util.stream.Stream;
import static org.hamcrest.CoreMatchers.equalTo;
import static org.junit.Assert.assertEquals;
@@ -1849,6 +1850,15 @@ public abstract class SqlOperatorBaseTest {
tester.checkString("{fn REVERSE('abc')}", "cba", "VARCHAR(3) NOT NULL");
tester.checkNull("{fn REVERSE(cast(null as varchar(1)))}");
+ tester.checkString("{fn LEFT('abcd', 3)}", "abc", "VARCHAR(4) NOT NULL");
+ tester.checkString("{fn LEFT('abcd', 4)}", "abcd", "VARCHAR(4) NOT NULL");
+ tester.checkString("{fn LEFT('abcd', 5)}", "abcd", "VARCHAR(4) NOT NULL");
+ tester.checkNull("{fn LEFT(cast(null as varchar(1)), 3)}");
+ tester.checkString("{fn RIGHT('abcd', 3)}", "bcd", "VARCHAR(4) NOT NULL");
+ tester.checkString("{fn RIGHT('abcd', 4)}", "abcd", "VARCHAR(4) NOT NULL");
+ tester.checkString("{fn RIGHT('abcd', 5)}", "abcd", "VARCHAR(4) NOT NULL");
+ tester.checkNull("{fn RIGHT(cast(null as varchar(1)), 3)}");
+
// REVIEW: is this result correct? I think it should be "abcCdef"
tester.checkScalar(
"{fn INSERT('abc', 1, 2, 'ABCdef')}",
@@ -1859,9 +1869,6 @@ public abstract class SqlOperatorBaseTest {
"foobar",
"CHAR(6) NOT NULL");
if (false) {
- tester.checkScalar("{fn LEFT(string, count)}", null, "");
- }
- if (false) {
tester.checkScalar("{fn LENGTH(string)}", null, "");
}
tester.checkScalar(
@@ -1899,9 +1906,6 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("{fn REPLACE('ciao', cast(null as varchar(3)), 'zz')}");
tester.checkNull("{fn REPLACE('ciao', 'bella', cast(null as varchar(3)))}");
- if (false) {
- tester.checkScalar("{fn RIGHT(string, count)}", null, "");
- }
tester.checkScalar(
"{fn RTRIM(' xxx ')}",
@@ -4347,6 +4351,52 @@ public abstract class SqlOperatorBaseTest {
tester.checkNull("upper(cast(null as varchar(1)))");
}
+ @Test public void testLeftFunc() {
+ Stream.of(SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL)
+ .map(this::tester)
+ .forEach(t -> {
+ t.setFor(SqlLibraryOperators.LEFT);
+ t.checkString("left('abcd', 3)", "abc", "VARCHAR(4) NOT NULL");
+ t.checkString("left('abcd', 0)", "", "VARCHAR(4) NOT NULL");
+ t.checkString("left('abcd', 5)", "abcd", "VARCHAR(4) NOT NULL");
+ t.checkString("left('abcd', -2)", "", "VARCHAR(4) NOT NULL");
+ t.checkNull("left(cast(null as varchar(1)), -2)");
+ t.checkNull("left('abcd', cast(null as Integer))");
+
+ // test for ByteString
+ t.checkString("left(x'ABCdef', 1)", "ab", "VARBINARY(3) NOT NULL");
+ t.checkString("left(x'ABCdef', 0)", "", "VARBINARY(3) NOT NULL");
+ t.checkString("left(x'ABCdef', 4)", "abcdef",
+ "VARBINARY(3) NOT NULL");
+ t.checkString("left(x'ABCdef', -2)", "", "VARBINARY(3) NOT NULL");
+ t.checkNull("left(cast(null as binary(1)), -2)");
+ t.checkNull("left(x'ABCdef', cast(null as Integer))");
+ });
+ }
+
+ @Test public void testRightFunc() {
+ Stream.of(SqlLibrary.MYSQL, SqlLibrary.POSTGRESQL)
+ .map(this::tester)
+ .forEach(t -> {
+ t.setFor(SqlLibraryOperators.RIGHT);
+ t.checkString("right('abcd', 3)", "bcd", "VARCHAR(4) NOT NULL");
+ t.checkString("right('abcd', 0)", "", "VARCHAR(4) NOT NULL");
+ t.checkString("right('abcd', 5)", "abcd", "VARCHAR(4) NOT NULL");
+ t.checkString("right('abcd', -2)", "", "VARCHAR(4) NOT NULL");
+ t.checkNull("right(cast(null as varchar(1)), -2)");
+ t.checkNull("right('abcd', cast(null as Integer))");
+
+ // test for ByteString
+ t.checkString("right(x'ABCdef', 1)", "ef", "VARBINARY(3) NOT NULL");
+ t.checkString("right(x'ABCdef', 0)", "", "VARBINARY(3) NOT NULL");
+ t.checkString("right(x'ABCdef', 4)", "abcdef",
+ "VARBINARY(3) NOT NULL");
+ t.checkString("right(x'ABCdef', -2)", "", "VARBINARY(3) NOT NULL");
+ t.checkNull("right(cast(null as binary(1)), -2)");
+ t.checkNull("right(x'ABCdef', cast(null as Integer))");
+ });
+ }
+
@Test public void testJsonExists() {
tester.checkBoolean("json_exists('{\"foo\":\"bar\"}', "
+ "'strict $.foo' false on error)", Boolean.TRUE);
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index b8dfd6d..4a4505b 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1472,24 +1472,24 @@ 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 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
+| {fn LCASE(string)} | Returns a string in which all alphabetic characters in *string* have been converted to lower case
| {fn LENGTH(string)} | Returns the number of characters in a string
| {fn LOCATE(string1, string2 [, integer])} | Returns the position in *string2* of the first occurrence of *string1*. Searches from the beginning of *string2*, unless *integer* is specified.
+| {fn LEFT(string, length)} | Returns the leftmost *length* characters from *string*
| {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 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
-| {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 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 REVERSE(string)} | Returns *string* with the order of the characters reversed
Not implemented:
* {fn CHAR(string)}
-* {fn LEFT(string, integer)}
-* {fn RIGHT(string, integer)}
#### Date/time
@@ -2092,6 +2092,9 @@ semantics.
| C | Operator syntax | Description
|:- |:-----------------------------------------------|:-----------
+| 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
| m | JSON_TYPE(jsonValue) | Returns a string value indicating the type of a *jsonValue*
| m | JSON_DEPTH(jsonValue) | Returns an integer value indicating the depth of a *jsonValue*
| m | JSON_PRETTY(jsonValue) | Returns a pretty-printing of *jsonValue*
@@ -2099,19 +2102,18 @@ semantics.
| m | JSON_KEYS(jsonValue [, path ]) | Returns a string indicating the keys of a JSON *jsonValue*
| m | JSON_REMOVE(jsonValue, path[, path]) | Removes data from *jsonValue* using a series of *path* expressions and returns the result
| m | JSON_STORAGE_SIZE(jsonValue) | Returns the number of bytes used to store the binary representation of a *jsonValue*
-| m | REVERSE(string) | Returns *string* with the order of the characters reversed
-| 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
-| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| o | LEAST(expr [, expr ]* ) | Returns the least of the expressions
+| m p | LEFT(string, length) | Returns the leftmost *length* characters from the *string*
| o | LTRIM(string) | Returns *string* with all blanks removed from the start
+| o | NVL(value1, value2) | Returns *value1* if *value1* is not null, otherwise *value2*
+| m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
+| m | REVERSE(string) | Returns *string* with the order of the characters reversed
+| m p | RIGHT(string, length) | Returns the rightmost *length* characters from the *string*
| o | RTRIM(string) | Returns *string* with all blanks removed from the end
-| o | SUBSTR(string, position [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set
-| o | GREATEST(expr [, expr ]*) | Returns the greatest of the expressions
-| o | LEAST(expr [, expr ]* ) | Returns the least of the expressions
-| o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
| m o p | SOUNDEX(string) | Returns the phonetic representation of *string*; throws if *string* is encoded with multi-byte encoding such as UTF-8
-| 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
-| m p | REPEAT(string, integer) | Returns a string consisting of *string* repeated of *integer* times; returns an empty string if *integer* is less than 1
| m | SPACE(integer) | Returns a string of *integer* spaces; returns an empty string if *integer* is less than 1
+| o | SUBSTR(string, position [, substring_length ]) | Returns a portion of *string*, beginning at character *position*, *substring_length* characters long. SUBSTR calculates lengths using characters as defined by the input character set
+| o p | TRANSLATE(expr, fromString, toString) | Returns *expr* with all occurrences of each character in *fromString* replaced by its corresponding character in *toString*. Characters in *expr* that are not in *fromString* are not replaced
Note: