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

["Samarth Jain (JIRA)" <ji...@apache.org>]

     [ 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)