[DRILL-6382] Views should "close" over options in effect at view creation time

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.13.0
• Assignee: Unassigned
• Reporter: Paul Rogers
• Created: 03/May/18 04:45
• Updated: 03/May/18 04:45

Description

Suppose I have the following JSON file:

{a: [ 123, "Fred", 123.45 ] }

Suppose I query the list with default options in place:

SELECT * FROM `json/scalar-list.json`;

Error: UNSUPPORTED_OPERATION ERROR: In a list of type BIGINT, 
  encountered a value of type VARCHAR. Drill does not support lists of different types.

Well, foo. The JSON contains a mixed scalar list. Luckily, I know about all-text mode:

ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `json/scalar-list.json`;
+--------------------------+
|            a             |
+--------------------------+
| ["123","Fred","123.45"]  |
+--------------------------+

No I can make a fancy query:

SELECT CAST(a[0] AS INT) AS custId,
                 a[1] AS name,
                 CAST(a[2] AS DOUBLE) AS balance
  FROM `json/scalar-list.json`;
+---------+-------+----------+
| custId  | name  | balance  |
+---------+-------+----------+
| 123     | Fred  | 123.45   |
+---------+-------+----------+

And I can package up my query as a view:

CREATE VIEW myView AS 
  SELECT CAST(a[0] AS INT) AS custId,
                   a[1] AS name,
                   CAST(a[2] AS DOUBLE) AS balance
    FROM `json/scalar-list.json`;

Let's test the view:

SELECT * FROM myView;
+---------+-------+----------+
| custId  | name  | balance  |
+---------+-------+----------+
| 123     | Fred  | 123.45   |
+---------+-------+----------+

Next, let's try out the view the way that the user will: with default options:

ALTER SESSION RESET  `store.json.all_text_mode`;
SELECT * FROM myView;
Error: UNSUPPORTED_OPERATION ERROR: In a list of type BIGINT,
  encountered a value of type VARCHAR. Drill does not support lists of different types.

Oh, no! What happened? Let's check the view file:

{
  "name" : "myView",
  "sql" : "SELECT CAST(`a`[0] AS INTEGER) AS `custId`, `a`[1] AS `name`, CAST(`a`[2] AS DOUBLE) AS `balance`\nFROM `json/scalar-list.json`",
  "fields" : [ {
    "name" : "custId",
    "type" : "INTEGER",
    "isNullable" : true
  }, {
    "name" : "name",
    "type" : "ANY",
    "isNullable" : true
  }, {
    "name" : "balance",
    "type" : "DOUBLE",
    "isNullable" : true
  } ],
  "workspaceSchemaPath" : [ "local", "data" ]
}

We can see from the file that the view captures the schema in effect at view creation, but it does not capture options in effect when the view was made. The user must remember to set the options.

Requested feature: capture the options in a new JSON tag in the view file. Pass those options along to operators created for this view. If any of the inputs to the view are views, then the inner view options override the outer view options.