drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Victoria Markman (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-1868) Filtering on an alias should return an error, user gets wrong result instead
Date Thu, 30 Jul 2015 18:09:04 GMT

    [ 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)

Mime
View raw message