[
https://issues.apache.org/jira/browse/DRILL-1868?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14247611#comment-14247611
]
Jinfeng Ni commented on DRILL-1868:
-----------------------------------
The 'month' in the WHERE clause is actually not referencing the alias in the
SELECT clause. In stead, it's treated as a regular column, which is the SQL
behavior. In schema-based system like Postgre, any regular column will be
verified against the table schema. In schema-less Drill system, the
verification will skip, since Drill does not know whether the input contains
column 'month' during planning time. In execution time, if input does not have
"month" column, Drill will populate nulls. That's why you see such result.
As a reference, Postgre does allow an alias uses the same name as regular
column's name in the table. But the column reference in WHERE clause does not
mean it's using the alias.
{code}
select * from emp;
id | position_id | salary
----+-------------+--------
1 | 1 | 4
2 | 1 | 4
3 | 2 | 2
select position_id + 100 as position_id from emp where position_id = 2;
position_id
-------------
102
{code}
> 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)
