[
https://issues.apache.org/jira/browse/PHOENIX-7155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17798252#comment-17798252
]
ASF GitHub Bot commented on PHOENIX-7155:
-----------------------------------------
kadirozde commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430324025
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java:
##########
@@ -764,4 +768,180 @@ public void testPartialIndexPreferredOverFullIndex()
throws Exception {
assertFalse(rs.next());
}
}
+
+ @Test
+ public void testPartialIndexWithJson() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String dataTableName = generateUniqueName();
+ conn.createStatement().execute("create table " + dataTableName +
+ " (id varchar not null primary key, " +
+ "A integer, B integer, C double, D varchar, jsoncol
json)");
+ String indexTableName = generateUniqueName();
+ String partialIndexJson = "json/json_partialindex_tests.json";
+ // Add rows to the data table before creating a partial index to
test that the index
+ // will be built correctly by IndexTool
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id1', 25, 2,
3.14, 'a','" + getJsonString(
+ partialIndexJson, "$[0]") + "')");
+
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (id, A, D, jsoncol)
values ('id2', 100, 'b','" + getJsonString(
+ partialIndexJson, "$[3]") + "')");
+ conn.commit();
+ conn.createStatement().execute("CREATE " + (uncovered ? "UNCOVERED
" : " ") +
+ (local ? "LOCAL " : " ") +"INDEX "
+ + indexTableName + " on " + dataTableName + "
(CAST(TO_NUMBER(JSON_VALUE(jsoncol, '$.info.age')) AS INTEGER)) " +
+ (uncovered ? "" : "INCLUDE (B, C, D)") + " WHERE
(CAST(TO_NUMBER(JSON_VALUE(jsoncol, '$.info.age')) AS INTEGER)) > 50 ASYNC");
+
+ IndexToolIT.runIndexTool(false, null, dataTableName,
indexTableName);
+
+ String selectSql = "SELECT D from " + dataTableName + " WHERE
(CAST(TO_NUMBER(JSON_VALUE(jsoncol, '$.info.age')) AS INTEGER)) > 60";
+ ResultSet rs = conn.createStatement().executeQuery(selectSql);
+ // Verify that the index table is used
+ assertPlan((PhoenixResultSet) rs, "", indexTableName);
+ assertTrue(rs.next());
+ assertEquals("b", rs.getString(1));
+ assertFalse(rs.next());
+
+ selectSql = "SELECT D from " + dataTableName + " WHERE
(CAST(TO_NUMBER(JSON_VALUE(jsoncol, '$.info.age')) AS INTEGER)) = 50";
+ rs = conn.createStatement().executeQuery(selectSql);
+ // Verify that the index table is not used
+ assertPlan((PhoenixResultSet) rs, "", dataTableName);
+
+ // Add more rows to test the index write path
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id3', 50, 2,
9.5, 'c','" + getJsonString(
+ partialIndexJson, "$[1]") + "')");
+ conn.commit();
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id4', 75, 2,
9.5, 'd','" + getJsonString(
+ partialIndexJson, "$[2]") + "')");
+ conn.commit();
+
+ // Verify that index table includes only the rows with A > 50
+ selectSql = "SELECT * from " + indexTableName;
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(75, rs.getInt(1));
+ assertTrue(rs.next());
+ assertEquals(100, rs.getInt(1));
+ assertFalse(rs.next());
+
+ // Overwrite an existing row that satisfies the index WHERE clause
using an atomic
+ // upsert such that the new version of the row does not satisfy
the index where clause
+ // anymore. This should result in deleting the index row.
+ String dml = "UPSERT INTO " + dataTableName + " values ('id2', 0,
2, 9.5, 'd', JSON_MODIFY(jsoncol, '$.info.age', '0')) "; //,
JSON_MODIFY(jsoncol, '$.info.age', '"0"')
+ conn.createStatement().execute(dml);
+ conn.commit();
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(75, rs.getInt(1));
+ assertFalse(rs.next());
+
+ // Retrieve update row from the data table and verify that the
index table is not used
+ selectSql = "SELECT ID from " + dataTableName + " WHERE
(CAST(TO_NUMBER(JSON_VALUE(jsoncol, '$.info.age')) AS INTEGER)) = 0";
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertPlan((PhoenixResultSet) rs, "", dataTableName);
+ assertTrue(rs.next());
+ assertEquals("id2", rs.getString(1));
+
+ // Test index verification and repair by IndexTool
+ verifyIndex(dataTableName, indexTableName);
+
+ try (Connection newConn = DriverManager.getConnection(getUrl())) {
+ PTable indexTable = PhoenixRuntime.getTableNoCache(newConn,
indexTableName);
+
assertTrue(StringUtils.deleteWhitespace(indexTable.getIndexWhere()).equals("CAST(TO_NUMBER(JSON_VALUE(JSONCOL,'$.info.age'))ASINTEGER)>50"));
+ }
+ }
+ }
+
+ @Test
+ public void testPartialIndexWithJsonExists() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String dataTableName = generateUniqueName();
+ conn.createStatement().execute("create table " + dataTableName +
+ " (id varchar not null primary key, " +
+ "A integer, B integer, C double, D varchar, jsoncol
json)");
+ String indexTableName = generateUniqueName();
+ String partialIndexJson = "json/json_partialindex_tests.json";
+ // Add rows to the data table before creating a partial index to
test that the index
+ // will be built correctly by IndexTool
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id1', 25, 2,
3.14, 'a','" + getJsonString(
+ partialIndexJson, "$[0]") + "')");
+
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (id, A, D, jsoncol)
values ('id2', 100, 'b','" + getJsonString(
+ partialIndexJson, "$[3]") + "')");
+ conn.commit();
Review Comment:
Commit is not necessary on a auto-commit connnection
> Validate Partial Index support with JSON
> ----------------------------------------
>
> Key: PHOENIX-7155
> URL: https://issues.apache.org/jira/browse/PHOENIX-7155
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: Ranganath Govardhanagiri
> Assignee: Ranganath Govardhanagiri
> Priority: Major
>
> Phoenix has recently added support for Partial Indexes - PHOENIX-7032. This
> task is to validate if Indexes built on top of JSON documents support Partial
> Indexes.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
