[ https://issues.apache.org/jira/browse/PHOENIX-7155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17798248#comment-17798248 ]
ASF GitHub Bot commented on PHOENIX-7155: ----------------------------------------- kadirozde commented on code in PR #1767: URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430318335 ########## 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 Review Comment: Remove "using an atomic upsert" from the comment > 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)