[
https://issues.apache.org/jira/browse/PHOENIX-7044?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17777087#comment-17777087
]
ASF GitHub Bot commented on PHOENIX-7044:
-----------------------------------------
ranganathg commented on code in PR #1708:
URL: https://github.com/apache/phoenix/pull/1708#discussion_r1365039969
##########
phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java:
##########
@@ -633,4 +637,34 @@ public void testJsonQuery() throws Exception {
compareJson(rs.getString(5), JsonDatatypes,
"$.datatypes.mixedArray");
}
}
+
+ @Test
+ public void testExpressionIndex() throws Exception {
+ Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
+ String tableName = generateUniqueName();
+ String indexName = "IDX_" + generateUniqueName();
+ try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
+ conn.setAutoCommit(true);
+ String
+ ddl =
+ "create table if not exists " + tableName +
+ " (pk integer primary key, col integer,
jsoncol.jsoncol json)";
+ conn.createStatement().execute(ddl);
+ conn.createStatement().execute(
+ "UPSERT INTO " + tableName + " (pk, col, jsoncol) VALUES
(1,2, '" + JsonDoc1 + "')");
+ conn.createStatement().execute(
+ "CREATE INDEX " + indexName + " ON " + tableName
+ + " (JSON_VALUE(JSONCOL,'$.type'),
JSON_VALUE(JSONCOL,'$.info.address.town')) include (col)");
+ String
+ selectSql =
+ "SELECT JSON_VALUE(JSONCOL,'$.type'), " +
+ "JSON_VALUE(JSONCOL,'$.info.address.town') FROM " +
tableName +
+ " WHERE JSON_VALUE(JSONCOL,'$.type') = 'Basic'";
+ ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " +
selectSql);
+ String actualExplainPlan = QueryUtil.getExplainPlan(rs);
+ IndexToolIT.assertExplainPlan(false, actualExplainPlan, tableName,
indexName);
+ } catch (SQLException e) {
+ assertEquals(SQLExceptionCode.DIVIDE_BY_ZERO.getErrorCode(),
e.getErrorCode());
Review Comment:
Fixed this.
> Support Index on a generated column that extracts a scalar value from the
> JSON column
> -------------------------------------------------------------------------------------
>
> Key: PHOENIX-7044
> URL: https://issues.apache.org/jira/browse/PHOENIX-7044
> Project: Phoenix
> Issue Type: Sub-task
> Components: phoenix
> Reporter: Ranganath Govardhanagiri
> Assignee: Ranganath Govardhanagiri
> Priority: Major
>
> As per the Design doc attached we can implement the below proposal:
> Using index expressions: Phoenix lets index’s PK to be defined as certain
> expressions. Aggregate (sum()), stateless(a constant value) or
> non-deterministic(rand()) is not allowed. But expressions like (mycol/2) is
> allowed. The Json function could be used as expression with some code
> changes.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
