[
https://issues.apache.org/jira/browse/DRILL-3370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14602320#comment-14602320
]
Aman Sinha commented on DRILL-3370:
-----------------------------------
+1 . LGTM.
Joannlyn, just to clarify, the original query :
{code}
select flatten(arr) as a from dfs.`/data/test/jsonarray.150.json` where a > 100;
{code}
in the Description not returning results is 'expected' behavior in Drill. The
filter is referencing an alias from the SELECT list. In most systems
(including Drill) this is not allowed because filters are evaluated before the
projected expressions in the SELECT list. The fact that you don't get an error
is unfortunate and due to the schema-less nature of Drill - this ideally needs
to be fixed and I believe there is an open JIRA for it (I don't know the
number).
> FLATTEN error with a where clause
> ---------------------------------
>
> Key: DRILL-3370
> URL: https://issues.apache.org/jira/browse/DRILL-3370
> Project: Apache Drill
> Issue Type: Bug
> Components: Functions - Drill
> Affects Versions: 1.0.0
> Reporter: Joanlynn LIN
> Assignee: Aman Sinha
> Fix For: 1.1.0
>
> Attachments: DRILL-3370.patch, jsonarray.150.json
>
>
> I've got a JSON file which contains 150 JSON strings all like this:
> {"arr": [94]}
> {"arr": [39]}
> {"arr": [180]}
> I was trying to Flatten() the arrays and filter the values using such an SQL
> query:
> select flatten(arr) as a from dfs.`/data/test/jsonarray.150.json` where a
> > 100;
> However, it returned no result. Then I modified my expression like this:
> select a from (select flatten(arr) as a from
> dfs.`/data/test/jsonarray.150.json`) where a > 100;
> It then failed:
> Error: SYSTEM ERROR:
> org.apache.drill.exec.exception.SchemaChangeException: Failure while trying
> to materialize incoming schema. Errors:
> Error in expression at index -1. Error: Missing function implementation:
> [flatten(BIGINT-REPEATED)]. Full expression: --UNKNOWN EXPRESSION--..
> Fragment 0:0
> [Error Id: 1d71bf0e-48da-43f8-8b36-6a513120d7e0 on slave2:31010]
> (state=,code=0)
> After a lot of attempts, I finally got it work:
> select a from (select flatten(arr) as a from
> dfs.`/data/test/jsonarray.150.json` limit 10000000) where a > 100;
> See, I just added a "limit 10000000" in this query and I am wondering if this
> is a bug or what in Drill?
> Looking forward to your attention and help. Many thanks.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
