You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@phoenix.apache.org by ka...@apache.org on 2023/12/21 09:36:35 UTC
(phoenix) branch PHOENIX-628-feature updated: PHOENIX-7155 Validate Partial Index support with JSON (#1767)
This is an automated email from the ASF dual-hosted git repository.
kadir pushed a commit to branch PHOENIX-628-feature
in repository https://gitbox.apache.org/repos/asf/phoenix.git
The following commit(s) were added to refs/heads/PHOENIX-628-feature by this push:
new ead8f05f61 PHOENIX-7155 Validate Partial Index support with JSON (#1767)
ead8f05f61 is described below
commit ead8f05f61b94e54fcd9e4daeaa82f4955b65acd
Author: RanganathG <ra...@gmail.com>
AuthorDate: Thu Dec 21 15:06:29 2023 +0530
PHOENIX-7155 Validate Partial Index support with JSON (#1767)
---
phoenix-core/pom.xml | 1 +
.../phoenix/end2end/index/PartialIndexIT.java | 219 ++++++++++--
.../phoenix/end2end/json/JsonFunctionsIT.java | 37 +-
.../it/resources/json/json_functions_tests.json | 371 ---------------------
.../phoenix/compile/CreateIndexCompiler.java | 3 +
.../expression/function/JsonExistsFunction.java | 6 +-
.../expression/function/JsonModifyFunction.java | 6 +-
.../expression/function/JsonQueryFunction.java | 6 +-
.../expression/function/JsonValueFunction.java | 8 +-
.../apache/phoenix/util/json/BsonJsonProvider.java | 18 +-
.../apache/phoenix/compile/WhereCompilerTest.java | 22 +-
11 files changed, 243 insertions(+), 454 deletions(-)
diff --git a/phoenix-core/pom.xml b/phoenix-core/pom.xml
index e77cbcee14..8a8eab88f5 100644
--- a/phoenix-core/pom.xml
+++ b/phoenix-core/pom.xml
@@ -223,6 +223,7 @@
<excludes>
<exclude>src/main/java/org/apache/phoenix/coprocessor/generated/*.java</exclude>
<exclude>src/main/resources/META-INF/services/java.sql.Driver</exclude>
+ <exclude>src/it/resources/json/*.json</exclude>
</excludes>
</configuration>
</plugin>
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
index 0f16113239..a187afafda 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/index/PartialIndexIT.java
@@ -17,36 +17,22 @@
*/
package org.apache.phoenix.end2end.index;
-import static org.apache.phoenix.mapreduce.index.PhoenixIndexToolJobCounters.*;
-import static org.junit.Assert.assertEquals;
-import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertTrue;
-
-import java.sql.Connection;
-import java.sql.Date;
-import java.sql.DriverManager;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Timestamp;
-import java.util.Arrays;
-import java.util.Calendar;
-import java.util.Collection;
-import java.util.Map;
-import java.util.Properties;
-
+import org.apache.commons.lang3.StringUtils;
import org.apache.hadoop.mapreduce.CounterGroup;
import org.apache.phoenix.end2end.IndexToolIT;
+import org.apache.phoenix.end2end.NeedsOwnMiniClusterTest;
import org.apache.phoenix.exception.PhoenixParserException;
import org.apache.phoenix.jdbc.PhoenixResultSet;
import org.apache.phoenix.mapreduce.index.IndexTool;
+import org.apache.phoenix.query.BaseTest;
+import org.apache.phoenix.query.QueryServices;
import org.apache.phoenix.schema.ColumnNotFoundException;
import org.apache.phoenix.schema.PTable;
import org.apache.phoenix.thirdparty.com.google.common.collect.Maps;
-import org.apache.phoenix.end2end.NeedsOwnMiniClusterTest;
-import org.apache.phoenix.query.BaseTest;
-import org.apache.phoenix.query.QueryServices;
-import org.apache.phoenix.util.*;
+import org.apache.phoenix.util.EnvironmentEdgeManager;
+import org.apache.phoenix.util.PhoenixRuntime;
+import org.apache.phoenix.util.PropertiesUtil;
+import org.apache.phoenix.util.ReadOnlyProps;
import org.junit.After;
import org.junit.Assert;
import org.junit.BeforeClass;
@@ -55,6 +41,23 @@ import org.junit.experimental.categories.Category;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
+import java.sql.Connection;
+import java.sql.Date;
+import java.sql.DriverManager;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.util.Arrays;
+import java.util.Calendar;
+import java.util.Collection;
+import java.util.Map;
+import java.util.Properties;
+
+import static org.apache.phoenix.mapreduce.index.PhoenixIndexToolJobCounters.*;
+import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
+import static org.junit.Assert.*;
+
@Category(NeedsOwnMiniClusterTest.class)
@RunWith(Parameterized.class)
public class PartialIndexIT extends BaseTest {
@@ -182,7 +185,7 @@ public class PartialIndexIT extends BaseTest {
+ "S UNSIGNED_DATE, T UNSIGNED_TIMESTAMP, U CHAR(10), V BINARY(1024), "
+ "W VARBINARY, Y INTEGER ARRAY, Z VARCHAR ARRAY[10], AA DATE ARRAY, "
+ "AB TIMESTAMP ARRAY, AC UNSIGNED_TIME ARRAY, AD UNSIGNED_DATE ARRAY, "
- + "AE UNSIGNED_TIMESTAMP ARRAY "
+ + "AE UNSIGNED_TIMESTAMP ARRAY, AF JSON "
+ "CONSTRAINT pk PRIMARY KEY (id,kp)) "
+ "MULTI_TENANT=true, COLUMN_ENCODED_BYTES=0" );
String indexTableName = generateUniqueName();
@@ -764,4 +767,174 @@ public class PartialIndexIT extends BaseTest {
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 json = "{\"info\":{\"age\": %s }}";
+ // 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','" +
+ String.format(json, 25) + "')");
+
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (id, A, D, jsoncol)" +
+ " values ('id2', 100, 'b','" + String.format(json, 100) + "')");
+ 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','" + String.format(
+ json, 50) + "')");
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id4', 75, 2, 9.5, 'd','" + String.format(
+ json, 75) + "')");
+
+ // 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')) ";
+ 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 jsonWithPathExists = "{\"info\":{\"address\":{\"exists\":true}}}";
+ String jsonWithoutPathExists = "{\"info\":{\"age\": 25 }}";
+ // 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','" + jsonWithPathExists + "')");
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (id, A, D, jsoncol) values ('id2', 100, 'b','" + jsonWithoutPathExists + "')");
+ conn.createStatement().execute("CREATE " + (uncovered ? "UNCOVERED " : " ") +
+ (local ? "LOCAL " : " ") + "INDEX " + indexTableName + " on " + dataTableName + " (A) " +
+ (uncovered ? "" : "INCLUDE (B, C, D)") + " WHERE JSON_EXISTS(JSONCOL, '$.info.address.exists') ASYNC");
+ IndexToolIT.runIndexTool(false, null, dataTableName, indexTableName);
+
+ String selectSql =
+ "SELECT " + (uncovered ? " " : "/*+ INDEX(" + dataTableName + " " + indexTableName + ")*/ ") +
+ " A, D from " + dataTableName + " WHERE A > 60 AND JSON_EXISTS(jsoncol, '$.info.address.exists')";
+ ResultSet rs = conn.createStatement().executeQuery(selectSql);
+ // Verify that the index table is used
+ assertPlan((PhoenixResultSet) rs, "", indexTableName);
+ assertTrue(rs.next());
+ assertEquals(70, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertFalse(rs.next());
+
+ // Add more rows to test the index write path
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id3', 20, 2, 3.14, 'a','" + jsonWithPathExists + "')");
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " values ('id4', 90, 2, 3.14, 'a','" + jsonWithPathExists + "')");
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (id, A, D, jsoncol) values ('id5', 150, 'b','" + jsonWithoutPathExists + "')");
+
+ // Verify that index table includes only the rows where jsonPath Exists
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(70, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertTrue(rs.next());
+ assertEquals(90, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertFalse(rs.next());
+
+ rs = conn.createStatement().executeQuery("SELECT Count(*) from " + dataTableName);
+ // Verify that the index table is not used
+ assertPlan((PhoenixResultSet) rs, "", dataTableName);
+ assertTrue(rs.next());
+ assertEquals(5, rs.getInt(1));
+
+ // 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.
+ conn.createStatement().execute(
+ "upsert into " + dataTableName + " (ID, B, jsoncol) values ('id4', null, '" + jsonWithoutPathExists + "')");
+ rs = conn.createStatement().executeQuery(selectSql);
+ assertTrue(rs.next());
+ assertEquals(70, rs.getInt(1));
+ assertEquals("a", rs.getString(2));
+ assertFalse(rs.next());
+
+ // 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("JSON_EXISTS(JSONCOL,'$.info.address.exists')"));
+ }
+ }
+ }
}
diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
index 7dd083ea97..957d619999 100644
--- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
+++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/json/JsonFunctionsIT.java
@@ -57,17 +57,14 @@ import static org.junit.Assert.fail;
@Category(ParallelStatsDisabledTest.class)
public class JsonFunctionsIT extends ParallelStatsDisabledIT {
public static String BASIC_JSON = "json/json_functions_basic.json";
- public static String FUNCTIONS_TEST_JSON = "json/json_functions_tests.json";
public static String DATA_TYPES_JSON = "json/json_datatypes.json";
String basicJson = "";
String dataTypesJson = "";
- String functionsJson = "";
@Before
public void setup() throws IOException {
basicJson = getJsonString(BASIC_JSON, "$[0]");
dataTypesJson = getJsonString(DATA_TYPES_JSON);
- functionsJson = getJsonString(FUNCTIONS_TEST_JSON);
}
@Test
@@ -158,39 +155,6 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
}
}
- @Test
- public void testSimpleJsonValue2() throws Exception {
- Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
- String tableName = 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 json)";
- conn.createStatement().execute(ddl);
- PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?,?,?)");
- stmt.setInt(1, 1);
- stmt.setInt(2, 2);
- stmt.setString(3, functionsJson);
- stmt.execute();
- conn.commit();
- ResultSet rs = conn.createStatement().executeQuery("SELECT JSON_VALUE(JSONCOL,'$.test'), " +
- "JSON_VALUE(JSONCOL, '$.testCnt'), " +
- "JSON_VALUE(JSONCOL, '$.infoTop[5].info.address.state')," +
- "JSON_VALUE(JSONCOL, '$.infoTop[4].tags[1]'), " +
- "JSON_QUERY(JSONCOL, '$.infoTop'), " +
- "JSON_QUERY(JSONCOL, '$.infoTop[5].info'), " +
- "JSON_QUERY(JSONCOL, '$.infoTop[5].friends') " +
- "FROM " + tableName + " WHERE JSON_VALUE(JSONCOL, '$.test')='test1'");
- assertTrue(rs.next());
- assertEquals("test1", rs.getString(1));
- assertEquals("SomeCnt1", rs.getString(2));
- assertEquals("North Dakota", rs.getString(3));
- assertEquals("sint", rs.getString(4));
- compareJson(rs.getString(5), functionsJson, "$.infoTop");
- compareJson(rs.getString(6), functionsJson, "$.infoTop[5].info");
- compareJson(rs.getString(7), functionsJson, "$.infoTop[5].friends");
- }
- }
-
private void compareJson(String result, String json, String path) throws JsonProcessingException {
Configuration conf = Configuration.builder().jsonProvider(new GsonJsonProvider()).build();
Object read = JsonPath.using(conf).parse(json).read(path);
@@ -445,6 +409,7 @@ public class JsonFunctionsIT extends ParallelStatsDisabledIT {
private static String getJsonString(String jsonFilePath) throws IOException {
return getJsonString(jsonFilePath, "$");
}
+
private static String getJsonString(String jsonFilePath, String jsonPath) throws IOException {
URL fileUrl = JsonFunctionsIT.class.getClassLoader().getResource(jsonFilePath);
String json = FileUtils.readFileToString(new File(fileUrl.getFile()));
diff --git a/phoenix-core/src/it/resources/json/json_functions_tests.json b/phoenix-core/src/it/resources/json/json_functions_tests.json
deleted file mode 100644
index 27df7d42c7..0000000000
--- a/phoenix-core/src/it/resources/json/json_functions_tests.json
+++ /dev/null
@@ -1,371 +0,0 @@
-{
- "testCnt": "SomeCnt1",
- "test": "test1",
- "batchNo": 1,
- "infoTop": [
- {
- "_id": "618d982e407a8dbd65781450",
- "index": 0,
- "guid": "4f5a46f2-7271-492a-8347-a8223516715f",
- "isActive": true,
- "balance": "$3,746.11",
- "picture": "http://placehold.it/32x32",
- "age": 20,
- "eyeColor": "green",
- "name": "Castaneda Golden",
- "gender": "male",
- "company": "AUSTEX",
- "email": "castanedagolden@austex.com",
- "phone": "+1 (979) 486-3061",
- "info": {
- "address": {
- "street": "function",
- "town": "Urbana",
- "state": "Delaware"
- }
- },
- "address": "322 Hancock Street, Nicut, Georgia, 5007",
- "about": "Esse anim minim nostrud aliquip. Quis anim ex dolore magna exercitation deserunt minim ad do est non. Magna fugiat eiusmod incididunt cupidatat. Anim occaecat nulla cillum culpa sunt amet.\\r\\n",
- "registered": "2015-11-06T01:32:28 +08:00",
- "latitude": 83.51654,
- "longitude": -93.749216,
- "tags": [
- "incididunt",
- "nostrud",
- "incididunt",
- "Lorem",
- "mollit",
- "tempor",
- "incididunt"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Cortez Bowman"
- },
- {
- "id": 1,
- "name": "Larsen Wolf"
- },
- {
- "id": 2,
- "name": "Colon Rivers"
- }
- ],
- "greeting": "Hello, Castaneda Golden! You have 10 unread messages.",
- "favoriteFruit": "banana"
- },
- {
- "_id": "618d982ef091f4785f15251f",
- "index": 1,
- "guid": "bcfc487d-de23-4721-86bd-809d37a007c2",
- "isActive": false,
- "balance": "$1,539.97",
- "picture": "http://placehold.it/32x32",
- "age": 31,
- "eyeColor": "brown",
- "name": "Jackson Dillard",
- "gender": "male",
- "company": "QUONATA",
- "email": "jacksondillard@quonata.com",
- "phone": "+1 (950) 552-3553",
- "info": {
- "address": {
- "street": "function",
- "town": "Cetronia",
- "state": "Massachusetts"
- }
- },
- "address": "848 Hampton Avenue, Shasta, Marshall Islands, 6596",
- "about": "Mollit nisi cillum sunt aliquip. Est ex nisi deserunt aliqua anim nisi dolor. Ullamco est consectetur deserunt do voluptate excepteur esse reprehenderit laboris officia. Deserunt sint velit mollit aliquip amet ad in tempor excepteur magna proident Lorem reprehenderit consequat.\\r\\n",
- "registered": "2018-05-13T10:54:03 +07:00",
- "latitude": -68.213281,
- "longitude": -147.388909,
- "tags": [
- "adipisicing",
- "Lorem",
- "sit",
- "voluptate",
- "cupidatat",
- "deserunt",
- "consectetur"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Casandra Best"
- },
- {
- "id": 1,
- "name": "Lauri Santiago"
- },
- {
- "id": 2,
- "name": "Maricela Foster"
- }
- ],
- "greeting": "Hello, Jackson Dillard! You have 4 unread messages.",
- "favoriteFruit": "strawberry"
- },
- {
- "_id": "618d982eecb0f6158d7415b7",
- "index": 2,
- "guid": "09b31b54-6341-4a7e-8e58-bec0f766d5f4",
- "isActive": true,
- "balance": "$1,357.52",
- "picture": "http://placehold.it/32x32",
- "age": 20,
- "eyeColor": "brown",
- "name": "Battle Washington",
- "gender": "male",
- "company": "ONTALITY",
- "email": "battlewashington@ontality.com",
- "phone": "+1 (934) 429-3950",
- "info": {
- "address": {
- "street": "function",
- "town": "Windsor",
- "state": "Virginia"
- }
- },
- "address": "299 Campus Place, Innsbrook, Nevada, 4795",
- "about": "Consequat voluptate nisi duis nostrud anim cupidatat officia dolore non velit Lorem. Pariatur sit consectetur do reprehenderit irure Lorem consectetur ad nostrud. Dolore tempor est fugiat officia ad nostrud. Cupidatat quis aute consectetur Lorem. Irure qui tempor deserunt nisi quis quis culpa veniam cillum est. Aute consequat pariatur ut minim sunt.\\r\\n",
- "registered": "2018-12-07T03:42:53 +08:00",
- "latitude": -6.967753,
- "longitude": 64.796997,
- "tags": [
- "in",
- "do",
- "labore",
- "laboris",
- "dolore",
- "est",
- "nisi"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Faye Decker"
- },
- {
- "id": 1,
- "name": "Judy Skinner"
- },
- {
- "id": 2,
- "name": "Angie Faulkner"
- }
- ],
- "greeting": "Hello, Battle Washington! You have 2 unread messages.",
- "favoriteFruit": "banana"
- },
- {
- "_id": "618d982e1298ef388f75cda0",
- "index": 3,
- "guid": "deebe756-c9cd-43f5-9dd6-bc8d2edeab01",
- "isActive": false,
- "balance": "$3,684.61",
- "picture": "http://placehold.it/32x32",
- "age": 27,
- "eyeColor": "brown",
- "name": "Watkins Aguirre",
- "gender": "male",
- "company": "WAAB",
- "email": "watkinsaguirre@waab.com",
- "phone": "+1 (861) 526-2440",
- "info": {
- "address": {
- "street": "function",
- "town": "Healy",
- "state": "Nebraska"
- }
- },
- "address": "245 Bouck Court, Malo, Minnesota, 8990",
- "about": "Elit fugiat aliquip occaecat nostrud deserunt eu in ut et officia pariatur ipsum non. Dolor exercitation irure cupidatat velit eiusmod voluptate esse enim. Minim aliquip do ut esse irure commodo duis aliquip deserunt ea enim incididunt. Consequat Lorem id duis occaecat proident mollit ad officia fugiat. Nostrud irure deserunt commodo consectetur cillum. Quis qui eiusmod ullamco exercitation amet do occaecat sint laboris ut laboris amet. Elit consequat fugiat cupidatat eni [...]
- "registered": "2021-05-27T03:15:12 +07:00",
- "latitude": 86.552038,
- "longitude": 175.688809,
- "tags": [
- "nostrud",
- "et",
- "ullamco",
- "aliqua",
- "minim",
- "tempor",
- "proident"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Dionne Lindsey"
- },
- {
- "id": 1,
- "name": "Bonner Logan"
- },
- {
- "id": 2,
- "name": "Neal Case"
- }
- ],
- "greeting": "Hello, Watkins Aguirre! You have 5 unread messages.",
- "favoriteFruit": "strawberry"
- },
- {
- "_id": "618d982e3cb0317d825dfbb5",
- "index": 4,
- "guid": "ac778765-da9a-4923-915b-1b967e1bee96",
- "isActive": true,
- "balance": "$2,787.54",
- "picture": "http://placehold.it/32x32",
- "age": 34,
- "eyeColor": "green",
- "name": "Barbra Fry",
- "gender": "female",
- "company": "SPACEWAX",
- "email": "barbrafry@spacewax.com",
- "phone": "+1 (895) 538-2479",
- "info": {
- "address": {
- "street": "function",
- "town": "Movico",
- "state": "Pennsylvania"
- }
- },
- "address": "812 Losee Terrace, Elbert, South Dakota, 9870",
- "about": "Ea Lorem nisi aliqua incididunt deserunt sint. Cillum do magna sint quis enim velit cupidatat deserunt pariatur esse labore. Laborum velit nostrud in occaecat amet commodo enim ex commodo. Culpa do est sit reprehenderit nulla duis ex irure reprehenderit velit aliquip. Irure et eiusmod ad minim laborum ut fugiat dolore in anim mollit aliquip aliqua sunt. Commodo Lorem anim magna eiusmod.\\r\\n",
- "registered": "2020-05-05T05:27:59 +07:00",
- "latitude": -55.592888,
- "longitude": 68.056625,
- "tags": [
- "magna",
- "sint",
- "minim",
- "dolore",
- "ad",
- "exercitation",
- "laborum"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Mccullough Roman"
- },
- {
- "id": 1,
- "name": "Lang Morales"
- },
- {
- "id": 2,
- "name": "Luann Carrillo"
- }
- ],
- "greeting": "Hello, Barbra Fry! You have 6 unread messages.",
- "favoriteFruit": "banana"
- },
- {
- "_id": "618d982e44e4e11611e5f62a",
- "index": 5,
- "guid": "d02e17de-fed9-4839-8d75-e8d05fe68c94",
- "isActive": true,
- "balance": "$1,023.39",
- "picture": "http://placehold.it/32x32",
- "age": 38,
- "eyeColor": "green",
- "name": "Byers Grant",
- "gender": "male",
- "company": "ZAGGLES",
- "email": "byersgrant@zaggles.com",
- "phone": "+1 (992) 570-3190",
- "info": {
- "address": {
- "street": "function",
- "town": "Chamberino",
- "state": "North Dakota"
- }
- },
- "address": "826 Cumberland Street, Shaft, Washington, 424",
- "about": "Deserunt tempor sint culpa in ex occaecat quis exercitation voluptate mollit occaecat officia. Aute aliquip officia id cupidatat non consectetur nulla mollit laborum ex mollit culpa exercitation. Aute nisi ullamco adipisicing sit proident proident duis. Exercitation ex id id enim cupidatat pariatur amet reprehenderit fugiat ea.\\r\\n",
- "registered": "2017-10-12T04:55:42 +07:00",
- "latitude": -26.03892,
- "longitude": -35.959528,
- "tags": [
- "et",
- "adipisicing",
- "excepteur",
- "do",
- "ad",
- "exercitation",
- "commodo"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Louise Clarke"
- },
- {
- "id": 1,
- "name": "Pratt Velazquez"
- },
- {
- "id": 2,
- "name": "Violet Reyes"
- }
- ],
- "greeting": "Hello, Byers Grant! You have 8 unread messages.",
- "favoriteFruit": "banana"
- },
- {
- "_id": "618d982ef6ed0ffe65e0f414",
- "index": 6,
- "guid": "37f92715-a4d1-476e-98d9-b4901426c5ea",
- "isActive": true,
- "balance": "$2,191.12",
- "picture": "http://placehold.it/32x32",
- "age": 33,
- "eyeColor": "brown",
- "name": "Rasmussen Todd",
- "gender": "male",
- "company": "ROUGHIES",
- "email": "rasmussentodd@roughies.com",
- "phone": "+1 (893) 420-3792",
- "info": {
- "address": {
- "street": "function",
- "town": "Floriston",
- "state": "Indiana"
- }
- },
- "address": "295 McClancy Place, Berlin, Federated States Of Micronesia, 303",
- "about": "Est cillum fugiat reprehenderit minim minim esse qui. Eiusmod quis pariatur adipisicing sunt ipsum duis dolor veniam. Aliqua ex cupidatat officia exercitation sint duis exercitation ut. Cillum magna laboris id Lorem mollit consequat ex anim voluptate Lorem enim et velit nulla. Non consectetur incididunt id et ad tempor amet elit tempor aliquip velit incididunt esse adipisicing. Culpa pariatur est occaecat voluptate. Voluptate pariatur pariatur esse cillum proident eiusmod [...]
- "registered": "2015-10-10T12:39:42 +07:00",
- "latitude": -20.559815,
- "longitude": 28.453852,
- "tags": [
- "reprehenderit",
- "velit",
- "non",
- "non",
- "veniam",
- "laborum",
- "duis"
- ],
- "friends": [
- {
- "id": 0,
- "name": "Stark Carney"
- },
- {
- "id": 1,
- "name": "Price Roberts"
- },
- {
- "id": 2,
- "name": "Lillian Henry"
- }
- ],
- "greeting": "Hello, Rasmussen Todd! You have 3 unread messages.",
- "favoriteFruit": "banana"
- }
- ]
-}
\ No newline at end of file
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java
index 4bd3a4bafe..bc75d1efde 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/CreateIndexCompiler.java
@@ -47,6 +47,7 @@ import org.apache.phoenix.schema.types.PChar;
import org.apache.phoenix.schema.types.PDataType;
import org.apache.phoenix.schema.types.PDate;
import org.apache.phoenix.schema.types.PDateArray;
+import org.apache.phoenix.schema.types.PJson;
import org.apache.phoenix.schema.types.PNumericType;
import org.apache.phoenix.schema.types.PTime;
import org.apache.phoenix.schema.types.PTimeArray;
@@ -110,6 +111,8 @@ public class CreateIndexCompiler {
return "ARRAY[" + getValue(PDate.INSTANCE) + "]";
} else if (type instanceof PArrayDataType) {
return "ARRAY" + type.getSampleValue().toString();
+ } else if (type instanceof PJson) {
+ return "'{a:1}'";
} else {
return "0123";
}
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonExistsFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonExistsFunction.java
index 997942c065..7a19181fcd 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonExistsFunction.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonExistsFunction.java
@@ -65,7 +65,7 @@ public class JsonExistsFunction extends ScalarFunction {
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -76,14 +76,14 @@ public class JsonExistsFunction extends ScalarFunction {
}
if (ptr.getLength() == 0) {
- return true;
+ return false;
}
String
jsonPathExprStr =
(String) PVarchar.INSTANCE.toObject(ptr, getJSONPathExpr().getSortOrder());
if (jsonPathExprStr == null) {
- return true;
+ return false;
}
boolean isPathValid = jsonDataFormat.isPathValid(top, jsonPathExprStr);
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
index a968d0b347..fcce9e1e0e 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonModifyFunction.java
@@ -70,7 +70,7 @@ public class JsonModifyFunction extends ScalarFunction {
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -81,14 +81,14 @@ public class JsonModifyFunction extends ScalarFunction {
}
if (ptr.getLength() == 0) {
- return true;
+ return false;
}
String
jsonPathExprStr =
(String) PVarchar.INSTANCE.toObject(ptr, getJSONPathExpr().getSortOrder());
if (jsonPathExprStr == null) {
- return true;
+ return false;
}
if (!getNewValueExpr().evaluate(tuple, ptr)) {
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonQueryFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonQueryFunction.java
index 9863439109..a2a3d00e42 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonQueryFunction.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonQueryFunction.java
@@ -69,7 +69,7 @@ public class JsonQueryFunction extends ScalarFunction {
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -80,14 +80,14 @@ public class JsonQueryFunction extends ScalarFunction {
}
if (ptr.getLength() == 0) {
- return true;
+ return false;
}
String
jsonPathExprStr =
(String) PVarchar.INSTANCE.toObject(ptr, getJSONPathExpr().getSortOrder());
if (jsonPathExprStr == null) {
- return true;
+ return false;
}
Object value = jsonDataFormat.getValue(top, jsonPathExprStr);
int valueType = jsonDataFormat.getValueType(top, jsonPathExprStr);
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonValueFunction.java b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonValueFunction.java
index af20ae47c3..a42b0cfc95 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonValueFunction.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/expression/function/JsonValueFunction.java
@@ -71,7 +71,7 @@ public class JsonValueFunction extends ScalarFunction {
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -82,14 +82,14 @@ public class JsonValueFunction extends ScalarFunction {
}
if (ptr.getLength() == 0) {
- return true;
+ return false;
}
String
jsonPathExprStr =
(String) PVarchar.INSTANCE.toObject(ptr, getJSONPathExpr().getSortOrder());
if (jsonPathExprStr == null) {
- return true;
+ return false;
}
Object value = jsonDataFormat.getValue(top, jsonPathExprStr);
@@ -109,7 +109,7 @@ public class JsonValueFunction extends ScalarFunction {
return false;
}
} else {
- return false;
+ ptr.set(PVarchar.INSTANCE.toBytes(null));
}
return true;
diff --git a/phoenix-core/src/main/java/org/apache/phoenix/util/json/BsonJsonProvider.java b/phoenix-core/src/main/java/org/apache/phoenix/util/json/BsonJsonProvider.java
index c39e7c95fc..f7d08ebf82 100644
--- a/phoenix-core/src/main/java/org/apache/phoenix/util/json/BsonJsonProvider.java
+++ b/phoenix-core/src/main/java/org/apache/phoenix/util/json/BsonJsonProvider.java
@@ -46,14 +46,16 @@ public class BsonJsonProvider extends AbstractJsonProvider {
JsonReader jsonReader = new JsonReader(json);
BsonType bsonType = jsonReader.readBsonType();
switch (bsonType) {
- case ARRAY:
- return BsonArray.parse(json);
- case DOCUMENT:
- return BsonDocument.parse(json);
- case STRING:
- return new BsonString(jsonReader.readString());
- default:
- throw new InvalidJsonException(String.format("Unsupported bson type %s", bsonType));
+ case ARRAY:
+ return BsonArray.parse(json);
+ case DOCUMENT:
+ return BsonDocument.parse(json);
+ case STRING:
+ return new BsonString(jsonReader.readString());
+ case INT32:
+ return new BsonInt32(jsonReader.readInt32());
+ default:
+ throw new InvalidJsonException(String.format("Unsupported bson type %s", bsonType));
}
}
diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
index 6e4d17121c..905c47d086 100644
--- a/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
+++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/WhereCompilerTest.java
@@ -1043,13 +1043,13 @@ public class WhereCompilerTest extends BaseConnectionlessQueryTest {
PhoenixConnection pconn = DriverManager.getConnection(getUrl(),
PropertiesUtil.deepCopy(TEST_PROPERTIES)).unwrap(PhoenixConnection.class);
String ddl = "create table myTable(ID varchar primary key, A integer, B varchar, " +
- "C date, D double, E integer)";
+ "C date, D double, E integer, F json)";
pconn.createStatement().execute(ddl);
ddl = "create table myTableDesc(ID varchar primary key DESC, A integer, B varchar, " +
- "C date, D double, E integer)";
+ "C date, D double, E integer, F json)";
pconn.createStatement().execute(ddl);
- final int NUM = 15;
+ final int NUM = 20;
String[] containingQueries = new String[NUM];
String[] containedQueries = new String[NUM];
@@ -1106,6 +1106,22 @@ public class WhereCompilerTest extends BaseConnectionlessQueryTest {
containedQueries[14] = "select * from myTable where " +
" CURRENT_DATE() - PHOENIX_ROW_TIMESTAMP() < 5 ";
+ containingQueries[15] = "select * from myTable where ID > 'i3' and A > 1 and JSON_VALUE(F, '$.type') > 'i3'";
+ containedQueries[15] = "select * from myTableDesc where (ID > 'i7' or ID = 'i4') and " +
+ "A > 2 * 10 and (JSON_VALUE(F, '$.type') > 'i7' or JSON_VALUE(F, '$.type') = 'i4')";
+
+ containingQueries[16] = "select * from myTable where JSON_VALUE(F, '$.type') is not null";
+ containedQueries[16] = "select * from myTable where JSON_VALUE(F, '$.type') > 'i3'";
+
+ containingQueries[17] = "select * from myTable where JSON_VALUE(F, '$.type') like '%abc'";
+ containedQueries[17] = "select * from myTable where (JSON_VALUE(F, '$.type') like '%abc' and ID > 'i1')";
+
+ containingQueries[18] = "select * from myTable where JSON_EXISTS(F, '$.type')";
+ containedQueries[18] = "select * from myTable where JSON_EXISTS(F, '$.type') and JSON_VALUE(F, '$.type') > 'i3'";
+
+ containingQueries[19] = "select * from myTable where JSON_VALUE(F, '$.type') IN ('i3', 'i7', 'i1') and A < 10";
+ containedQueries[19] = "select * from myTableDesc where JSON_VALUE(F, '$.type') IN ('i1', 'i7') and A < 10 / 2";
+
for (int i = 0; i < NUM; i++) {
Assert.assertTrue(WhereCompiler.contains(getDNF(pconn, containingQueries[i]),
getDNF(pconn, containedQueries[i])));