What is the data type of PlanId in Mongo? Is it an integer? If so, what happens if you remove the quotes from that last query?
On 6 February 2022 03:43:20 GMT+02:00, Daniel Clark <[email protected]> wrote: >Hi James, > >Yes, in Drill 1.19 the original query returns 82 records, where the PlanId >= '1623263140' . > >On Sat, Feb 5, 2022 at 2:13 AM James Turton <[email protected]> wrote: > >> Let's get this subset of the problem figured out first. Is there an >> "element" with that PlanId in Mongo? >> >> P.S. screenshots don't make it to the mailing list. >> >> On 2022/02/04 17:12, Daniel Clark wrote: >> > After running this query >> > >> > select >> > * >> > from >> > `mongo.grounds`.`Elements` element >> > where element.PlanId = '1623263140'; >> > >> > Nothing is returned. Please see the attached screenshot. >> > >> > Screen Shot 2022-02-04 at 10.08.07 AM.png >> > >> > On Fri, Feb 4, 2022 at 10:03 AM James Turton <[email protected] >> > <mailto:[email protected]>> wrote: >> > >> > Do you get records from this query? >> > >> > select >> > * >> > from >> > `mongo.grounds`.`Elements` element >> > where element.PlanId = '1623263140'; >> > >> > Incidentally, for the full query including the WHERE clause, the form >> > below is probably going to be a good deal faster than the ones we've >> > written so far. I reintroduced some backticks, just in case that was >> > causing any trouble. >> > >> > with element as ( >> > select >> > `_id`, >> > ElementTypeName, >> > ElementSubTypeName, >> > PlanId >> > FROM >> > `mongo.grounds`.`Elements` >> > where PlanId = '1623263140' >> > ), element_effort as ( >> > select >> > `_id`, >> > EffortTypeName >> > FROM >> > `mongo.grounds`.`Elements_Efforts` >> > ) >> > select >> > * >> > from >> > element >> > join >> > element_effort on element.`_id` = element_effort.`_id` >> > >> > On 2022/02/03 17:05, Daniel Clark wrote: >> > > I tried again with this query: >> > > >> > > with element as ( >> > > select >> > > _id, >> > > ElementTypeName, >> > > ElementSubTypeName, >> > > PlanId >> > > FROM >> > > `mongo.grounds`.`Elements` >> > > ), element_effort as ( >> > > select >> > > _id, >> > > EffortTypeName >> > > FROM >> > > `mongo.grounds`.`Elements_Efforts` >> > > ) >> > > select >> > > * >> > > from >> > > element >> > > join >> > > element_effort on element._id = element_effort._id >> > > where element.PlanId = '1623263140' >> > > >> > > The query completed successfully, but it did not return any rows. >> > I've >> > > attached the log and the profile. >> > >> -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
