[PHOENIX-1684] Functional Index using REGEXP_SUBSTR doesn't work correctly

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.0.0
• Labels: 4.3.1
• Assignee: Thomas D'Silva
• Reporter: Thomas D'Silva
• Created: 25/Feb/15 19:05
• Updated: 05/Aug/15 17:27
• Resolved: 27/Mar/15 19:03

Description

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.

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();
		}
	}