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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
