[ https://issues.apache.org/jira/browse/DRILL-4875?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15467766#comment-15467766 ]
Sudheesh Katkam commented on DRILL-4875: ---------------------------------------- I am surprised this returns any result as index notation (_t.arr\[0\].key_) is not being used; I expect the query to fail. Your use-case is solved by flatten(). > select over array of maps returns incorrect results > --------------------------------------------------- > > Key: DRILL-4875 > URL: https://issues.apache.org/jira/browse/DRILL-4875 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.9.0 > Environment: 4 node cluster CentOS > Reporter: Khurram Faraaz > Priority: Critical > > select over array of maps returns incorrect results > Drill 1.9.0 git commit ID : 28d315bbb > Data used in test > {noformat} > [test@centos-a ~]# cat arrayofmaps.json > {"arr":[{"key":"a"},{"key":"b"},{"key":"c"},{"key":"d"}]} > {noformat} > Array elements are > {noformat} > 0: jdbc:drill:schema=dfs.tmp> select t.arr from `arrayofmaps.json` t; > +----------------------------------------------------+ > | arr | > +----------------------------------------------------+ > | [{"key":"a"},{"key":"b"},{"key":"c"},{"key":"d"}] | > +----------------------------------------------------+ > 1 row selected (0.289 seconds) > {noformat} > The below query should return 4 rows, each row containing one value a,b,c and > d respectively > However query just returns only one value, which is not correct. > {noformat} > 0: jdbc:drill:schema=dfs.tmp> select t.arr.key from `arrayofmaps.json` t > where t.arr.key is not null; > +---------+ > | EXPR$0 | > +---------+ > | a | > +---------+ > 1 row selected (0.33 seconds) > {noformat} > Explain plan for above failing query > {noformat} > 0: jdbc:drill:schema=dfs.tmp> explain plan for select t.arr.key from > `arrayofmaps.json` t where t.arr.key is not null; > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(EXPR$0=[$0]) > 00-02 SelectionVectorRemover > 00-03 Filter(condition=[IS NOT NULL($0)]) > 00-04 Project(ITEM=[ITEM($0, 'key')]) > 00-05 Scan(groupscan=[EasyGroupScan > [selectionRoot=maprfs:/tmp/arrayofmaps.json, numFiles=1, > columns=[`arr`.`key`], files=[maprfs:///tmp/arrayofmaps.json]]]) > {noformat} -- This message was sent by Atlassian JIRA (v6.3.4#6332)