Thomas D'Silva created PHOENIX-1684:
---------------------------------------
Summary: Functional Index using REGEXP_SUBSTR doesn't work
correctly
Key: PHOENIX-1684
URL: https://issues.apache.org/jira/browse/PHOENIX-1684
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.0.0, 5.0.0
Reporter: Thomas D'Silva
Assignee: Thomas D'Silva
If you create a function index using REGEXP_SUBSTR(column_name,'id:[0-9]+') ,
the index is not used correctly. This is probably because of the special
characters in the regex.
{code}
protected void helpTestFunctionWithArgs(boolean mutable, boolean localIndex)
throws Exception {
Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
Connection conn = DriverManager.getConnection(getUrl(), props);
try {
conn.createStatement().execute(
"CREATE TABLE t (k VARCHAR NOT NULL
PRIMARY KEY, v VARCHAR) "
+ (mutable ?
"IMMUTABLE_ROWS=true" : ""));
String query = "SELECT * FROM t";
ResultSet rs =
conn.createStatement().executeQuery(query);
assertFalse(rs.next());
String ddl = "CREATE " + (localIndex ? "LOCAL" : "")
+ " INDEX idx ON t
(REGEXP_SUBSTR(v,'id:[0-9]+'))";
PreparedStatement stmt = conn.prepareStatement(ddl);
stmt.execute();
query = "SELECT * FROM idx";
rs = conn.createStatement().executeQuery(query);
assertFalse(rs.next());
stmt = conn.prepareStatement("UPSERT INTO t
VALUES(?,?)");
stmt.setString(1, "k1");
stmt.setString(2, "v1");
stmt.execute();
stmt.setString(1, "k2");
stmt.setString(2, "v2");
stmt.execute();
conn.commit();
query = "SELECT k FROM t WHERE
REGEXP_SUBSTR(v,'id:[0-9]+') = '1'";
rs = conn.createStatement().executeQuery("EXPLAIN " +
query);
if (localIndex) {
assertEquals(
"CLIENT PARALLEL 1-WAY RANGE
SCAN OVER IDX ['1']\n"
+ " SERVER
FILTER BY FIRST KEY ONLY",
QueryUtil.getExplainPlan(rs));
} else {
assertEquals(
"CLIENT PARALLEL 1-WAY RANGE
SCAN OVER IDX ['1']\n"
+ " SERVER
FILTER BY FIRST KEY ONLY",
QueryUtil.getExplainPlan(rs));
}
rs = conn.createStatement().executeQuery(query);
assertTrue(rs.next());
assertEquals("k1", rs.getString(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
