[DRILL-1868] Provide options to handle nonexistent columns in schema-less query

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Fix Version/s: Future
• Component/s: SQL Parser
• Labels: document_if_not_fixed
• Assignee: Unassigned
• Reporter: Victoria Markman
• Created: 15/Dec/14 18:25
• Updated: 08/Oct/15 20:18

Description

git.commit.id.abbrev=c65928f

0: jdbc:drill:schema=dfs> select * from `test.json`;
+------------+------------+
| eventdate  |    sold    |
+------------+------------+
| 2014-01-01 | 100        |
| 2014-01-01 | 100        |
| 2014-02-01 | 200        |
+------------+------------+
3 rows selected (0.099 seconds)

0: jdbc:drill:schema=dfs> -- Correct result
0: jdbc:drill:schema=dfs> SELECT
. . . . . . . . . . . . >                 extract( month from eventdate ) as `month`,
. . . . . . . . . . . . >                 extract( year  from eventdate ) as `year`
. . . . . . . . . . . . > 
. . . . . . . . . . . . > FROM            `test.json`
. . . . . . . . . . . . > WHERE           extract( month from eventdate ) IS NOT NULL;
+------------+------------+
|   month    |    year    |
+------------+------------+
| 1          | 2014       |
| 1          | 2014       |
| 2          | 2014       |
+------------+------------+
3 rows selected (0.074 seconds)

0: jdbc:drill:schema=dfs> -- Wrong result, should throw an error
0: jdbc:drill:schema=dfs> SELECT
. . . . . . . . . . . . >                 extract( month from eventdate ) as `month`,
. . . . . . . . . . . . >                 extract( year  from eventdate ) as `year`
. . . . . . . . . . . . > 
. . . . . . . . . . . . > FROM            `test.json`
. . . . . . . . . . . . > WHERE           `month` IS NOT NULL;
+------------+------------+
|   month    |    year    |
+------------+------------+
+------------+------------+
No rows selected (0.079 seconds)

0: jdbc:drill:schema=dfs> -- Wrong result, should throw an error
0: jdbc:drill:schema=dfs> SELECT
. . . . . . . . . . . . >                 extract( month from eventdate ) as xyz,
. . . . . . . . . . . . >                 extract( year  from eventdate ) as `year`
. . . . . . . . . . . . > 
. . . . . . . . . . . . > FROM            `test.json`
. . . . . . . . . . . . > WHERE           xyz IS NOT NULL;
+------------+------------+
|    xyz     |    year    |
+------------+------------+
+------------+------------+
No rows selected (0.073 seconds)

 
0: jdbc:drill:schema=dfs> -- Correct result
0: jdbc:drill:schema=dfs> SELECT *
. . . . . . . . . . . . > FROM
. . . . . . . . . . . . >         (
. . . . . . . . . . . . >         SELECT
. . . . . . . . . . . . >                 extract( month from eventdate ) as `month`,
. . . . . . . . . . . . >                 extract( year  from eventdate ) as `year`
. . . . . . . . . . . . > 
. . . . . . . . . . . . >         FROM    `test.json`
. . . . . . . . . . . . >         WHERE           `month` IS NULL
. . . . . . . . . . . . >         )
. . . . . . . . . . . . > WHERE `month` IS NOT NULL;
+------------+------------+
|   month    |    year    |
+------------+------------+
| 1          | 2014       |
| 1          | 2014       |
| 2          | 2014       |
+------------+------------+
3 rows selected (0.099 seconds)

Issue Links

• relates to: DRILL-2988 Correlated exists subquery returns wrong result if join columns in subquery are not fully qualified