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 2017/01/11 23:48:32 UTC
calcite git commit: [CALCITE-420] Add REPLACE function,
callable with and without JDBC escape syntax (Riccardo Tommasini)
Repository: calcite
Updated Branches:
refs/heads/master 17dc06fe5 -> 5184aa7ba
[CALCITE-420] Add REPLACE function, callable with and without JDBC escape syntax (Riccardo Tommasini)
Close apache/calcite#348
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/5184aa7b
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/5184aa7b
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/5184aa7b
Branch: refs/heads/master
Commit: 5184aa7baba6298a31fa063e5ac2c55e41a21a13
Parents: 17dc06f
Author: riccardotommasini <to...@gmail.com>
Authored: Sun Jan 8 16:19:54 2017 +0100
Committer: Julian Hyde <jh...@apache.org>
Committed: Wed Jan 11 14:38:09 2017 -0800
----------------------------------------------------------------------
.../calcite/adapter/enumerable/RexImpTable.java | 2 ++
.../apache/calcite/runtime/SqlFunctions.java | 5 +++
.../apache/calcite/sql/SqlJdbcFunctionCall.java | 1 +
.../calcite/sql/fun/SqlStdOperatorTable.java | 7 ++++
.../org/apache/calcite/util/BuiltInMethod.java | 2 ++
.../calcite/sql/parser/SqlParserTest.java | 4 +++
.../calcite/sql/test/SqlOperatorBaseTest.java | 34 ++++++++++++++++----
site/_docs/reference.md | 2 +-
8 files changed, 50 insertions(+), 7 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
----------------------------------------------------------------------
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 1c3e0ea..f06680b 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
@@ -170,6 +170,7 @@ import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RAND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RAND_INTEGER;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.RANK;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.REINTERPRET;
+import static org.apache.calcite.sql.fun.SqlStdOperatorTable.REPLACE;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROUND;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROW;
import static org.apache.calcite.sql.fun.SqlStdOperatorTable.ROW_NUMBER;
@@ -218,6 +219,7 @@ 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(REPLACE, BuiltInMethod.REPLACE.method, NullPolicy.STRICT);
defineMethod(TRANSLATE3, BuiltInMethod.TRANSLATE3.method, NullPolicy.STRICT);
defineMethod(CHARACTER_LENGTH, BuiltInMethod.CHAR_LENGTH.method,
NullPolicy.STRICT);
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
----------------------------------------------------------------------
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 920f93b..c0f1d55 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -1832,6 +1832,11 @@ public class SqlFunctions {
return org.apache.commons.lang3.StringUtils.replaceChars(s, search, replacement);
}
+ /** SQL {@code REPLACE(string, search, replacement)} function. */
+ public static String replace(String s, String search, String replacement) {
+ return s.replace(search, replacement);
+ }
+
/** Helper for "array element reference". Caller has already ensured that
* array and index are not null. Index is 1-based, per SQL. */
public static Object arrayItem(List list, int item) {
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
----------------------------------------------------------------------
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 9945057..936d2c2 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlJdbcFunctionCall.java
@@ -710,6 +710,7 @@ public class SqlJdbcFunctionCall extends SqlFunction {
}
});
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));
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/main/java/org/apache/calcite/sql/fun/SqlStdOperatorTable.java
----------------------------------------------------------------------
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 fcf1352..5c71006 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
@@ -1121,6 +1121,13 @@ public class SqlStdOperatorTable extends ReflectiveSqlOperatorTable {
*/
public static final SqlFunction SUBSTRING = new SqlSubstringFunction();
+ /** The {@code REPLACE(string, search, replace)} function. Not standard SQL,
+ * but in Oracle and Postgres. */
+ public static final SqlFunction REPLACE =
+ new SqlFunction("REPLACE", SqlKind.OTHER_FUNCTION,
+ ReturnTypes.ARG0_NULLABLE_VARYING, null,
+ OperandTypes.STRING_STRING_STRING, SqlFunctionCategory.STRING);
+
public static final SqlFunction CONVERT =
new SqlConvertFunction("CONVERT");
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
----------------------------------------------------------------------
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 4ac7fea..39526a3 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -269,6 +269,8 @@ public enum BuiltInMethod {
TRUNCATE_OR_PAD(SqlFunctions.class, "truncateOrPad", String.class, int.class),
TRIM(SqlFunctions.class, "trim", boolean.class, boolean.class, String.class,
String.class),
+ REPLACE(SqlFunctions.class, "replace", String.class, 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),
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index b8c0ed7..38c0e68 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -3525,6 +3525,10 @@ public class SqlParserTest {
"POSITION('mouse' IN 'house')");
}
+ @Test public void testReplace() {
+ checkExp("replace('x', 'y', 'z')", "REPLACE('x', 'y', 'z')");
+ }
+
// check date/time functions.
@Test public void testTimeDate() {
// CURRENT_TIME - returns time w/ timezone
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java
----------------------------------------------------------------------
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 8c3fcce..4861506 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
@@ -1696,12 +1696,23 @@ public abstract class SqlOperatorBaseTest {
if (false) {
tester.checkScalar("{fn REPEAT(string, count)}", null, "");
}
- if (false) {
- tester.checkScalar(
- "{fn REPLACE(string1, string2, string3)}",
- null,
- "");
- }
+
+ tester.checkString("{fn REPLACE('JACK and JUE','J','BL')}",
+ "BLACK and BLUE", "VARCHAR(12) NOT NULL");
+
+ // REPLACE returns NULL in Oracle but not in Postgres or in Calcite.
+ // When [CALCITE-815] is implemented and SqlConformance#emptyStringIsNull is
+ // enabled, it will return empty string as NULL.
+ tester.checkString("{fn REPLACE('ciao', 'ciao', '')}", "",
+ "VARCHAR(4) NOT NULL");
+
+ tester.checkString("{fn REPLACE('hello world', 'o', '')}", "hell wrld",
+ "VARCHAR(11) NOT NULL");
+
+ tester.checkNull("{fn REPLACE(cast(null as varchar(5)), 'ciao', '')}");
+ 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, "");
}
@@ -3574,6 +3585,17 @@ public abstract class SqlOperatorBaseTest {
"INTEGER NOT NULL");
}
+ @Test public void testReplaceFunc() {
+ tester.setFor(SqlStdOperatorTable.REPLACE);
+ tester.checkString("REPLACE('ciao', 'ciao', '')", "",
+ "VARCHAR(4) NOT NULL");
+ tester.checkString("REPLACE('hello world', 'o', '')", "hell wrld",
+ "VARCHAR(11) NOT NULL");
+ tester.checkNull("REPLACE(cast(null as varchar(5)), 'ciao', '')");
+ tester.checkNull("REPLACE('ciao', cast(null as varchar(3)), 'zz')");
+ tester.checkNull("REPLACE('ciao', 'bella', cast(null as varchar(3)))");
+ }
+
@Test public void testCharLengthFunc() {
tester.setFor(SqlStdOperatorTable.CHAR_LENGTH);
tester.checkScalarExact("char_length('abc')", "3");
http://git-wip-us.apache.org/repos/asf/calcite/blob/5184aa7b/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 8c6e74c..50574da 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -1132,6 +1132,7 @@ See also: UNNEST relational operator converts a collection to a relation.
| {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
Not implemented:
@@ -1140,7 +1141,6 @@ Not implemented:
* {fn DIFFERENCE(string, string)}
* {fn LEFT(string, integer)}
* {fn REPEAT(string, integer)}
-* {fn REPLACE(string, string, string)}
* {fn RIGHT(string, integer)}
* {fn SOUNDEX(string)}
* {fn SPACE(integer)}