PHOENIX-1984 Make INSTR 1-based instead of 0-based Bring functionality of INSTR built-in function in-line with other SQL string functions, with indexing of strings starting at 1.
Signed-off-by: Gabriel Reid <gabri...@ngdata.com> Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/c2fed1da Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/c2fed1da Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/c2fed1da Branch: refs/heads/calcite Commit: c2fed1dac8305f489939fc18e47cd2c2a6c596d8 Parents: d3ff079 Author: NAVEEN MADHIRE <vmadh...@indiana.edu> Authored: Mon May 18 22:14:57 2015 -0500 Committer: Gabriel Reid <gabri...@ngdata.com> Committed: Thu May 21 17:24:47 2015 +0200 ---------------------------------------------------------------------- .../apache/phoenix/end2end/InstrFunctionIT.java | 12 ++--- .../expression/function/InstrFunction.java | 2 +- .../expression/function/InstrFunctionTest.java | 48 ++++++++++---------- 3 files changed, 31 insertions(+), 31 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/c2fed1da/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java index 57c0661..b869ff4 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/InstrFunctionIT.java @@ -63,7 +63,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "ASC", "abcdefghijkl","fgh"); String queryToExecute = "SELECT INSTR(name, 'fgh') FROM SAMPLE"; - testInstr(conn, queryToExecute, 5); + testInstr(conn, queryToExecute, 6); } @Test @@ -71,7 +71,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "DESC", "abcdefghijkl","fgh"); String queryToExecute = "SELECT INSTR(name, 'fgh') FROM SAMPLE"; - testInstr(conn, queryToExecute, 5); + testInstr(conn, queryToExecute, 6); } @Test @@ -79,7 +79,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "ASC", "abcde fghijkl","lmn"); String queryToExecute = "SELECT INSTR(name, 'lmn') FROM SAMPLE"; - testInstr(conn, queryToExecute, -1); + testInstr(conn, queryToExecute, 0); } @Test @@ -87,7 +87,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "DESC", "abcde fghijkl","lmn"); String queryToExecute = "SELECT INSTR(name, 'lmn') FROM SAMPLE"; - testInstr(conn, queryToExecute, -1); + testInstr(conn, queryToExecute, 0); } @Test @@ -95,7 +95,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "ASC", "AÉɦFGH","Éɦ"); String queryToExecute = "SELECT INSTR(name, 'Éɦ') FROM SAMPLE"; - testInstr(conn, queryToExecute, 1); + testInstr(conn, queryToExecute, 2); } @Test @@ -103,7 +103,7 @@ public class InstrFunctionIT extends BaseHBaseManagedTimeIT { Connection conn = DriverManager.getConnection(getUrl()); initTable(conn, "DESC", "AÉɦFGH","Éɦ"); String queryToExecute = "SELECT INSTR(name, 'Éɦ') FROM SAMPLE"; - testInstr(conn, queryToExecute, 1); + testInstr(conn, queryToExecute, 2); } @Test http://git-wip-us.apache.org/repos/asf/phoenix/blob/c2fed1da/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java index 317d4b3..7a002f8 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/InstrFunction.java @@ -82,7 +82,7 @@ public class InstrFunction extends ScalarFunction{ String sourceStr = (String) PVarchar.INSTANCE.toObject(ptr, getChildren().get(0).getSortOrder()); - position = sourceStr.indexOf(strToSearch); + position = sourceStr.indexOf(strToSearch) + 1; ptr.set(PInteger.INSTANCE.toBytes(position)); return true; } http://git-wip-us.apache.org/repos/asf/phoenix/blob/c2fed1da/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java b/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java index 603ad39..359d772 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/expression/function/InstrFunctionTest.java @@ -49,59 +49,59 @@ public class InstrFunctionTest { @Test public void testInstrFunction() throws SQLException { - inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 5, SortOrder.ASC); + inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 6, SortOrder.ASC); - inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 5, SortOrder.DESC); + inputExpression("abcdefghijkl",PVarchar.INSTANCE, "fgh", 6, SortOrder.DESC); - inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 5, SortOrder.ASC); + inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 6, SortOrder.ASC); - inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 5, SortOrder.DESC); + inputExpression("abcde fghijkl",PVarchar.INSTANCE, " fgh", 6, SortOrder.DESC); - inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", -1, SortOrder.DESC); + inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", 0, SortOrder.DESC); - inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", -1, SortOrder.ASC); + inputExpression("abcde fghijkl",PVarchar.INSTANCE, "lmn", 0, SortOrder.ASC); - inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 5, SortOrder.ASC); + inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 6, SortOrder.ASC); - inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 5, SortOrder.DESC); + inputExpression("ABCDEFGHIJKL",PVarchar.INSTANCE, "FGH", 6, SortOrder.DESC); - inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 8, SortOrder.ASC); + inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 9, SortOrder.ASC); - inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 8, SortOrder.DESC); + inputExpression("ABCDEFGHiJKL",PVarchar.INSTANCE, "iJKL", 9, SortOrder.DESC); - inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 5, SortOrder.ASC); + inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 6, SortOrder.ASC); - inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 5, SortOrder.DESC); + inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, " ", 6, SortOrder.DESC); inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, "", 0, SortOrder.ASC); inputExpression("ABCDE FGHiJKL",PVarchar.INSTANCE, "", 0, SortOrder.DESC); - inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 0, SortOrder.ASC); + inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 1, SortOrder.ASC); - inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 0, SortOrder.DESC); + inputExpression("ABCDEABC",PVarchar.INSTANCE, "ABC", 1, SortOrder.DESC); - inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 2, SortOrder.ASC); + inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 3, SortOrder.ASC); - inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 2, SortOrder.DESC); + inputExpression("AB01CDEABC",PVarchar.INSTANCE, "01C", 3, SortOrder.DESC); - inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 4, SortOrder.ASC); + inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 5, SortOrder.ASC); - inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 4, SortOrder.DESC); + inputExpression("ABCD%EFGH",PVarchar.INSTANCE, "%", 5, SortOrder.DESC); //Tests for MultiByte Characters - inputExpression("AÉɦFGH",PVarchar.INSTANCE, "Éɦ", 1, SortOrder.ASC); + inputExpression("AÉɦFGH",PVarchar.INSTANCE, "Éɦ", 2, SortOrder.ASC); - inputExpression("AÉɦFGH",PVarchar.INSTANCE, "Éɦ", 1, SortOrder.DESC); + inputExpression("AÉɦFGH",PVarchar.INSTANCE, "Éɦ", 2, SortOrder.DESC); - inputExpression("AÉɦFGH",PVarchar.INSTANCE, "ɦFGH", 2, SortOrder.ASC); + inputExpression("AÉɦFGH",PVarchar.INSTANCE, "ɦFGH", 3, SortOrder.ASC); - inputExpression("AÉɦFGH",PVarchar.INSTANCE, "ɦFGH", 2, SortOrder.DESC); + inputExpression("AÉɦFGH",PVarchar.INSTANCE, "ɦFGH", 3, SortOrder.DESC); - inputExpression("AÉɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 2, SortOrder.ASC); + inputExpression("AÉɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 3, SortOrder.ASC); - inputExpression("AÉɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 2, SortOrder.DESC); + inputExpression("AÉɦF/GH",PVarchar.INSTANCE, "ɦF/GH", 3, SortOrder.DESC); }