[CALCITE-2572] SQL standard semantics for SUBSTRING function (Andrew Pilloud)
Close apache/calcite#842 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/4f1b5eff Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/4f1b5eff Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/4f1b5eff Branch: refs/heads/master Commit: 4f1b5eff9dbed20ecd8b327ada7c6db1bfd8d78d Parents: 19826da Author: Andrew Pilloud <apill...@google.com> Authored: Wed Sep 19 10:57:55 2018 -0700 Committer: Julian Hyde <jh...@apache.org> Committed: Wed Sep 19 20:11:30 2018 -0700 ---------------------------------------------------------------------- .../apache/calcite/runtime/SqlFunctions.java | 42 +++++++++++++--- .../calcite/sql/test/SqlOperatorBaseTest.java | 52 ++++++++++++++++++++ 2 files changed, 86 insertions(+), 8 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/4f1b5eff/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 d795102..c7c3875 100644 --- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java +++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java @@ -108,23 +108,49 @@ public class SqlFunctions { } /** SQL SUBSTRING(string FROM ... FOR ...) function. */ - public static String substring(String s, int from, int for_) { - return s.substring(from - 1, Math.min(from - 1 + for_, s.length())); + public static String substring(String c, int s, int l) { + int lc = c.length(); + if (s < 0) { + s += lc + 1; + } + int e = s + l; + if (e < s) { + throw new IllegalArgumentException("substring error: negative substring length not allowed"); + } + if (s > lc || e < 1) { + return ""; + } + int s1 = Math.max(s, 1); + int e1 = Math.min(e, lc + 1); + return c.substring(s1 - 1, e1 - 1); } /** SQL SUBSTRING(string FROM ...) function. */ - public static String substring(String s, int from) { - return s.substring(from - 1); + public static String substring(String c, int s) { + return substring(c, s, c.length() + 1); } /** SQL SUBSTRING(binary FROM ... FOR ...) function. */ - public static ByteString substring(ByteString b, int from, int for_) { - return b.substring(from - 1, Math.min(from - 1 + for_, b.length())); + public static ByteString substring(ByteString c, int s, int l) { + int lc = c.length(); + if (s < 0) { + s += lc + 1; + } + int e = s + l; + if (e < s) { + throw new IllegalArgumentException("substring error: negative substring length not allowed"); + } + if (s > lc || e < 1) { + return ByteString.EMPTY; + } + int s1 = Math.max(s, 1); + int e1 = Math.min(e, lc + 1); + return c.substring(s1 - 1, e1 - 1); } /** SQL SUBSTRING(binary FROM ...) function. */ - public static ByteString substring(ByteString b, int from) { - return b.substring(from - 1); + public static ByteString substring(ByteString c, int s) { + return substring(c, s, c.length() + 1); } /** SQL UPPER(string) function. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/4f1b5eff/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 aac111c..c892f94 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 @@ -5209,14 +5209,66 @@ public abstract class SqlOperatorBaseTest { "ab", "VARCHAR(3) NOT NULL"); tester.checkString( + "substring('abc' from 2 for 8)", + "bc", + "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from 0 for 2)", + "a", + "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from 0 for 0)", + "", + "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from 8 for 2)", + "", + "VARCHAR(3) NOT NULL"); + tester.checkFails( + "substring('abc' from 1 for -1)", + "substring error: negative substring length not allowed", + true); + tester.checkString( "substring('abc' from 2)", "bc", "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from 0)", "abc", "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from 8)", "", "VARCHAR(3) NOT NULL"); + tester.checkString( + "substring('abc' from -2)", "bc", "VARCHAR(3) NOT NULL"); tester.checkString( "substring(x'aabbcc' from 1 for 2)", "aabb", "VARBINARY(3) NOT NULL"); tester.checkString( + "substring(x'aabbcc' from 2 for 8)", + "bbcc", + "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from 0 for 2)", + "aa", + "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from 0 for 0)", + "", + "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from 8 for 2)", + "", + "VARBINARY(3) NOT NULL"); + tester.checkFails( + "substring(x'aabbcc' from 1 for -1)", + "substring error: negative substring length not allowed", + true); + tester.checkString( "substring(x'aabbcc' from 2)", "bbcc", "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from 0)", "aabbcc", "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from 8)", "", "VARBINARY(3) NOT NULL"); + tester.checkString( + "substring(x'aabbcc' from -2)", "bbcc", "VARBINARY(3) NOT NULL"); if (Bug.FRG296_FIXED) { // substring regexp not supported yet
