[jira] [Created] (PHOENIX-1684) Functional Index using REGEXP_SUBSTR doesn't work correctly

["Thomas D'Silva (JIRA)" <ji...@apache.org>]

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)