Paul Rogers created DRILL-6382:
----------------------------------
Summary: Views should "close" over options in effect at view
creation time
Key: DRILL-6382
URL: https://issues.apache.org/jira/browse/DRILL-6382
Project: Apache Drill
Issue Type: Improvement
Affects Versions: 1.13.0
Reporter: Paul Rogers
Suppose I have the following JSON file:
{noformat}
{a: [ 123, "Fred", 123.45 ] }
{noformat}
Suppose I query the list with default options in place:
{noformat}
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.
{noformat}
Well, foo. The JSON contains a mixed scalar list. Luckily, I know about
all-text mode:
{noformat}
ALTER SESSION SET `store.json.all_text_mode` = true;
SELECT * FROM `json/scalar-list.json`;
+--------------------------+
| a |
+--------------------------+
| ["123","Fred","123.45"] |
+--------------------------+
{noformat}
No I can make a fancy query:
{noformat}
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 |
+---------+-------+----------+
{noformat}
And I can package up my query as a view:
{noformat}
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`;
{noformat}
Let's test the view:
{noformat}
SELECT * FROM myView;
+---------+-------+----------+
| custId | name | balance |
+---------+-------+----------+
| 123 | Fred | 123.45 |
+---------+-------+----------+
{noformat}
Next, let's try out the view the way that the user will: with default options:
{noformat}
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.
{noformat}
Oh, no! What happened? Let's check the view file:
{noformat}
{
"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" ]
}
{noformat}
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.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
