You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "ranganathg (via GitHub)" <gi...@apache.org> on 2023/12/18 10:45:46 UTC
[PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
ranganathg opened a new pull request, #1767:
URL: https://github.com/apache/phoenix/pull/1767
[PHOENIX-7155](https://issues.apache.org/jira/browse/PHOENIX-7155)
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1432665955
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java:
##########
@@ -764,4 +768,174 @@ 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.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.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id4', 75, 2, 9.5, 'd','" + getJsonString(
+ partialIndexJson, "$[2]") + "')");
+
+ // 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
+ // 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);
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(75, rs.getInt(1));
+ assertFalse(rs.next());
+
+ // Retrieve the updated 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)"
+ + (salted ? " SALT_BUCKETS=4" : ""));
+ 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', 70, 2, 3.14, 'a','" + getJsonString(
Review Comment:
To improve the readability of the tests, we should include the json doc payload directly in the string for the upsert statement instead of retrieving from a file. The json doc should include only the content necessary for this test. For example, for this test the string for the doc could be ‘{"info": {"address ": {"exits" : true}}}’. If the same json string is used multiple times then we can define a string constant.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
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
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864129962
> We should also add JSON specific tests to WhereCompilerTest#testWhereInclusion().
Pushed new changes for this.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864535679
> It is not easy to read the test when the input is in a non-java file that cannot be accessed with a shortcut or link. If the input is large then this is acceptable but for a tiny input, it is inconvenient.
Took your comment and this made the code much simpler and easy to read. Thank you :)
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430321012
##########
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
Review Comment:
Retrieve the updated row ...
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430334103
##########
phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java:
##########
@@ -110,6 +111,8 @@ private String getValue(PDataType type) {
return "ARRAY[" + getValue(PDate.INSTANCE) + "]";
} else if (type instanceof PArrayDataType) {
return "ARRAY" + type.getSampleValue().toString();
+ } else if (type instanceof PJson) {
+ return null;
Review Comment:
Can we return a simple JSON doc instead of null here?
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430370140
##########
phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java:
##########
@@ -110,6 +111,8 @@ private String getValue(PDataType type) {
return "ARRAY[" + getValue(PDate.INSTANCE) + "]";
} else if (type instanceof PArrayDataType) {
return "ARRAY" + type.getSampleValue().toString();
+ } else if (type instanceof PJson) {
+ return null;
Review Comment:
There was a problem with that - initially i was returning '{"a":1}' but when I was running tests creating the index with the expression, I found that the jsonPath in the expression is applied on the sample json and that ran into issue. Didn't debug it further and rather choose to return null.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
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 connection
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864534572
> @ranganathg - If you keep the JSON, exceptions to the license check can be added here: https://github.com/apache/phoenix/blob/master/pom.xml#L636
This is nice. Thank you @gjacoby126
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864419068
> I would prefer to have json data in separate json files itself. If it's within a Java file, its not readable and IDE support to modify JSON with java static map wouldn't be clean. Does that make sense?
It is not easy to read the test when the input is in a non-java file that cannot be accessed with a shortcut or link. If the input is large then this is acceptable but for a tiny input, it is inconvenient.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1863737286
> Haven't had a chance to look at the patch, but wanted to point out that we have Apache License failures on this PR coming from the new JSON files.
Thanks @gjacoby126 . As per my understanding - The [JSON specification](https://www.json.org/json-en.html) does not include comments, and they cannot be used in JSON files.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde merged PR #1767:
URL: https://github.com/apache/phoenix/pull/1767
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1432709956
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java:
##########
@@ -764,4 +768,174 @@ 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.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.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id4', 75, 2, 9.5, 'd','" + getJsonString(
+ partialIndexJson, "$[2]") + "')");
+
+ // 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
+ // 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);
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(75, rs.getInt(1));
+ assertFalse(rs.next());
+
+ // Retrieve the updated 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)"
+ + (salted ? " SALT_BUCKETS=4" : ""));
+ 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', 70, 2, 3.14, 'a','" + getJsonString(
Review Comment:
Sure makes sense. Will make these changes
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430335984
##########
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();
+ 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");
Review Comment:
I assume the where clause will include JSON_EXISTS
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1430371350
##########
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();
+ 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");
Review Comment:
yes yes, i have changes locally which im debugging
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on code in PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#discussion_r1431326736
##########
phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java:
##########
@@ -110,6 +111,8 @@ private String getValue(PDataType type) {
return "ARRAY[" + getValue(PDate.INSTANCE) + "]";
} else if (type instanceof PArrayDataType) {
return "ARRAY" + type.getSampleValue().toString();
+ } else if (type instanceof PJson) {
+ return null;
Review Comment:
Done
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "kadirozde (via GitHub)" <gi...@apache.org>.
kadirozde commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1863992169
> > Haven't had a chance to look at the patch, but wanted to point out that we have Apache License failures on this PR coming from the new JSON files.
>
> Thanks @gjacoby126 . As per my understanding - The [JSON specification](https://www.json.org/json-en.html) does not include comments, and they cannot be used in JSON files.
We can eliminate the json file and use a static map (see BaseTest) or an array instead.
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "gjacoby126 (via GitHub)" <gi...@apache.org>.
gjacoby126 commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864513749
@ranganathg - If you keep the JSON, exceptions to the license check can be added here: https://github.com/apache/phoenix/blob/master/pom.xml#L636
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864002932
> We can eliminate the json file and use a static map (see BaseTest) or an array instead.
I would prefer to have json data in separate json files itself. If it's within a Java file, its not readable and not IDE support to modify JSON with java static map wouldn't be clean.
Does that make sense?
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [PR] PHOENIX-7155 Validate Partial Index support with JSON [phoenix]
Posted by "ranganathg (via GitHub)" <gi...@apache.org>.
ranganathg commented on PR #1767:
URL: https://github.com/apache/phoenix/pull/1767#issuecomment-1864695870
> @ranganathg - If you keep the JSON, exceptions to the license check can be added here: https://github.com/apache/phoenix/blob/master/pom.xml#L636
What is the right change here?
<exclude>phoenix-core/src/it/resources/*.json</exclude>
or
<exclude>**/*.json</exclude>
How do I check if this fixes the validation on my local machine before pushing changes?
--
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: issues-unsubscribe@phoenix.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org