Thanks Kathleen. SELECT camic.provenance.image.case_id caseid from mongo.users.`contacts2` camic where camic.provenance.image.case_id > 10;
works for me as well. I have also resolved the bug report that I opened as "Invalid" Regards, Pradeeban. On Thu, Aug 18, 2016 at 5:33 PM, Kathleen Li <k...@maprtech.com> wrote: > 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 > > -- 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
