[DRILL-4578] "children" missing from results of full scan over JSON data

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 1.10.0
• Component/s: Storage - JSON

Environment

4 node cluster CentOS

• Assignee: Serhii Harnyk
• Reporter: Khurram Faraaz
• Created: 05/Apr/16 05:46
• Updated: 24/Mar/17 12:52
• Resolved: 31/Jan/17 09:36

Description

One of the fields named "children" is missing from the output of SELECT * over the JSON data, with or without enabling all_text_mode for JSON data.
Projecting just the "children" field returns a null.
Note that children field holds an empty array.

Drill 1.7.0-SNAPSHOT commit ID e7e9b73c

0: jdbc:drill:schema=dfs.tmp> alter session set `store.json.all_text_mode`=true;
+-------+------------------------------------+
|  ok   |              summary               |
+-------+------------------------------------+
| true  | store.json.all_text_mode updated.  |
+-------+------------------------------------+
1 row selected (0.118 seconds)
0: jdbc:drill:schema=dfs.tmp> select * from `employee.json`;
+------------+-----------+----------+------+------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------+---------------------------------------------------------------+
| firstName  | lastName  | isAlive  | age  | height_cm  |                                           address                                           |                                     phoneNumbers                                     |                            hobbies                            |
+------------+-----------+----------+------+------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------+---------------------------------------------------------------+
| John       | Smith     | true     | 45   | 177.6      | {"streetAddress":"29 4th Street","city":"New York","state":"NY","postalCode":"10021-3100"}  | [{"type":"home","number":"212 555-1234"},{"type":"office","number":"646 555-4567"}]  | ["scuba diving","hiking","biking","rock climbing","surfing"]  |
+------------+-----------+----------+------+------------+---------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------+---------------------------------------------------------------+
1 row selected (0.214 seconds)
0: jdbc:drill:schema=dfs.tmp> select t.children from `employee.json` t;
+-----------+
| children  |
+-----------+
| null      |
+-----------+
1 row selected (0.148 seconds)

JSON data used in test

[root@centos-01 ~]# cat employee.json
{
  "firstName": "John",
  "lastName": "Smith",
  "isAlive": true,
  "age": 45,
  "height_cm": 177.6,
  "address": {
    "streetAddress": "29 4th Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    }
  ],
  "children": [],
  "hobbies": ["scuba diving","hiking","biking","rock climbing","surfing"]
}

Issue Links

• duplicates: DRILL-3562 Query fails when using flatten on JSON data where some documents have an empty array