Hi James, The amended query runs, however, over 2x slower than Drill 1.19. Thanks.
On Tue, Feb 1, 2022 at 4:24 AM James Turton <[email protected]> wrote: > If you feel like playing around you can try out this variation. > > SELECT `Elements_Efforts`.`EffortTypeName` AS `EffortTypeName`, > `Elements`.`ElementSubTypeName` AS `ElementSubTypeName`, > `Elements`.`ElementTypeName` AS `ElementTypeName`, > `Elements`.`PlanID` AS `PlanID` > FROM `mongo.grounds`.`Elements` `Elements` > INNER JOIN `mongo.grounds`.`Elements_Efforts` `Elements_Efforts` ON > convert_to(`Elements`.`_id`, > 'JSON') = convert_to(`Elements_Efforts`.`_id`, 'JSON') > WHERE (`Elements`.`PlanID` = '1623263140') > GROUP BY `Elements_Efforts`.`EffortTypeName`, > `Elements`.`ElementSubTypeName`, > `Elements`.`ElementTypeName`, > `Elements`.`PlanID` > > > On 2022/02/01 11:08, James Turton wrote: > > I think luoc is exactly right in his diagnosis. So Daniel, does this > query run fine on Drill 1.19 with store.mongo.all_text_mode = true? > > Thanks > James > > On 2022/02/01 05:39, luoc wrote: > > Hi Daniel, > I should guess the reason for this case, but I need to verify it first. > Basically, under the full text, the ObjectId exists as a key-value pair > (Map structure), not as a character or number value. > > On Jan 31, 2022, at 23:45, Daniel Clark<[email protected]> > <[email protected]> wrote: > > > Here it is. Please see the attached file. > > On Mon, Jan 31, 2022 at 4:22 AM James Turton<[email protected]> > <[email protected]> wrote: > Please also attach the query profile if you can. > > Thanks > James > > > >
