[
https://issues.apache.org/jira/browse/DRILL-1868?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14648044#comment-14648044
]
Victoria Markman commented on DRILL-1868:
-----------------------------------------
It looks like it's we need to address this issue sooner than later. Recent
customer experience shows that this behavior is confusing.
Potential solutions could be:
* throw a warning if column was not found in the table
* have a mode, where we throw an error if column was not found
Documentation might help, but it is not clear where would I go looking for a
solution ? Alias section states that aliases are not allowed in WHERE, HAVING
and GROUP BY.
But then I would expect to get an error ...
> 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: Improvement
> Components: SQL Parser
> Reporter: Victoria Markman
> Assignee: Jinfeng Ni
> Labels: document_if_not_fixed
>
> 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)
