[
https://issues.apache.org/jira/browse/PHOENIX-1684?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Samarth Jain updated PHOENIX-1684:
----------------------------------
Labels: 4.3.1 (was: )
> 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
> Labels: 4.3.1
>
> 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)
