You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Jacques Nadeau (JIRA)" <ji...@apache.org> on 2014/12/15 19:39:14 UTC
[jira] [Commented] (DRILL-1868) Filtering on an alias should return
an error, user gets wrong result instead
[ https://issues.apache.org/jira/browse/DRILL-1868?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14246985#comment-14246985 ]
Jacques Nadeau commented on DRILL-1868:
---------------------------------------
What is happening is Drill is using dynamic schema to create a new column called month and populate it will nulls in the anticipation that it may show up as some point in the file. You can experience this by running:
{code}
SELECT month FROM `test.json`
{code}
I'm not sure what the right set of behavior should be given this possibility.
> Filtering on an alias should return an error, user gets wrong result instead
> -----------------------------------------------------------------------------
>
> Key: DRILL-1868
> URL: https://issues.apache.org/jira/browse/DRILL-1868
> Project: Apache Drill
> Issue Type: Bug
> Components: SQL Parser
> Reporter: Victoria Markman
>
> git.commit.id.abbrev=c65928f
> {code}
> 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)
> {code}
>
> {code}
> 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)
> {code}
> {code}
> 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)
> {code}
> {code}
> 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)
> {code}
> {code}
> 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)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)