PHOENIX-7155 Validate Partial Index support with JSON (#1767)
diff --git a/phoenix-core/pom.xml b/phoenix-core/pom.xml
index e77cbce..8a8eab8 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 0f16113..a187afa 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,10 +17,29 @@
*/
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 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.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;
+import org.junit.Test;
+import org.junit.experimental.categories.Category;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
import java.sql.Connection;
import java.sql.Date;
@@ -35,25 +54,9 @@
import java.util.Map;
import java.util.Properties;
-import org.apache.hadoop.mapreduce.CounterGroup;
-import org.apache.phoenix.end2end.IndexToolIT;
-import org.apache.phoenix.exception.PhoenixParserException;
-import org.apache.phoenix.jdbc.PhoenixResultSet;
-import org.apache.phoenix.mapreduce.index.IndexTool;
-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.junit.After;
-import org.junit.Assert;
-import org.junit.BeforeClass;
-import org.junit.Test;
-import org.junit.experimental.categories.Category;
-import org.junit.runner.RunWith;
-import org.junit.runners.Parameterized;
+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)
@@ -182,7 +185,7 @@
+ "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 @@
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 7dd083e..957d619 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 @@
@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 @@
}
}
- @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 @@
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 27df7d4..0000000
--- 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 enim occaecat ullamco.\\r\\n",
- "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 duis proident minim magna sit voluptate exercitation est.\\r\\n",
- "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 4bd3a4b..bc75d1e 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.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 @@
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 997942c..7a19181 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 @@
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -76,14 +76,14 @@
}
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 a968d0b..fcce9e1 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 @@
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -81,14 +81,14 @@
}
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 9863439..a2a3d00 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 @@
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -80,14 +80,14 @@
}
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 af20ae4..a42b0cf 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 @@
return false;
}
if (ptr == null || ptr.getLength() == 0) {
- return true;
+ return false;
}
// Column name or JSON string
@@ -82,14 +82,14 @@
}
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 @@
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 c39e7c9..f7d08eb 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 @@
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 6e4d171..905c47d 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 @@
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 @@
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])));
