Hi Prdedeeban, Please check out the following doc: http://drill.apache.org/docs/select-statements/
Column Aliases. You cannot reference column aliases in the following clauses: * WHERE * GROUP BY * HAVING http://drill.apache.org/docs/select-statements/ The following works for me without using column alias: 0: jdbc:drill:zk=drill1:5181,drill2:5181,dril> SELECT camic.provenance.image.case_id caseid from test camic where camic.provenance.image.case_id > 10; +---------+ | caseid | +---------+ | 100.0 | +---------+ Kathleen On 8/18/16, 2:03 PM, "Pradeeban Kathiravelu" <kk.pradee...@gmail.com> wrote: >Hi, >I have this simple data in a Mongo database. >{"_id":{"$oid":"56a784b76952647b7b51c562"},"provenance":{"image":{"case_id":100,"subject_id":"TCGA"}}} > >When I run > >*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2` >camic* > >I get the below. > >+---------+ >| caseid | >+---------+ >| 100 | >+---------+ > >When I run > > > >*SELECT camic.provenance.image.case_id caseidFROM mongo.users.`contacts2` >camicWHERE caseid > 10;* >I expect the same outcome as above. > >However, I get the below (no results). > >+---------+ >| caseid | >+---------+ >+---------+ > > >*tail -f sqlline.log* indicates the below. (see the highlighted line >specifically). > >2016-08-18 16:56:07,337 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:foreman] >INFO o.a.drill.exec.work.foreman.Foreman - Query text for query id >2849df17-8fbd-eb57-03c8-cb2181bc81c7: SELECT camic.provenance.image.case_id >caseid >FROM mongo.users.`contacts2` camic >WHERE caseid > 10 >2016-08-18 16:56:08,491 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.s.m.MongoScanBatchCreator - Number of record readers >initialized : 1 >2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.w.fragment.FragmentExecutor - >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested >AWAITING_ALLOCATION --> RUNNING >2016-08-18 16:56:08,512 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.w.f.FragmentStatusReporter - >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: RUNNING >2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.s.mongo.MongoRecordReader - Filters Applied : Document{{}} >2016-08-18 16:56:08,513 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.s.mongo.MongoRecordReader - Fields Selected :Document{{_id=0, >caseid=1, provenance=1}} >*2016-08-18 16:56:08,514 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >WARN o.a.d.e.e.ExpressionTreeMaterializer - Unable to find value vector of >path `caseid`, returning null instance.* >2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.w.fragment.FragmentExecutor - >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State change requested RUNNING >--> FINISHED >2016-08-18 16:56:08,520 [2849df17-8fbd-eb57-03c8-cb2181bc81c7:frag:0:0] >INFO o.a.d.e.w.f.FragmentStatusReporter - >2849df17-8fbd-eb57-03c8-cb2181bc81c7:0:0: State to report: FINISHED > > > >Tested with 1.6.0 and 1.7.0. Reported this as DRILL-4855 ><https://issues.apache.org/jira/browse/DRILL-4855> > >Can someone confirm whether this is a bug, or is it simply I am doing >something that is not supported, or doing something wrong? > >Thank you. >Regards, >Pradeeban. > >-- >Pradeeban Kathiravelu. >PhD Researcher, Erasmus Mundus Joint Doctorate in Distributed Computing, >INESC-ID Lisboa / Instituto Superior Técnico, Universidade de Lisboa, >Portugal. >Biomedical Informatics Software Engineer, Emory University School of >Medicine. > >Blog: [Llovizna] http://kkpradeeban.blogspot.com/ >LinkedIn: www.linkedin.com/pub/kathiravelu-pradeeban/12/b6a/b03
