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 1eff60b [CALCITE-3704] Implement STRCMP function
1eff60b is described below
commit 1eff60b70a1d71022862cca5e5b3bb1178a84392
Author: XuQianJin-Stars <x1q...@163.com>
AuthorDate: Sat Jan 18 11:16:32 2020 +0800
[CALCITE-3704] Implement STRCMP function
---
.../org/apache/calcite/adapter/enumerable/RexImpTable.java | 2 ++
.../main/java/org/apache/calcite/runtime/SqlFunctions.java | 5 +++++
.../java/org/apache/calcite/sql/fun/SqlLibraryOperators.java | 9 +++++++++
core/src/main/java/org/apache/calcite/util/BuiltInMethod.java | 1 +
.../java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java | 10 ++++++++++
core/src/test/resources/sql/functions.iq | 11 +++++++++++
site/_docs/reference.md | 1 +
7 files changed, 39 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 c82358f..795048f 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
@@ -120,6 +120,7 @@ import static
org.apache.calcite.sql.fun.SqlLibraryOperators.RIGHT;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.SHA1;
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.STRCMP;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TANH;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TO_BASE64;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.TRANSLATE3;
@@ -330,6 +331,7 @@ public class RexImpTable {
defineMethod(ASCII, BuiltInMethod.ASCII.method, NullPolicy.STRICT);
defineMethod(REPEAT, BuiltInMethod.REPEAT.method, NullPolicy.STRICT);
defineMethod(SPACE, BuiltInMethod.SPACE.method, NullPolicy.STRICT);
+ defineMethod(STRCMP, BuiltInMethod.STRCMP.method, NullPolicy.STRICT);
defineMethod(SOUNDEX, BuiltInMethod.SOUNDEX.method, NullPolicy.STRICT);
defineMethod(DIFFERENCE, BuiltInMethod.DIFFERENCE.method,
NullPolicy.STRICT);
defineMethod(REVERSE, BuiltInMethod.REVERSE.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 be5e8c3..51d7766 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -370,6 +370,11 @@ public class SqlFunctions {
return repeat(" ", n);
}
+ /** SQL STRCMP(String,String) function. */
+ public static int strcmp(String s0, String s1) {
+ return (int) Math.signum(s1.compareTo(s0));
+ }
+
/** SQL SOUNDEX(string) function. */
public static String soundex(String s) {
return SOUNDEX.soundex(s);
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 99f3fd8..3768323 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
@@ -243,6 +243,15 @@ public abstract class SqlLibraryOperators {
OperandTypes.INTEGER,
SqlFunctionCategory.STRING);
+ @LibraryOperator(libraries = {MYSQL})
+ public static final SqlFunction STRCMP =
+ new SqlFunction("STRCMP",
+ SqlKind.OTHER_FUNCTION,
+ ReturnTypes.INTEGER_NULLABLE,
+ null,
+ OperandTypes.STRING_STRING,
+ SqlFunctionCategory.STRING);
+
@LibraryOperator(libraries = {MYSQL, POSTGRESQL, ORACLE})
public static final SqlFunction SOUNDEX =
new SqlFunction("SOUNDEX",
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 a339323..7d30ac5 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -314,6 +314,7 @@ public enum BuiltInMethod {
REPEAT(SqlFunctions.class, "repeat", String.class, int.class),
SPACE(SqlFunctions.class, "space", int.class),
SOUNDEX(SqlFunctions.class, "soundex", String.class),
+ STRCMP(SqlFunctions.class, "strcmp", String.class, String.class),
DIFFERENCE(SqlFunctions.class, "difference", String.class, String.class),
REVERSE(SqlFunctions.class, "reverse", String.class),
LEFT(SqlFunctions.class, "left", String.class, int.class),
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 e16a7af..ca67272 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
@@ -4460,6 +4460,16 @@ public abstract class SqlOperatorBaseTest {
tester1.checkNull("SPACE(cast(null as integer))");
}
+ @Test public void testStrcmpFunc() {
+ final SqlTester tester1 = tester(SqlLibrary.MYSQL);
+ tester1.setFor(SqlLibraryOperators.STRCMP);
+ tester1.checkString("STRCMP('mytesttext', 'mytesttext')", "0", "INTEGER
NOT NULL");
+ tester1.checkString("STRCMP('mytesttext', 'mytest_text')", "-1", "INTEGER
NOT NULL");
+ tester1.checkString("STRCMP('mytest_text', 'mytesttext')", "1", "INTEGER
NOT NULL");
+ tester1.checkNull("STRCMP('mytesttext', cast(null as varchar(1)))");
+ tester1.checkNull("STRCMP(cast(null as varchar(1)), 'mytesttext')");
+ }
+
@Test public void testSoundexFunc() {
final SqlTester tester1 = oracleTester();
tester1.setFor(SqlLibraryOperators.SOUNDEX);
diff --git a/core/src/test/resources/sql/functions.iq
b/core/src/test/resources/sql/functions.iq
index d625288..2f16e14 100644
--- a/core/src/test/resources/sql/functions.iq
+++ b/core/src/test/resources/sql/functions.iq
@@ -31,6 +31,17 @@ select cbrt(-8);
!ok
+# STRCMP
+select strcmp('mytesttext', 'mytesttext');
++--------+
+| EXPR$0 |
++--------+
+| 0 |
++--------+
+(1 row)
+
+!ok
+
# XML Functions
SELECT ExtractValue('<a>c</a>', '//a');
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 957b6f0..6e6402d 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -2352,6 +2352,7 @@ semantics.
| m o p | SOUNDEX(string) | Returns the phonetic
representation of *string*; throws if *string* is encoded with multi-byte
encoding such as UTF-8
| m | SPACE(integer) | Returns a string of
*integer* spaces; returns an empty string if *integer* is less than 1
| o | SUBSTR(string, position [, substringLength ]) | Returns a portion of
*string*, beginning at character *position*, *substringLength* characters long.
SUBSTR calculates lengths using characters as defined by the input character set
+| m | STRCMP(string, string) | Returns 0 if both of
the strings are same and returns -1 when the first argument is smaller than the
second and 1 when the second one is smaller the first one.
| o | TANH(numeric) | Returns the hyperbolic
tangent of *numeric*
| o p | TO_DATE(string, format) | Converts *string* to a
date using the format *format*
| o p | TO_TIMESTAMP(string, format) | Converts *string* to a
timestamp using the format *format*
