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)