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]> wrote: > > > Here it is. Please see the attached file. > >> On Mon, Jan 31, 2022 at 4:22 AM James Turton <[email protected]> wrote: >> Please also attach the query profile if you can. >> >> Thanks >> James >> >> On 2022/01/31 08:09, luoc wrote: >> > Hi Daniel, >> > What is the data type of the `_id` field? The default ObjectId, or >> > String or key-value pair (Struct)? >> > >> >> On Jan 31, 2022, at 11:12, Daniel Clark <[email protected]> wrote: >> >> >> >> >> >> Hello, >> >> >> >> I'm running this mongo query on the 1.20.0-SNAPSHOT build. It runs >> >> without error on the 1.19 release. >> >> >> >> 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 >> >> (`Elements`.`_id` = `Elements_Efforts`.`_id`) >> >> WHERE (`Elements`.`PlanID` = '1623263140') >> >> GROUP BY `Elements_Efforts`.`EffortTypeName`, >> >> `Elements`.`ElementSubTypeName`, >> >> `Elements`.`ElementTypeName`, >> >> `Elements`.`PlanID` >> >> >> >> The error message returned is: >> >> >> >> org.apache.drill.common.exceptions.UserRemoteException: SYSTEM ERROR: >> >> UnsupportedOperationException: Map, Array, Union or repeated scalar type >> >> should not be used in group by, order by or in a comparison operator. >> >> Drill does not support compare between MAP:REQUIRED and MAP:REQUIRED. >> >> >> >> Fragment: 0:0 >> >> >> >> Please, refer to logs for more information. >> >> >> >> [Error Id: 21b3260d-9ebf-4156-a5fa-4748453b5465 on localhost:31010] >> >> >> >> I've tried searching the mailing list archives, as well as googling the >> >> error. The stack trace mentions that memory was leaked by the query. Any >> >> ideas? Full stack trace attached. >> >> <stacktrace.txt> >> > > <profile.json>
